Embedding a database in your cross-platform projects


amber_devices_2Recently 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:

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:

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:

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:

Managing embedded databases

If you want to embed a database in your project, first of all you need to load the proper driver:

Then we set the URL of the database and create the connection:

And finally it’s time to execute some queries, creating a table, inserting some values, or reading the table.

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:

So all we have to do is just copy the db file to the app private location:

Full sample

GluonSQLite 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.