Tutorial: SQLite and Processing, Part II

--D. Thiebaut 17:01, 30 June 2011 (EDT)
Updated --D. Thiebaut (talk) 17:03, 31 July 2013 (EDT)

---

This is the second part of a quick tutorial to accessing an SQLite database from Processing. Check out Part 1 for how to run the example in Processing's IDE. This tutorial uses Processing 2.

Setup

• First follow the steps of this great tutorial for creating Processing applications with Eclipse. Remember the steps for adding the core.jar library to your Eclipse project. You will need to repeat them for every new Processing project you want to create. The other option you'll have is to copy paste the skeleton project into another new project, in which case the core library should follow automatically into your new project.
• Note: sometimes it is hard to get the Build Path option for the core.jar file. If this option does not appear, a work-around is to right click on the project, and pick Properties, Java Build Path, then click on the Libraries tab, then Add Jar, pick the core.jar file, and then click Ok.

Download the SQLite Library for Processing

• We download the SQLite library recommended on the Procssing/libraries Website: the Bezier SQLib library.
• We need 3 files from the library, which is setup to use inside a sketch, but with Eclipse we need to reorganize them slightly.
• In a Terminal window (we are working on a Mac, the procedure is similar under Windows):

  cd ~/Downloads
  ls
  BezierSQLib-0.2.0.zip             (the file we just downloaded from http://processing.org/reference/libraries/)

  mkdir Bezier
  mv BezierSQLib-0.2.0.zip  Bezier
  cd Bezier
  unzip BezierSQLib-0.2.0.zip 
  ls 

  BezierSQLib-0.2.0.zip	examples/		library.properties
  documentation/		library/		source/

  cp library/sqlite-jdbc-3.7.2.jar ..         (copy the sqlite jdbc file in ~/Download folder)
  cp source/de/bezier/data/sql/SQL*.java ..   (copy SQL.java and SQLite.java to ~/Download folder)
  cp source/de/bezier/data/sql/mapper/NameMapper.java ..
  cp source/de/bezier/data/sql/mapper/UnderScoreToCamelCaseMapper.java ..

The Download folder should now contain six files

  ls -1 ~/Downloads
  Bezier/
  NameMapper.java
  SQLite.java
  SQL.java
  sqlite-jdbc-3.7.2.jar
  UnderScoreToCamelCaseMapper
 

• Edit the java files and modify them to remove all references to the package de.bezier.etc . A copy of the modified files is available here.

A New Eclipse Project

Create the Initial Applet

• Open Eclipse and create a new Java Project
• Create a new class in your package, and call it Main.java.
• In the Edit Window, enter the following code:

package tutorial1;

import processing.core.*;

public class Main extends PApplet {

	public void setup() {
		// define the window size, make graphics softer, and make
		// the background white
		size(600, 600);
		smooth();
		background(255);
	}

	public void draw() {
                // erase screen
		background(255);

                // change color of circle paint depending on mouse button
		if (mousePressed) 
			fill(0);
		else 
			fill(255);
		
                // draw a circle where the mouse is located
		ellipse(mouseX, mouseY, 80, 80);
	}
}

• Click on the white triangle in a green circle under the top menu, and run the Applet. Notice that a circle should appear and follow the mouse. Notice also that it should change color as you click the mouse button.

If this works, then the Processing setup is working well

Import SQLite Files into the Project

• With the new project selected, click on File/Import and pick File System.
• Enter ~/Downloads in the From directory field
• Select the files SQL.java, SQLite.java, UnderScoreToCamelCaseMapper.java, NameMapper.java , and sqlite-jdbc-3.7.2.jar in the file window, and import them.
• Once the files are in the project drag and drop the java files in the (default package) area.
• Right-click on the file sqlite-jdbc-3.7.2.jar and click on Build Path, then Add to Build-Path.

Create an SQLite Database

• By default Processing expects data file in a directory called data, that is usually a first-level directory in the sketch. With Eclipse we simply need to create a data folder in the source folder that contains the (default package).
• Once the data folder is created, import into it the test.db SQLite database from the Bezier archive. Its location is ~/Downloads/Bezier/examples/SQLite_example1/data, and its name test.db. Simply import it into the newly created data directory using File, Import, File System.

New Test Code

• Replace the Main class contents with this new pair of setup()/draw() functions taken from example1 of the Bezier archive:

public class Testing extends PApplet {

	SQLite db;
	
	public void setup() {
	    size( 100, 100 );

	    db = new SQLite( this, "test.db" );  // open database file

	    if ( db.connect() ) {
	        // list table names
	        db.query( "SELECT name as \"Name\" FROM SQLITE_MASTER where type=\"table\"" );
	        
	        while (db.next()) 
	            System.out.println( db.getString("Name") );
	        
	        // read all in table "table_one"
	        db.query( "SELECT * FROM table_one" );
	        
	        while (db.next()) 
	            System.out.println( db.getString("field_one") + "\n" + db.getInt("field_two") );
	    }
	    
	    noLoop();
	}
	
	public void draw() {
		
	}
}

Testing Access to the Database

• Simply run the applet and verify that the output in the Console window looks similar to the one shown below:

Using this database: /Users/thiebaut/.../bin/data/test.db
Using driver org.sqlite.JDBC 3.7
table_one
foo
-100 
bar
99

• If you get this output, then you have all the steps necessary to develop an SQLite Processing applet.

Note a strange phenomenon associated with Eclipse: when creating the data subfolder in the src folder also creates a data subfolder in the bin folder, which is the one that is actually accessed by the program. If you want your applet to access a database that is located in another location on your computer, simply create a link to it in the bin/data directory.

Some SQLite-command examples

Below are some basic CREATE TABLE, INSERT INTO, or SELECT FROM commands used to test the setup of our database and prepare some of the operations we may have to perform.

	SQLite db;  // the database that is initialized in some other part of the code

	int lastId = -1;
	
	// create the table if it doesn't exist
	String query = "CREATE TABLE IF NOT EXISTS dateIdTable "
			+ "( Id INTEGER PRIMARY KEY, date DATE);";
	db.query( query );

        // insert a new entry.  NULL is used for the auto-increment index
	query = "INSERT INTO dateIdTable VALUES( NULL, DATE('now') );";
	db.query( query );

        // display content of last inserted data in table
	query = "SELECT last_insert_rowid() as last FROM dateIdTable;";
	db.query( query );
	while ( db.next() ) {
		lastId = db.getInt( "last" );
		break;
	}
	
        // create a new table and include the lastId as part of its name 
	String table = "freq_" + lastId;
	String query = "CREATE TABLE IF NOT EXISTS " + table   
			+ " ( Id INTEGER PRIMARY KEY, time TIME, count INTEGER, millisecs INTEGER );";
	db.query( query );

        // insert some data in the new table
	query = "INSERT INTO " + table 
				+ String.format( " VALUES( NULL, TIME('now'), %d, %d );", count, millisecs );
	db.query( query );
	
	// get the names of all the tables in the database 
	ArrayList<String> tables = new ArrayList<String>();

	// list table names
	db.query("SELECT name as \"Name\" FROM SQLITE_MASTER where type=\"table\"");
	while (db.next()) { 
		String table = db.getString( "Name" );
		tables.add( table );
	}
		
	for ( String table: tables ) 
		System.out.println( "\nFound table: " + table );