Recently we’ve been asked several times about the possibilities of working with JDBC resources (e.g. MySQL or Derby) and JavaFXPorts.
While technically it is possible to directly access a database (the only difficulty is in choosing the right dependencies (some of the latest mysql jars don’t work well on iOS, for instance)), it is not really good practice to allow this from the client application. In these kinds of situations it typically makes better sense to use the backend services available on each platform to reach out over the network (e.g. using something like Gluon Cloud).
However, things are different when the problem is just having a local database embedded within your application (i.e. embedded within the device), where there is no security involved and there is no need to access any outside backend service.
For this type of database, SQLite is the perfect solution, since it’s an embedded SQL database engine, purposefully intended for these situations. Have a look at this post explaining when SQLite is more appropriate than MySQL or any other server-based RDBMS.
In order to create real cross-platform Java applications, it is encouraged to stick with Java standards as much as possible. The JDBC API is the standard way in Java to talk with relational databases. Luckily, there are drivers for both Android and iOS systems that provide a JDBC interface on top of the SQLite functionality offered by the platforms. By using these drivers, developers can use familiar and portable JDBC technology in their mobile and desktop applications.
Choosing the right Java SQLite to JDBC wrapper
If you find your project listed in those bulleted points where SQLite is more convenient, and you want to provide a cross-platform solution, the next step is deciding which of the several implementations that include a JDBC driver, is required for each platform.
We have been testing a few of them, and this is how our build.gradle file looks like:
dependencies{
// https://github.com/xerial/sqlite-jdbc
desktopRuntime 'org.xerial:sqlite-jdbc:3.8.11.2'
// https://github.com/xerial/sqlite-jdbc
embeddedRuntime 'org.xerial:sqlite-jdbc:3.7.2'
// https://github.com/SQLDroid/SQLDroid
androidRuntime 'org.sqldroid:sqldroid:1.0.3'
// iOS: no extra dependency required, SQLite is bundled
}
Desktop and Embedded
While xerial SQLite JDBC library requires no configuration, since native libraries for major OSs, including Windows, Mac OS X and Linux, are assembled into a single JAR file, it is not suitable for mobile devices. The latest version works fine on desktop, but has some issues on embedded devices (Raspberry Pi), where an older 3.7.2 works.
Wait a minute: Embedded?? Really??
Ah! We announced it during the NetBeans Day session with James Gosling at JavaOne: Gluon’s plugin for NetBeans already allows deployment on embedded devices as well, using the remote platform concept.
Check the documentation, and if you want to use it, make sure you have the latest version (1.0.3 by now), and add this on your build.gradle file, with your own settings:
jfxmobile {
embedded {
remotePlatforms {
Pi50 {
host = '192.168.1.10'
username = 'pi'
password = 'raspberry'
execPrefix = 'sudo '
workingDir = '/home/pi/Documents/'
jreLocation = '/opt/jdk1.8.0_65'
}
}
}
}
Use runEmbedded to deploy and run the project on your Raspberry Pi.
Mobile
For Android we rely on SQLDroid, a JDBC driver for Android’s SQLite database android.database.sqlite.SQLiteDatabase, given that Android doesn’t provide one.
For iOS we’ll use SQLiteJDBC already bundled with RoboVM, based on the original version by Christian Werner. We don’t need any dependency, but we must link the SQLite package using the forceLinkClasses option:
jfxmobile {
ios {
forceLinkClasses = [ 'SQLite.**.*']
}
}
What about local storage?
And given that we want to embed a database with our app, we’ll need access to the local storage. Gluon Charm Down already has the API for finding the right URL for any platform: PlatformFactory.getPlatform().getPrivateStorage().
Let’s add those dependencies to the script file.
This is how it looks like:
buildscript {
repositories {
jcenter()
}
dependencies {
classpath 'org.javafxports:jfxmobile-plugin:1.0.6'
}
}
apply plugin: 'org.javafxports.jfxmobile'
repositories {
jcenter()
}
ext.CHARM_DOWN_VERSION = "1.0.0"
dependencies{
compile "com.gluonhq:charm-down-common:$CHARM_DOWN_VERSION"
desktopRuntime "com.gluonhq:charm-down-desktop:$CHARM_DOWN_VERSION"
desktopRuntime 'org.xerial:sqlite-jdbc:3.8.11.2'
embeddedRuntime "com.gluonhq:charm-down-desktop:$CHARM_DOWN_VERSION"
embeddedRuntime 'org.xerial:sqlite-jdbc:3.7.2'
androidRuntime "com.gluonhq:charm-down-android:$CHARM_DOWN_VERSION"
androidRuntime 'org.sqldroid:sqldroid:1.0.3'
iosRuntime "com.gluonhq:charm-down-ios:$CHARM_DOWN_VERSION"
}
mainClassName = 'com.gluonhq.sqlite.GluonSQLite'
jfxmobile {
android {
manifest = 'src/android/AndroidManifest.xml'
}
ios {
forceLinkClasses = [ 'com.gluonhq.**.*', 'SQLite.**.*']
infoPList = file('src/ios/Default-Info.plist')
}
embedded {
remotePlatforms {
Pi50 {
host = '192.168.1.10'
username = 'pi'
password = 'raspberry'
execPrefix = 'sudo '
workingDir = '/home/pi/Documents'
jreLocation = '/opt/jdk1.8.0_65'
}
}
}
}
Managing embedded databases
If you want to embed a database in your project, first of all you need to load the proper driver:
@Override public void init(){
try {
if (JavaFXPlatform.isAndroid()) {
Class.forName("org.sqldroid.SQLDroidDriver");
} else if (JavaFXPlatform.isIOS()) {
Class.forName("SQLite.JDBCDriver");
} else { // desktop and embedded
Class.forName("org.sqlite.JDBC");
}
} catch (ClassNotFoundException e) {
System.out.println("Error class not found " + e);
}
}
Then we set the URL of the database and create the connection:
File dir;
String dbUrl = "jdbc:sqlite:";
try {
dir = PlatformFactory.getPlatform().getPrivateStorage();
File db = new File (dir, DB_NAME);
dbUrl = dbUrl + db.getAbsolutePath();
} catch (IOException ex) {
System.out.println("Error " + ex);
}
try {
connection = DriverManager.getConnection(dbUrl);
} catch (SQLException ex) {
System.out.println("Error establishing connection " +ex);
}
And finally it’s time to execute some queries, creating a table, inserting some values, or reading the table.
if (connection != null) {
stmt = connection.createStatement();
stmt.executeUpdate("drop table if exists person");
stmt.executeUpdate("create table person (id integer, firstname string, lastname string)");
stmt.executeUpdate("insert into person values(1, 'Johan', 'Vos')");
}
Loading an existing database
Sometimes you may want to provide an existing database to the project, avoiding the need to create it all over again. This can be achieved by adding the database file to the resources folder that is bundled inside the JAR/APK/IPA file.
So the problem now is how to extract the file and move it to the proper folder of the app?
This can be easily done in all the platforms with this utility method:
private void extractDatabase(String pathIni, String pathEnd, String name) {
try (InputStream myInput = GluonSQLite.class.getResourceAsStream(pathIni+ "/" + name)) {
String outFileName = pathEnd + "/" + name;
try (OutputStream myOutput = new FileOutputStream(outFileName)) {
byte[] buffer = new byte[1024];
int length;
while ((length = myInput.read(buffer)) > 0) {
myOutput.write(buffer, 0, length);
}
myOutput.flush();
} catch (IOException ex) { }
} catch (IOException ex) { }
}
So all we have to do is just copy the db file to the app private location:
File dir;
String dbUrl = "jdbc:sqlite:";
try {
dir = PlatformFactory.getPlatform().getPrivateStorage();
File db = new File (dir, DB_NAME);
// extract the database from JAR/APK/IPA and move it to app location
extractDatabase("/databases", dir.getAbsolutePath(), DB_NAME);
dbUrl = dbUrl + db.getAbsolutePath();
} catch (IOException ex) {
System.out.println("Error " + ex);
}
Full sample
GluonSQLite sample
Now you can go to the repository, clone it, open the GluonSQLite sample with your favorite IDE, build it and run it or deploy it on your device.
While this is clearly a very minimal use of the SQLite possibilities, with this sample we have shown how it can be easily added to your cross-platform project to embed and use a database.