SpatiaLite logo

Language bindings: Java / JDBC
2011 January 28

Back to Language Bindings

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.

Java sample program

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.
Back to Language Bindings

CC-BY-SA logo Author: Alessandro Furieri a.furieri@lqt.it
This work is licensed under the Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0) license.
GNU logo Permission is granted to copy, distribute and/or modify this document under the terms of the
GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation;
with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts.