Java SQL Server Delete

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

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.

Was this tutorial helpful?