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.

Thursday, December 13, 2012

Monitoring JDBC Performance with JDbMonitor

There may be some situations where we need to monitor our JDBC calls. There are few mechanisms available for monitoring JDBC calls. I have tried JDbMonitor in some of my projects because it is easy to use and remove without any code change.

JDbMonitor works as a proxy between our application the JDBC driver. Essentially there may be some performance issue if we use this in a production environment. JDbMonitor provides a GUI application to monitor our JDBC calls.
In the current version(1.1) it supports
  • Statemt
  • PreparedStatement
but callable and batch operations are not supported yet.

Lets have a look on how to configure JDbMonitor to monitor our JDBC calls in step by step.
Step 1 : Add (jdbmonitor-driver.jar,jdbmonitor-common.jar and the JDBC driver) to your class path.
In my case I am using MySQL driver.


Step 2 : Point JDBC Driver to com.jdbmonitor.MonitorDriver where the connection is created.

package com.kani;

import java.sql.Connection;
import java.sql.DriverManager;

public class ConnectionManager {
 
 private static final String HOST="localhost";
 private static final String DBNAME="test_db";
 private static final String USERNAME="root";
 private static final String PASSWORD="";
 

    public static Connection getNewDBConnection() {
        Connection conn = null;
        try {
            Class.forName("com.jdbmonitor.MonitorDriver");
            conn = DriverManager.getConnection("jdbc:mysql://"+HOST+"/"+DBNAME, USERNAME, PASSWORD);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
}

Step 3 : Run jdbmonitor client program. and connect(f2)
it should show all the supported JDBC operations if it is connected successfully.

Note : If you are using a property file you can remove JDBmonitor without any code change. And it is possible to monitor underlying Hibernate JDBC operations easily.

 
© Copyright 2035 kani.stack.notez
Theme by Yusuf Fikri