Question 126505 in Android Studio Question posted in
The official documentation can be found here.

Android Studio – How to access an SQLite database to query in Android Studio

So I found a video online https://www.youtube.com/watch?v=sq45s9gggsw that walks you through the whole process of simply displaying a SQLite database, and once I get this down I can move forward but I can not seem to get it to work. The program crashes every time saying the table I am querying can’t be found, but it also seems that all of the try catch statements are being overlooked in that I deleted the database from my assets folder and it still is giving me the same response. I’ve gone over this for hours and can’t see what is going on her is my code

Main Activity

package com.example.testdatabase;

import androidx.appcompat.app.AppCompatActivity;
import android.widget.TextView;
import android.os.Bundle;


public class MainActivity extends AppCompatActivity {

    TextView lst;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        lst = (TextView) findViewById(R.id.list);
        MyDBHandler dbHandler = new MyDBHandler(this,null,null,2);
        lst.setText(dbHandler.loadHandler());
    }
}

Database Handler

package com.example.testdatabase;


import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;


import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.IOException;

public class MyDBHandler extends SQLiteOpenHelper{
    public static final int DB_VERSION = 2;
    private static final String DB_NAME = "student.db";
    private static String DB_PATH = "/data/user/0/com.example.testdatabase/databases/";
    SQLiteDatabase myDataBase;
    private final Context mContext;


    public MyDBHandler(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, DB_NAME, factory, DB_VERSION);
        this.mContext = context;
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {
    }

    private boolean checkDatabase(){
        try {
            final String mPath = DB_PATH + DB_NAME;
            final File file = new File(mPath);
            if (file.exists())
                return true;
                else
                    return false;
            }   catch (SQLiteException e){
                e.printStackTrace();
                return false;
        }
    }
    private void copyDatabase() throws IOException{
        try{
            InputStream mInputStream = mContext.getAssets().open(DB_NAME);
            String outFileName = DB_NAME;
            OutputStream mOutputStream = new FileOutputStream(outFileName);

            byte[] buffer = new byte[1024];
            int length;
            while((length = mInputStream.read(buffer)) > 0){
                mOutputStream.write(buffer,0,length);
            }
            mOutputStream.flush();
            mOutputStream.close();
        }
        catch (Exception e){
            e.printStackTrace();
        }
    }
    public void createDatabase() throws IOException{
        boolean mDatabaseExist = checkDatabase();
        if (!mDatabaseExist){
            this.getReadableDatabase();
            this.close();
            try{
                copyDatabase();
            }
            catch(IOException mIOException){
                mIOException.printStackTrace();
                throw new Error("Error copying Database");
            } finally {
                this.close();
            }
        }
    }
    @Override
    public synchronized  void close(){
        if (myDataBase != null)
            myDataBase.close();
        SQLiteDatabase.releaseMemory();
        super.close();
    }
    public String loadHandler(){
        try{
            createDatabase();
        }
        catch (IOException e){
            e.printStackTrace();
        }
        String result = "";
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor c = db.rawQuery("select * from Student", null);

        while(c.moveToNext()){
            int result_id = c.getInt(0);
            String result_name = c.getString(1);
            result += String.valueOf(result_id) + " " + result_name + System.getProperty("line.separator");
        }
        c.close();
        db.close();

        return result;
    }
}

The video also has you create a Student Class which I don’t see the point of

package com.example.testdatabase;

public class Student {
    private int _id;
    private String _studentName;

    public Student(int id, String studentName){
        this._id = id;
        this._studentName = studentName;
    }
    public void setID(int id){
        this._id = id;
    }
    public int getID(){
        return this._id;
    }
    public void setStudentName(String studentName){
        this._studentName = studentName;
    }
    public String getStudentName(){
        return this._studentName;
    }
}

I keep getting a result in the debug as E/SQLiteLog: (1) no such table: Student
with errors at the line in the loadHandler method that calls the SQL query. It seems like it is skipping past all the try/catch statements to check if the database even exists.

Any help would be VERY appreciated

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    So I ended up going with a different method that worked out quite well. This is what I did:

    
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;

import com.readystatesoftware.sqliteasset.SQLiteAssetHelper;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;


public class DBHelper extends SQLiteAssetHelper {

    public static final String DBNAME = "example.db"; //<<<< must be same as file name
    public static final int DBVERSION = 1;
    SQLiteDatabase myDataBase;
    private final Context mContext;

