SQLite Java: Deleting Data

Summary: This tutorial walks you through deleting data in a table from a Java program using JDBC.

To delete one or more rows of a database table from a Java program, you use the following steps:

1. First, create a database connection to the SQLite database.
2. Next, prepare the DELETE statement. If you want the statement to take parameters, use the question mark (?) placeholder inside the statement.
3. Then, create a new instance of the  PreparedStatement class by calling the prepareStatement() method of the Connection object.
4. After that, supply values in place of the question mark placeholder using the set* method of the PreparedStatement object e.g., setInt(), setString(), etc.
5. Finally, execute the DELETE statement by calling the executeUpdate() method of the PreparedStatement object.

The following program deletes the warehouse with id 3 in the warehouses table of the my.db database created in the creating table tutorial.

import java.sql.DriverManager;
import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        var url = "jdbc:sqlite:my.db";
        var sql = "DELETE FROM warehouses WHERE id = ?";
        var id = 3;

        try (var conn = DriverManager.getConnection(url);
             var pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, id);

            // execute the delete statement
            pstmt.executeUpdate();

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

Verify the deletion

First, use the following SELECT statement to check the warehouses table before executing the program:

SELECT
  id,
  name,
  capacity
FROM
  warehouses;Code language: SQL (Structured Query Language) (sql)

Output:

id  name                capacity
--  ------------------  --------
1   Raw Materials       3000.0
2   Semifinished Goods  4000.0
3   Finished Products   5500.0Code language: CSS (css)

Second, run the program.

Third, use the same SELECT statement above to query data from the warehouses table:

SELECT
  id,
  name,
  capacity
FROM
  warehouses;

Output:

id  name                capacity
--  ------------------  --------
1   Raw Materials       3000.0
2   Semifinished Goods  4000.0Code language: CSS (css)

The output shows that the row with id 2 is deleted.

In this tutorial, you have learned how to delete data in the SQLite database from the Java program.