SQLite Java: Select Data

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

To query data from a table, you use the following steps:

1. First, create a Connection object to connect to the SQLite database.
2. Next, create an instance of the Statement class from the Connection object.
3. Then, create an instance of the ResultSet class by calling the executeQuery method of the Statement object. The executeQuery() method accepts a SELECT statement.
4. After that, loop through the result set using the next() method of the ResultSet object.
5. Finally, use the get* method of the ResultSet object such as getInt(), getString(), getDouble(), etc., to get the data in each iteration.

Querying all rows from a table

The following program selects all rows from the warehouses table.

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

public class Main {
    public static void main(String[] args) {
        var url = "jdbc:sqlite:my.db";
        var sql = "SELECT id, name, capacity FROM warehouses";

        try (var conn = DriverManager.getConnection(url);
             var stmt = conn.createStatement();
             var rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                System.out.printf("%-5s%-25s%-10s%n",
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getDouble("capacity")
                );
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        }
    }
}Code language: Java (java)

Output:

1    Raw Materials            3000.0    
2    Semifinished Goods       4000.0    
3    Finished Products        5500.0    Code language: CSS (css)

Querying data with parameters

The following program retrieves the warehouses whose capacity are greater than 3600.

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

public class Main {
    public static void main(String[] args) {
        var url = "jdbc:sqlite:my.db";
        var sql = "SELECT id, name, capacity FROM warehouses WHERE capacity > ?";
        var capacity = 3600;

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

            pstmt.setDouble(1, capacity);

            var rs = pstmt.executeQuery();

            while (rs.next()) {
                System.out.printf("%-5s%-25s%-10s%n",
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getDouble("capacity")
                );
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        }
    }
}Code language: Java (java)

Output:

2    Semifinished Goods       4000.0    
3    Finished Products        5500.0   Code language: CSS (css)

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