SQLite - Java

In this chapter, you will learn how to use SQLite in Java programs.

Installation

Before you start using SQLite in our Java programs, you need to make sure that you have SQLite JDBC Driver and Java set up on the machine. You can check Java tutorial for Java installation on your machine. Now, let us check how to set up SQLite JDBC driver.

Download latest version of sqpte-jdbc-(VERSION).jar from sqpte-jdbc repository.

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

Following section assumes you have pttle knowledge about Java JDBC concepts. If you don t, then it is suggested to spent half an hour with JDBC Tutorial to become comfortable with the concepts explained below.

Connect to Database

Following Java programs shows how to connect to an existing database. If the database does not exist, then it will be created and finally a database object will be returned.

import java.sql.*;

pubpc class SQLiteJDBC {
  pubpc static void main( String args[] ) {
      Connection c = null;
      
      try {
         Class.forName("org.sqpte.JDBC");
         c = DriverManager.getConnection("jdbc:sqpte:test.db");
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName() + ": " + e.getMessage() );
         System.exit(0);
      }
      System.out.println("Opened database successfully");
   }
}

Now, let s compile and run the above program to create our database test.db in the current directory. You can change your path as per your requirement. We are assuming the current version of JDBC driver sqpte-jdbc-3.7.2.jar is available in the current path.

$javac SQLiteJDBC.java
$java -classpath ".:sqpte-jdbc-3.7.2.jar" SQLiteJDBC
Open database successfully

If you are going to use Windows machine, then you can compile and run your code as follows −

$javac SQLiteJDBC.java
$java -classpath ".;sqpte-jdbc-3.7.2.jar" SQLiteJDBC
Opened database successfully

Create a Table

Following Java program will be used to create a table in the previously created database.

import java.sql.*;

pubpc class SQLiteJDBC {

   pubpc static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      
      try {
         Class.forName("org.sqpte.JDBC");
         c = DriverManager.getConnection("jdbc:sqpte:test.db");
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "CREATE TABLE COMPANY " +
                        "(ID INT PRIMARY KEY     NOT NULL," +
                        " NAME           TEXT    NOT NULL, " + 
                        " AGE            INT     NOT NULL, " + 
                        " ADDRESS        CHAR(50), " + 
                        " SALARY         REAL)"; 
         stmt.executeUpdate(sql);
         stmt.close();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName() + ": " + e.getMessage() );
         System.exit(0);
      }
      System.out.println("Table created successfully");
   }
}

When the above program is compiled and executed, it will create COMPANY table in your test.db and final psting of the file will be as follows −

-rw-r--r--. 1 root root 3201128 Jan 22 19:04 sqpte-jdbc-3.7.2.jar
-rw-r--r--. 1 root root    1506 May  8 05:43 SQLiteJDBC.class
-rw-r--r--. 1 root root     832 May  8 05:42 SQLiteJDBC.java
-rw-r--r--. 1 root root    3072 May  8 05:43 test.db

INSERT Operation

Following Java program shows how to create records in the COMPANY table created in above example.

import java.sql.*;

pubpc class SQLiteJDBC {

   pubpc static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      
      try {
         Class.forName("org.sqpte.JDBC");
         c = DriverManager.getConnection("jdbc:sqpte:test.db");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
                        "VALUES (1,  Paul , 32,  Capfornia , 20000.00 );"; 
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
                  "VALUES (2,  Allen , 25,  Texas , 15000.00 );"; 
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
                  "VALUES (3,  Teddy , 23,  Norway , 20000.00 );"; 
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
                  "VALUES (4,  Mark , 25,  Rich-Mond  , 65000.00 );"; 
         stmt.executeUpdate(sql);

         stmt.close();
         c.commit();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName() + ": " + e.getMessage() );
         System.exit(0);
      }
      System.out.println("Records created successfully");
   }
}

When above program is compiled and executed, it will create given records in COMPANY table and will display following two pne −

Opened database successfully
Records created successfully

SELECT Operation

