SpatiaLite and Java / JDBC

shortly telling my first-hand experience

2011 January 8
Quick intro: few days ago an help message about SpatiaLite/JDBC was posted into the SpatiaLite User Group mailing list: http://groups.google.com/group/spatialite-users
I'm not at all a Java fan (I'm personally fond of C language), so I simply replied to this mail in the most generic way.
Anyway, all this seemed to be unsatisfying and frustrating: so I duly decided to bring up again my rusty Java skills, testing directly by myself the current state-of-the-art for SpatiaLite and JDBC.

Java is widespread: lots of developers use Java as their main development language.
So I'm quite sure that an up-to-date review about this topic surely is welcome for any people interested into SpatiaLite.


Honest disclaimer: every programmer has one or even more idiosyncrasies: after all, we simply are human beings.
So any developer has his most beloved language, and symmetrically has strong negative feelings about other languages.
It's like a mixture of rational judgment and irrational skin-level feelings.
All this said, I'm not afraid to admit that Java positively isn't my preferred language.

Some years ago (more or less near 2005), I had to use Java as my primary development language: I have no embarrass at all admitting that my first impression was really enthusiastic.
Java seemed to be designed very closely to C and C++: and after all it marked a big improvement because it was really efficient, supported an impressively feature-rich run-time, and was easily portable on every platform.
Write once, run everywhere. This seems really good, isn't ?

But after a while I discovered the hidden face of Java: actual portability was by far worse than expected.
Managing lots and lots of JAR packages, fighting against CLASSPATH and JNI binary libraries oddities was really painful, and too much often the final result was some unstable and unsafe configuration.
So I learned the hard way what Java really means: Write once, debug everywhere.

Conclusion: as soon as I realized what reality was, I quickly abandoned Java, coming back again to C. Oh yes, C isn't at all perfect.
But at least using C you can quite easily write truly portable software. And maintaining several binary distributions so to support Windows, Linux and MacOsX isn't as difficult as you can expect.

Test environment

Linux Debian:
just to be sure to test the up-to-date state-of-the-art I've actually used Debian Squeeze (32 bit).
This way I'm sure that all required packages are reasonably using the most recent version.

Java:
I was really curious about OpenJava (I had never used it before), so I decided not to use Sun Java.
After the first initial troubles I discovered I had to install the following packages: SQLite's JDBC connector:
I used the one from http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC
I've actually downloaded the latest supported version: http://www.xerial.org/maven/repository/artifact/org/xerial/sqlite-jdbc/3.7.2/sqlite-jdbc-3.7.2.jar
Btw this one isn't the latest version supported by SQLite, because v.3.7.3 was released on 2010 October 8, and v.3.7.4 is available since 2010 December 8: so the Xerial support seems to be slightly outdated. Not a big issue, anyway.

My first test


Just to check if my environment was a valid one at first I simply compiled and then ran the standard Xerial demo. You'll easily find the code on their main HTML page about JDBC.
I simply copied the Java code from the HTML page into a text file named Sample.java

Important notice: following Xerial instructions I simply copied the sqlite-jdbc-3.7.2.jar file directly under the same directory where I placed the Sample.java source, so to avoid any CLASSPATH related headache.

$ javac Sample.java
$ java -classpath ".:sqlite-jdbc.3.7.2.jar" Sample

All right: everything worked as expected. Anyway this first test simply stressed basic SQLite capabilities.
I had now going further on, attempting to test if the Xerial JDBC connector could actually support SpatiaLite.

First SpatiaLite test (failure)


The most recent libspatialite-2.4.0-RC4 was already installed on my test platform (actually this is one of the Linux workhorses I currently use for development and testing).
I had built this package by myself, so the corresponding shared library was /usr/local/lib/libspatialite.so
The first obvious thing to be done was loading the SpatiaLite's shared library, so to enable the JDBC connector supporting SpatiaLite.
So I simply added the following line to the souce code (immediately after establishing the connection).

