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 );