Update 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 update 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. Iterate the ResultSet and use ResultSet.updateXXX() wherever you need to update the record.

4. Then use ResultSet.updateRow() to update the changes.

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

Example

We have a table employee_details that has 3 records

ID	Name	Designation	Salary
1	Steve	Sr Developer	5000
2	Mark	Sr Developer	4500
3	Robin	Jr Developer	3500

Now we will write code to get all the records and then update the salary for each of them to add 500.

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 ResultSetUpdateExample {

	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);
			while (resultSet.next()) {
				int salary = resultSet.getInt("salary");
				salary = salary + 500;
				resultSet.updateInt("salary", salary);
				resultSet.updateRow();
			}

		} 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 ResultSetUpdateExample {

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

while (resultSet.next()) {

int salary = resultSet.getInt("salary");

salary = salary + 500;

resultSet.updateInt("salary", salary);

resultSet.updateRow();

}

} 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

When we fire a direct query to the database to check the records that were updated we see the below results for updated salaries.

ID	Name	Designation	Salary
1	Steve	Sr Developer	5500
2	Mark	Sr Developer	5000
3	Robin	Jr Developer	4000