Kind of as reply to
Tom's mini howto for using SQLite with Processing, but also since I've been dabbling with it myself recently, here's an alternative take on using an embedded database from within Processing (or more generally in Java)...
Mainly due to Java's strong focus on server side development, over the past few years there have been several large scale community efforts to create Java native database engines, which don't rely on underlying C code, are high performant and portable: the essence of the Java way. The other benefit is that such DB engines can be embedded and distributed with your application without requiring any further installation. One such development effort is
Apache Derby, a project which started in 1996, swapped owners several times (amongst them IBM) and then became an incubator project at
Apache in 2004. Sun also joined the project and has been bundling it as library (under the name
JavaDB) as part of the JDK (v6+) since December 2006. So in other words if you have Java6 installed you also should have Derby. But even if you don't (for example Mac users), you can
download Derby from here and unzip it to any folder on your hard drive.
The following deals with setting up Processing to work with Derby:
- Create a new folder structure /derby/library within Processing's /libraries folder
- Copy the file derby.jar from Derby's /lib folder into the newly created folder
Before we can start using the database now we first need to create a new database. Derby comes with its own commandline client "ij" which is located in the
/bin directory of the main Derby install dir (If you're going to use this tool more often it would make sense to add this
/bin directory to your system path).
Launch
ij from the commandline and then create a new database with this command:
ij> connect 'jdbc:derby:/path/to/database;create=true';
Databases are just folders and can be stored anywhere. For example on Windows the path
/dev/derby/mydb would refer to
C:\dev\derby\mydb...
Next create some a simple table in the new database and add some data:
ij> create table cities (
cityID integer not null primary key,
name varchar(32) not null
);
insert into cities values(1,'london');
insert into cities values(2,'berlin');
insert into cities values(3,'san francisco');
Given that all worked fine so far we can finally move on to a small Processing demo to query our exciting dataset:
import org.apache.derby.*;
import java.sql.*;
String driver = "org.apache.derby.jdbc.EmbeddedDriver";
String connectID = "jdbc:derby:/derby/testdb";
Connection conn;
void setup() {
size(100,100);
try {
query();
}
catch(SQLException e) {
e.printStackTrace();
}
}
void query() throws SQLException {
try{
Class.forName(driver);
}
catch(java.lang.ClassNotFoundException e) {
e.printStackTrace();
}
try {
conn = DriverManager.getConnection(connectID);
Statement st=conn.createStatement();
ResultSet results=st.executeQuery("SELECT * FROM cities");
while (results.next()){
println("City: "+results.getString("name"));
}
results.close();
}
catch (Exception e) {
e.printStackTrace();
}
finally {
// always make sure we close the connection
if (conn!=null) conn.close();
}
}
Now, this is obviously an absolute bare bones demo, however Florian Jennet wrote
a little database library last year to hide all these excessive try/catch clauses. Unfortunately he's also hardcoded the database connection string to only work with MySQL and there's no source supplied with the library so one could change it and easily add support for other JDBC drivers... (nudge! :)
Speaking of databases, here's another food for thought. SQLite, MySQL, Apache Derby et al are all based on the relational database model. Java on the other hand is object oriented and as you can see it takes a relative large effort to exchange data between both worlds without any further help. To ease these tasks there're various powerful
object relationship mappers available acting as translators between the worlds of objects and that of SQL. Last but not least, native object oriented databases are yet another alternative approach here which is far more aligned with the language.
db4o is such an object database and allows you to store and query complex object hierarchies in a most natural (in a Java context) way. For example with db4o you can store and restore the entire state of an application -
with just a single line of code. This in turn not just saves you a lot of time and headaches, but also enables building more complex, data intensive applications. If you're interested, the db4o site has a very easy to follow
tutorial...
