From my experience I can say there are three main frameworks to work locally with SQLite database under Android mobile system: native SQLite android library, ORMlite framework and sqlitegen framework.

It will be analysed 3 valuable function’s parts to choice an optimal framework, what suits for the most android projects, as follows: performance, functionality and stability.

Introduction

Native Android SQLite library

Exposes methods to manage a SQLite database. SQLiteDatabase has all methods to create, delete, execute SQL commands, and perform other common database management tasks. More info is here

ORMlite

ORMLite is an open source software framework that provides lightweight object relational mapping (ORM) between Java classes and SQL database. It supports Android mobile platform. The framework provides a simple powerful wrapper around the DAO functions without the complexity of other frameworks such as Hibernate.

Example:
@DatabaseTable
public class TestGroup implements Serializable {
@DatabaseField(generatedId = true)
private Integer id;
@DatabaseField
private String name;
public Integer getId() {return id;}
public String getName() {return name;}
public void setName(String name) {this.name = name;}
}
@DatabaseTable
public class TestDetail implements Serializable {
public static final String DATE_FIELD_NAME = “lastClickDate”;
@DatabaseField(generatedId = true)
private Integer id;
@DatabaseField(columnName = DATE_FIELD_NAME)
private Date lastClickDate;
@DatabaseField(index = true)
private String name;
@DatabaseField(canBeNull = true, foreign = true)
private TestGroup group;
public Integer getId() {return id;}
public void setId(Integer id) {this.id = id;}
public TestGroup getGroup() {return group;}
public void setGroup(TestGroup group) {this.group = group;}
public Date getLastClickDate() {return lastClickDate; }
public String getName() {return name;}
public void setName(String name) {this.name = name;}
}
As we can see in example above, the framework supports primary and foreign keys, indexes and relationships between tables. So we have here everything to create data model as well as re-engeeniring it within our android project. More info is here

Sqlitegen

Actually it is a plugin to Eclipse, what generates the classes, having all the tedious code for persisting the object described by the interface. It will be generated by the plugin at project build time from an annotated interface that described the table and fields for the class. The generated class will be designed to be efficient at runtime; it won’t need to use reflection etc.
package android.androidVNC;
import com.antlersoft.android.db.*;
@TableInterface(ImplementingClassName=“AbstractConnectionBean”,TableName=“CONNECTION_BEAN”)
interface IConnectionBean{
        @FieldAccessor
        long get_Id();
        @FieldAccessor
        String getNickname();
        @FieldAccessor
        String getAddress();
        @FieldAccessor
        int getPort();
        @FieldAccessor
        String getPassword();
        @FieldAccessor
        String getColorModel();
        @FieldAccessor
        boolean getForceFull();
        @FieldAccessor
        String getRepeaterId();
}

Performance test: ORMlite vs. Native SQLite

To compare the performance of ORMlite and Native SQL frameworks I have created two identical tables with 2 fields: auto incremented primary key id and one long value. The test program inserts 1000 rows as long values and shows processing time for both frameworks:

I have repeated the test 10 times. Here is the result:

Vertical axis is execution time in miliseconds, horizontal one is iterations.

As we can see on the diagram above, the Native Android SQLite library running approximately 20% faster than ORMlite framework.

Stability test of ORMlite

Stability of native SQLite android library stays not under question, so I will skip the investigation of this framework and go to analyse the ORMlite.

The stability test of ORMlite framework creates two tables: master and detail (see source example above) and makes updates of detail table:

After more as 220000 updates I have received no errors or exceptions with the framework. I can suppose the ORMlite is running stable with implemented functionality.

Frankly speaking the total test of stability needs more complicated examples to check more complex data structures and database functionality. But this takes to much time, what I cannot invest into analyse now. So I follow the feedback of other users from internet community, who has the ORMlite in production already and has positive opinion about the stability of the framework.

My experience says, there are a lot of situation during db structure design, where a developer makes a relationship error and than attempts to execute his project. The result is the app hangs and the framework outputs no error log. It is a bad place in the framework within development time. To try to avoid the problem I can recommend to compile and execute your project after so oft as you create new table or new relationship.

Conclusion

Comparing table for ORMlite and Native SQLite library: