Insert Record using ResultSet in JDBC

We know that ResultSet objects are used to get the data that is retrieved using a Select statement.
ResultSet can also be used to insert or update records in the database.

Steps to remember when you insert Record using ResultSet in JDBC

1. ResultSet should be Updatable You can do this while creating Statement object

statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_UPDATABLE);

1	statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_UPDATABLE);

2. Get a ResultSet object after executeQuery()

3. Use ResultSet.moveToInsertRow() to initiate the insert process.

4. Add details of the record that needs to be added to the ResultSet object using updateXXX() methods

5. Use ResultSet.insertRow() to insert the record.

6. Use ResultSet.moveToCurrentRow() for any further processing on the ResultSet object.

Lets see an example on how to insert record using Resultset in Java in database.

Example

package com.kscodes.sampleproject.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ResultSetInsertExample {

	public static String DRIVER_CLASS_NAME = "com.mysql.jdbc.Driver";
	public static String CONNECTION_URL = "jdbc:mysql://localhost:3306/mysql";
	public static String CONNECTION_USER = "kscodes";
	public static String CONNECTION_PASSWORD = "kscodes";

	public static void main(String[] args) {

		String selectSql = "SELECT * FROM employee_details";
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = getConnection();
			statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_UPDATABLE);
			resultSet = statement.executeQuery(selectSql);
			
			System.out.println("Move the ResultSet to insert row");
			resultSet.moveToInsertRow();
			
			System.out.println("Set the record details to insert");
			resultSet.updateInt(1, 100);
			resultSet.updateString(2, "Mark");
			resultSet.updateString(3, "Johnson");
			resultSet.updateString(3, "Staff");
			resultSet.updateInt(4, 2500);
			
			System.out.println("Use insertRow()");
			resultSet.insertRow();			
					
			System.out.println("Move the ResultSet to current row.");
			resultSet.moveToCurrentRow();

		} catch (SQLException e) {
			System.out.println("An exception occured.Exception is :: " + e);
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}
				if (statement != null) {
					statement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (Exception e) {
			}
		}

	}

	public static Connection getConnection() {

		try {
			Class.forName(DRIVER_CLASS_NAME);
		} catch (ClassNotFoundException e) {
			System.out.println("Error while registering JDBC driver");
			return null;
		}

		Connection connection = null;
		try {
			connection = DriverManager.getConnection(CONNECTION_URL,
					CONNECTION_USER, CONNECTION_PASSWORD);

		} catch (SQLException e) {
			System.out.println("Failed to create Connection");
			return null;
		}
		System.out.println("Connection created Successfully.");

		return connection;
	}
}

package com.kscodes.sampleproject.jdbc;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class ResultSetInsertExample {

public static String DRIVER_CLASS_NAME = "com.mysql.jdbc.Driver";

public static String CONNECTION_URL = "jdbc:mysql://localhost:3306/mysql";

public static String CONNECTION_USER = "kscodes";

public static String CONNECTION_PASSWORD = "kscodes";

public static void main(String[] args) {

String selectSql = "SELECT * FROM employee_details";

Connection connection = null;

Statement statement = null;

ResultSet resultSet = null;

try {

connection = getConnection();

statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY,

ResultSet.CONCUR_UPDATABLE);

resultSet = statement.executeQuery(selectSql);

System.out.println("Move the ResultSet to insert row");

resultSet.moveToInsertRow();

System.out.println("Set the record details to insert");

resultSet.updateInt(1, 100);

resultSet.updateString(2, "Mark");

resultSet.updateString(3, "Johnson");

resultSet.updateString(3, "Staff");

resultSet.updateInt(4, 2500);

System.out.println("Use insertRow()");

resultSet.insertRow();

System.out.println("Move the ResultSet to current row.");

resultSet.moveToCurrentRow();

} catch (SQLException e) {

System.out.println("An exception occured.Exception is :: " + e);

} finally {

try {

if (resultSet != null) {

resultSet.close();

}

if (statement != null) {

statement.close();

}

if (connection != null) {

connection.close();

}

} catch (Exception e) {

}

public static Connection getConnection() {

try {

Class.forName(DRIVER_CLASS_NAME);

} catch (ClassNotFoundException e) {

System.out.println("Error while registering JDBC driver");

return null;

}

Connection connection = null;

try {

connection = DriverManager.getConnection(CONNECTION_URL,

CONNECTION_USER, CONNECTION_PASSWORD);

} catch (SQLException e) {

System.out.println("Failed to create Connection");

return null;

}

System.out.println("Connection created Successfully.");

return connection;

}

Output

Connection created Successfully.
Move the ResultSet to insert row
Set the record details to insert
Use insertRow()
Move the ResultSet to current row.

Connection created Successfully.

Move the ResultSet to insert row

Set the record details to insert

Use insertRow()

Move the ResultSet to current row.

You can fire a direct query to the database to check the records that were inserted.