    public DBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, DBNAME,null, DBVERSION);
        this.mContext = context;
    }


    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {}

    private boolean checkDatabase(){
        try {
            final String mPath = DBNAME;
            final File file = new File(mPath);
            if (file.exists())
                return true;
            else
                return false;
        }   catch (SQLiteException e){
            e.printStackTrace();
            return false;
        }
    }
    private void copyDatabase() throws IOException {
        try{ InputStream mInputStream = mContext.getAssets().open(DBNAME);
            String outFileName = DBNAME;
            OutputStream mOutputStream = new FileOutputStream(outFileName);

            byte[] buffer = new byte[1024];
            int length;
            while((length = mInputStream.read(buffer)) > 0){
                mOutputStream.write(buffer,0,length);
            }
            mOutputStream.flush();
            mOutputStream.close();
        }
        catch (Exception e){
            e.printStackTrace();
        }
    }
    public void createDatabase() throws IOException{
        boolean mDatabaseExist = checkDatabase();
        if (!mDatabaseExist){

            try{
                copyDatabase();
            }
            catch(IOException mIOException){
                mIOException.printStackTrace();
                throw new Error("Error copying Database");
            } finally {
                this.close();
            }
        }
    }
    @Override
    public synchronized  void close(){
        if (myDataBase != null)
            myDataBase.close();
        SQLiteDatabase.releaseMemory();
        super.close();
    }
    public String loadHandler(String sqlinput){
        try{
            createDatabase();
        }
        catch (IOException e){
            e.printStackTrace();
        }
        String result = "";
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor c = db.rawQuery("select * from Appliances where appliance_id IN (" + sqlinput + ")", null);

        while(c.moveToNext()){
            int result_id = c.getInt(0);
            String result_name = c.getString(1);
            Number result_va = c.getDouble(2);
            Number result_w = c.getDouble(3);

            result += String.valueOf(result_id) + " " + result_name + " " + result_va + " " + result_w + System.getProperty("line.separator");
        }
        c.close();
        db.close();

        return result;
    }
}

    What is unique is that i'm using import com.readystatesoftware.sqliteasset.SQLiteAssetHelper; Which is allowing my program to do exactly what it is that I was hoping it would, allowing me to query the code from other classes of the code.


  2. 0

    This is an issue with the code you have copied not coping with later Android Versions.

    When the database does not exists (the first run). Then it uses this.getReadableDatabase(); to overcome the issue that the databases folder does not exist. Without the folder the copy fails.

    • The this.getReadableDatabase(); does get an empty database and creates the databases folder in the process.

    BUT on Android version 9+ the default logging mode is WAL (Journal Mode was the previous default). What happens is that the this.getReadableDatabase(); creates a database and the WAL file (_wal) is created for the newly created database.

    The newly created database is then overwritten by the copy but the WAL file remains.

    When the attempt is made to open the database it sees that the WAL file does not belong to the copied database. The exception is captured and and the open creates a database, so that it can fulfil it’s task. The database is therefore empty.

    It is wrong to use getReadableDatabase(); when all you have to do is get the parent of the of the DB_PATH and do a mkDirs().

    So I would suggest using :-

    private boolean checkDatabase(){
    boolean rv = true;
    final String mPath = DB_PATH + DB_NAME;
    File file = new File(mPath);
    if (!file.exists()) {
        rv = false;
        if (!(file.getParentFile()).exists()) {
            (file.getParentFile()).mkdirs();
        }
    }
    return rv;
}
    • so this will create the directories in DB_PATH if it doesn’t exist.

    In conjunction with :-

    public void createDatabase() throws IOException{
    boolean mDatabaseExist = checkDatabase();
    if (!mDatabaseExist){
        //this.getReadableDatabase(); /*<<<<<<<<<< COMMENTED OUT*/ 
        //this.close();  /*<<<<<<<<<< COMMENTED OUT*/ 
        try{
            copyDatabase();
        }
        catch(IOException mIOException){
            mIOException.printStackTrace();
            throw new Error("Error copying Database");
        } finally {
            this.close();
        }
    }
}
    • so this will not use getReadable database but instead the databases folder would have been created.

    Working Example

    Here’s a working example based upon your code but quite heavily modified.

    MyDBHandler

    public class MyDBHandler extends SQLiteOpenHelper {
   public static final int DB_VERSION = 2;
   private static final String DB_NAME = "student.db";
   private static String DB_FULL_PATH;
   private static MyDBHandler INSTANCE;
   private SQLiteDatabase myDataBase;


   private MyDBHandler(Context context) {
      super(context, DB_NAME, null, DB_VERSION);
      boolean databaseCopied;
      DB_FULL_PATH = context.getDatabasePath(DB_NAME).getPath();
      try {
        databaseCopied = createDatabase(context);
      } catch (IOException e) {
         e.printStackTrace();
         throw new RuntimeException("Failed to copy Database from asset File");
      }
      myDataBase = this.getWritableDatabase();
      if (databaseCopied) {
         logDbSchema();
      }
   }

   public static MyDBHandler getInstance(Context context) {
      if (INSTANCE == null) {
         INSTANCE = new MyDBHandler(context);
      }
      return INSTANCE;
   }

   @Override
   public void onCreate(SQLiteDatabase sqLiteDatabase) {
   }

   @Override
   public void onUpgrade(SQLiteDatabase db, int i, int i1) {
   }

   /* CHANGED */
   private boolean checkDatabase(){
      boolean rv = true;
      File dbfile = new File(DB_FULL_PATH);
      if (!dbfile.exists()) {
         rv = false;
         if (!dbfile.getParentFile().exists()) {
            dbfile.getParentFile().mkdirs();
         }
      }
      return rv;
   }
   private void copyDatabase(Context context) throws IOException {
      InputStream assetFile;
      OutputStream dbFile;
      try{
         assetFile = context.getAssets().open(DB_NAME);
         dbFile = new FileOutputStream(new File(DB_FULL_PATH));

         byte[] buffer = new byte[1024 * 8];
         int length;
         while((length = assetFile.read(buffer)) > 0){
            dbFile.write(buffer,0,length);
         }
         dbFile.flush();
         dbFile.close();
         assetFile.close();
      }
      catch (Exception e){
         e.printStackTrace();
      }
   }

   /* Amended to return boolean that indicates whether ro not ass was copied(attempted) */
   /* true then database did not exist and copy was attempted */
   public boolean createDatabase(Context context) throws IOException{
      boolean rv = !checkDatabase();
      if (rv){
         try{
            copyDatabase(context);
         }
         catch(IOException mIOException){
            mIOException.printStackTrace();
            throw new Error("Error copying Database");
         } finally {
            this.close();
         }
      }
      return rv;
   }

   /* Should really only close the database as a last resort
      opening again and again it is resource intensive
    */
   @Override
   public synchronized  void close(){
      if (myDataBase != null)
         myDataBase.close();
      SQLiteDatabase.releaseMemory();
      super.close();
   }

   /* Added for debugging */
   private void logDbSchema() {
      Cursor c = myDataBase.query("sqlite_master",null,null,null,null,null,null);
      DatabaseUtils.dumpCursor(c);
      c.close();
   }
}

    NOTES

    • A singleton approach has been utilised thus the constructor has been made private. Instead you instantiate a MyDBHandler via the getInstance method which will either retrieve an existing instance or if the existing instance is null get a new instance.
    • The test to see if the database exists is undertaken when getting the instance.
    • The loadHandler has been done away with.
    • The database path is not hard coded but ascertained via the App’s Context (this future proofs the App should path designations be changed)
    • the checkDatabase method returns a boolean, true if the database needed to be created, false if not. The result is then utilised to extract and dump the database schema (for debugging).
    • it is strongly suggest that you do not overuse the close method (if even ever using it).

    To demonstrate MainActivity :-

    public class MainActivity extends AppCompatActivity {

    MyDBHandler dbHandler;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        dbHandler = MyDBHandler.getInstance(this);
        Cursor testdb = dbHandler.getWritableDatabase().query("sqlite_master",null,null,null,null,null,null);
        DatabaseUtils.dumpCursor(testdb);
        testdb.close();
    }
}

    and the asset database :-

    enter image description here

    Re the comment

    Thank you for your response I’m still encountering the same issue, and i’m wondering if what you are saying is the case then the line public String loadHandler(){ try{ createDatabase(); } catch (IOException e){ e.printStackTrace(); } String result = ""; SQLiteDatabase db = this.getReadableDatabase(); Might be creating still the issue. What would I replace that with?

    The Working Example above does away with the loadHandler. However, you will notice that the getInstance method does in fact use getWritableDatabase (getReadableDatabase actually gets a writable database unless there are issues in that the database cannot be written to).

    HOWEVER, I suspect that you ongoing issue might be that you are unaware that once the database exists be it empty of tables or not, then if exists and the copy from the asset will not replace the database. You have to delete the database, the easiest way is to uninstall the App and rerun the App.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search