fbpx
Responsive Ad Area
Home » Blog » SQLite: JDBC and SQLite

Share This Post

Java / Main Slider / Tutorial

SQLite: JDBC and SQLite

SQLite: JDBC and SQLite

SQLite Banner

SQLite and Java

First, you must go to download page and get the binaries for either 32 or 64 bit, placing them in the system path. If you don’t know how to do that, google adding folders to the system path. There will be an SQLite.exe file. Run it as a command to verify that it is in the path and working. But for Java coding get the jar file at Jar file. A very good resource you will need is A good tutorial and Another tutorial.

Working with Java and Files Tutorial Trail
Left ArrowSerialize Java to XMLH2 DB and Javaright nav arrow

Setup for simple example

Download the SQLite jar file from BitBucket SQL downloads. Put it and the source below in the same folder. Set the classpath to include that jar file and the ‘.’ current folder or use -cp option with ‘sqlite-jdbc-3.21.0.jar;.’ when using java.exe.

One Step Create, Write and Read from DB

First, we create the database by opening it. Then we create the table. I didn’t capitalize the names but I think style guide says you should. Next, we do some simple insert queries with sample data. Finally, we do a select query to get the data back and output it to the console. I could show you how to delete or update a record too but for now, this is what you get.

import java.sql.*;

public class ReadWriteSQLite{
 String url = "jdbc:sqlite:C://dev/java/db/test2.db";
 String sqlTableCreate = "CREATE TABLE datarecords (\n"
      + "	id int PRIMARY KEY NOT NULL,\n"
      + "	name text,\n"
      + "	weight real,\n"
      + "  date text,\n" // date ("YYYY-MM-DD HH:MM:SS.SSS")
      + "  active int\n" // boolean
      + ")";
 public ReadWriteSQLite(){
  try {
   Class.forName("org.sqlite.JDBC"); 
   Connection conn = DriverManager.getConnection(url);
   Statement stmt =conn.createStatement();
   stmt.executeUpdate(sqlTableCreate);
   String sqlInsertUpdate = "INSERT INTO datarecords (id,name,weight,date,active)" +
     "VALUES (1,'John Brown',175.2342, '2008-12-13 23:53:23.353',1)";
   stmt.executeUpdate(sqlInsertUpdate);
   sqlInsertUpdate = "INSERT INTO datarecords (id,name,weight,date,active)" +
     "VALUES (2,'George Washington',248.9263, '1776-03-14 02:15:22.543',0)";
   stmt.executeUpdate(sqlInsertUpdate);
   //stmt  = conn.createStatement();
   String sqlSelectQuery = "SELECT * FROM datarecords";
   ResultSet rs    = stmt.executeQuery(sqlSelectQuery);
 while (rs.next()) {
  System.out.println(rs.getInt("id") +  "\t" + 
      rs.getString("name") + "\t" +
      rs.getDouble("weight") + "\t" +
      rs.getString("date") + "\t" +
      rs.getInt("active") + "\t"
      );
 }	
  } catch (SQLException sqle) {
   sqle.printStackTrace();
  } catch (ClassNotFoundException cnfe){
      cnfe.printStackTrace();
  }
 }
 
 
 public static void main(String args[]){
  new ReadWriteSQLite();
 }
}

The Output

c:\dev\java\arksoft\post\files>java -cp "sqlite.jar;." ReadWriteSQLite
1       John Brown      175.2342        2008-12-13 23:53:23.353 1
2       George Washington       248.9263        1776-03-14 02:15:22.543 0

 

Share This Post

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>