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:My first test
|
$
javac Sample.java $ java -classpath ".:sqlite-jdbc.3.7.2.jar" Sample |
First SpatiaLite test (failure)
| stmt.execute("SELECT load_extension('/usr/local/lib/libspatialite.so'); |
|
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')"); |
Final SpatiaLite test (full success)
|
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 |
|
Conclusions
|
|
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
|
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); |
|
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(); |