Insert a Row using an updatable ResultSet

In previous post How to update a Row in a Database Table Using an updatable ResultSet we learnt how to update a record in database table using a updatable ResultSet . In this post we will learn how to Insert a Row using an updatable ResultSet in database table.

Project structure in eclipse:

SQL Query to create employee_table and insert few rows in MySQL (jdbcdb schema)
(Note:Make sure you have created employee_table and inserted few records in jdbcdb database schema)
DB.sql

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`)
) ;

INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Martin','martin.cs2017@yahoo.com',71000,'2017-05-17 00:00:00',400);
INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Paul','paul.cs2017@gmail.com',91000,'2017-05-18 00:00:00',800);
INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Sam','sam.cs2014@gmail.com',76000,'2017-05-16 00:00:00',300);
INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Paul','paul.cs2009@yahoo.com',60000,'2017-05-17 00:00:00',400);

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`)

) ;

INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Martin','[email protected]',71000,'2017-05-17 00:00:00',400);

INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Paul','[email protected]',91000,'2017-05-18 00:00:00',800);

INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Sam','[email protected]',76000,'2017-05-16 00:00:00',300);

INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Paul','[email protected]',60000,'2017-05-17 00:00:00',400);

After running above db script in your MySQL jdbcdb schema employee_table will have following content.

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;

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();
		}
	}
	
	public static Connection getConnection(){
		return connection;
	}
}

package com.infotech.util;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

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();

}

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 + "]";

}

Now let’s run our ClientTest.java class to insert a row in employee_table
You can modified this program as per your requirement.

package com.infotech.client;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import com.infotech.model.Employee;
import com.infotech.util.DBUtil;
/**
 * @author KK JavaTutorials
 *JDBC program to Insert a record in database using JDBC updatable ResultSet
 */
public class ClientTest {

	public static void main(String[] args) throws SQLException {
		Employee employee = getEmployee() ;
		createEmployee(employee);
	}

