retrieve a record from sqlite

Question

Sorry for my bad English.
I am new to Android Studio.

I have an external database. With columns id, text1, text2...text6 Somehow i succeeded to add that database to my application following a youtube tutorial. That works fine.

I added it to asset and in mainActivity is copied to data/data/mypackage/databases. I checked that in emulator file explorer.

Now I need to get data from that database but I can't figure it out how to do it.

id column is auto increment integer. I have an array in my application containing x randomly generated integers in a certain range. Now i want to make a query to database for the first integer from array and update textViews in my activity with the results. After user clicks on a button i need to send another query for next integer from the array and so on.

I don't know hot to open database, send query... I tried so many tutorials, searched on the Internet for the solution but non of them worked. Now i just confused.

I have experience in SQL. That shouldn't be a problem. In PHP and MySql it can be done with just few lines of code. Can anybody help me to solve the problem.

This is my dbHelper

package com.examle.mydbapp;

import android.annotation.SuppressLint;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.SQLException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.util.Log;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Locale;

class dbHelper extends SQLiteOpenHelper {


    private static final String DATABASE_PATH = "/data/data/com.example.mydbapp/databases/";
    private static final String DATABASE_NAME = "quotes.db";
    private  static final int SCHEMA_VERSION = 1;

    public SQLiteDatabase dbSglite;

    private final Context myContext;

    public dbHelper(Context context) {
        super(context, DATABASE_NAME, null, SCHEMA_VERSION);
        this.myContext=context;
       }

       @Override
    public void onCreate(SQLiteDatabase db){

       }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public void createDatabase(){
        createDB();
    }

    private void createDB(){

        boolean dbExist = DBExists();

        if(!dbExist){
            this.getReadableDatabase();
            copyDBFromResource();

        }


    }

    private boolean DBExists(){

        SQLiteDatabase db = null;

        try {
            String databasePath = DATABASE_PATH+DATABASE_NAME;
            db = SQLiteDatabase.openDatabase(databasePath,null, SQLiteDatabase.OPEN_READWRITE);
            db.setLocale(Locale.getDefault());
            db.setLockingEnabled(true);
            db.setVersion(1);
        } catch (SQLiteException e) {

            Log.e("SqlHelper", "database not found");
        }

        if (db != null) {
            db.close();
        }
        return db != null ? true : false;

    }

    private void copyDBFromResource() {
        InputStream inputStream = null;
        OutputStream outputStream = null;
        String dbFilePath=DATABASE_PATH+DATABASE_NAME;

        try {
            inputStream = myContext.getAssets().open(DATABASE_NAME);
            outputStream = new FileOutputStream(dbFilePath);

            byte[] buffer = new byte[1024];
            int length;
            while ((length=inputStream.read(buffer))>0){
                outputStream.write(buffer, 0, length);
            }

            outputStream.flush();
            outputStream.close();
            inputStream.close();

        } catch (IOException e) {

            throw new Error("Problem copying database.");
        }

    }

    public void openDataBase() throws SQLException {

        String myPath = DATABASE_PATH + DATABASE_NAME;
        dbSglite = SQLiteDatabase.openDatabase(myPath, null,
                SQLiteDatabase.OPEN_READWRITE);

    }

}

Answer 1

in short, assuming you want to access the data from an activity, then

You could a) create an instance of the databasehelper b) invoke the databasehelper's createDatabase method and then c) retrieve an SQLiteDatabase instance by invoking the databasehelper's getWriteableDatbase method.

(You have probably already done a and b)

e.g. :-

    dbhlpr = new dbHelper(this);
    // Create the Database if need be
    dbhlpr.createDatabase();
    // An example of getting a instance outside of the helper
    SQLiteDatabase db = dbhlpr.getWritableDatabase();

You could also use SQLiteDatabase db = dbhlpr.dbSglite; instead of SQLiteDatabase db = dbhlpr.getWritableDatabase();

• where in the above dbhlpr is a class variable defined using dbHelper dbhlpr;

You can then retrieve data from the tables by creating a Cursor(s). In the following examples (preceded by the previous example code and assuming that the table is called quotes) you could do something along the lines of the following examples :-

EX 1 (from sqlite_master)

    Cursor csr = db.query("sqlite_master",null,null,null,null,null,null);
    while (csr.moveToNext()) {
        String tblinfo = "Row " + csr.getPosition();
        for (int i=0; i < csr.getColumnCount(); i++) {
            tblinfo = tblinfo + " Column = " + csr.getColumnName(i) + " Value = " + csr.getString(i);
        }
        Log.d("SQLITEINFO",tblinfo);
    }
    csr.close();

