SQLite and Java SE 7

1) Download SQLite files to c:\sqlite
1.1) Download the following files from http://www.sqlite.org/download.html :
a) http://www.sqlite.org/2013/sqlite-shell-win32-x86-3071602.zip
b) http://www.sqlite.org/2013/sqlite-dll-win32-x86-3071602.zip




1.2) Extract the files to c:\sqlite
1.2) Run Console Command.
1.3) Type: c:\sqlite\sqlite3
1.4) (in the sqlite Prompt) Type: .quit





2) Create a sample database, employee.db
2.1) Run Command Console
Type: cd c:\sqlite
Type: sqlite3 employee.db
(In Sqlite Prompt) Type: .database


3) Create Table.
Type: CREATE TABLE employeedetails (EMPNAME varchar(20));


Type: .table (to check that table exists)




4) Insert records.
Type: INSERT INTO employeedetails (EMPNAME) VALUES ('EMPNAME1');
Type: INSERT INTO employeedetails (EMPNAME) VALUES ('EMPNAME2');
Type: INSERT INTO employeedetails (EMPNAME) VALUES ('EMPNAME3');


5) Retrieve records.
Type: SELECT EMPNAME FROM employeedetails;



6) Get JDBC for Sqlite
6.1) Download sqlitejdbc from http://sqlitebot.googlecode.com/files/sqlitejdbc-v056.jar
6.2) Save the jar file under c:\learnjava\sqlite


7) Create ConnectSQLite.java
7.1) Create ConnectSQLite.java under c:\learnjava\sqlite
7.2) Edit ConnectSQLite.java:

/**
 * ConnectSQLite.java
 */

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
/**jav
 * @author www.javaworkspace.com
 * 
 */
public class ConnectSQLite {
 public static void main(String[] args) {
  Connection connection = null;
  ResultSet resultSet = null;
  Statement statement = null;
  try {
   Class.forName("org.sqlite.JDBC");
   connection = DriverManager
     .getConnection("jdbc:sqlite:C:\\sqlite\\employee.db");
   statement = connection.createStatement();
   resultSet = statement
     .executeQuery("SELECT EMPNAME FROM EMPLOYEEDETAILS");
   while (resultSet.next()) {
    System.out.println("EMPLOYEE NAME:"
      + resultSet.getString("EMPNAME"));
   }
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   try {
    resultSet.close();
    statement.close();
    connection.close();
   } catch (Exception e) {
    e.printStackTrace();
   }
  }
 }
}

8) Compile and Run.
(Assume that you have registered Java Bin as a path in Windows Environment Variable)

8.1) Type: cd c:\learnjava\sqlite

8.2) Type: javac ConnectSQLite.java

8.3) Type: java -cp "c:\learnjava\sqlite\sqlitejdbc-v056.jar";. ConnectSQLite

8.4) You will get the output slightly similar to Step 5.




Reference:
http://www.javaworkspace.com/connectdatabase/connectSQLite.do