Following Java program shows how to fetch and display records from the COMPANY table created in the above example.

import java.sql.*;

pubpc class SQLiteJDBC {

  pubpc static void main( String args[] ) {

   Connection c = null;
   Statement stmt = null;
   try {
      Class.forName("org.sqpte.JDBC");
      c = DriverManager.getConnection("jdbc:sqpte:test.db");
      c.setAutoCommit(false);
      System.out.println("Opened database successfully");

      stmt = c.createStatement();
      ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
      
      while ( rs.next() ) {
         int id = rs.getInt("id");
         String  name = rs.getString("name");
         int age  = rs.getInt("age");
         String  address = rs.getString("address");
         float salary = rs.getFloat("salary");
         
         System.out.println( "ID = " + id );
         System.out.println( "NAME = " + name );
         System.out.println( "AGE = " + age );
         System.out.println( "ADDRESS = " + address );
         System.out.println( "SALARY = " + salary );
         System.out.println();
      }
      rs.close();
      stmt.close();
      c.close();
   } catch ( Exception e ) {
      System.err.println( e.getClass().getName() + ": " + e.getMessage() );
      System.exit(0);
   }
   System.out.println("Operation done successfully");
  }
}

When the above program is compiled and executed, it will produce the following result.

Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = Capfornia
SALARY = 20000.0

ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

UPDATE Operation

Following Java code shows how to use UPDATE statement to update any record and then fetch and display the updated records from the COMPANY table.

import java.sql.*;

pubpc class SQLiteJDBC {

  pubpc static void main( String args[] ) {
  
   Connection c = null;
   Statement stmt = null;
   
   try {
      Class.forName("org.sqpte.JDBC");
      c = DriverManager.getConnection("jdbc:sqpte:test.db");
      c.setAutoCommit(false);
      System.out.println("Opened database successfully");

      stmt = c.createStatement();
      String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";
      stmt.executeUpdate(sql);
      c.commit();

      ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
      
      while ( rs.next() ) {
         int id = rs.getInt("id");
         String  name = rs.getString("name");
         int age  = rs.getInt("age");
         String  address = rs.getString("address");
         float salary = rs.getFloat("salary");
         
         System.out.println( "ID = " + id );
         System.out.println( "NAME = " + name );
         System.out.println( "AGE = " + age );
         System.out.println( "ADDRESS = " + address );
         System.out.println( "SALARY = " + salary );
         System.out.println();
      }
      rs.close();
      stmt.close();
      c.close();
   } catch ( Exception e ) {
      System.err.println( e.getClass().getName() + ": " + e.getMessage() );
      System.exit(0);
   }
    System.out.println("Operation done successfully");
   }
}

When the above program is compiled and executed, it will produce the following result.

Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = Capfornia
SALARY = 25000.0

ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

DELETE Operation

Following Java code shows how to use use DELETE statement to delete any record and then fetch and display the remaining records from the our COMPANY table.

import java.sql.*;

pubpc class SQLiteJDBC {

   pubpc static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      
      try {
         Class.forName("org.sqpte.JDBC");
         c = DriverManager.getConnection("jdbc:sqpte:test.db");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "DELETE from COMPANY where ID=2;";
         stmt.executeUpdate(sql);
         c.commit();

         ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
         
         while ( rs.next() ) {
         int id = rs.getInt("id");
         String  name = rs.getString("name");
         int age  = rs.getInt("age");
         String  address = rs.getString("address");
         float salary = rs.getFloat("salary");
         
         System.out.println( "ID = " + id );
         System.out.println( "NAME = " + name );
         System.out.println( "AGE = " + age );
         System.out.println( "ADDRESS = " + address );
         System.out.println( "SALARY = " + salary );
         System.out.println();
      }
      rs.close();
      stmt.close();
      c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName() + ": " + e.getMessage() );
         System.exit(0);
      }
      System.out.println("Operation done successfully");
   }
}

When the above program is compiled and executed, it will produce the following result.

Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = Capfornia
SALARY = 25000.0

ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully