How to update the contents of a record of a table in a database using JDBC API?

A. You can update/modify the existing contents of a record in a table using the UPDATE query. Using this you can update all the records of the table or specific records.

Syntax

UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition];

To update the contents of a record in a table using JDBC API you need to:

• Register the driver: Register the driver class using the registerDriver() method of the DriverManager class. Pass the driver class name to it, as parameter.

• Establish a connection: Connect ot the database using the getConnection() method of the DriverManager class. Passing URL (String), username (String), password (String) as parameters to it.

• Create Statement: Create a Statement object using the createStatement() method of the Connection interface.

• Execute the Query: Execute the query using the executeUpdate() method of the Statement interface.

Example

Assume we have a table named customers in the database named mydatabase in MySQL, with 12 records as:

+----+-----------+------+---------+----------------+ | ID | NAME      | AGE | SALARY   | ADDRESS        | +----+-----------+------+---------+----------------+ | 1 | Amit       | 25   | 3000.00 | Hyderabad      | | 2 | Kalyan     | 27   | 4000.00 | Vishakhapatnam | | 3 | Renuka     | 30   | 5000.00 | Delhi          | | 4 | Archana    | 24   | 1500.00 | Mumbai         | | 5 | Koushik    | 30   | 9000.00 | Kota           | | 6 | Hardik     | 45   | 6400.00 | Bhopal         | | 7 | Trupthi    | 33   | 4360.00 | Ahmedabad      | | 8 | Mithili    | 26   | 4100.00 | Vijayawada     | | 9 | Maneesh    | 39   | 4000.00 | Hyderabad      | | 10 | Rajaneesh | 30   | 6400.00 | Delhi          | | 11 | Komal     | 29   | 8000.00 | Ahmedabad      | | 12 | Manyata   | 25   | 5000.00 | Vijayawada     | +----+-----------+------+---------+----------------+

Following JDBC program establishes connection with MySQL and updates the addresses of the customers with ids 4, 5, 6 and, 7 to Delhi and, retrieves and displays the contents of the table after the update.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class UpdateRecordsExample {    public static void main(String args[]) throws SQLException {       //Registering the Driver       DriverManager.registerDriver(new com.mysql.jdbc.Driver());       //Getting the connection       String mysqlUrl = "jdbc:mysql://localhost/MyDatabase";       Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");       System.out.println("Connection established......");       //Creating the Statement       Statement stmt = con.createStatement();       //Query to update records       String query = "update customers set address = 'Delhi' where ID in (4, 5, 6, 7 )";       //Executing the query       int i = stmt.executeUpdate(query);       System.out.println("Rows updated: "+i);       System.out.println("Contents of the customers table after updating the records: ");       //Retrieving data       ResultSet rs = stmt.executeQuery("Select * from customers");       while(rs.next()) {          System.out.print("ID: "+rs.getInt("ID")+", ");          System.out.print("Name: "+rs.getString("Name")+", ");          System.out.print("Age: "+rs.getInt("Age")+", ");          System.out.print("Salary: "+rs.getInt("Salary")+", ");          System.out.print("Address: "+rs.getString("Address"));          System.out.println();       }    } }

Output

Connection established...... Rows updated: 4 Contents of the customers table after updating the records: ID: 1, Name: Amit, Age: 25, Salary: 3000, Address: Hyderabad ID: 2, Name: Kalyan, Age: 27, Salary: 4000, Address: Vishakhapatnam ID: 3, Name: Renuka, Age: 30, Salary: 5000, Address: Delhi ID: 4, Name: Archana, Age: 24, Salary: 1500, Address: Delhi ID: 5, Name: Koushik, Age: 30, Salary: 9000, Address: Delhi ID: 6, Name: Hardik, Age: 45, Salary: 6400, Address: Delhi ID: 7, Name: Trupthi, Age: 33, Salary: 4360, Address: Delhi ID: 8, Name: Mithili, Age: 26, Salary: 4100, Address: Vijayawada ID: 9, Name: Maneesh, Age: 39, Salary: 4000, Address: Hyderabad ID: 10, Name: Rajaneesh, Age: 30, Salary: 6400, Address: Delhi ID: 11, Name: Komal, Age: 8000, Salary: 29, Address: Ahmedabad ID: 12, Name: Manyata, Age: 5000, Salary: 25, Address: Vijayawada