Getting started with SQLite and JDBC

From SwinBrain

In this article we will be looking at the setting up of a basic JDBC connection to a SQLite database, and the use of this connection to retrieve data from the database. The JDBC page outlines the overall architecture of JDBC, if you are unfamiliar with JDBC please read the JDBC page first. This page focuses on the use of the DriverManager to connect to the database, the JDBC, Getting Started with a DataSource provides a JDBC illustration using the newer DataSource approach against an Apache Derby database.

First of all: Download the SQLite JDBC driver

Before you can connect to a SQLite database, you will need to download the SQLite JDBC driver from the projects downloads page. Make sure you download the latest (biggest version number) pure Java JAR file, with included binaries (for example "sqlitejdbc-v056.jar"), do not download the -pure JARs.

Step 1: Setting up the driver

The first step when programming with JDBC is to register a driver for the DBMS you wish to connect to. Registering a driver is normally done more dynamically to more easily allow changes between different drivers. The following code shows the dynamic loading and registration of a Driver with the Driver Manager, please note that this approach has changed with JDBC version 2 but is used in this example to illustrate the architecture.

import java.sql.*;
 
/**
 * This class demonstrates how to create a connection
 * to a SQLite database using JDBC with the DriverManager.
 */
public class SQLiteDemo
{
    //FIXME: This should be provided via external setup (config file), not hard coded!
    public static final String driver = "org.sqlite.JDBC";
    
    public static void main(String[] args)
    {
        //STEP 1: Setup the Driver
        try
        {
            //Load the JDBC driver class dynamically.
            Driver d = (Driver)Class.forName(driver).newInstance();
            DriverManager.registerDriver(d);
        }
        catch(Exception e)
        {
            System.out.println("Error loading database driver: " + e.toString());
            return;
        }
    }
}

Step 2: Connect to the database

At this point we need to specify a database to connect to. We will be using the database that is created in the Create a SQLite Database article.

Now that the SQLite JDBC driver is registered, the next step is to create a connection to a database. The Driver Manager is responsible for creating connection objects via its getConnection method. This method accepts a database URL (or connection string) which indicates some details of the database to connect to. Note again that the approach shown here has changed in JDBC 2, this example demonstrates the architecture of JDBC.

...
public class SQLiteDemo
{
    ...
    public static void main(String[] args)
    {
        //STEP 1: Setup the Driver
        ...
 
        //STEP 2: Create connection to database using database URL
        Connection con;
        try
        {
            //FIXME: Load database filename via external config.
            String url = "jdbc:sqlite:HelloWorld.sqlite";
            con = DriverManager.getConnection(url);
        }
        catch(SQLException e)
        {
            System.out.println("Error creating connection: " + e.toString());
            return;
        }
    }
}

Step 3: Create a Statement and run a Query

Once we have a connection to the database we can create a Statement object and use it to run an SQL query.

...
public class SQLiteDemo
{
    ...
    public static void main(String[] args)
    {
        //STEP 1: Setup the Driver
        ...
 
        //STEP 2: Create connection to database using database URL
        ...
 
        //STEP 3: Execute SQL query using Statement object
        Statement stmt;
        ResultSet res;
        try
        {
            String sql = "SELECT FirstName, LastName, Email FROM Doctors ORDER BY LastName";
            stmt = con.createStatement();
            res = stmt.executeQuery(sql);
        }
        catch(SQLException e)
        {
            System.out.println("Error creating or running statement: " + e.toString());
            try
            {
                con.close();
            }
            catch(Exception e)
            {
            }
            return;
        }
 
    }
}

A Statement object is created by the Connection that we have constructed in step 2 ("con"). Once we have the Statement we can use its executeQuery method to run the passed in string of SQL. The executeQuery method returns a ResultSet object that we can later make use of to read the results of the executed query.

Step 4: Read results

Now that we have the ResultSet object, we can read the records (and associated field values) from the result of the executed statement. The ResultSet object's next method moves its internal result cursor to the next record of the set and returns true unless there is no more data left to be read. The getX methods of the ResultSet are used to retrieve the field values from the current record. (The "current" record is the one the ResultSet's cursor is positioned at.) Fields can be accessed via column name (as shown in the example) or index.

...
public class SQLiteDemo
{
    ...
    public static void main(String[] args)
    {
        //STEP 1: Setup the Driver
        ...
 
        //STEP 2: Create connection to database using database URL
        ...
 
        //STEP 3: Execute SQL query using Statement object
        ...
 
        //STEP 4: Read results
        try
        {
            String fName, lName, email;
            while(res.next())
            {
                email = res.getString("Email");
                fName = res.getString("FirstName");
                lName = res.getString("LastName");
                System.out.println(fName + " " + lName + "  |  " + email);
            }
        }
        catch(Exception e)
        {
            System.out.println("Error processing results: " + e.toString());
            try
            {
                res.close();
                stmt.close();
                con.close();
            }
            catch(Exception ex)
            {
            }
            return;
        }
 
    }
}

Step 5: Close Up

After you're done with a ResultSet or a Connection object, you need to close them to free their associated resources (and plus, it's just good practice). Each of these objects can be closed (freed) using their respective close methods as shown below.

...
public class SQLiteDemo
{
    ...
    public static void main(String[] args)
    {
        //STEP 1: Setup the Driver
        ...
 
        //STEP 2: Create connection to database using database URL
        ...
 
        //STEP 3: Execute SQL query using Statement object
        ...
 
        //STEP 4: Read results
        ...
 
        //STEP 5: Close stuff
        try
        {
            res.close();
            stmt.close();
            con.close();
        }
        catch(SQLException e)
        {
            System.out.println("Error closing connections: " + e.toString());
            return;
        }
 
    }
}

Step 6: Compile and Run

Now you can compile and run the code to check that it is working correctly. The following command will create a SQLiteDemo.class file, which you can then run using the java command.

javac SQLiteDemo.java

And to Run:

java -cp .;sqlitejdbc-v056.jar SQLiteDemo

Useful Links

SwinBrain Links

• Download the source code associated with this sample
• Read about the architecture of JDBC
• See how the ADO.NET API achieves the same result for the Java language in the Getting started with SQLite and ADO.NET article.

External Links

• API links for JDBC Driver Manager, Connection, Statement
• SQLite JDBC driver homepage.
• The official JDBC overview.
• JDBC Basics Java book 'Creating a Connection' tutorial, outlining how to create a connection to a database.