Java and SQLite – Deleting Data

In order to delete data in an SQLite database, the SQL ‘Delete’ statement needs to be used. The following example deletes a record with a specific ‘id’ from the ‘person’ table, which was used in the examples for selecting, inserting and updating data.

Deleting data works in a similar way to the other examples, where a parameterised query is used. Firstly, a check is made to see if the database file actually exists. If it doesn’t, a message is displayed and execution of the program is halted. If successfully found, a connection to the database is established, then the query parameter for the ‘id’ of the record to be deleted is defined as a variable, which is bound in to the following SQL statement. The SQL statement is then executed and a confirmation message is displayed. The execution of the SQL statement is wrapped in a ‘try-catch’ block to catch any errors that may arise.

import java.io.File;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class DemoSQLite {

    public static void main(String[] args) {

        // Database and connection variables.
        File database = new File("C://Demo//testDB.db");
        Connection connect = null;

        // Check if database file exists.
        if (!database.isFile()) {

            // Confirm incorrect database location and stop program execution.
            System.out.println("Error locating database.");
            System.exit(0);

        }

        try {

            // Connect to database.
            connect = DriverManager.getConnection("jdbc:sqlite:" + database.getPath());

        } catch (SQLException e) {

            // Confirm unsuccessful connection and stop program execution.
            System.out.println("Database connection unsuccessful.");
            System.exit(0);

        }

        // Query parameter.
        Integer id = 2;

        // SQL to delete data in the person table.
        String sqlDelete =
                "DELETE FROM person " +
                "WHERE id = ?";

        try {

            // Create statement and bind the parameter.
            PreparedStatement statement = connect.prepareStatement(sqlDelete);
            statement.setInt(1, id);

            // Execute query.
            statement.executeUpdate();

            // Confirm successful deletion of person information.
            System.out.println("Person information deleted successfully.");

            // Close the statement and database connection.
            statement.close();
            connect.close();

        } catch (SQLException e) {

            // Confirm error deleting person information and quit.
            System.out.println("Error deleting person information.");
            System.exit(0);

        }

    }

}

The contents of the ‘person’ table now looks as follows.