Java SQL Server: Updating Data

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

This tutorial begins where the Querying Data from SQL Server using the LIKE Operator tutorial left off.

Updating data

Step 1. Define a new method in the AuthorDB class that updates the data of an Author object:

public void update(Author author) throws DBException {
  if (author == null) {
    return;
  }

  var sql = "UPDATE Authors SET FirstName =?, LastName=?, BirthDate = ? WHERE AuthorID=?";

  try (var statement = connection.prepareStatement(sql)) {
    // Bind values to parameters
    statement.setString(1, author.getFirstName());
    statement.setString(2, author.getLastName());
    statement.setDate(3, Date.valueOf(author.getBirthDate()));
    statement.setInt(4, author.getAuthorId());

    // Execute the update
    statement.executeUpdate();
  } catch(SQLException e) {
    throw new DBException(e.getMessage());
  }

}Code language: Java (java)

How it works.

First, define update() method that accepts an Author object and throws a DBException if an error occurs during the update:

public void update(Author author) throws DBException {Code language: Java (java)

Second, return immediately if the author object is null:

if (author == null) {
  return;
}Code language: Java (java)

Third, construct an UPDATE statement that updates author data based on author id:

var sql = "UPDATE Authors SET FirstName =?, LastName=?, BirthDate = ? WHERE AuthorID=?";Code language: Java (java)

Fourth, create a prepared statement object:

try (var statement = connection.prepareStatement(sql)) {Code language: Java (java)

Fifth, bind values to the statement:

statement.setString(1, author.getFirstName());
statement.setString(2, author.getLastName());
statement.setDate(3, Date.valueOf(author.getBirthDate()));
statement.setInt(4, author.getAuthorId());Code language: Java (java)

Sixth, execute the UPDATE statement by calling the executeUpdate() method of the PreparedStatement object:

statement.executeUpdate();Code language: Java (java)

Finally, throw a new DBException if an error occurs during the update:

} catch(SQLException e) {
  throw new DBException(e.getMessage());
}Code language: Java (java)

Step 2. Modify the main() method of the Main class to find an author with id 1 and update his first name:

import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        try (var connection = SQLServerConnection.connect()) {
            var authorDB = new AuthorDB(connection);
            // Find author with id 1
            var author = authorDB.findById(1);
            // Change the first name
            author.setFirstName("Johnson");
            // Update the author
            authorDB.update(author);

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

How it works.

First, connect to the SQL Server:

try (var connection = SQLServerConnection.connect()) {Code language: Java (java)

Second, create an AuthorDB object:

var authorDB = new AuthorDB(connection);Code language: Java (java)

Third, find the author with id 1:

var author = authorDB.findById(1);Code language: Java (java)

Fourth, change the first name to Johnson:

author.setFirstName("Johnson");Code language: Java (java)

Fifth, apply the change to the database by calling the update() method of the AuthorDB object:

authorDB.update(author);Code language: Java (java)

Finally, display an error if the author does not exist or the update fails:

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

Step 3. Launch the SQL Server Management Studio, connect to the SQL Server, and execute the following query to retrieve data of the author id 3:

SELECT * FROM Authors
WHERE AuthorId = 1;Code language: Java (java)

Output:

AuthorID | FirstName | LastName | BirthDate
---------+-----------+----------+-----------
1        | John      | Doe      | 1990-12-31
(1 row)Code language: Java (java)

Step 4. Run the Java program to update the author’s first name:

Step 5. Rerun the query to verify the update:

SELECT * FROM Authors
WHERE AuthorId = 1;Code language: Java (java)

Output:

AuthorID | FirstName | LastName | BirthDate
---------+-----------+----------+-----------
1        | Johnson   | Doe      | 1990-12-31
(1 row)Code language: Java (java)

The output shows that the author’s first name was updated to Johnson.

Download the project source code

Download the project source code

Summary

• Call the executeUpdate() method of the PreparedStatement object to execute an UPDATE statement to update data in a table from a Java program.