Tommy LeeI 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
- Making sure it is compatible
- Install Sugar ORM
- Database transfer script
- Running your application
- 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.
- Make a ‘android_metadata’ table and have a field named ‘locale’ with the value of ‘en_US’.
- 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.
- 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.
- 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.
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
- YOUR_PACKAGE to your own unique name. Eg: com.yoursite.app.yourapp
- myDBName to your edited database. Eg: Example.db
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 |
public class DataBaseHelper extends SQLiteOpenHelper{ //The Android's default system path of your application database. private static String DB_PATH = "/data/data/YOUR_PACKAGE/databases/"; private static String DB_NAME = "myDBName"; private SQLiteDatabase myDataBase; private final Context myContext; /** * Constructor * Takes and keeps a reference of the passed context in order to access to the application assets and resources. * @param context */ public DataBaseHelper(Context context) { super(context, DB_NAME, null, 1); this.myContext = context; } /** * Creates a empty database on the system and rewrites it with your own database. * */ public void createDataBase() throws IOException{ boolean dbExist = checkDataBase(); if(dbExist){ //do nothing - database already exist }else{ //By calling this method and empty database will be created into the default system path //of your application so we are gonna be able to overwrite that database with our database. this.getReadableDatabase(); try { copyDataBase(); } catch (IOException e) { throw new Error("Error copying database"); } } } /** * Check if the database already exist to avoid re-copying the file each time you open the application. * @return true if it exists, false if it doesn't */ private boolean checkDataBase(){ File dbFile = myContext.getDatabasePath(DB_NAME); return dbFile.exists(); } /** * Copies your database from your local assets-folder to the just created empty database in the * system folder, from where it can be accessed and handled. * This is done by transfering bytestream. * */ private void copyDataBase() throws IOException{ //Open your local db as the input stream InputStream myInput = myContext.getAssets().open(DB_NAME); // Path to the just created empty db String outFileName = myContext.getDatabasePath(DB_NAME).getPath(); //Open the empty db as the output stream OutputStream myOutput = new FileOutputStream(outFileName); //transfer bytes from the inputfile to the outputfile byte[] buffer = new byte[1024]; int length; while ((length = myInput.read(buffer))>0){ myOutput.write(buffer, 0, length); } //Close the streams myOutput.flush(); myOutput.close(); myInput.close(); } public void openDataBase() throws SQLException{ //Open the database String myPath = myContext.getDatabasePath(DB_NAME).getPath(); myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY); } @Override public synchronized void close() { if(myDataBase != null) myDataBase.close(); super.close(); } @Override public void onCreate(SQLiteDatabase db) { } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } // Add your public helper methods to access and get content from the database. // You could return cursors by doing "return myDataBase.query(....)" so it'd be easy // to you to create adapters for your views. } |
Run your application
Initiate an instance of the class in your onCreate() method of lets say MainActivity to initiate the transfer.
|
1 2 3 4 5 6 7 8 9 10 |
private DataBaseHelper myDB; myDB = new DataBaseHelper(getApplicationContext()); try { myDB.createDataBase(); } catch(IOException e) { // do nothing } |
Do remember to insert the same database name you use in your manifest.
|
1 2 3 |
<meta-data android:name="DATABASE" android:value="Example.db" /> |
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.
Tommy Lee