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`) ) ; |
DBUtil.java class which is responsible to connect with MySQL database.
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
|
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
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
|
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); } |
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
|
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… Read More »