Note: these pages are no longer maintained

Never the less, much of the information is still relevant.
Beware, however, that some of the command syntax is from older versions, and thus may no longer work as expected.
Also: external links, from external sources, inside these pages may no longer function.

	Language bindings: Java / JDBC
2015 January 25

This documentation page has been extensively updated: you can eventually read the previous version from here

Back to Language Bindings

Test environment

Windows 7 Professional SP1 (64 bit)

Java (JDK+JRE) 8.0_31 (64 bit)

Windows XP Professional SP3 (32 bit) [virtual machine]

Java (JDK+JRE) 7.0_75 (32 bit)

SQLite's JDBC connector

Xerial sqlite-jdbc 3.8.7 sqlite-jdbc-3.8.7.jar

Spatialite Loadable Extension and related dependencies

32 bit mod_spatialite-4.2.1-rc1-win-x86.7z
64 bit mod_spatialite-4.2.1-rc1-win-amd64.7z

Important notice: freshly updated versions of the Xerial JDBC connector are now available from https://bitbucket.org/xerial/sqlite-jdbc/downloads

Java sample program #1

This first sample program will create and populate a brand new spatialite-test.sqlite DB file.
The main topics covered by this sample are:

establishing a Connection to some SQLite DB file
loading the mod_spatialite Spatial Extension
creating few Spatial Tables
inserting 100,000 rows (including Geometries) in each table
executing SQL queries and scrolling a ResultSet
transactions and exceptions handling

just copy & paste all the following Java source.
then save this source into a file named 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-test.sqlite",
            config.toProperties());
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(30); // set timeout to 30 sec.

            // loading SpatiaLite
            stmt.execute("SELECT load_extension('mod_spatialite')");

            // checking SQLite and SpatiaLite version + target CPU
            String sql = "SELECT sqlite_version(), spatialite_version(), spatialite_target_cpu()";
            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()) {
                // read the result set
                String msg = "SQLite version: ";
                msg += rs.getString(1);
                System.out.println(msg);
                msg = "SpatiaLite version: ";
                msg += rs.getString(2);
                System.out.println(msg);
                msg = "target CPU: ";
                msg += rs.getString(3);
                System.out.println(msg);
            }

            // enabling Spatial Metadata
            // this automatically initializes SPATIAL_REF_SYS and GEOMETRY_COLUMNS
            sql = "SELECT InitSpatialMetadata(1)";
            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";
            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);
            }
        }
    }
}

Java sample program #2 (multi-threading)

This second sample program will query the spatialite-test.sqlite DB file created and populated by the previous sample program.
The main topics covered by this sample are:

establishing a Connection to some SQLite DB file
loading the mod_spatialite Spatial Extension
executing SQL queries and scrolling a ResultSet
using multi-threading (parallel execution of many queries at the same time)

just copy & paste all the following Java source.
then save this source into a file named SpatialiteThreads.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 SpatialiteThreads {
    // max concurrent threads supported by Spatialite
    public static final int MAX_THREADS = 64;

    private static class myThread extends Thread {
        //
        // the (private) Class implementing each child Thread
        //
        int slot;
        int thread_no;

        public myThread(int slot) {
            // ctor
            this.slot = slot;
        }

        public void setThreadNo(int thread_no) {
            // setting the Thread counter
            this.thread_no = thread_no;
        }

        public void run() {
            // actual Thread implementation
            // all real work happens here
            boolean ok1 = false;
            boolean ok2 = false;
            Connection conn = null;

            // welcome message
            System.out.println("start: Slot #"+slot+"    Thread #"+thread_no);

            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-test.sqlite",
                                                   config.toProperties());
                Statement stmt = conn.createStatement();
                stmt.setQueryTimeout(30); // set timeout to 30 sec.

                // loading SpatiaLite
                stmt.execute("SELECT load_extension('mod_spatialite')");

                // preparing the SQL query statement
                String sql = "";
                switch (thread_no % 3) {
                    // each Thread will select its own query between three
                    // possible alternative depending on thread-counter value
                case 0:
                    sql = "SELECT * FROM test_pt WHERE ";
                    sql += "ST_Intersects(geom, BuildMbr(10.23, 10.23, 10.25, 10.25)) = 1";
                    break;
                case 1:
                    sql = "SELECT * FROM test_ln WHERE ";
                    sql += "ST_Intersects(geom, BuildMbr(40.09, 30.09, 40.1, 30.1)) = 1";
                    break;
                case 2:
                    sql = "SELECT Sum(ST_Area(geom)), Sum(ST_Perimeter(geom)) ";
                    sql += "FROM test_pg WHERE id BETWEEN 10000 AND 10005";
                    break;
                }

                // creating a ResultSet
                ResultSet rs = stmt.executeQuery(sql);
                while(rs.next()) {
                    // reading the result set
                    String name;
                    double value;
                    switch (thread_no % 3) {
                    case 0:
                        name = rs.getString(2);
                        if (name.equals("test POINT #10241")) {
                            ok1 = true;
                        }
                        if (name.equals("test POINT #10249")) {
                            ok2 = true;
                        }
                        break;
                    case 1:
                        name = rs.getString(2);
                        if (name.equals("test LINESTRING #12606")) {
                            ok1 = true;
                        }
                        if (name.equals("test LINESTRING #12618")) {
                            ok2 = true;
                        }
                        break;
                    case 2:
                        value = rs.getDouble(1);
                        if (value > 9601.440123 && value < 9601.440125) {
                            ok1 = true;
                        }
                        value = rs.getDouble(2);
                        if (value > 960.071999 && value < 960.072001) {
                            ok2 = true;
                        }
                        break;
                    }
                }

            } 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);
                }
            }

            // goodbye message
            if (ok1 == true && ok2 == true) {
                System.out.println("    stop: Slot #"+slot+"    Thread #"+thread_no);
            } else {
                System.out.println("    ***** ERROR ***** stop: Slot #"+slot+"    Thread #"+thread_no);
            }
            // thread termination: quitting
        }
    }

    public static void main(String[] args) throws ClassNotFoundException {
        //
        // the Main Class is just intended to dispatch all children Threads
        //
        int thread_no = 0;
        int i;
        int slot;

        // load the sqlite-JDBC driver using the current class loader
        Class.forName("org.sqlite.JDBC");

        // creating and initializing all children threads
        myThread thread_array[] = new myThread [MAX_THREADS];
        for (slot = 0; slot < MAX_THREADS; slot++) {
            thread_array[slot] = new myThread(slot);
        }

        while (thread_no < 1000) {
            // looping on threads activation
            for (slot = 0; slot < MAX_THREADS; slot++) {
                // scanning all threads one by one
                if (thread_array[slot].isAlive() != true) {
                    // found a free slot:
                    // - this thred wasn't yet previously executed
                    //   or
                    // - this thread was already executed and is now terminated
                    if (thread_no >= MAX_THREADS) {
                        // Java forbids to restart yet again a terminated thread
                        // so we'll now create a fresh thread on the same slot
                        thread_array[slot] = new myThread(slot);
                    }
                    thread_array[slot].setThreadNo(thread_no++);
                    // starting thread execution
                    thread_array[slot].start();
                }
            }
        }
    }
}

