Friday 28 January 2011

Java: Using SQLite

SQLite - is embedded relational database management system.In other words you can use sql database without server.

First of all you should download SQLite JDBC driver (or from dropbox) and add it into your project classpath. Next some code manipulations. Here is an example:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class SQLiteTest{
  private static Connection con;

public void run() throws Exception {

  // sqlite driver
  Class.forName("org.sqlite.JDBC");
  // database path, if it's new database,
  // it will be created in the project folder
  con = DriverManager.getConnection("jdbc:sqlite:mydb.db");
  Statement stat = con.createStatement();
  stat.executeUpdate("drop table if exists user");

  //creating table
  stat.executeUpdate("create table user(id integer,"
    + "firstName varchar(30)," + "age INT," + "sex varchar(15),"
    + "weight INT," + "height INT,"
    + "primary key (id));");

  // inserting data
  PreparedStatement prep = con
    .prepareStatement("insert into user values(?,?,?,?,?,?);");
  prep.setString(2, "John");
  prep.setString(3, "21");
  prep.setString(4, "male");
  prep.setString(5, "77");
  prep.setString(6, "185");
  prep.execute();

  // getting data
  ResultSet res = stat.executeQuery("select * from user");
  while (res.next()) {
     System.out.println(res.getString("id") + " " + res.getString("age")
              + " " + res.getString("firstName") + " "
              + res.getString("sex") + " " + res.getString("weight")
              + " " + res.getString("height"));
  }
}
  /**
  * @param args
  */
  public static void main(String[] args) {
    try {
      new SQLiteTest().run();
    } catch (Exception e) {
       e.printStackTrace();
      }
  }

}

References:
SQLite official docummentation

17 comments:

  1. Cool, plz give more tuts on Java and SQLite development. It will be interesting !

    ReplyDelete
  2. How can i move the ResultSet value as variable to a jsp page...?

    ReplyDelete
    Replies
    1. create a data model and and an array of the data model object. then create an object of the data model. using this object store the data in the variables of that object. add this object to the array and they. save the array in the session. so you can use the array any where in the session.

      Delete
  3. According best practies you should create model object, in our case Person, and return List in request, request.setAttribute("persons", persons)

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
  4. "id INT" is wrong. Must be "id INTEGER"...

    ReplyDelete
    Replies
    1. according documentation int is valid too, see it http://www.sqlite.org/datatype3.html

      Delete
  5. Then why result of System.out.println(res.getString("id") in your programm is "null?" Use "integer" and it will be "1".

    ReplyDelete
    Replies
    1. You're right for primary key it's wrong - " Other integer type names like "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary key column to behave as an ordinary table column with integer affinity and a unique index, not as an alias for the rowid." http://www.sqlite.org/lang_createtable.html#rowid

      Delete
    2. Yes, a little but important exclusion...

      Delete
  6. how insert data in sqlite db from method?

    ReplyDelete
  7. the upload import files is already in eclipse or it download these files and place in eclipse folder

    ReplyDelete
  8. i have a problem..
    If an action event has ocurred in jinternal frame how to update that to a sqlite existing database....
    Please reply...thanks in advance..

    ReplyDelete
  9. hiii

    If i run the above code i am getting the following error

    java.sql.SQLException: No suitable driver found for jdbc:sqlite:mydb.db

    Please anyone help me to solve my problem

    ReplyDelete
  10. how to export jar with sqlite file in it? so i can transfer the jar file to any computer with the database embbeded and accessible by the program

    ReplyDelete