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);

    }

}

retrieve a record from sqlite

Answers (1)

EX 1 (from sqlite_master)

EX 2 (all rows from quotes table)

EX 3 - Specific row using WHERE clause and placeholder ?

Things to note:-

Testing

Output from Test 1 run with no existing database :-

Output from Test 2 - database exists :-

DatabaseHelper used for the above

Related Questions