Spatialite and Java

.. content::

Get the libraries

To work with spatialite in java you will need to do a couple of steps before coding away.

1. Download the native libraries. You will find everything necessary on the main homepage of the spatialite libraries.

   For Linux you will have to compile the library yourself, if they are not available in the repositories of your distribution. But that is a quite simple task on linux, so get you source code tarball and compile away!

   In the case of windows things are way more tricky when it comes to compile with all the necessary support. Thanks to the father of Spatialite, the binaries are available for download here.

2. Put the native libraries in the library path of your operating system.

   On my linux system I compiled the libraries in the most standard way and simply added the resulting paths to the library and execution paths:

   export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib/
   export PATH=$PATH:/usr/local/bin/
   

   On windows I had some problems. In windows ther is no such thing as an LD_LIBRARY_PATH. The path in which the libraries are in need to be added directly to the PATH variable. Since in my windows installation I had a picle of other spatial applications and libraries (for example Osgeo4W), basically I experienced continuos crashes of even the simplest java programs I was testing. Finally I realized that putting the libraries path as the first entry of the PATH variable solved the problems that seemed to be due to some conflicts.

3. Get the java jdbc libraries.

   After trying different options I decided that the Xerial drivers are the best way to go. Their biggest problem is that the error messages they produce 85% of the times are not directly bound to the problem. So one needs to use a lot of imagination to really find issues.

   If you are working with maven, this is the simplest way to get the drivers into your project. Add this to you pom.xml:

   <dependency>
           <groupId>org.xerial</groupId>
           <artifactId>sqlite-jdbc</artifactId>
           <version>3.8.10.1</version>
   </dependency>
   

Now you are ready to start to develop!!

Popup your favourite IDE and reference the libraries in it.

Code Snippets

Open an existing database

// enabling dynamic extension loading
// absolutely required by SpatiaLite
SQLiteConfig config = new SQLiteConfig();
config.enableLoadExtension(true);
// create a database connection
conn = DriverManager.getConnection("jdbc:sqlite:" + dbPath, config.toProperties());
try (Statement stmt = conn.createStatement()) {
    // set timeout to 30 sec.
    stmt.setQueryTimeout(30);
    // load SpatiaLite
    stmt.execute("SELECT load_extension('mod_spatialite')");
}

Create a new database