stmt.execute("SELECT load_extension('/usr/local/lib/libspatialite.so');

Too much simplistic: this way I've got a discouraging error message: NOT AUTHORIZED.
After a little while I've found an useful suggestion browsing the Web: a preliminary step is absolutely required in order to enable extension dynamic loading.

import org.sqlite.SQLiteConfig;
...
SQLiteConfig config = new SQLiteConfig;
config.enableLoadExtension(true);
Connection conn = DriverManager.getConnection("path", config.toProperties);
Statement stmt = conn.createStatement();
stmt.execute("SELECT load_extension('/usr/local/lib/libspatialite.so')");

All right; now the SpatiaLite's shared library was successfully loaded.
And this one was the unique misadventure I experienced during my JDBC testing: once I was able resolving this issue then anything ran absolutely smooth and without any further accident.
Except for one JDBC oddity I noticed: but I'll account for this at the end of the story.

Final SpatiaLite test (full success)


SpatialiteSample.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import org.sqlite.SQLiteConfig;

public class SpatialiteSample
{
  public static void main(String[] args) throws ClassNotFoundException
  {
    // load the sqlite-JDBC driver using the current class loader
    Class.forName("org.sqlite.JDBC");

    Connection conn = null;
    try
    {
      // enabling dynamic extension loading
      // absolutely required by SpatiaLite
      SQLiteConfig config = new SQLiteConfig();
      config.enableLoadExtension(true);

      // create a database connection
      conn = DriverManager.getConnection("jdbc:sqlite:spatialite.sample",
      config.toProperties());
      Statement stmt = conn.createStatement();
      stmt.setQueryTimeout(30); // set timeout to 30 sec.

      // loading SpatiaLite
      stmt.execute("SELECT load_extension('/usr/local/lib/libspatialite.so')");

      // enabling Spatial Metadata
      // using v.2.4.0 this automatically initializes SPATIAL_REF_SYS and GEOMETRY_COLUMNS
      String sql = "SELECT InitSpatialMetadata()";
      stmt.execute(sql);

      // creating a POINT table
      sql = "CREATE TABLE test_pt (";
      sql += "id INTEGER NOT NULL PRIMARY KEY,";
      sql += "name TEXT NOT NULL)";
      stmt.execute(sql);
      // creating a POINT Geometry column
      sql = "SELECT AddGeometryColumn('test_pt', ";
      sql += "'geom', 4326, 'POINT', 'XY')";
      stmt.execute(sql);

      // creating a LINESTRING table
      sql = "CREATE TABLE test_ln (";
      sql += "id INTEGER NOT NULL PRIMARY KEY,";
      sql += "name TEXT NOT NULL)";
      stmt.execute(sql);
      // creating a LINESTRING Geometry column
      sql = "SELECT AddGeometryColumn('test_ln', ";
      sql += "'geom', 4326, 'LINESTRING', 'XY')";
      stmt.execute(sql);

      // creating a POLYGON table
      sql = "CREATE TABLE test_pg (";
      sql += "id INTEGER NOT NULL PRIMARY KEY,";
      sql += "name TEXT NOT NULL)";
      stmt.execute(sql);
      // creating a POLYGON Geometry column
      sql = "SELECT AddGeometryColumn('test_pg', ";
      sql += "'geom', 4326, 'POLYGON', 'XY')";
      stmt.execute(sql);

      // inserting some POINTs
      // please note well: SQLite is ACID and Transactional,
      // so (to get best performance) the whole insert cycle
      // will be handled as a single TRANSACTION
      conn.setAutoCommit(false);
      int i;
      for (i = 0; i < 100000; i++)
      {
        // for POINTs we'll use full text sql statements
        sql = "INSERT INTO test_pt (id, name, geom) VALUES (";
        sql += i + 1;
        sql += ", 'test POINT #";
        sql += i + 1;
        sql += "', GeomFromText('POINT(";
        sql += i / 1000.0;
        sql += " ";
        sql += i / 1000.0;
        sql += ")', 4326))";
        stmt.executeUpdate(sql);
      }
      conn.commit();

      // checking POINTs
      sql = "SELECT DISTINCT Count(*), ST_GeometryType(geom), ";
      sql += "ST_Srid(geom) FROM test_pt";
      ResultSet rs = stmt.executeQuery(sql);
      while(rs.next())
      {
        // read the result set
        String msg = "> Inserted ";
        msg += rs.getInt(1);
        msg += " entities of type ";
        msg += rs.getString(2);
        msg += " SRID=";
        msg += rs.getInt(3);
        System.out.println(msg);
      }

      // inserting some LINESTRINGs
      // this time we'll use a Prepared Statement
      sql = "INSERT INTO test_ln (id, name, geom) ";
      sql += "VALUES (?, ?, GeomFromText(?, 4326))";
      PreparedStatement ins_stmt = conn.prepareStatement(sql);
      conn.setAutoCommit(false);
      for (i = 0; i < 100000; i++)
      {
        // setting up values / binding
        String name = "test LINESTRING #";
        name += i + 1;
        String geom = "LINESTRING (";
        if ((i%2) == 1)
        {
          // odd row: five points
          geom += "-180.0 -90.0, ";
          geom += -10.0 - (i / 1000.0);
          geom += " ";
          geom += -10.0 - (i / 1000.0);
          geom += ", ";
          geom += -10.0 - (i / 1000.0);
          geom += " ";
          geom += 10.0 + (i / 1000.0);
          geom += ", ";
          geom += 10.0 + (i / 1000.0);
          geom += " ";
          geom += 10.0 + (i / 1000.0);
          geom += ", 180.0 90.0";
        }
        else
        {
          // even row: two points
          geom += -10.0 - (i / 1000.0);
          geom += " ";
          geom += -10.0 - (i / 1000.0);
          geom += ", ";
          geom += 10.0 + (i / 1000.0);
          geom += " ";
          geom += 10.0 + (i / 1000.0);
        }
        geom += ")";
        ins_stmt.setInt(1, i+1);
        ins_stmt.setString(2, name);
        ins_stmt.setString(3, geom);
        ins_stmt.executeUpdate();
      }
      conn.commit();

      // checking LINESTRINGs
      sql = "SELECT DISTINCT Count(*), ST_GeometryType(geom), ";
      sql += "ST_Srid(geom) FROM test_ln";
      rs = stmt.executeQuery(sql);
      while(rs.next())
      {
        // read the result set
        String msg = "> Inserted ";
        msg += rs.getInt(1);
        msg += " entities of type ";
        msg += rs.getString(2);
        msg += " SRID=";
        msg += rs.getInt(3);
        System.out.println(msg);
      }

      // inserting some POLYGONs
      // this time too we'll use a Prepared Statement
      sql = "INSERT INTO test_pg (id, name, geom) ";
      sql += "VALUES (?, ?, GeomFromText(?, 4326))";
      ins_stmt = conn.prepareStatement(sql);
      conn.setAutoCommit(false);
      for (i = 0; i < 100000; i++)
      {
        // setting up values / binding
        String name = "test POLYGON #";
        name += i + 1;
        ins_stmt.setInt(1, i+1);
        ins_stmt.setString(2, name);
        String geom = "POLYGON((";
        geom += -10.0 - (i / 1000.0);
        geom += " ";
        geom += -10.0 - (i / 1000.0);
        geom += ", ";
        geom += 10.0 + (i / 1000.0);
        geom += " ";
        geom += -10.0 - (i / 1000.0);
        geom += ", ";
        geom += 10.0 + (i / 1000.0);
        geom += " ";
        geom += 10.0 + (i / 1000.0);
        geom += ", ";
        geom += -10.0 - (i / 1000.0);
        geom += " ";
        geom += 10.0 + (i / 1000.0);
        geom += ", ";
        geom += -10.0 - (i / 1000.0);
        geom += " ";
        geom += -10.0 - (i / 1000.0);
        geom += "))";
        ins_stmt.setInt(1, i+1);
        ins_stmt.setString(2, name);
        ins_stmt.setString(3, geom);
        ins_stmt.executeUpdate();
      }
      conn.commit();

      // checking POLYGONs
      sql = "SELECT DISTINCT Count(*), ST_GeometryType(geom), ";
      sql += "ST_Srid(geom) FROM test_pg";
      rs = stmt.executeQuery(sql);
      while(rs.next())
      {
        // read the result set
        String msg = "> Inserted ";
        msg += rs.getInt(1);
        msg += " entities of type ";
        msg += rs.getString(2);
        msg += " SRID=";
        msg += rs.getInt(3);
        System.out.println(msg);
      }
    }
    catch(SQLException e)
    {
      // if the error message is "out of memory",
      // it probably means no database file is found
      System.err.println(e.getMessage());
    }
    finally
    {
      try
      {
        if(conn != null)
          conn.close();
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }
  }
}

$ javac -classpath ".:sqlite-jdbc.3.7.2.jar" SpatialiteSample.java
$ java -classpath ".:sqlite-jdbc.3.7.2.jar" SpatialiteSample

Telling the full story is boring and very few interesting: you can study and test the sample code by yourself, and that's absolutely all.

Conclusions

  1. the Xerial JDBC connector seems to be perfectly aimed to support SpatiaLite
  2. performance is really good: more or less, exactly the same you can get using a C-based process.
    But that's not too much surprising after all: using the Xerial JDBC connector we are actually using binary C libraries, and Java merely acts like a wrapping shell.

Caveat

Although the Xerial JDBC connector seems to be really good, I noticed several potential flaws.
Very shortly said, your SQL statements have to be absolutely clean and well tested: because when the JDBC connector encounters some invalid SQL (not at all an exceptional condition during the development life-cycle), it's most probable you'll then get a fatal JVM crash than a soft error exception.

I became quite crazy attempting to identify the cause for so frequent crashes during my tests: untill I finally realized that the problem simply was some stupid missing braket or quotation mark in complex SQL statements.
C can safely survive to all this without any damage, nicely reporting a soft and polite error message.
On the other side JDBC / JVM are unexorably unforgiving (and unstable) when they handle such trivial errors.

JDBC oddities


As I previously stated, I noticed a real JDBC oddity. It's now time to explain better this stupid issue.

C language snippet / SQLite API
strcpy(sql, "INSERT INTO xxx (id, geometry) VALUES (?, ");
strcat(sql, "GeomFromText('POINT(? ?, ? ?)', 4326))";
sqlite3_prepare_v2 (db_handle, sql, strlen (sql), &stmt, NULL);
sqlite3_bind_int (stmt, 0, 1);
sqlite3_bind_double (stmt, 1, 10.01);
sqlite3_bind_double (stmt, 2, 20.02);
sqlite3_bind_double (stmt, 3, 30.03);
sqlite3_bind_double (stmt, 4, 40.04);
sqlite3_step (stmt);
Java language snippet / JDBC
sql = "INSERT INTO xxx (id, geometry) VALUES (?, ";
sql += "GeomFromText('POINT(? ?, ? ?)', 4326))";
stmt = conn.prepareStatement(sql);

stmt.setInt(1, 1);
stmt.setDouble(2, 10.01);
stmt.setDouble(3, 20.02);
stmt.setDouble(4, 30.03);
stmt.setDouble(5, 40.04);
stmt.executeUpdate();
Post-mortem: JDBC attempts to be smarter than you. While parsing the Prepared Statement JDBC discovers your dirty trick: the latest four args are enclosed within single quotes, so JDBC simply ignores them at all, because it intends the string literal as an absolutely untouchable entity.
You can check by yourself using ParameterMetaData.getParameterCount(); this prepared statement simply expects a single arg to be bounded.