EX 2 (all rows from quotes table)

    // Get and process data from the quotes table
    Cursor csr2 = db.query("quotes",null,null,null,null,null,null);
    while (csr2.moveToNext()) {
        String tblinfo = "Row " + csr2.getPosition();
        for (int i=0; i < csr2.getColumnCount(); i++) {
            tblinfo = tblinfo + " Column = " + csr2.getColumnName(i) + " Value = " + csr2.getString(i);
        }
        Log.d("CURSOR 2 INFO",tblinfo);
    }
    csr2.close();

EX 3 - Specific row using WHERE clause and placeholder ?

    // Get and processs data for a specific row according to id
    Cursor csr3 = db.query("quotes",null,"id=?",new String[]{"1"},null,null,null);
    if (csr3.moveToFirst()) {
        Log.d("CURSOR 3 INFO",
                " ID is" + csr3.getLong(0) +
                        " ID is " + csr3.getLong(csr3.getColumnIndex("id")) +
                        " TEXT1 is " + csr3.getString(csr3.getColumnIndex("text1"))
                // ..... etc
        );
    }
    csr3.close();

Things to note:-

• An empty cursor is a valid cursor a; query will not return a null cursor.
• Using column offsets, e.g. csr3.getLong(0) can be problematic, hence the alternative more flexible csr3.getLong(csr3.getColumnIndex("id"))
• When a Cursor is returned from the query method it's position is before the first row (-1). To access the data you must use a move???? method e.g. csr3.moveToFirst(). The move???? methods return true if the move was made, false if it couldn't be made.

Testing

The above code has been tested using a DatabaseHelper that is very much the same as the DatabaseHelper provided in the question.

The Database was created outside of the app with a single table added named quotes with two rows of data added as per :-

Output from Test 1 run with no existing database :-

-15 11:30:20.066 8446-8446/? E/SQLiteLog: (14) cannot open file at line 30046 of [9491ba7d73]
11-15 11:30:20.066 8446-8446/? E/SQLiteLog: (14) os_unix.c:30046: (2) open(/data/data/mjt.usingrecyclerviews/databases/quotes.db) - 
11-15 11:30:20.066 8446-8446/? E/SQLiteDatabase: Failed to open database '/data/data/mjt.usingrecyclerviews/databases/quotes.db'.
android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14): Could not open database
    at android.database.sqlite.SQLiteConnection.nativeOpen(Native Method)
    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:209)
    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:193)
    at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:463)
    at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:185)
    at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:177)
    at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:806)
    at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:791)
    at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:694)
    at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:669)
    at mjt.usingrecyclerviews.dbHelper.DBExists(dbHelper.java:68)
    at mjt.usingrecyclerviews.dbHelper.createDB(dbHelper.java:54)
    at mjt.usingrecyclerviews.dbHelper.createDatabase(dbHelper.java:49)
    at mjt.usingrecyclerviews.MainActivity.onCreate(MainActivity.java:27)
    at android.app.Activity.performCreate(Activity.java:5990)
    at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1106)
    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2278)
    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2387)
    at android.app.ActivityThread.access$800(ActivityThread.java:151)
    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1303)
    at android.os.Handler.dispatchMessage(Handler.java:102)
    at android.os.Looper.loop(Looper.java:135)
    at android.app.ActivityThread.main(ActivityThread.java:5254)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:903)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698)
11-15 11:30:20.066 8446-8446/? E/SqlHelper: database not found
11-15 11:30:20.093 8446-8446/? D/ONCREATE: OnCreate Method Called.
11-15 11:30:20.096 8446-8446/? D/SQLITEINFO: Row 0 Column = type Value = table Column = name Value = quotes Column = tbl_name Value = quotes Column = rootpage Value = 2 Column = sql Value = CREATE TABLE quotes (id INTEGER PRIMARY KEY, text1 TEXT,  text2 TEXT, text3 TEXT, text4 TEXT, text5 TEXT)
11-15 11:30:20.096 8446-8446/? D/SQLITEINFO: Row 1 Column = type Value = table Column = name Value = android_metadata Column = tbl_name Value = android_metadata Column = rootpage Value = 3 Column = sql Value = CREATE TABLE android_metadata (locale TEXT)
11-15 11:30:20.096 8446-8446/? D/CURSOR 2 INFO: Row 0 Column = id Value = 1 Column = text1 Value = Some quote Column = text2 Value = Another quote Column = text3 Value = Yet another quote Column = text4 Value = This is a quote Column = text5 Value = Too many quotes
11-15 11:30:20.096 8446-8446/? D/CURSOR 2 INFO: Row 1 Column = id Value = 2 Column = text1 Value = Seccond quote Column = text2 Value = Third Quote Column = text3 Value = Fourth Quote Column = text4 Value = Fifth Quote Column = text5 Value = Sixth Quote
11-15 11:30:20.096 8446-8446/? D/CURSOR 3 INFO:  ID is1 ID is 1 TEXT1 is Some quote

