Spatialite access from Java/jdbc

[Brent Wood]

Hi,

A colleague is trying to build a Java application accessing a spatialite database, developing on a Mac with Maven..

We can connect to the database with sqlite3 jdbc fine, but cannot get any spatialite functionality to work.

We have gone through a few options suggested a few hours of Google searching without success.

We are using sqlite-jdbc-3.27.2.1, (we tried also spatialite-jdbc-3.7.2-2.4 driver but it can’t load the libproj.0.dylib)

Current code snippet for connecting to the db below...

Any advice/help appreciated.

Thanks,

 

  Brent Wood

private static final String SQLITE_JDBC = "org.sqlite.JDBC";

 

Class.forName(SQLITE_JDBC);

 

SQLiteConfig config = new SQLiteConfig();

config.enableLoadExtension(true);

conn = DriverManager.getConnection(“jdbc:sqlite:path_to_db”, config.toProperties());

 

conn.createStatement().execute("SELECT load_extension('mod_spatialite.dylib')");

}

} catch (Exception e) {

e.printStackTrace();

 

(we tried also spatialite-jdbc-3.7.2-2.4 driver but it can’t load the libproj.0.dylib)

[Andrea Peri]

Hi,

This code is an extraction from a code I'm developing on a JSP (java server pages) system.

The code work perfectly.

I'm using the last released jdbc sqlite xerial driver.

sqlite-jdbc-3.28.1-20190625.022321-1.jar

-----------------------

    Connection connDB = null;
    ResultSet rs = null;
    Statement stmt = null;
    try {

        Class.forName("org.sqlite.JDBC");

        String theDbName = new String("/absolute-path-to-db-1/db1.sqlite");
        String theAttachedDbName = new String("/absolute-path-to-db-attached/db2.sqlite");

        SQLiteConfig configSQLITE = new SQLiteConfig();
        configSQLITE.enableLoadExtension(true);

        connDB = DriverManager.getConnection("jdbc:sqlite::memory:",configSQLITE.toProperties());

        stmt = connDB.createStatement();
        stmt.setQueryTimeout(300); // set timeout to 300 sec.

        theQuerySQL = "ATTACH DATABASE '" + theAttachedDbName + "' AS DB";
        stmt.execute(theQuerySQL);

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

        stmt.execute("SELECT InitSpatialMetadata(1)");

        ....

         } catch (SQLException se) {
        out.flush();
        out.write("<pre>\n"+se.toString()+"</pre>\n");
        out.flush();
    }
    finally
    {
        try
        {
            if(rs != null) {
                rs.close();
                rs = null;
            }
            if(stmt != null) {
                stmt.execute("SELECT DropGeoTable('...')");
                stmt.execute("SELECT DropGeoTable('...')");
                stmt.execute( "DETACH DATABASE DB");

                stmt.close();
                stmt = null;
            }
            if(connDB != null) {
                connDB.close();
                connDB = null;
            }

        } catch(SQLException e) {
        // connection close failed.
        System.err.println(e);
        }
    }

---------------------

Regards,

Andrea Peri.

[andrea antonello]

Hi Brent,
sorry for the late reply, I missed this somehow.

I am using spatialite via java all the time. The biggest problem is
having it compiled properly on your system.

Once you have that in your library path, it is quite straightforward.

The Hortonmachine project has a section dedicated to it. Have a look
at this module:
https://github.com/TheHortonMachine/hortonmachine/tree/master/dbs
It contains a SpatialiteDb class that does all the necessary.

But I would suggest to first see if the spatialite native installation
is properly picked. To do so you could download a working version of
the Hortonmachine with the applications:
https://github.com/TheHortonMachine/hortonmachine/releases/tag/hortonmachine-0.9.5
In there there is a script hm-dbviewer.sh that runs a simple db
viewer. If yourt libs are in the path and working, then you should be
able to open a spatialite db. If that works, you can just take the
code from the dbs module to get it done.

Hope that helps,
Cheers,
Andrea

> --
> You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.
> To post to this group, send email to spatiali...@googlegroups.com.
> Visit this group at https://groups.google.com/group/spatialite-users.
> To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/d35f8220-4a4f-4a50-9e28-9c7da42c61df%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.