Monday, December 17, 2012

Using SQLite with JDBC

I had to implement an application which should utilize a portable database. For better portability the application was implemented in java. As my first choice I selected several flat files for storing data in a hierarchical manner .

At the run time of the application , read operations were much higher than the write operations. It took minutes for traversing and search through each file on a search operation.

Considering all above facts I selected "SQLite" for storing and managing data in my application. Though it was bit slow while feeding data to the application , it was pretty fast when reading data from the database.


Xerial SQLite JDBC driver is a good solution as a JDBC driver for SQLite. Lets see some examples on how to use above JDBC driver in java applications.

Creating Database File :
in order to create the DB file we can either use a tool like SQLiteAdministrator or write code(execute DDL code using JDBC) to create tables and other SQL objects inside the DB. In most of the cases we can use the first approach.
I have used SQLiteAdministrator for creating database file. For the sake of simplicity I have created a table with 2 columns.

CREATE TABLE [student] (
[name] VARCHAR(50)  NULL,
[description] TEXT  NULL
)


Creating a JDBC connection to the SQLite database : 
download your preferred  SQLite JDBC driver version from here. Add it to the build path of your java application.

Connection Manager(ConnectionMgr.java)
import java.sql.Connection;
import java.sql.DriverManager;


public class ConnectionMgr {
 
 public static Connection getConnection(){
     Connection connection = null;
  try {
   Class.forName("org.sqlite.JDBC");
   connection = DriverManager.getConnection("jdbc:sqlite:C:/Users/kanishkad/Desktop/sample.s3db");
  } catch (Exception e) {

  }
  return connection;
 }
}

It accepts absolute path as well as the relative path. in above code sample I have added the absolute path for the DB file.

The rest part is nothing other than using JDBC API.
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Random;

import org.sqlite.SQLiteJDBCLoader;


public class MainDemo {

 /**
  * @param args
  * @throws SQLException 
  */
 public static void main(String[] args) throws SQLException {
  addData();
  selectData();
 }
 
 public static void selectData() throws SQLException{
  long t1=System.currentTimeMillis();
  Connection conn=ConnectionMgr.getConnection();
  String query="SELECT * FROM student WHERE description LIKE \'%app%'";
  Statement prep=conn.createStatement();
  ResultSet rs=prep.executeQuery(query);
  while(rs.next()){
   String name=rs.getString(1);
   String desc=rs.getString(2);
   System.out.println(name+"\t"+desc);
  }
  conn.close();
  long t2=System.currentTimeMillis();
  System.out.println("Elapsed time : " + (t2-t1)/1000.0);
 }
 
 public static void addData() throws SQLException{
  long t1=System.currentTimeMillis();
  Connection conn=ConnectionMgr.getConnection();
  String query="INSERT INTO STUDENT(name,description) VALUES(?,?)";
  PreparedStatement prep=conn.prepareStatement(query);
  for (int j = 0; j < 1000; j++) {
   prep.setString(1,getRandomString(50));
   prep.setString(2,getRandomString(500));
   prep.addBatch();
   System.out.println(j);
  }
  prep.executeBatch();
  conn.close();
  long t2=System.currentTimeMillis();
  System.out.println("Elapsed time : " + (t2-t1)/1000.0);
 }
 
 public static String getRandomString(int size){
  StringBuilder stb=new StringBuilder();
  String allowedCahrs="QWERTYUIOPASDFGHJKLZXCVBNMqwertyuiopasdfghjklzxcvbnm1234567890";
  Random rand=new Random();
  for(int i=0;i<size;i++){
   int randIdx=rand.nextInt(allowedCahrs.length());
   stb.append(allowedCahrs.charAt(randIdx));
  }
  return stb.toString();
 }

}


Hope you may understand the JDBC part.

3 comments:

  1. Thanks for the article! But please get rid of the shaded font. Its very blurry!

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. database connectivity is work fine when i run project ,but when i run build jar file , that time not getting connection with database , please give solution for this problem

    ReplyDelete

© kani.stack.notez 2012 | Blogger Template by Enny Law - Ngetik Dot Com - Nulis