Note! File error was trapped and bypassed/handled (i.e. just because the database didn't exist)

Output from Test 2 - database exists :-

11-15 11:33:17.111 8511-8511/mjt.usingrecyclerviews D/SQLITEINFO: Row 0 Column = type Value = table Column = name Value = quotes Column = tbl_name Value = quotes Column = rootpage Value = 2 Column = sql Value = CREATE TABLE quotes (id INTEGER PRIMARY KEY, text1 TEXT,  text2 TEXT, text3 TEXT, text4 TEXT, text5 TEXT)
11-15 11:33:17.111 8511-8511/mjt.usingrecyclerviews D/SQLITEINFO: Row 1 Column = type Value = table Column = name Value = android_metadata Column = tbl_name Value = android_metadata Column = rootpage Value = 3 Column = sql Value = CREATE TABLE android_metadata (locale TEXT)
11-15 11:33:17.111 8511-8511/mjt.usingrecyclerviews D/CURSOR 2 INFO: Row 0 Column = id Value = 1 Column = text1 Value = Some quote Column = text2 Value = Another quote Column = text3 Value = Yet another quote Column = text4 Value = This is a quote Column = text5 Value = Too many quotes
11-15 11:33:17.112 8511-8511/mjt.usingrecyclerviews D/CURSOR 2 INFO: Row 1 Column = id Value = 2 Column = text1 Value = Seccond quote Column = text2 Value = Third Quote Column = text3 Value = Fourth Quote Column = text4 Value = Fifth Quote Column = text5 Value = Sixth Quote
11-15 11:33:17.113 8511-8511/mjt.usingrecyclerviews D/CURSOR 3 INFO:  ID is1 ID is 1 TEXT1 is Some quote

DatabaseHelper used for the above

As the packagename was hard coded in the Databasehelper changes had to be made. So rather than hard code the package the database path (without databasename and separatinf /) is obtained via context.getDatabasePath(DATABASE_NAME).getParent();. Just in case the databases folder/directory doesn't exist the changed helper will create the directories using File databasesdir = new File(myContext.getDatabasePath(DATABASE_NAME).getParent()); databasesdir.mkdirs();

The full Helper is :-

class dbHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "quotes.db";
    private  static final int SCHEMA_VERSION = 1;

    public SQLiteDatabase dbSglite;
    private String mDBPAth;

    private final Context myContext;

    public dbHelper(Context context) {
        super(context, DATABASE_NAME, null, SCHEMA_VERSION);
        this.myContext=context;
        this.mDBPAth = context.getDatabasePath(DATABASE_NAME).getParent();
    }

    @Override
    public void onCreate(SQLiteDatabase db){
        Log.d("ONCREATE","OnCreate Method Called.");

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public void createDatabase(){
        createDB();
    }

    private void createDB(){

        boolean dbExist = DBExists();

        if(!dbExist){
            copyDBFromResource();

        }
        dbSglite = getWritableDatabase();
    }

    private boolean DBExists(){

        SQLiteDatabase db = null;

        try {
            String databasePath = myContext.getDatabasePath(DATABASE_NAME).getPath();
            db = SQLiteDatabase.openDatabase(databasePath,null, SQLiteDatabase.OPEN_READWRITE);
            db.setLocale(Locale.getDefault());
            db.setLockingEnabled(true);
            db.setVersion(1);
        } catch (SQLiteException e) {

            Log.e("SqlHelper", "database not found");
        }

        if (db != null) {
            db.close();
        }
        return db != null;
    }

    private void copyDBFromResource() {
        InputStream inputStream = null;
        OutputStream outputStream = null;

        try {
            inputStream = myContext.getAssets().open(DATABASE_NAME);
            File databasesdir =  new File(myContext.getDatabasePath(DATABASE_NAME).getParent());
            databasesdir.mkdirs();
            outputStream = new FileOutputStream(mDBPAth+"/"+DATABASE_NAME);
            byte[] buffer = new byte[1024];
            int length;
            while ((length=inputStream.read(buffer))>0){
                outputStream.write(buffer, 0, length);
            }

            outputStream.flush();
            outputStream.close();
            inputStream.close();

        } catch (IOException e) {
            e.printStackTrace();
            throw new Error("Problem copying database.");
        }
    }

    public void openDataBase() throws SQLException {

        String myPath = myContext.getDatabasePath(DATABASE_NAME).getPath();
        dbSglite = SQLiteDatabase.openDatabase(myPath, null,
                SQLiteDatabase.OPEN_READWRITE);
    }
}