When inserting from Java to MySQL, get Auto_Increment_ID (automatic numbering value) as the return value

Introduction

Shows how to get the Auto_Increment value when inserting a record from Java code into the DB using JDBC.

You can get the last numbered value by SELECT LAST_INSERT_ID (), but you can use it when you want to get it as a return value.

usage environment

• MySQL 8.0
• Java 8

Prerequisites

First, create the following database / table. --Database name: testdb --Table name: test

Here, prepare the id column and the Char type name column` which are the primary keys with ʻAuto_Increment set in the test table.

The SQL to create the table is as follows.

CREATE TABLE test(
  id INT NOT NULL AUTO_INCREMENT,
  name CHAR(30) NOT NULL,
  PRIMARY KEY (id)
);

If you check the column information of the table (SHOW COLUMNS FROM table name), you can see that Auto_Increment is set in the id column of the primary key.

How to get Auto_Increment_ID

First, the Java source is shown.

Test.java

public class Test {

	public static void main(String[] args) {
		 int autoIncrementKey = 0;

	     try {
	     //Driver load
	     Class.forName("com.mysql.jdbc.Driver");

	     //Connect to MySQL
	     Connection con = DriverManager.getConnection("jdbc:mysql://localhost/testdb?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC", "user", "password");
         
	     String sql = "insert into test(name) values (?)";
	     
         //Statement generation
         //Here RETURN_GENERATED_Set KEYS
	     PreparedStatement stmt = con.prepareStatement(sql, java.sql.Statement.RETURN_GENERATED_KEYS);

	     stmt.setString(1, "XXX");
	     stmt.executeUpdate();

         // getGeneratedKeys()By Auto_Get the Incremented ID
	     ResultSet res = stmt.getGeneratedKeys();

	     if(res.next()){
	    	 autoIncrementKey = res.getInt(1);
	     }

	     System.out.println(autoIncrementKey);

	     res.close();
	     stmt.close();
	     con.close();

		} catch (Exception e){
			System.out.println(e);
		}
	}

}

When generating a PreparedStatement, set java.sql.Statement.RETURN_GENERATED_KEYSin thesecond argument.

Then, after executing the Insert statement with stmt.exceuteUpdate (), by executing stmt.getGeneratedKeys (), the automatically numbered id can be obtained.

in conclusion

There are few opportunities to use raw Java and SQL, but as a reminder.