Using Your Own SQLite Database in Android with Sugar ORM

I wanted to know how to use an existing SQLite database for my app but can’t to find any useful on the web. Then, I found Juan-Manuel FluxΓ  post. It’s exactly what I wanted.

It was a 2009 post so I took the opportunity and updated some part code as there are newer API for accessing them. To break things down. His code is a helper to copy pre-built or existing SQLite database in the asset folder located inside your main folder and copied it over to the application database directory.

Put the trigger code inside the onCreate() of your MainActivity to run it. The code only move the database but doesn’t give you all the necessary CRUD operation methods. You can write it yourself based on your own logic in the same file or use a ORM library to do the heavy lifting.

I don’t want to reinvent the wheel so I plugin in Sugar ORM to handle all the CRUD operations and leave me to write SQL query quietly.

The Steps

  1. Making sure it is compatible
  2. Install Sugar ORM
  3. Database transfer script
  4. Running your application
  5. Summary

 

Making sure it is compatible

If you are running Windows I would recommend using DB Browser for SQLite to manipulate the database. There are things to change to make it workable for Android.

  1. Make a ‘android_metadata’ table and have a field named ‘locale’ with the value of ‘en_US’.
  2. Rename your old ID field to ‘_id’ so Android will know where to bind the field. This become important if you are using CursorAdapter which the ListView uses.
  3. Create another field and label it “ID”. I know it’s weird but you need this for Sugar ORM to reference else it won’t run.
  4. Have all the fields in uppercase.

For example, I have an Example.DB and inside it have table ‘part’ and ‘vehicle’. This is how it should look like.

android-existing-db-editing

Note: I know it is a bit quirky with 2 id fields. The things you need to do is to make sure ‘_id’ is copied over to ‘ID’ field to keep things in synced. For my case, I have a static database.

Install Sugar ORM

Install Sugar ORM via Gradle. You can refer to their documentation here. If you are still not using Gradle then it’s time to upgrade your code base and build tools dude.

Transfer the Database

This class from Juan will transfer your database from the asset folder into the database folder in the Android system. Just need to replace the 2 items

  1. YOUR_PACKAGE to your own unique name. Eg: com.yoursite.app.yourapp
  2. myDBName to your edited database. Eg: Example.db

Run your application

Initiate an instance of the class in your onCreate() method of lets say MainActivity to initiate the transfer.

Do remember to insert the same database name you use in your manifest.

Once it started running Sugar ORM will take over and create other necessary files and you can start using immediately.

Summary

In a nutshell, you need to prepare the database for Android consumption, install Sugar ORM for you to manipulate the database and run the above script to transfer the database to its proper location.

You May Also Like