SQLite Programming in Java (Part 1)

Introduction
This tutorial is an introduction to SQLite programming in Java and it explains how we can establish a connection to SQLite database from Java application.

About SQLite
SQLite is a software library which implements SQL database engine, which is self-contained and serverless means no server that needs to be started, stopped or configured and is zero configuration means no setup or installation required.

Using SQLite with Java
The below given program demonstrate the steps required to establish connection to the sqlite database. For the sake of simplicity i have divided the program into different parts.

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

About DriverManager class
It is responsible for managing Java DataBase Connectivity (JDBC) drivers.

About Connection class
To establish connection with the database, java applications which make use of JDBC API has to obtain instance of Connection class first. The DriverManager class provides getConnection(String url) method which will create and return a Connection object. The url provides a way to identify the database using a protocol. The URL format for SQLite is given below.

jdbc:sqlite:dbname

In the above url you can replace string “dbname” with your database name. SQLite database is a file which ends with a “.db” extension and it will be cross platform.

About Statement class
Once you obtain connection to database, your next job is to create an instance of Statement class. Statement objects are used to obtain results by executing static SQL statements and they can be used to execute both Data Definition Language (DDL) commands and Data Manipulation Language (DML) commands. Statement class provides executeQuery(command) method to execute DML commands and executeUpdate(command) method to execute DDL commands.Simply statement object send the commands to the database and get results back.

The below given code block shows, how we can integrate this two together.

Class.forName("org.sqlite.JDBC");
connection = DriverManager.getConnection("jdbc:sqlite:test.db");
statement = connection.createStatement();

Why do we use forName(url) method
The forName(url) method is used to load any class whose complete name is specified as the url at run time. Here this will load the JDBC class from org.sqlite package.

What goes wrong and how should we treat them

  • When we call the forName(url) method, if the class is not available at run time it will throw ClassNotFoundException
  • When we call getConnection() method, if the url is null or any access error occur this will throw SQLException
  • When we call createStatement() method, if there are any access error occur or a closed connection this will throw SQLException

So we must write this three statements inside the try-catch-finally block.

How to close the connection and quite the application
Its simple, we close active statement and connection. Both Connection class and Statement class have a method called close() which releases connection or statement object’s JDBC and database resources once called. First you should close the statement by calling close() method of statement object and then close() method of connection object. The reverse order is not recommended.

Please find below the complete sample program

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

public class SQLiteJavaDemo {
    public static void main(String[] args) {
        try {
            Class.forName("org.sqlite.JDBC");
            Connection con = DriverManager.getConnection("jdbc:sqlite:demo.db");
            Statement st = con.createStatement();
            System.out.println("Connection created.");
            st.close();
            con.close();
            System.out.println("Connection closed.");
        } catch (SQLException | ClassNotFoundException ex) {
            System.err.println(ex);
        }
    }
}

In Part-2 we will see how to use statement object to execute sql commands.