Latest web development tutorials

SQLite - Java

installation

In the Java program before using SQLite, we need to make sure the machine has SQLite JDBC Driver driver and Java. You can view the Java tutorial on how to install Java on your computer. Now, let's see how to install SQLite JDBC driver on the machine.

  • From sqlite-jdbc download the latest version ofsqlite-jdbc- (VERSION) .jarlibraries.

  • Download add in your class path jar filesqlite-jdbc- (VERSION) .jar,or use it in -classpath options, which will be explained in the examples that follow.

In the lower part of the learning knowledge before, you must have a preliminary understanding of the concept of Java JDBC. If you have not yet understand the relevant knowledge, it is recommended that you spend the first half hour of learning relevant knowledge under JDBC tutorial that will help you learn the following explanation of knowledge.

Connect to the database

The following Java program shows how to connect to an existing database. If the database does not exist, it is created, and finally returns a database object.

import java.sql *.;

public class SQLiteJDBC
{
  public static void main (String args [])
  {
    Connection c = null;
    try {
      Class.forName ( "org.sqlite.JDBC");
      c = DriverManager.getConnection ( "jdbc: sqlite: 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, create our databasetest.db in the current directory.You can change the path as needed. We assume that the next version of the JDBC driver current path is availablesqlite-jdbc-3.7.2.jar.

$ Javac SQLiteJDBC.java
$ Java -classpath ":. Sqlite-jdbc-3.7.2.jar" SQLiteJDBC
Open database successfully

If you want to use a Windows machine, you can follow the following compile and run your code as shown:

$ Javac SQLiteJDBC.java
$ Java -classpath ";. Sqlite-jdbc-3.7.2.jar" SQLiteJDBC
Opened database successfully

Create a table

The following Java program will be used to create a table in the database previously created:

import java.sql *.;

public class SQLiteJDBC
{
  public static void main (String args [])
  {
    Connection c = null;
    Statement stmt = null;
    try {
      Class.forName ( "org.sqlite.JDBC");
      c = DriverManager.getConnection ( "jdbc: sqlite: 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 creates COMPANY table intest.db, the final list of files as follows:

-rw-r -. r-- 1 root root 3201128 Jan 22 19:04 sqlite-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

The following Java code shows how to create records in the COMPANY table created above:

import java.sql *.;

public class SQLiteJDBC
{
  public static void main (String args [])
  {
    Connection c = null;
    Statement stmt = null;
    try {
      Class.forName ( "org.sqlite.JDBC");
      c = DriverManager.getConnection ( "jdbc: sqlite: 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, 'California', 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 the above program is compiled and executed, it will create in the COMPANY table for a given record, and displays the following two lines:

Opened database successfully
Records created successfully

SELECT operations

The following Java program shows how to get from the COMPANY table created earlier and displays the record:

import java.sql *.;

public class SQLiteJDBC
{
  public static void main (String args [])
  {
    Connection c = null;
    Statement stmt = null;
    try {
      Class.forName ( "org.sqlite.JDBC");
      c = DriverManager.getConnection ( "jdbc: sqlite: 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 produces the following result:

Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
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

The following Java code shows how to use the UPDATE statement to update any records, then get from COMPANY table and display the updated record:

import java.sql *.;

public class SQLiteJDBC
{
  public static void main (String args [])
  {
    Connection c = null;
    Statement stmt = null;
    try {
      Class.forName ( "org.sqlite.JDBC");
      c = DriverManager.getConnection ( "jdbc: sqlite: 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 produces the following result:

Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
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

The following Java code shows how to use the DELETE statement deletes any records, and then taken from the COMPANY table and displays the remaining recording:

import java.sql *.;

public class SQLiteJDBC
{
  public static void main (String args [])
  {
    Connection c = null;
    Statement stmt = null;
    try {
      Class.forName ( "org.sqlite.JDBC");
      c = DriverManager.getConnection ( "jdbc: sqlite: 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 produces the following result:

Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
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