In Android the recommended SQLite database access method is to create a new database using the
SQLiteOpenHelper class. However I wanted to be able to load a pre-existing SQLite database into an Android application
and access it with a JDBC driver.
The reason is that I want my students to be able to play around with making a database with an application like
SQLite Browser and then build on their work in their applications. I
also wanted all of my students using similar database access methods so they can help each other as much as possible so I wanted them using JDBC. I've found that it is possible but first a few warnings:
- This method is inappropriate for large databases as it duplicates the whole database doubling the memory footprint
- This method also pulls in an external JDBC library, SQLDroid, further increasing footprint size
- The SQLDroid library is not guaranteed to be complete or correct across all android devices
But if you still want to load your existing SQLite database into your Android project and access it with a JDBC driver, here's how you can do it. I've started a fresh, empty project to try to keep it as simple as possible:
Move your existing SQLite database into your projects 'assets' folder
First you may need to create your assets folder if you don't see it in your project already
- right click on your project, select New > Folder > Assets Folder
- add the 'assets' folder to your main project:
- Navigate to your assets folder in your file browser (finder or windows explorer etc). I found it in /app/src/main/assets
- drag and drop your database into this folder and it should appear in android studio. My database is a database of birds.
(Now is a good time to run your program and make sure it still runs without run-time errors!)
Add code to your project to copy your database into internal memory on the phone
The assets folder is a read-only directory that gets packaged up into your .apk to load on the phone. But we want to continue to add and remove from the database so when the app is run for the first time only, we will copy the database into internal memory.
- In your main activity, create a method called LoadDatabase and call it in the onCreate method. You'll also need a string called 'dbName' and four imports shown below for later
- In LoadDatabase, check to see if there is already a database in Internal Storage. If not, copy it over from Assets
(Now is a good time to run your program and make sure it still runs without run-time errors!)
If you're running your phone at API level 23 or less, you should be able to use Android Device Monitor to see your new database file in Internal Storage after your program runs!
- Android/tools/Android/Android Device Monitor
- select the file 'explorer' tab
- navigate to data/data//files
Add the SQLDroid JDBC Driver to your project
Open your Gradle file (Module: app) and add this line:
- Save and Sync your Gradle file
(Now is a good time to run your program and make sure it still runs without run-time errors!)
Access your database in Internal Storage using your JDBC driver
- back in the main activity, I've added another method called 'TestDatabase' and called it from the onCreate Method
- We'll need a few more imported classes:
- Then we can load the SQLDroid JDBC driver and use it to make a connection to our database in Internal Storage.
In this case I'm using the JDBC driver to read all the 'BirdSpecies' records from my table 'BirdList' but you can use
other SQL methods to read and write whatever is appropriate for your database
Run your program
Run your program and hopefully your data will appear as a toast as soon as the program loads. Here you see sparrow appear as a 'Toast'. 'Parrot' follows next
Many thanks to the
SQLDroid team for the driver and
Juan-Manuel Fluxà for demo code on loading databases from Assets to Internal Storage
Hope that helps!
