JDBC Update

Summary: in this tutorial, you will learn how to update data in a table from a Java program using JDBC.

Updating data in a table using JDBC

Here are the steps for updating data in a table using JDBC:

• First, open a new database connection.
• Second, create a new PreparedStatement object that accepts an UPDATE statement.
• Third, set the parameters for the statement by calling the set* methods of the PreparedStatement object.
• Call the executeUpdate() to execute the statement to update data in a table.
• Finally, close the database connection.

The following program shows how to update the price of the product id 1 to 11.95 in the products table:

import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        var id = 1;
        var price = 11.95;
        var sql = "UPDATE products SET price = ? WHERE id = ?";

        try (var connection = DBConnection.connect();
            var pstmt = connection.prepareStatement(sql)) {

            pstmt.setDouble(1, price);
            pstmt.setInt(2, id);

            int rowsUpdated  = pstmt.executeUpdate();

            var message = rowsUpdated > 0
                    ? "Row updated successfully."
                    : "Failed to update row.";

            System.out.println(message);

        } catch (SQLException e) {
            System.err.println(e.getMessage());
        }
    }
}Code language: Java (java)

How it works.

First, declare and initialize variables for holding the product id and price:

var id = 1;
var price = 11.95;Code language: SQL (Structured Query Language) (sql)

Second, construct an UPDATE statement:

var sql = "UPDATE products SET price = ? WHERE id = ?";Code language: SQL (Structured Query Language) (sql)

In this syntax, the question marks (?) are placeholders that correspond to the price and id. When you execute the UPDATE statement, you need to provide both price and id.

Third, create a new connection to the database:

var connection = DBConnection.connect()Code language: SQL (Structured Query Language) (sql)

Fourth, create a PreparedStatement object with the UPDATE statement:

var pstmt = connection.prepareStatement(sql)Code language: SQL (Structured Query Language) (sql)

Fifth, set the values for the price and id by calling the setDouble() and setInt() methods:

pstmt.setDouble(1, price);
pstmt.setInt(2, id);Code language: SQL (Structured Query Language) (sql)

Sixth, execute the UPDATE statement and return a number of updated rows:

int rowsUpdated  = pstmt.executeUpdate();Code language: SQL (Structured Query Language) (sql)

Seventh, assign a message based on the number of inserted rows and display it:

var message = rowsUpdated > 0
                    ? "Row updated successfully."
                    : "Failed to update row.";Code language: SQL (Structured Query Language) (sql)

Finally, show the error message if an exception occurs in the catch block:

System.err.println(e.getMessage());Code language: SQL (Structured Query Language) (sql)

If you execute the program and no exception occurs, you’ll see the following message:

Row updated successfully.Code language: SQL (Structured Query Language) (sql)

Verify the update

First, connect to the MariaDB server using the bob account:

mysql -u bob -pCode language: plaintext (plaintext)

It’ll prompt you for a password for the bob user account. Please enter a valid password and press the Enter key to connect.

Second, change the current database to sales:

use sales;Code language: SQL (Structured Query Language) (sql)

Third, retrieve the product with id 1 from the products table:

select * from products 
where id = 1;Code language: SQL (Structured Query Language) (sql)

Output:

+----+---------+-------+
| id | name    | price |
+----+---------+-------+
|  1 | T-Shirt | 11.95 |
+----+---------+-------+
1 row in set (0.00 sec)Code language: plaintext (plaintext)

Finally, quit the mysql program:

quitCode language: SQL (Structured Query Language) (sql)

Summary

• Use the PreparedStatement to execute an UPDATE statement to update data in a table.