Portable JDBC vs SQLite on Android

Question

I am using SQLite in a project used by an android application. Currently I am using the SQLite implementation provided in android.database.sqlite.

I want to make a desktop application which uses the same codebase. So I need to separate all the shared behaviour into a separate portable project/jar.

My problem is I'm currently making heavy use of android.database.sqlite. If possible I do not want to re-write every database access call to be compatible with JDBC or whatever I will have to use without using the android provided SQLite.

To solve this problem with minimal impact on the existing code. I intent to write a SQLite interface (compatible with android.database.sqlite) which the shared code will use... on android it will be implemented trivially by android.database.sqlite, and on the desktop it will be implemented by somehow mutilating SQLite through JDBC to match android.database.sqlite.

This is proving difficult as I often supply Object[] arrays to be bound to prepared statements which JDBC requires strict typing, and I am not familiar with JDBC at all.

Is there any other way to use SQLite in Java which is similar to android.database.sqlite, or any other approaches which may save me the effort (and inevitable debugging) associated with re-writing many database access points?

Disclamer: I have never until now tried using JDBC.

Simplified question: What is the best way to use SQLite in java? JDBC, other?

Answer 1

Here is what I would do:

1. Create an interface for database operations. It would include methods to add, modify, delete records and save/commit if required. This interface could be extended as and when required.
2. Create an implementation for JDBC/SQLite. Have a configuration entry to select appropriate implementation preferably at build time.

What this means in your case is:

1. Create the interface.
2. Create an implementation which internally makes use of SQLite.
3. Create an implementation which internally makes use of some JDBC implementation.

This way, your application will become abstracted from the underlying database which is being used. This will improve the portability.

Answer 2

I think creating a wrapper would be a good idea, but may involve a lot of effort in terms of development as well as testing. Maybe you can start a project on google and get a few more people involved.

On a side note, I believe there's already such a project on google code called sqldroid

Answer 3

You can create something like a DataMapper for your domain so extending the BCE pattern to BCDE. The role of a data mapper is to abstract from the underlying database technology so increasing later reuse