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.
id | firstname | lastname | title | dob |
1 | Bob | Smith | Mr | 1980-01-20 |
3 | Fred | Bloggs | Mr | 1975-05-07 |
4 | Alan | White | Mr | 1989-03-20 |
5 | Fiona | Bloggs | Mrs | 1985-05-19 |