Update record using Prepared Statement in Java

PreparedStatement is an interface and has pre-compiled SQL statements. PreparedStatements are used to efficiently execute the same SQL statement multiple times. They are also used to set the parameters in query dynamically.

Steps to Update record using Prepared Statement

1. Get a connection Object

Connection connection = getConnection();

1	Connection connection = getConnection();

2. Create a prepareStatement object

String updateSql = "UPDATE employee_details"
				+ " SET designation= ? WHERE salary = ?";
PreparedStatement preparedStatement = connection.prepareStatement(updateSql);

String updateSql = "UPDATE employee_details"

+ " SET designation= ? WHERE salary = ?";

PreparedStatement preparedStatement = connection.prepareStatement(updateSql);

3. Add parameters (if required)

preparedStatement.setString(1, "Sr Developer");
preparedStatement.setInt(2, 1000);

1 2	preparedStatement.setString(1, "Sr Developer"); preparedStatement.setInt(2, 1000);

ParameterIndex starts from 1.

4. execute the query using executeUpdate.

int rowCount = preparedStatement.executeUpdate();

1	int rowCount = preparedStatement.executeUpdate();

Full Example : Update record using Prepared Statement

package com.kscodes.sampleproject.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class PreparedUpdateExample {

	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 updateSql = "UPDATE employee_details"
				+ " SET designation= ? WHERE salary = ?";

		try (Connection connection = getConnection();
				PreparedStatement preparedStatement = connection
						.prepareStatement(updateSql)) {

			preparedStatement.setString(1, "Sr Developer");
			preparedStatement.setInt(2, 1000);

			int rowCount = preparedStatement.executeUpdate();

			System.out.println("Record Updated successfully in database. Row Count returned is :: "
							+ rowCount);

		} catch (SQLException e) {
			System.out.println("An exception occured while updating data in database. Exception is :: "
							+ 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.PreparedStatement;

import java.sql.SQLException;

public class PreparedUpdateExample {

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 updateSql = "UPDATE employee_details"

+ " SET designation= ? WHERE salary = ?";

try (Connection connection = getConnection();

PreparedStatement preparedStatement = connection

.prepareStatement(updateSql)) {

preparedStatement.setString(1, "Sr Developer");

preparedStatement.setInt(2, 1000);

int rowCount = preparedStatement.executeUpdate();

System.out.println("Record Updated successfully in database. Row Count returned is :: "

+ rowCount);

} catch (SQLException e) {

System.out.println("An exception occured while updating data in database. Exception is :: "

+ 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.
Record Updated successfully in database. Row Count returned is :: 2

1 2	Connection created Successfully. Record Updated successfully in database. Row Count returned is :: 2

Reference

1. PreparedStatement API