Java and SQLite – Inserting Data

In order to insert data into an SQLite database, the SQL ‘Insert’ statement needs to be used. The following example inserts a record into the same ‘person’ table that was used in the example for selecting data.

Inserting data works in a similar fashion as selecting data using parameters. 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 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 parameters.
        String firstname = "Fiona";
        String lastname = "Jones";
        String title = "Miss";
        String dob = "1985-05-19";

        // SQL to insert data into the person table.
        String sqlInsert = 
                "INSERT INTO person " +
                "       (firstname, lastname, title, dob) " +
                "VALUES (?, ?, ?, ?) ";

        try {

            // Create statement and bind the parameters.
            PreparedStatement statement = connect.prepareStatement(sqlInsert);
            statement.setString(1, firstname);
            statement.setString(2, lastname);
            statement.setString(3, title);
            statement.setString(4, dob);

            // Execute query.
            statement.executeUpdate();

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

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

        } catch (SQLException e) {

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

        }

    }

}

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