Java and SQLite – Updating Data

In order to update data in an SQLite database, the SQL ‘Update’ statement needs to be used. The following example updates the record in the ‘person’ table that was added in the example for inserting data.

Updating data follows the same pattern as inserting data. 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 parameters are defined as variables, which are bound in to the SQL statement when it is executed and a confirmation message is displayed.

One of the parameters is for an ‘id’, so that only the specified record is updated, in this case, the record with an ‘id’ of five. The other parameters are for updating the ‘lastname’ and ‘title’ fields against the record. 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 parameters.
        String lastname = "Bloggs";
        String title = "Mrs";
        Integer id = 5;

        // SQL to update data in the person table.
        String sqlUpdate =
                "UPDATE person " +
                "SET lastname = ?, " +
                "    title = ? " +
                "WHERE id = ?";

        try {

            // Create statement and bind the parameters.
            PreparedStatement statement = connect.prepareStatement(sqlUpdate);
            statement.setString(1, lastname);
            statement.setString(2, title);
            statement.setInt(3, id);

            // Execute query.
            statement.executeUpdate();

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

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

        } catch (SQLException e) {

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

        }

    }

}

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