Blog Archive

Java prepared Statement get auto increment value after insert in MySQL

How to get auto generated keys from MySQL database?

 PreparedStatement stmt = conn.prepareStatement(sql, 
   PreparedStatement.RETURN_GENERATED_KEYS);
 //...

 ResultSet res = stmt.getGeneratedKeys();
 while (res.next())
 System.out.println("Generated key: " + res.getInt(1));

Sample Java source code to retrieve auto increment key values in MYSQL using JDBC prepared statement

    //insert new customer information and retrieve the generated customer id
    public String insertCustomer(String storeId, Customer customer) { 

        String customerId = "";
        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String todaysDate = dateFormat.format(System.currentTimeMillis());
       
        try {      
            Context ctx = (Context) new InitialContext().lookup("java:comp/env");
            conn = ((DataSource) ctx.lookup("jdbc/mysql")).getConnection(); 

            sql = "INSERT into customer " +
            "(customer_id,first_name,last_name,email,active,store_id,address_id,create_date,last_update)" +
            " values(null,?,?,?,?,?,?,?,?)";
            stmt = conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
            stmt.setString(1,customer.getFirstName()); 
            stmt.setString(2,customer.getLastName());
            stmt.setString(3,customer.getEmail());
            stmt.setInt(4,customer.isActive()? 1: 0);
            stmt.setInt(5,Integer.parseInt(storeId)); 
            stmt.setInt(6,5);
            stmt.setString(7, todaysDate);
            stmt.setString(8, todaysDate);

            stmt.executeUpdate();

            // Using the getGeneratedKeys() method to retrieve
            // the key(s). In this case there is only one key column
            ResultSet keyResultSet = stmt.getGeneratedKeys();
            int newCustomerId = 0;
            if (keyResultSet.next()) {
                newCustomerId = (int) keyResultSet.getInt(1);
                customerId = String.valueOf(newCustomerId);
            }

            stmt.close();                                                             
            stmt = null;                                                              
            conn.close();                                                             
            conn = null;                                                   

        }                                                               
        catch(Exception e){

            System.out.println(e);

        }                      

        finally {                                                       
            
            if (stmt != null) {                                            
                try {                                                         
                    stmt.close();                                                
                } catch (SQLException sqlex) {                                
                    // ignore -- as we can't do anything about it here           
                }                                                             

                stmt = null;                                            
            }                                                        

            if (conn != null) {                                      
                try {                                                   
                    conn.close();                                          
                } catch (SQLException sqlex) {                          
                    // ignore -- as we can't do anything about it here     
                }                                                       

                conn = null;                                            
            }                                                        
        }              

        return customerId;

    }