	public static void createEmployee(Employee employee) {

		String SQL = "SELECT *FROM employee_table";
		try (Connection connection = DBUtil.getConnection();
				//Making Updatable ResultSet
				PreparedStatement ps = connection.prepareStatement(SQL,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE)) {

			ResultSet rs = ps.executeQuery();
			
			rs.moveToInsertRow();
			
			rs.updateString("employee_name", employee.getEmployeeName());
			rs.updateString("email", employee.getEmail());
			rs.updateDouble("salary", employee.getSalary());
			rs.updateDate("date_of_joining", new java.sql.Date(employee.getDoj().getTime()));
			rs.updateBigDecimal("bonus", employee.getBonus());
			
			rs.insertRow();
			System.out.println("Employee is created..");
			
			while (rs.next()) {
				int empId = rs.getInt("employee_id");
				String eName = rs.getString("employee_name");
				String email = rs.getString("email");
				Double salary = rs.getDouble("salary");
				BigDecimal bonus = rs.getBigDecimal("bonus");
				Date date = rs.getDate("date_of_joining");
				
				employee = new Employee();
				employee.setEmployeeName(eName);
				employee.setBonus(bonus);
				employee.setDoj(date);
				employee.setEmail(email);
				employee.setEmployee_id(empId);
				employee.setSalary(salary);
				System.out.println(employee);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	private static Employee getEmployee() {
		Employee employee = new Employee();
		employee.setBonus(new BigDecimal(290));
		employee.setDoj(new Date());
		employee.setEmployeeName("John");
		employee.setEmail("john.cs2016@yahoo.com");
		employee.setSalary(90000.00);
		return employee;
	}
}

package com.infotech.client;

import java.math.BigDecimal;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Date;

import com.infotech.model.Employee;

import com.infotech.util.DBUtil;

/**

* @author KK JavaTutorials

*JDBC program to Insert a record in database using JDBC updatable ResultSet

public class ClientTest {

public static void main(String[] args) throws SQLException {

Employee employee = getEmployee() ;

createEmployee(employee);

}

public static void createEmployee(Employee employee) {

String SQL = "SELECT *FROM employee_table";

try (Connection connection = DBUtil.getConnection();

//Making Updatable ResultSet

PreparedStatement ps = connection.prepareStatement(SQL,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE)) {

ResultSet rs = ps.executeQuery();

rs.moveToInsertRow();

rs.updateString("employee_name", employee.getEmployeeName());

rs.updateString("email", employee.getEmail());

rs.updateDouble("salary", employee.getSalary());

rs.updateDate("date_of_joining", new java.sql.Date(employee.getDoj().getTime()));

rs.updateBigDecimal("bonus", employee.getBonus());

rs.insertRow();

System.out.println("Employee is created..");

while (rs.next()) {

int empId = rs.getInt("employee_id");

String eName = rs.getString("employee_name");

String email = rs.getString("email");

Double salary = rs.getDouble("salary");

BigDecimal bonus = rs.getBigDecimal("bonus");

Date date = rs.getDate("date_of_joining");

employee = new Employee();

employee.setEmployeeName(eName);

employee.setBonus(bonus);

employee.setDoj(date);

employee.setEmail(email);

employee.setEmployee_id(empId);

employee.setSalary(salary);

System.out.println(employee);

}

} catch (Exception e) {

e.printStackTrace();

}

private static Employee getEmployee() {

Employee employee = new Employee();

employee.setBonus(new BigDecimal(290));

employee.setDoj(new Date());

employee.setEmployeeName("John");

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

employee.setSalary(90000.00);

return employee;

}

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

Employee is created..
Employee [employee_id=1, employeeName=Martin, email=martin.cs2017@yahoo.com, salary=71000.0, doj=2017-05-17, bonus=400]
Employee [employee_id=2, employeeName=Paul, email=paul.cs2001@gmail.com, salary=91000.0, doj=2017-05-18, bonus=800]
Employee [employee_id=3, employeeName=Sam, email=sam.cs2014@gmail.com, salary=76000.0, doj=2017-05-16, bonus=300]
Employee [employee_id=4, employeeName=Paul, email=paul.cs2009@yahoo.com, salary=60000.0, doj=2017-05-17, bonus=400]
Employee [employee_id=5, employeeName=John, email=john.cs2016@yahoo.com, salary=90000.0, doj=2018-08-01, bonus=290]

Employee is created..

Employee [employee_id=1, employeeName=Martin, email=martin.cs2017@yahoo.com, salary=71000.0, doj=2017-05-17, bonus=400]

Employee [employee_id=2, employeeName=Paul, email=paul.cs2001@gmail.com, salary=91000.0, doj=2017-05-18, bonus=800]

Employee [employee_id=3, employeeName=Sam, email=sam.cs2014@gmail.com, salary=76000.0, doj=2017-05-16, bonus=300]

Employee [employee_id=4, employeeName=Paul, email=paul.cs2009@yahoo.com, salary=60000.0, doj=2017-05-17, bonus=400]

Employee [employee_id=5, employeeName=John, email=john.cs2016@yahoo.com, salary=90000.0, doj=2018-08-01, bonus=290]

Above eclipse console output clearly says that a new employee is created with employee_id 5
If you query database employee_table then you will find one more record is inserted with employee_id 5 as below:

That’s all about How to insert a Row in a Database Table Using an updatable ResultSet

You May Also Like:

Calling StoredProcedure Using CallableStatement
Get ResultSet By Calling StoredProcedure Using CallableStatement
Calling database custom Function Using CallableStatement
JDBC batch update with Statement
JDBC batch update with PreparedStatement
JDBC batch update with CallableStatement
How to retrieve data from JDBC resultset in java
How to update a Row in a Database Table Using an updatable ResultSet
JDBC ResultSet navigation methods example in java

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

Leave a Reply Cancel reply