Java SQL Server: Deleting Data

Summary: in this tutorial, you will learn how to delete a row from a table in a Java program.

This tutorial begins where the Updating Data in SQL Server from Java tutorial left off.

Deleting data #

Step 1. Define a new method in the AuthorDB class that deletes an author by ID:

public void delete(int authorId) throws DBException {
  var sql = "DELETE FROM authors WHERE AuthorID=?";

  try (var statement = connection.prepareStatement(sql)) {
    // Bind values to parameters
    statement.setInt(1, authorId);

    // Execute the delete statement
    statement.executeUpdate();
  } catch(SQLException e) {
    throw new DBException(e.getMessage());
  }
}Code language: Java (java)

How it works.

First, define delete() method that accepts an author id and throws a DBException if an error occurs when deleting the author:

public void delete(int authorId) throws DBException {Code language: Java (java)

Second, construct an DELETE statement that deletes a row from the Authors table based on an author id:

var sql = "DELETE FROM authors WHERE AuthorID=?";Code language: Java (java)

Third, create a prepared statement object:

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

Fourth, bind the authorId value to the parameter of the statement:

statement.setInt(1, authorId);Code language: Java (java)

Fifth, execute the DELETE 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 delete an author with id 1:

import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        try (var connection = SQLServerConnection.connect()) {
            var authorDB = new AuthorDB(connection);
            authorDB.delete(1);
        } 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, delete the author with the id 1:

authorDB.delete(1);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 1:

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 delete the author with id 1.

Step 5. Rerun the query to verify the deletion:

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

Output:

AuthorID | FirstName | LastName | BirthDate
---------+-----------+----------+-----------

(0 rows affected)Code language: Java (java)

The output shows an empty result set, meaning that the Java program has successfully deleted the author with the id 1.

Download the project source code #

Download the project source code

Summary #

• Call the executeUpdate() method of the PreparedStatement object to execute a DELETE statement to delete a row from a table in a Java program.