or “How to read SQLite DB from a desktop”
SQLite is the relational, embedded, ACID compliant database that comes with Android. Due to this fact it is certainly the most deployed DB engine on this planet. In case your application need to have CRUD features for local persisted data and the complexity level is beyond a simple text file, you have to consider it.
A challenge is to look into the (raw) DB from your desktop (if you dont want to build and integrate a DB viewer into your app). As Android apps store databases into their respective /data subfolder and if you don’t have a rooted phone, you cant look inside this folder.
I am not aware of any tool that can open a connection to the DB remotely, so the best way is to copy the DB file into the accessible SD card (or whatever the phone and its manufacturer considers as SD card, even the internal memory mounted as SD card), download it to your desktop and open it with a tool like the SQLite DB Browser.
Let’s put some sourcecode here as reference
Create a simple demo DB
No bells and whistles, no helper classes, etc. just the most simple way to create DB and a table.
private void createDB() {
SQLiteDatabase sampleDB = this.openOrCreateDatabase("MYDEMODB", MODE_PRIVATE, null);
sampleDB.execSQL("CREATE TABLE IF NOT EXISTS MYTABLE (Last VARCHAR, First VARCHAR, Role VARCHAR);");
sampleDB.execSQL("INSERT INTO MYTABLE Values ('Smith','John','CEO');");
sampleDB.execSQL("INSERT INTO MYTABLE Values ('Thomson','Allan','CTO');");
sampleDB.close();
}
Copy the DB to your SD card
private void exportDB() {
File mySd = Environment.getExternalStorageDirectory();
File myData = Environment.getDataDirectory();
FileChannel src = null;
FileChannel des = null;
String currentDBPath = "/data/" + getApplicationContext().getPackageName() + "/databases/MYDEMODB";
String exportDBPath = "MYDEMODB";
File currentDB = new File(myData, currentDBPath);
File backupDB = new File(mySd, exportDBPath);
try {
src = new FileInputStream(currentDB).getChannel();
des = new FileOutputStream(backupDB).getChannel();
des.transferFrom(src, 0, src.size());
src.close();
des.close();
} catch (IOException e) {
e.printStackTrace();
}
}
After download to your local drive you can use the SQLite Browser to open the file. Very useful data debugging or for apps that collect data and you can’t implement an upload of the data to a server via the internet connection.
SQLite DB Browser (Structure View)
SQLite DB Browser (Data View)
The JavaDude Weblog 