How to get primary key value (auto-generated keys) from inserted queries using JDBC?

In this post we will learn How to get primary key value (auto-generated keys) from inserted queries using JDBC?

Project structure in eclipse:

SQL Query to create employee_table in MySQL (jdbcdb schema)

CREATE TABLE `employee_table` (
  `employee_id` int(11) NOT NULL auto_increment,
  `employee_name` varchar(60) NOT NULL,
  `email` varchar(45) NOT NULL,
  `salary` double default NULL,
  `date_of_joining` datetime default NULL,
  `bonus` decimal(10,0) default NULL,
  PRIMARY KEY  (`employee_id`)
) ;

CREATE TABLE `employee_table` (

`employee_id` int(11) NOT NULL auto_increment,

`employee_name` varchar(60) NOT NULL,

`email` varchar(45) NOT NULL,

`salary` double default NULL,

`date_of_joining` datetime default NULL,

`bonus` decimal(10,0) default NULL,

PRIMARY KEY (`employee_id`)

) ;

DBUtil.java class which is responsible to connect with MySQL database.

package com.infotech.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/*
 * @author KK JavaTutorials
 * Utility class which is responsible to connect with MySQL database.
 */
public class DBUtil {
	private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";
	private static final String DB_USERNAME="root";
	private static final String DB_PASSWORD="root";
	private static final String DB_URL ="jdbc:mysql://localhost:3306/jdbcdb";
	
	private static Connection connection = null;
	static{
		try {
			Class.forName(DB_DRIVER_CLASS);
			connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
	}
	
	//Utility method returns connection object
	public static Connection getConnection(){
		return connection;
	}
}

package com.infotech.util;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

* @author KK JavaTutorials

* Utility class which is responsible to connect with MySQL database.

public class DBUtil {

private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";

private static final String DB_USERNAME="root";

private static final String DB_PASSWORD="root";

private static final String DB_URL ="jdbc:mysql://localhost:3306/jdbcdb";

private static Connection connection = null;

static{

try {

Class.forName(DB_DRIVER_CLASS);

connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);

} catch (ClassNotFoundException | SQLException e) {

e.printStackTrace();

}

//Utility method returns connection object

public static Connection getConnection(){

return connection;

}

Model class Employee.java

package com.infotech.model;

import java.math.BigDecimal;
import java.util.Date;

public class Employee {

	private int employee_id;
	private String employeeName;
	private String email;
	private Double salary;
	private Date doj;
	private BigDecimal bonus;
	
	public int getEmployee_id() {
		return employee_id;
	}
	public void setEmployee_id(int employee_id) {
		this.employee_id = employee_id;
	}
	public String getEmployeeName() {
		return employeeName;
	}
	public void setEmployeeName(String employeeName) {
		this.employeeName = employeeName;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public Double getSalary() {
		return salary;
	}
	public void setSalary(Double salary) {
		this.salary = salary;
	}
	public Date getDoj() {
		return doj;
	}
	public void setDoj(Date doj) {
		this.doj = doj;
	}
	public BigDecimal getBonus() {
		return bonus;
	}
	public void setBonus(BigDecimal bonus) {
		this.bonus = bonus;
	}
	@Override
	public String toString() {
		return "Employee [employee_id=" + employee_id + ", employeeName=" + employeeName + ", email=" + email
				+ ", salary=" + salary + ", doj=" + doj + ", bonus=" + bonus + "]";
	}
}

package com.infotech.model;

import java.math.BigDecimal;

import java.util.Date;

public class Employee {

private int employee_id;

private String employeeName;

private String email;

private Double salary;

private Date doj;

private BigDecimal bonus;

public int getEmployee_id() {

return employee_id;

}

public void setEmployee_id(int employee_id) {

this.employee_id = employee_id;

}

public String getEmployeeName() {

return employeeName;

}

public void setEmployeeName(String employeeName) {

this.employeeName = employeeName;

}

public String getEmail() {

return email;

}

public void setEmail(String email) {

this.email = email;

}

public Double getSalary() {

return salary;

}

public void setSalary(Double salary) {

this.salary = salary;

}

public Date getDoj() {

return doj;

}

public void setDoj(Date doj) {

this.doj = doj;

}

public BigDecimal getBonus() {

return bonus;

}

public void setBonus(BigDecimal bonus) {

this.bonus = bonus;

}

@Override

public String toString() {

return "Employee [employee_id=" + employee_id + ", employeeName=" + employeeName + ", email=" + email

+ ", salary=" + salary + ", doj=" + doj + ", bonus=" + bonus + "]";

}

DAO Interface and It’s implementation

package com.infotech.dao;
import java.util.List;

import com.infotech.model.Employee;

public interface EmployeeDAO {
	public abstract void createEmployees(List<Employee> employeeList);
}

package com.infotech.dao;

import java.util.List;

import com.infotech.model.Employee;

public interface EmployeeDAO {

public abstract void createEmployees(List<Employee> employeeList);

}

package com.infotech.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

import com.infotech.dao.EmployeeDAO;
import com.infotech.model.Employee;
import com.infotech.util.DBUtil;
import com.mysql.jdbc.Statement;

public class EmployeeDAOImpl implements EmployeeDAO {

