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:
- First, create a
Connection
object to connect to the SQLite database. - Next, create an instance of the
Statement
class from theConnection
object. - Then, create an instance of the
ResultSet
class by calling theexecuteQuery
method of theStatement
object. TheexecuteQuery()
method accepts a SELECT statement. - After that, loop through the result set using the
next()
method of theResultSet
object. - Finally, use the get* method of the
ResultSet
object such asgetInt()
,getString()
,getDouble()
, etc., to get the data in each iteration.
The following program selects all rows from the warehouses
table.
package net.sqlitetutorial;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
*
* @author sqlitetutorial.net
*/
public class SelectApp {
/**
* 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;
}
/**
* select all rows in the warehouses table
*/
public void selectAll(){
String sql = "SELECT id, name, capacity FROM warehouses";
try (Connection conn = this.connect();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)){
// loop through the result set
while (rs.next()) {
System.out.println(rs.getInt("id") + "\t" +
rs.getString("name") + "\t" +
rs.getDouble("capacity"));
}
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
SelectApp app = new SelectApp();
app.selectAll();
}
}
Code language: Java (java)
The following illustrates the output of the program:
Querying data with parameters
To use parameters in the query, you use the PreparedStatement
object instead. For example, the following method selects the warehouse whose capacity is greater than a specified capacity.
/**
* Get the warehouse whose capacity greater than a specified capacity
* @param capacity
*/
public void getCapacityGreaterThan(double capacity){
String sql = "SELECT id, name, capacity "
+ "FROM warehouses WHERE capacity > ?";
try (Connection conn = this.connect();
PreparedStatement pstmt = conn.prepareStatement(sql)){
// set the value
pstmt.setDouble(1,capacity);
//
ResultSet rs = pstmt.executeQuery();
// loop through the result set
while (rs.next()) {
System.out.println(rs.getInt("id") + "\t" +
rs.getString("name") + "\t" +
rs.getDouble("capacity"));
}
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
Code language: Java (java)
To find the warehouses whose capacities are greater than 3600, you use the getCapacityGreaterThan()
method as follows:
SelectApp app = new SelectApp();
app.getCapacityGreaterThan(3600);
Code language: Java (java)
The following is the output:
In this tutorial, you have learned how to query data from the table in the SQLite database from a Java program.