SQLite Java: Inserting Data

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. If you use parameters for the statement, use a question mark (?) for each parameter.
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 warehouses table that we created in the creating table tutorial.

package net.sqlitetutorial;

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

/**
 *
 * @author n3565
 */
public class InsertApp {

    /**
     * Connect to the test.db database
     *
     * @return the Connection object
     */
    private Connection connect() {
        // SQLite connection string
        String url = "jdbc:sqlite:C://sqlite/db/test.db";
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return conn;
    }

    /**
     * Insert a new row into the warehouses table
     *
     * @param name
     * @param capacity
     */
    public void insert(String name, double capacity) {
        String sql = "INSERT INTO warehouses(name,capacity) VALUES(?,?)";

        try (Connection conn = this.connect();
                PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, name);
            pstmt.setDouble(2, capacity);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {

        InsertApp app = new InsertApp();
        // insert three new rows
        app.insert("Raw Materials", 3000);
        app.insert("Semifinished Goods", 4000);
        app.insert("Finished Goods", 5000);
    }

}

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; 

---

How to insert many records to MySQL faster,Java?

Suppose you have your name and address in an Employee, and now you need to insert many Employee records into db, you can use JDBC batch operation and optimize the performance.

  PreparedStatement ps = con.prepareStatement("INSERT INTO registration_table(name,address) VALUES (?, ?)");   

  for(Employee employee: employees){
      ps.setString(1,employee.getName());
      ps.setString(2,employee.getAddress());
      ps.addBatch();// add to batch
      ps.clearParameters();
  }

  int[] results = ps.executeBatch();// execute with batch rather than execute many SQL separately. 

---

This is the efficient way for batch insert

Connection connection = new getConnection();
Statement statement = connection.createStatement();

for (String query : queries) {
    statement.addBatch(query);
}
statement.executeBatch();
statement.close();
connection.close();