	@Override
	public void createEmployees(List<Employee> employeeList) {
		
		String SQL = "INSERT INTO employee_table(employee_name,email,salary,date_of_joining,bonus)VALUES(?,?,?,?,?)";
		try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL,Statement.RETURN_GENERATED_KEYS)) {
			for (Employee employee : employeeList) {
				ps.setString(1, employee.getEmployeeName());
				ps.setString(2, employee.getEmail());
				ps.setDouble(3, employee.getSalary());
				ps.setDate(4, new Date(employee.getDoj().getTime()));
				ps.setBigDecimal(5, employee.getBonus());
				
				ps.addBatch();
			}
			ps.executeBatch();
			
			ResultSet rs = ps.getGeneratedKeys();
			while (rs.next()) {
				System.out.println("Generated Employee ID:"+rs.getInt(1));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

package com.infotech.dao.impl;

import java.sql.Connection;

import java.sql.Date;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.util.List;

import com.infotech.dao.EmployeeDAO;

import com.infotech.model.Employee;

import com.infotech.util.DBUtil;

import com.mysql.jdbc.Statement;

public class EmployeeDAOImpl implements EmployeeDAO {

@Override

public void createEmployees(List<Employee> employeeList) {

String SQL = "INSERT INTO employee_table(employee_name,email,salary,date_of_joining,bonus)VALUES(?,?,?,?,?)";

try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL,Statement.RETURN_GENERATED_KEYS)) {

for (Employee employee : employeeList) {

ps.setString(1, employee.getEmployeeName());

ps.setString(2, employee.getEmail());

ps.setDouble(3, employee.getSalary());

ps.setDate(4, new Date(employee.getDoj().getTime()));

ps.setBigDecimal(5, employee.getBonus());

ps.addBatch();

}

ps.executeBatch();

ResultSet rs = ps.getGeneratedKeys();

while (rs.next()) {

System.out.println("Generated Employee ID:"+rs.getInt(1));

}

} catch (Exception e) {

e.printStackTrace();

}

Now let’s run our ClientTest.java class to get primary key value s(auto-generated keys) from inserted queries.

package com.infotech.client;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.infotech.dao.EmployeeDAO;
import com.infotech.dao.impl.EmployeeDAOImpl;
import com.infotech.model.Employee;
/**
 * @author KK JavaTutorials
 *JDBC client program to show How to get primary key value (auto-generated keys) from inserted
 * queries using JDBC
 */
public class ClientTest {

	public static void main(String[] args) {
		List<Employee> employeeList = getEmployeesList();
		EmployeeDAO employeeDAO = new EmployeeDAOImpl();
		employeeDAO.createEmployees(employeeList);
	}

	private static List<Employee> getEmployeesList() {

		Employee employee1 = new Employee();
		employee1.setBonus(new BigDecimal(290));
		employee1.setDoj(new Date());
		employee1.setEmployeeName("John");
		employee1.setEmail("john.cs2016@yahoo.com");
		employee1.setSalary(90000.00);
		
		Employee employee2 = new Employee();
		employee2.setBonus(new BigDecimal(290));
		employee2.setDoj(new Date());
		employee2.setEmployeeName("Sean");
		employee2.setEmail("sean.cs2006@yahoo.com");
		employee2.setSalary(40000.00);
		
		Employee employee3 = new Employee();
		employee3.setBonus(new BigDecimal(290));
		employee3.setDoj(new Date());
		employee3.setEmployeeName("Robin");
		employee3.setEmail("robin.s2017@yahoo.com");
		employee3.setSalary(95000.00);
		
		List<Employee> empList = new ArrayList<>();	
		empList.add(employee1);
		empList.add(employee2);
		empList.add(employee3);
		return empList;
	}
}

package com.infotech.client;

import java.math.BigDecimal;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

import com.infotech.dao.EmployeeDAO;

import com.infotech.dao.impl.EmployeeDAOImpl;

import com.infotech.model.Employee;

/**

* @author KK JavaTutorials

*JDBC client program to show How to get primary key value (auto-generated keys) from inserted

* queries using JDBC

public class ClientTest {

public static void main(String[] args) {

List<Employee> employeeList = getEmployeesList();

EmployeeDAO employeeDAO = new EmployeeDAOImpl();

employeeDAO.createEmployees(employeeList);

}

private static List<Employee> getEmployeesList() {

Employee employee1 = new Employee();

employee1.setBonus(new BigDecimal(290));

employee1.setDoj(new Date());

employee1.setEmployeeName("John");

employee1.setEmail("[email protected]");

employee1.setSalary(90000.00);

Employee employee2 = new Employee();

employee2.setBonus(new BigDecimal(290));

employee2.setDoj(new Date());

employee2.setEmployeeName("Sean");

employee2.setEmail("[email protected]");

employee2.setSalary(40000.00);

Employee employee3 = new Employee();

employee3.setBonus(new BigDecimal(290));

employee3.setDoj(new Date());

employee3.setEmployeeName("Robin");

employee3.setEmail("[email protected]");

employee3.setSalary(95000.00);

List<Employee> empList = new ArrayList<>();

empList.add(employee1);

empList.add(employee2);

empList.add(employee3);

return empList;

}

After running ClientTest.java program you will look below output on eclipse console:

Generated Employee ID:1
Generated Employee ID:2
Generated Employee ID:3

Generated Employee ID:1

Generated Employee ID:2

Generated Employee ID:3

if you query database employee_table you will find three rows are inserted with primary keys 1,2 and 3 as below.

That’s all about this topic How to get primary key value (auto-generated keys) from inserted queries using JDBC?

You May Also Like:

How to store file in MySQL database using JDBC
How to read files/CLOB data from MySQL database using JDBC
Writing and Reading MySQL BLOB/binary files Using JDBC
ResultSetMetaData in JDBC example

If you have any feedback or suggestion please feel free to drop in blow comment box.

Leave a Reply Cancel reply