CRUD manipulation in sqlite with JAVA JDBC

Using sqlite with java JDBC

SQLite is a relational database management system contained in a C library. In contrast to many other database management systems, SQLite is not a client–server database engine. Rather, it is embedded into the end program [Wikipedia].

SQLite Connection in JAVA using sqlite JDBC

Like any other connection with mysql, oracle or sqlserver you can do it this way:

  1. Download JAVA JDBC for sqlite from [sqlite-jdbc]
  2. Import it into your project
  3. Use it in java
import java.sql.*;

public class SQLite {

    public static void main(String[] args) {
        Connection c = null;
        try{
            String dbPath = "sqlite.db";
            //create a connection
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:"+dbPath);
            System.out.println("Connection accepted");
        }catch(Exception e){
            System.out.println("Can not connect to the db" + e);
            System.exit(0);
        }
        if(c!=null){
            try {
                c.close();
            }catch (Exception e){
                System.out.println(e);
            }
        }
    }
}

Table creation with SQLite in JAVA

For table creation you can use any tool for generating sqlite database or just use normal sql to create your database, it really depends on your situation but for now we will handle it the JAVA way. So in order to create our database we will need to verify if the database exists else we will create that database and create our table with sql commands using java. In this example we will create a table User with an auto incremented id and a name.

import java.io.File;
import java.sql.*;

public class SQLite {

    public static void main(String[] args) {
        Connection c = null;
        try{
            String dbPath = "sqlite.db";
            boolean exist = true;
            if(!new File(dbPath).exists()){
                exist = false;
            }
            //create a connection
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:"+dbPath);

            //create table
            if(!exist){
                String tableQuery= "CREATE TABLE User (id integer primary key, name varchar(255))";
                Statement stmt =c.createStatement();
                stmt.executeUpdate(tableQuery);
                stmt.close();
            }
            System.out.println("Connection accepted");
        }catch(Exception e){
            System.out.println("Can not connect to the db" + e);
            System.exit(0);
        }
        if(c!=null){
            try {
                c.close();
            }catch (Exception e){
                System.out.println(e);
            }
        }
    }
}

Insert, Delete and Update data with SQLite jdbc in JAVA using Prepared Statement

1 What is a Prepared Statement

In database management systems a prepared statement or parameterized statement is a feature used to execute the same or similar database statements repeatedly with high efficiency. Typically used with SQL statements such as queries or updates. The typical workflow of using a prepared statement is as follows:

  1. Prepare: The application creates the statement template as "INSERT INTO products (name, price) VALUES (?, ?);" and send it to the DBMS. Certain values are left unspecified, called parameters, placeholders or bind variables (labelled "?")
  2. Then, the DBMS compiles (parses, optimizes and translates) the statement template, and stores the result without executing it.
  3. Execute: At a later time, the application supplies (or binds) values for the parameters of the statement template, and the DBMS executes the statement (possibly returning a result). The application may execute the statement as many times as it wants with different values.

2 WHY using a Prepared Statement

As compared to executing statements directly, prepared statements offer two main advantages:

  • Performance: The overhead of compiling the statement is incurred only once, although the statement is executed multiple times. However not all optimization can be performed at the time the statement template is compiled, for two reasons: the best plan may depend on the specific values of the parameters, and the best plan may change as tables and indexes change over time.
  • Security: Prepared statements are resilient against SQL injection because values which are transmitted later using a different protocol are not compiled like the statement template. If the statement template is not derived from external input, SQL injection cannot occur.

2 how to use a Prepared Statement in JAVA

import java.io.File;
import java.sql.*;

public class SQLite {

    public static void main(String[] args) {
        Connection c = null;
        try{
            String dbPath = "sqlite.db";
            boolean exist = true;
            if(!new File(dbPath).exists()){
                exist = false;
            }
            //create a connection
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:"+dbPath);

            //create table
            if(!exist){
                String tableQuery= "CREATE TABLE User (id integer primary key, name varchar(255))";
                Statement stmt =c.createStatement();
                stmt.executeUpdate(tableQuery);
                stmt.close();
            }

            //insert data
            String createQuery= "insert into User('name') values (?)";
            PreparedStatement preparedStatement = c.prepareStatement(createQuery);
            preparedStatement.setString(1, "yamicode" + Math.random());
            preparedStatement.executeUpdate();
            preparedStatement.close();
            
        }catch(Exception e){
            System.out.println("Can not connect to the db" + e);
            System.exit(0);
        }
        if(c!=null){
            try {
                c.close();
            }catch (Exception e){
                System.out.println(e);
            }
        }
    }
}

Read data with SQLite jdbc in JAVA

In the following case you can use a prepared Statement, but since in my example i don't have any parameter i used a simple statement.

import java.io.File;
import java.sql.*;

public class SQLite {

    public static void main(String[] args) {
        Connection c = null;
        try{
            String dbPath = "sqlite.db";
            boolean exist = true;
            if(!new File(dbPath).exists()){
                exist = false;
            }

            //create a connection
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:"+dbPath);

            //create table
            if(!exist){
                String tableQuery= "CREATE TABLE User (id integer primary key, name varchar(255))";
                Statement stmt =c.createStatement();
                stmt.executeUpdate(tableQuery);
                stmt.close();
            }

            //insert data
            String createQuery= "insert into User('name') values (?)";
            PreparedStatement preparedStatement = c.prepareStatement(createQuery);
            preparedStatement.setString(1, "yamicode" + Math.random());
            preparedStatement.executeUpdate();
            preparedStatement.close();

            //read data
            String readQuery= "select * from User";
            Statement stmtRead =c.createStatement();
            ResultSet resultSet = stmtRead.executeQuery(readQuery);
            while(resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                System.out.println("User id: " + id + " name: " + name);
            }
            stmtRead.close();

        }catch(Exception e){
            System.out.println("Can not connect to the db" + e);
            System.exit(0);
        }
        if(c!=null){
            try {
                c.close();
            }catch (Exception e){
                System.out.println(e);
            }
        }
    }
}