SQLite Java: Inserting Data

Summary: in this tutorial, you will learn how to insert data into a table in an SQLite database using the Java JDBC.

To insert data into a table using the INSERT statement, you use the following steps:

1. First, connect to the SQLite database.
2. Next, prepare the INSERT statement. Use a question mark (?) for each parameter if you have parameters in the statement.
3. Then, create an instance of the PreparedStatement from the Connection object.
4. After that, set the corresponding values for each placeholder using the set method of the PreparedStatement object such as setInt(), setString(), etc.
5. Finally, call the executeUpdate() method of the PreparedStatement object to execute the statement.

The following program inserts three rows into the table warehouses that we created in the creating table tutorial.

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

public class Main {

    public static void main(String[] args) {

        String url = "jdbc:sqlite:my.db";

        var names = new String[] {"Raw Materials", "Semifinished Goods", "Finished Goods"};
        var capacities = new int[] {3000,4000,5000};

        String sql = "INSERT INTO warehouses(name,capacity) VALUES(?,?)";

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

            for(int i = 0; i < 3; i++){
                pstmt.setString(1, names[i]);
                pstmt.setDouble(2, capacities[i]);
                pstmt.executeUpdate();
            }

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

How it works.

First, initialize a connection string to the my.db sqlite database file:

String url = "jdbc:sqlite:my.db";Code language: Java (java)

Second, declare two arrays that store warehouse names and their capacities:

var names = new String[] {"Raw Materials", "Semifinished Goods", "Finished Goods"};
var capacities = new int[] {3000,4000,5000};Code language: Java (java)

Third, construct an INSERT statement that inserts a row into the warehouses table:

String sql = "INSERT INTO warehouses(name,capacity) VALUES(?,?)";Code language: Java (java)

In this statement, the question marks (?) will be replaced with the corresponding name and capacity.

Fourth, create a connection to the sqlite database by calling the getConnection() method of the DriverManager class:

var conn = DriverManager.getConnection(url);Code language: Java (java)

Fifth, create a PreparedStatement object from the Connection object with the INSERT statement:

var pstmt = conn.prepareStatement(sql)Code language: Java (java)

Sixth, iterate over the names and capacities arrays. In each iteration, bind the name and capacity and call the executeUpdate() statement to run the INSERT statement:

for (int i = 0; i < 3; i++) {
  pstmt.setString(1, names[i]);
  pstmt.setDouble(2, capacities[i]);
  pstmt.executeUpdate();
}Code language: Java (java)

We use the try-with-resources statement to close the statement and database connection properly.

Seventh, display the error message in the catch block if any SQL exception occurs:

System.err.println(e.getMessage());Code language: Java (java)

Verifying the inserts

After running the program, you can check the warehouses table in the test.db database using the following SELECT statement:

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 Goods      5000.0Code language: CSS (css)

In this tutorial, you have learned how to insert data into a table in the SQLite database from the Java program.