How can I efficiently refactor SQLite access in Java?

Question

I am writing an Android app, in Java, which uses an SQLite database containing dozens of tables. I have a few Datasource classes set up to pull data from these tables and turn them into their respective objects. My problem is that I do not know the most efficient way to structure code that accesses the database in Java.

The Datasource classes are getting very repetitive and taking a long time to write. I would like to refactor the repetition into a parent class that will abstract away most of the work of accessing the database and creating objects.

The problem is, I am a PHP (loosely-typed) programmer and I'm having a very hard time solving this problem in a strictly-typed way.

Thinking in PHP, I'd do something like this:

public abstract class Datasource {

    protected String table_name;
    protected String entity_class_name;

    public function get_all () {

        // pseudo code -- assume db is a connection to our database, please.
        Cursor cursor  =  db.query( "select * from {this.table_name}");

        class_name  =  this.entity_class_name;
        entity  =  new $class_name;

        // loops through data in columns and populates the corresponding fields on each entity -- also dynamic
        entity  =  this.populate_entity_with_db_hash( entity, cursor );

        return entity;
    }
}

public class ColonyDatasource extends Datasource {

    public function ColonyDataSource( ) {
        this.table_name  =  'colony';
        this.entity_class_name  =  'Colony';
    }
}

Then new ColonyDatasource.get_all() would get all the rows in table colony and return a bunch of Colony objects, and creating the data source for each table would be as easy as creating a class that has little more than a mapping of table information to class information.

Of course, the problem with this approach is that I have to declare my return types and can't use variable class names in Java. So now I'm stuck.

What should one do instead?

(I am aware that I could use a third-party ORM, but my question is how someone might solve this without one.)

Answer 1

If your queries are virtually identical except for certain parameters, consider using prepared statements and binding

In SQLite, do prepared statements really improve performance?

Answer 2

First, is that you don't want to do these lines in your Java code:

class_name  =  this.entity_class_name;
entity  =  new $class_name;

It is possible to do what you are suggesting, and in languages such as Java it is called reflection. https://en.wikipedia.org/wiki/Reflection_(computer_programming)

In this (and many cases) using reflection to do what you want is a bad idea for many reasons.

To list a few:

• It is VERY expensive
• You want the compiler to catch any mistakes, eliminating as many runtime errors as possible.
• Java isn't really designed to be quacking like a duck: What's an example of duck typing in Java?

Your code should be structured in a different way to avoid this type of approach.

Sadly, I do believe that because it is strictly typed, you can't automate this part of your code:

// loops through data in columns and populates the corresponding fields on each entity -- also dynamic
        entity  =  this.populate_entity_with_db_hash( entity, cursor );

Unless you do it through means of reflection. Or shift approaches entirely and begin serializing your objects (¡not recommending, just saying it's an option!). Or do something similar to Gson https://code.google.com/p/google-gson/. I.e. turn the db hash into a json representation and then using gson to turn that into an object.

What you could do, is automate the "get_all" portion of the object in the abstract class since that would be repetitive nearly every instance, but use an implementation so that you can have the abstract function rest assured that it can call a method of it's extending object. This will get you most of the way towards your "automated" approach, reducing the amount of code you must retype.

To do this we must consider the fact that Java has:

• Generics (https://en.wikipedia.org/wiki/Generics_in_Java)
• Function overloading.
• Every Object in Java extends from the Object class, always.
  • Very Liskov-like https://en.wikipedia.org/wiki/Liskov_substitution_principle
• Package scope: What is the default scope of a method in Java?

Try something like this (highly untested, and most likely wont compile) code:

// Notice default scoping
interface DataSourceInterface {
    //This is to allow our GenericDataSource to call a method that isn't defined yet.
    Object cursorToMe(Cursor cursor);
}

//Notice how we implement here?, but no implemented function declarations!
public abstract class GenericDataSource implements DataSourceInterface {
    protected SQLiteDatabase database;

    // and here we see Generics and Objects being friends to do what we want.
    // This basically says ? (wildcard) will have a list of random things
    // But we do know that these random things will extend from an Object
    protected List<? extends Object> getAll(String table, String[] columns){
        List<Object> items = new ArrayList<Object>();

        Cursor cursor = database.query(table, columns, null, null, null, null,null);
        cursor.moveToFirst();
        while (!cursor.isAfterLast()) {
            // And see how we can call "cursorToMe" without error!
            // depending on the extending class, cursorToMe will return 
            //   all sorts of different objects, but it will be an Object nonetheless!
            Object object = this.cursorToMe(cursor);
            items.add(object);
            cursor.moveToNext();
        }
        // Make sure to close the cursor
        cursor.close();
        return items;
    }
}

//Here we extend the abstract, which also has the implements.
// Therefore we must implement the function "cursorToMe"
public class ColonyDataSource extends GenericDataSource {
    protected String[] allColumns = {
        ColonyOpenHelper.COLONY_COLUMN_ID, 
        ColonyOpenHelper.COLONY_COLUMN_TITLE, 
        ColonyOpenHelper.COLONY_COLUMN_URL
    };

    // Notice our function overloading!
    //    This getAll is also changing the access modifier to allow more access
    public List<Colony> getAll(){
        //See how we are casting to the proper list type?
        // Since we know that our getAll from super will return a list of Colonies.
        return  (List<Colony>)super.getAll(ColonyOpenHelper.COLONY_TABLE_NAME, allColumns);
    }


    //Notice, here we actually implement our db hash to object
    // This is the part that would only be able to be done through reflection or what/not
    // So it is better to just have your DataSource object do what it knows how to do.
    public Colony cursorToMe(Cursor cursor) {
        Colony colony = new Colony();
        colony.setId(cursor.getLong(0));
        colony.setTitle(cursor.getString(1));
        colony.setUrl(cursor.getString(2));
        return colony;
    }
}

Answer 3

So another option that I have yet to explore fully is something called Java Persistence API, there are projects that implement annotations very similar to this. The majority of these are in the form of an ORM which provide you with Data access objects (http://en.wikipedia.org/wiki/Data_access_object)

An open source project called "Hibernate" seems to be one of the go-to solutions for ORM in Java, but I have also heard that it is a very heavy solution. Especially for when you start considering a mobile app.

An android specific ORM solution is called OrmLite (http://ormlite.com/sqlite_java_android_orm.shtml), this is based off of Hibernate, but is very much stripped down and without as many dependencies for the very purpose of putting it on an android phone.

I have read that people using one will transition to the other very nicely.