How to connect to an SQLite database using a JDBC program?

A. SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. It is a database, which is zero-configured, which means like other databases you do not need to configure it in your system.

SQLite engine is not a standalone process like other databases, you can link it statically or dynamically as per your requirement with your application. SQLite accesses its storage files directly.

The URL to connect with SQLite database is jdbc:sqlite:test.db and, the driver class name to connect to it is org.sqlite.JDBC.

Before you proceed with the example:

• Download latest version of sqlite-jdbc-(VERSION).jar from sqlite-jdbcrepository.

• Add downloaded jar file sqlite-jdbc-(VERSION).jar in your class path, or you can use it along with -classpath option as explained in the following examples.

Example

Assume we have a table named employee_data in SQLite database with 4 records as shown below:

ID      NAME       AGE    ADDRESS     SALARY
------- -------- ------- ---------- ----------
1        Paul       32   California   20000.0
2        Allen      25   Texas        15000.0
3        Teddy      23   Norway       20000.0
4        Mark       25   Richmond     65000.0

Following JDBC program establishes connection with SQLite database, retrieves the content of the table named employee_data and displays it.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ConnectToSQLite {
   public static void main(String[] args) throws Exception {
      //Registering the Driver
      DriverManager.registerDriver(new org.sqlite.JDBC());
      //Getting the connection
      String url = "jdbc:sqlite:test.db";
      Connection con = DriverManager.getConnection(url, "root", "password");
      System.out.println("Connection established......");
      //Query to retrieve records
      String query = "Select * from employee_data";
      //Executing the query
      ResultSet rs = stmt.executeQuery(query);
      System.out.println("Contents of the employee_data table:");
      while(rs.next()) {
         System.out.print("ID: "+rs.getInt("ID")+", ");
         System.out.print("Name: "+rs.getString("Name")+", ");
         System.out.print("Age: "+rs.getInt("Age")+", ");
         System.out.print("Salary: "+rs.getInt("Salary")+", ");
         System.out.print("Address: "+rs.getString("Address"));
         System.out.println();
      }
   }
}

Output

Connections established......
Contents of the employee_data table:
ID: 1, Name: Paul, Age: 32, Salary: 20000, Address: California
ID: 2, Name: Allen, Age: 25, Salary: 15000, Address: Texas
ID: 3, Name: Teddy, Age: 23, Salary: 20000, Address: Norway
ID: 4, Name: Mark, Age: 25, Salary: 65000, Address: Rich-Mond