Today I'm providing you with an implementation of OrmLite i made for my Android applications that uses an existing SQLite database.

I've attached the android project code, you can clone it.

This is an example usage from within your Activities:

// Reference to the Repository
Repo repo = new Repo(this);

// Creates an User object
User user = new User("Bruno", "Shadow", "MySecretPassword",
                "email@gmail.com");

// Saves it to the repository, it updates the entry if the primary key is already in the db
user.save(repo);

// Retrieves an User object from the database
user = repo.Users.getByUsername("Shadow");

// Deletes the user from the database
user.delete(repo);

I won't go too much into the implementation details, I'll just provide you with the general infos and i'll show what you need to do to use this in your own projects (how to add new models, or repository methods to use them in your code, etc..).

Classes Overview

Under the data package you will find:

A DatabaseManager, a DatabaseHelper, and a DatabaseInitializer class.

The Repo class (also under the data package) needs an instance of a Database Helper, that will be the "interface" to the SQLite database.

The Helper is provided by a an instance of our DatabaseManager, the type specified for the DatabaseManager instance is our DatabaseHelper, that is a subclass of the standard OrmLite class OrmLiteSqliteOpenHelper.

The DatabaseHelper will use an instance of DatabaseInitializer.

DatabaseInitializer checks if the database already exists into the application data folder, if it still doesn't, it procedes to copy our existing database (into the assets folder of the project) to the data directory of the application.

The Database Helper also provides the methods to get the Data Access Object of our models/tables.

Setting up the pieces

To use this in your own application you will need to modify a couple of things first.

OrmLite uses the resource string open_helper_classname to reference our database helper, you will need to adjust the value of this string to match your application packages path.

You will also need to modify the destination folder where the database will be copied: the static string DB_PATH in DatabaseInitializer class.

Don't forget do download the ormlite-android jar file and adjust the path to the lib into the project build path

Using the Repository

Our repository will need to be instantiated in our Activities to be used:

Repo repo = new Repo(this);

We're passing this as a parameter to provide the Database Helper with the current application context.

I structured the code to provide a sub-repository object for each model/table in our database.

Our main Repo class will contain sub-repositories, such as the User Repository:

public class Repo {

    DatabaseHelper db;

    public RepoUsers Users;

    public Repo(Context context)
    {
        DatabaseManager<DatabaseHelper> manager = new DatabaseManager<DatabaseHelper>();
        db = manager.getHelper(context);

        Users = new RepoUsers(db);

    }

}

For example, we can reference our Users table doing repo.Users

For each model you will create a class named Repo, in our sample the User repository is the class RepoUsers.

Our RepoUsers class is the following:

public class RepoUsers {

    Dao<User, String> userDao;

    public RepoUsers(DatabaseHelper db)
    {
        try {
            userDao = db.getUserDao();
        } catch (SQLException e) {
            // TODO: Exception Handling
            e.printStackTrace();
        }
    }

    public int create(User user)
    {
        try {
            return userDao.create(user);
        } catch (SQLException e) {
            // TODO: Exception Handling
            e.printStackTrace();
        }
        return 0;
    }
    public int update(User user)
    {
        try {
            return userDao.update(user);
        } catch (SQLException e) {
            // TODO: Exception Handling
            e.printStackTrace();
        }
        return 0;
    }
    public int delete(User user)
    {
        try {
            return userDao.delete(user);
        } catch (SQLException e) {
            // TODO: Exception Handling
            e.printStackTrace();
        }
        return 0;
    }
    public User getByUsername(String username)
    {        
        try {
            QueryBuilder<User, String> qb = userDao.queryBuilder();

            qb.where().eq("username", username);

            PreparedQuery<User> pq = qb.prepare();
            return userDao.queryForFirst(pq);
        } catch (SQLException e) {
            // TODO: Exception Handling
            e.printStackTrace();
        }
        return null;
    }
    public List<User> getAll()
    {        
        try {
            return userDao.queryForAll();
        } catch (SQLException e) {
            // TODO: Exception Handling
            e.printStackTrace();
        }
        return null;
    }
}

The Database Helper

Our database helper has the reference to the Data Access Objects, that are needed to query/updating the table of the database.

We will have a reference to each of the DAO we need, in our case, to the User DAO:

private Dao<User, String> userDao = null;

It has the code for creating/dropping the database table into the overridden methods of the OrmLiteSqliteOpenHelper base class:

// onCreate
TableUtils.createTable(connectionSource, User.class);
// onUpgrade
TableUtils.dropTable(connectionSource, User.class, true);

And it provides a public method for each table to get the Data Access Object from the helper instance:

public Dao<User, String> getUserDao() throws SQLException {
    if (userDao == null) {
        userDao = BaseDaoImpl.createDao(getConnectionSource(), User.class);
    }
    return userDao;
}

Models

I've put the application models under the models package, you can put them wherever you like. The sample application provides a single model: User.

public class User {

    @DatabaseField(id = true)
    String username;
    @DatabaseField
    String password;
    @DatabaseField
    String email;
    @DatabaseField
    String alias;

    public User() {
        // needed by ormlite
    }

    public User(String alias, String username, String password, String email) {
        this.alias = alias;
        this.username = username;
        this.password = password;
        this.email = email;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getUsername() {
        return username;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getPassword() {
        return password;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getEmail() {
        return email;
    }

    public void setAlias(String alias) {
        this.alias = alias;
    }

    public String getAlias() {
        return alias;
    }

    public int save(Repo repo)
    {
        if(repo.Users.getByUsername(username) == null)
        {
            return repo.Users.create(this);
        }
        else
        {
            return repo.Users.update(this);
        }
    }

    public int delete(Repo repo)
    {
        return repo.Users.delete(this);
    }

    public String toString()
    {
        return alias;
    }
}

For infos about the OrmLite models syntax/usage, you can go to the official OrmLite website.

Into the models i specified 2 methods:

• save(): creates/updates the object into the database

• delete(): deletes the object from the database

Every method specified on the model will need the Repository object, that's because the Database Helper needs a reference to the Application Context on runtime (we pass the application context into the Repo constructor as a parameter into our activities).

Adding a new model

To add a new model you will need to do the following:

1. Create the table on the database in the assets folder

2. Create the Model class matching the properties types of the database table

3. Into the DatabaseHelper you will need to add a new DAO object with the type of your model, and the relevant code into onCreate(), onUpgrade(), close() (for table and data access object management)

4. Create a RepoModel class (you can copy an existing one and adjust/add methods you will need)

5. Add the new RepoModel class to the main Repo.

I hope it is clear enough, looking at the source code can help a lot.

If you have any question feel free to ask

Browse on GitHub