Compiling the Java sample programs

$ javac -classpath ".;sqlite-jdbc-3.8.7.jar" SpatialiteSample.java
$ javac -classpath ".;sqlite-jdbc-3.8.7.jar" SpatialiteThreads.java
$

Just invoke the javac compiler from the Command Shell: carefully check to define an appropriate classpath, so to include the Xerial sqlite-jdbc connector.

Check the DLLs configuration

$ sqlite3
SQLite version 3.8.7.4 2014-12-09 01:34:36
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT load_extension('mod_spatialite');

sqlite> SELECT spatialite_version(), spatialite_target_cpu();
4.2.1-rc1|x86_64-w64-mingw32
sqlite> .quit
$

SpatiaLite depends on several further DLLs; you'll find all them into the download package.
The most simple file-system layout you can eventually adopt is to copy all the stuff (JDBC connector, Java source and any DLL) directly into your current working directory.

Anyway Windows can still reserve some unexpected surprise, thus forbidding to successfully load the SpatiaLite extension. In this case Java itself will offer a very poor (and possibly misleading) diagnostic help; so a more reasonable approach is the one to check first if the SpatiaLite extension could be effectively loaded using some other sw supporting a better diagnostic.
So, from the Command Shell:

start the sqlite3 front-end
execute the load_extension SQL statement
then carefully check the version and target CPU infos
if anything runs exactly as expected you are now ready to effectively test your Java sample.
otherwise, if you'll encounter some trouble, you'll surely get a precise and useful diagnostic pointing your attention into the right direction.

Please note: in case of troubles, you can eventually use the nice Dependency Walker tool in order to get further help.

Running the Java sample program #1

$ java -classpath ".;sqlite-jdbc-3.8.7.jar" SpatialiteSample
SQLite version: 3.8.7
SpatiaLite version: 4.2.1-rc1
target CPU: x86_64-w64-mingw32
> Inserted 100000 entities of type POINT SRID=4326
> Inserted 100000 entities of type LINESTRING SRID=4326
> Inserted 100000 entities of type POLYGON SRID=4326
$

The above screen-shot exactly represents what you'll get in case of full success.

Important notice: don't delete the spatialite-test.sqlite DB-file just created by this first sample program; it's still required by the following multi-threaded sample program.

Running the Java sample program #2

$ java -classpath ".;sqlite-jdbc-3.8.7.jar" SpatialiteThreads
start: Slot #1    Thread #1
start: Slot #2    Thread #2
start: Slot #3    Thread #3
start: Slot #4    Thread #4
...
    stop: Slot #2    Thread #581
start: Slot #17    Thread #585
    stop: Slot #49    Thread #552
start: Slot #2    Thread #586
start: Slot #49    Thread #587
    stop: Slot #61    Thread #555
...
    stop: Slot #14    Thread #970
    stop: Slot #20    Thread #985
    stop: Slot #39    Thread #997
    stop: Slot #24    Thread #991
$

The above screen-shot exactly represents what you'll get in case of full success.

Back to Language Bindings

	Author: Alessandro Furieri a.furieri@lqt.it
	This work is licensed under the Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0) license.

	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.