How to get mysql auto increment key value using java jdbc

How to get mysql auto increment key value using java jdbc

By Yashwant Chavan, Views 30960, Date 02-Jun-2012

JDBC 3.0 introduced to get auto generated keys using getGeneratedKeys() method, It return the ResultSet object with the help of next() method of result set we retrieve the auto generated key value.

tags java

Mysql Auto generated Key Value

Sometimes we need to retrive auto generated key for the inserted statement. Refer the below example to get the Auto generated emp_id column value using JDBC.Get the Statement from Jdbc Connection. Statement object use to sending SQL statements to the database.

Statement statement = connection.createStatement();

Executes the below program, Which insert the record in the trn_employee table. After record insertion it return the auto generated primary key value

statement.executeUpdate("INSERT INTO trn_employee (EMP_NAME, EMP_SALARY) VALUES('Manish',19000)");

getGeneratedKeys() method returns the ResultSet object and from that we retrive the primary key number.

package com.net.example;

import java.sql.*;

public class JdbcAutoGeneratedKeyExample {
	
	public static void main(String[] args) {
		
		JdbcAutoGeneratedKeyExample jdbcMysqlSelectExample = new JdbcAutoGeneratedKeyExample();
		Connection connection = jdbcMysqlSelectExample.getConnection();
		Statement statement = null;
		ResultSet rs = null;
		try {
			statement = connection.createStatement();
			statement.executeUpdate("INSERT INTO trn_employee (EMP_NAME, EMP_SALARY) VALUES('Manish',19000)");
			rs = statement.getGeneratedKeys();
			if (rs.next()) {
				System.out.println("Auto Generated Primary Key " + rs.getInt(1));
			}
		} catch (SQLException e) {
			System.out.println("SQLException Occured..");
		} finally {
			try {
				if (rs != null) {
					rs.close(); // close result set
				}
				if (statement != null) {
					statement.close(); // close statement
				}
				if (connection != null) {
					connection.close(); // close connection
				}
			} catch (SQLException e) {
				System.out.println("SQLException Occured..");
			}
		}
	}

	/***
	 * Get Connection
	 * 
	 * @return
	 */
	private Connection getConnection() {
		Connection connection = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/technicalkeeda", "root", "");
		} catch (ClassNotFoundException e) {
			System.out.println("ClassNotFoundException Occured...");
		} catch (SQLException e) {
			System.out.println("SQLException Occured...");
		}
		return connection;
	}
}

Note : On Every execution of program , it generates next auto incremented number.

Output

Auto Generated Primary Key 13
Auto Generated Primary Key 14
Auto Generated Primary Key 15
Auto Generated Primary Key 16
...
...
...
Yashwant

Yashwant

Hi there! I am founder of technicalkeeda.com and programming enthusiast. My skills includes Java,J2EE, Spring Framework, Nodejs, PHP and lot more. If you have any idea that you would want me to develop? Lets connect: yashwantchavan[at][gmail.com]