SQLite Java: Update Data

Summary: this tutorial shows you how to update data in a table from a Java program using JDBC.

To update existing data of a table, you follow the steps below:

1. First, connect to the SQLite database.
2. Next, prepare the UPDATE statement. For the UPDATE statement that uses parameters, you use the question marks (?) placeholder in the SET and WHERE clauses.
3. Then, instantiate an object the PreparedStatement class by calling the prepareStatement() method of the Connection object.
4. After that, set a value for each placeholder using the set* method of the PreparedStatement object, for example, setString(), setInt(), etc.
5. Finally, execute the UPDATE statement by calling the executeUpdate() method of the PreparedStatement object.

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 sqlitetutorial.net
 */
public class UpdateApp {

    /**
     * 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;
    }

    /**
     * Update data of a warehouse specified by the id
     *
     * @param id
     * @param name name of the warehouse
     * @param capacity capacity of the warehouse
     */
    public void update(int id, String name, double capacity) {
        String sql = "UPDATE warehouses SET name = ? , "
                + "capacity = ? "
                + "WHERE id = ?";

        try (Connection conn = this.connect();
                PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // set the corresponding param
            pstmt.setString(1, name);
            pstmt.setDouble(2, capacity);
            pstmt.setInt(3, id);
            // update 
            pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        
        UpdateApp app = new UpdateApp();
        // update the warehouse with id 3
        app.update(3, "Finished Products", 5500);
    }

}
Code language: Java (java)

First, you check the warehouse table before running the update program using the following SELECT statement.

SELECT
	id,
	name,
	capacity
FROM
	warehouses;Code language: SQL (Structured Query Language) (sql)

Second, execute the UpdateApp program.

Third, query data from the warehouses table again using the same SELECT statement above.

In this tutorial, you have learned how to update data in the SQLite database from the Java program.