Will Will - 1 month ago 7
Java Question

Update SQL database with preparedStatement in Java

I have a java app with an SQL database using preparedStatement to insert rows into the database. I want the program to be able to update rows based on the serial number (unique).

Connection conn = null;
Statement st = null;
try {
conn = DriverManager.getConnection ("jdbc:derby://localhost:1527/db01", "Administrator", "admin"); //run procedure getConnection to connect to the database - see below
st = conn.createStatement(); //set up a statement st to enable you to send SQL statements to the database.
} catch (SQLException ex) {
Logger.getLogger(FormTwo1.class.getName()).log(Level.SEVERE, null, ex);
}


System.out.println ("Successful Connection");


...

String query = "insert into TB01(SERIAL,BLADETYPE,STARTT1,AIRT1,FOAMT1,SCT1,FINISHT1) values (?, ?, ?, ?, ?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(query)) {
pstmt.setString(1, bladeSerial);
pstmt.setString(2, itemText);
pstmt.setString(3, String.valueOf(startTime1));
pstmt.setString(4, String.valueOf(airTime1));
pstmt.setString(5, String.valueOf(foamTime1));
pstmt.setString(6, String.valueOf(scTime1));
pstmt.setString(7, String.valueOf(finishTime1));
pstmt.executeUpdate();
} catch (SQLException ex) {
// Exception handling
Logger.getLogger(FormTwo1.class.getName()).log(Level.SEVERE, null, ex);
}


Where serial, bladetype are VARCHAR and startT1, foamTime1, scTime1 & finishTime1 are all LocalTime variables (hence the string.valueof for formatting).

The database is db01, the table is TB01

I want the program to insert/update the records dependent on whether the serial number is already in the db.

Answer

The code is now working. Thanks for Prashant for his answer. Once slightly adapted it worked well

String query = ("UPDATE TB01 SET BLADETYPE=?,STARTT1=?,AIRT1=?,FOAMT1=?,SCT1=?,FINISHT1=? WHERE SERIAL=?");
try (PreparedStatement pstmt = conn.prepareStatement(query)) {
    pstmt.setString(7, bladeSerial);
    pstmt.setString(1, itemText);
    pstmt.setString(2, String.valueOf(startTime1));
    pstmt.setString(3, String.valueOf(airTime1));
    pstmt.setString(4, String.valueOf(foamTime1));
    pstmt.setString(5, String.valueOf(scTime1));
    pstmt.setString(6, String.valueOf(finishTime1));
    pstmt.executeUpdate();
                                                             }
catch (SQLException ex) {
    // Exception handling
    Logger.getLogger(FormTwo1.class.getName()).log(Level.SEVERE, null, ex);
                          }

Note as SERIAL had moved to the end of the string, the order also needed changing on the setString commands.

Comments