How Spatialite can be used easily within a Java class (and within a JAVA Application)

After several days of hard studying and hacking, I could discover how to use SPATIALITE from a Java class or less generically from a Java application.

I should really thank Taro L. Saito and A. Furieri for having helped me in accomplishing this important task for implementing my project for Google Summer Of Code 2010.

I propose you’re reading a simple example for explaining how could I work with sqlite and spatialite from a sample java Class. I add also all the dependencies and other stuffs necessary for using it.

First of all you need to take provide yourself with the libraries you’re going to use. They are:

1 - SQLiteJDBC : that’s the java driver for using sqlite and its database files 2 - SPATIALITE : that’s where to find the SpatiaLite library whose functionalities you want to include within your Java class.

You should make a class like the one proposed here as example, include in the classpath the SQLiteJDBC driver (that is the SQLite diver for Java ) and finally activate SPATIALITE from the code with the “classical” code for loading any extension in SqLite (that is : SELECT load_extension(path_to_my_lib) ).

Here you can find some sample code working on my Ubuntu 9.10.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import org.sqlite.SQLiteConfig;

public class Test { public static void main(String[] args) throws ClassNotFoundException, SQLException { try { Class.forName(“org.sqlite.JDBC”);

	Connection connection = null;
	 
	SQLiteConfig config = new SQLiteConfig();
	config.enableLoadExtension(true);
		    
	connection = DriverManager.getConnection("jdbc:sqlite:spatialiteDB.db", config.toProperties());
	Statement statement = connection.createStatement();
	ResultSet rs = statement.executeQuery("SELECT Geometry FROM Towns");
	
	while(rs.next())
	{
		System.out.println("Type = " + rs.getString("Geometry"));
	}
	
	statement.execute("select load_extension('/home/luca/workspace/SqliteTests/lib/ext/libspatialite.so')");
	//
  
	ResultSet rs2 = statement.executeQuery("SELECT PK_UID, Area(Geometry), AsText(Centroid(Geometry)), Dimension(Geometry), GeometryType(Geometry) FROM Regions ORDER BY Area(Geometry) DESC LIMIT 5;");
  
	while(rs2.next())
	{
		System.out.println(" = = = = = = = = = = = = ");
		System.out.println("Id = " + rs2.getString(1) );
		System.out.println("Id = " + rs2.getString(2) );
		System.out.println("Id = " + rs2.getString(3) );
		System.out.println("Id = " + rs2.getString(4) );
		System.out.println("Id = " + rs2.getString(5) );
    }
    
	statement.close();
  
}catch (Exception e) {
	System.out.println("ERROR "+ e.getMessage());
}

} }

</code>

Thanks again Sandro and Taro. It works now!