javasqlderby

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.


Solution

  • 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.