Inserting Data Into Table Using JDBC PreparedStatement?

In this tutorial, you will learn how to use PreparedStatement object to insert data into MySQL table.

In the previous tutorial, we have shown you how to use the PreparedStatement object to update data. When you call the executeUpdate() method, you get the number of rows affected.  When you insert a record into a table, you may want to get the inserted ID back to the program for further processing. Let’s see how we can do it.

First, as always, you open a new connection to MySQL. You can utilized the utility class MySQLJDBCUtilthat we developed in the previous tutorial.

1	Connection conn = MySQLJDBCUtil.getConnection();

Then, you construct an INSERT statement with placeholders and create a new PreparedStatementobject by calling the prepareStatement() method of the Connection object. You pass the INSERT statement as the first argument and an integer with value  Statement.RETURN_GENERATED_KEYS as the the second argument to the method. The second argument instructs JDBC to give the inserted ID back.

1 2 3 4 5

String sql = "INSERT INTO candidates(first_name,last_name,dob,phone,email) "             + "VALUES(?,?,?,?,?)";   PreparedStatement pstmt = conn.prepareStatement(sql,                               Statement.RETURN_GENERATED_KEYS);

Next, you supply values for placeholders by calling setYYY() method of the PreparedStatement object.

1 2 3 4 5 6

// set parameters for statement pstmt.setString(1, firstName); pstmt.setString(2, lastName); pstmt.setDate(3, dob); pstmt.setString(4, phone); pstmt.setString(5, email);

After that, you call the executeUpdate() method to execute the INSERT statement. This method returns the number of rows affected. We check the return value to see if the record has been inserted successfully.

1 2 3 4 5

int rowAffected = pstmt.executeUpdate(); if(rowAffected == 1) {    // process further here }

Finally, to get the inserted id, you call the getGeneratedKeys() method of the PreparedStatementobject. The method returns a ResultSet . You just need to get data out of this ResultSet as follows:

1 2 3 4 5

// get candidate id int candidateId = 0; ResultSet rs = pstmt.getGeneratedKeys(); if(rs.next())    candidateId = rs.getInt(1);

The following is the complete example of inserting data into the candidates table and get the inserted ID back.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75

package org.mysqltutorial;   import java.sql.Connection; import java.sql.Date; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement;   /** * * @author mysqltutorial.org */ public class Main {       /**      * Insert a new candidate      * @param firstName      * @param lastName      * @param dob      * @param email      * @param phone      * @return      */     public static int insertCandidate(String firstName,String lastName,Date dob,                                        String email, String phone) {         // for insert a new candidate         ResultSet rs = null;         int candidateId = 0;                  String sql = "INSERT INTO candidates(first_name,last_name,dob,phone,email) "                    + "VALUES(?,?,?,?,?)";                  try (Connection conn = MySQLJDBCUtil.getConnection();              PreparedStatement pstmt = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);) {                          // set parameters for statement             pstmt.setString(1, firstName);             pstmt.setString(2, lastName);             pstmt.setDate(3, dob);             pstmt.setString(4, phone);             pstmt.setString(5, email);               int rowAffected = pstmt.executeUpdate();             if(rowAffected == 1)             {                 // get candidate id                 rs = pstmt.getGeneratedKeys();                 if(rs.next())                     candidateId = rs.getInt(1);               }         } catch (SQLException ex) {             System.out.println(ex.getMessage());         } finally {             try {                 if(rs != null)  rs.close();             } catch (SQLException e) {                 System.out.println(e.getMessage());             }         }                  return candidateId;     }     /**      * @param args the command line arguments      */     public static void main(String[] args) {          // insert a new candidate          int id = insertCandidate("Bush", "Lily", Date.valueOf("1980-01-04"),                         "bush.l@yahoo.com", "(408) 898-6666");                   System.out.println(String.format("A new candidate with id %d has been inserted.",id));     } }

Let’s run the program.

It shows that you have successfully inserted a new candidate into the candidates table with id 134.

In this tutorial, we have shown you how to use PreparedStatement object to insert a new record into a MySQL table and get the inserted ID back for further processing.