Static database connection SQLite

Question

I would like to ask anyone to please help me to modify my database connection code. What I have now is it's a part of one of my method class and it will make a connection every time I call it ( which I know its very inefficient). But when I try to move the connection part out from the method it breaks the code. I just wonder if I could make a static class somewhere so I can reuse it in other method as well.

Thanks so much for any help in advance, it is really appreciated.

Here is the method code:

    public void getListDetail(){

   //listDetailData.clear();

    ShoppingListDatabase databaseConnection = new ShoppingListDatabase(this);
    final SQLiteDatabase db = databaseConnection.open();
    final ArrayList<ShoppingItem> lists = ShoppingListItemTable.selectAllItems(db, selectedID);
    databaseConnection.close();

    //create a list adapter and set adapter
    ShoppingListItemAdapter adapter = new ShoppingListItemAdapter(this, R.layout.activity_item_detail_list, lists);
    ListView_ListDetail = findViewById(R.id.ListView_ListDetail);
    ListView_ListDetail.setAdapter(adapter);
    adapter.notifyDataSetChanged();
   // ((ArrayAdapter<String>)ListView_ListDetail.getAdapter()).notifyDataSetChanged();
}

Database class:

package com.puyakul.prin.psychic_shopping;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.ContactsContract;
import android.util.Log;

public class ShoppingListDatabase {

    private static final String TAG = "ShoppingListDatabase";
    private static final String DATABASE_NAME = "ShoppingListDatabase";
    private static final int DATABASE_VERSION = 3;

    private SQLiteDatabase mDb;
    private DatabaseHelper mDbHealper;
    private final Context mCtx;

    public ShoppingListDatabase(Context ctx){
        this.mCtx = ctx;
    }

    /**
     * DatabaseHelper class.
     *
     * Database helper class to manage connections with the database.
     */
    private static class DatabaseHelper extends SQLiteOpenHelper
    {
        DatabaseHelper(Context context){
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            Log.d(TAG, "DatabaseHelper onCreate");
            db.execSQL(ShoppingListItemTable.CREATE_STATEMENT);
            db.execSQL(ShoppingListTable.CREATE_STATEMENT);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.d(TAG, "DatabaseHelper onUpgrade");
            db.execSQL("DROP TABLE IF EXISTS " + ShoppingListItemTable.TABLE_NAME);
            db.execSQL("DROP TABLE IF EXISTS " + ShoppingListTable.TABLE_NAME);
            onCreate(db); //this will recreate the database as if it were new
        }
    }

    public SQLiteDatabase open(){
        mDbHealper = new DatabaseHelper(mCtx);
        mDb = mDbHealper.getReadableDatabase();
        return mDb;
    }

    public void close(){
        mDb = null;
    }

}

I have tried to declare variables within onCreate so I can use with other methods in the class like this

    //================DATABASE CONNECTION=====================//
    private ShoppingListDatabase databaseConnection;
    private SQLiteDatabase db  = databaseConnection.open();


    private String selectedList;
    private int selectedID;


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        this.getWindow().setSoftInputMode(WindowManager.LayoutParams.SOFT_INPUT_STATE_ALWAYS_HIDDEN);
        setContentView(R.layout.activity_main_lists_detail);

      ShoppingListDatabase databaseConnection = new ShoppingListDatabase(this);
      SQLiteDatabase db = databaseConnection.open();
      lists = ShoppingListItemTable.selectAllItems(db, selectedID);

and this is the error

 Process: com.puyakul.prin.psychic_shopping, PID: 5635
    java.lang.RuntimeException: Unable to instantiate activity ComponentInfo{com.puyakul.prin.psychic_shopping/com.puyakul.prin.psychic_shopping.MainListsDetail}: java.lang.NullPointerException: Attempt to invoke virtual method 'android.database.sqlite.SQLiteDatabase com.puyakul.prin.psychic_shopping.ShoppingListDatabase.open()' on a null object reference
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2548)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2707)
        at android.app.ActivityThread.-wrap12(ActivityThread.java)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1460)
        at android.os.Handler.dispatchMessage(Handler.java:102)
        at android.os.Looper.loop(Looper.java:154)
        at android.app.ActivityThread.main(ActivityThread.java:6077)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:866)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:756)
     Caused by: java.lang.NullPointerException: Attempt to invoke virtual method 'android.database.sqlite.SQLiteDatabase com.puyakul.prin.psychic_shopping.ShoppingListDatabase.open()' on a null object reference
        at com.puyakul.prin.psychic_shopping.MainListsDetail.<init>(MainListsDetail.java:46)
        at java.lang.Class.newInstance(Native Method)
        at android.app.Instrumentation.newActivity(Instrumentation.java:1078)
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2538)

MikeT · Accepted Answer · 2018-05-13 05:52:31Z

Opening and closing the connection can be inefficient/costly so perhaps introduce/use the following :-

public SQLiteDatabase open(){
    if (mDb == null) {
        mDbHealper = new DatabaseHelper(mCtx);
        mDb = mDbHealper.getReadableDatabase();
    }
    return mDb;
}

or alternately :-

public synchronized SQLiteDatabase open(){
    if (mDb == null) {
        mDbHealper = new DatabaseHelper(mCtx);
        mDb = mDbHealper.getReadableDatabase();
    }
    return mDb;
}

and never call the close, except when the main activity is being destroyed. Then you will retrieve the one connection.

Here's some example uses based upon your ShoppingListDatabase class.

First a modified class with a few extra methods inside (addShoppingList, getAllShoppingListsAsCursor and logDBTables) and just a very simple shoppinglist table :-

public class ShoppingListDatabase {

    private static final String TAG = "ShoppingListDatabase";
    private static final String DATABASE_NAME = "ShoppingListDatabase";
    private static final int DATABASE_VERSION = 3;

    private static final String TBNAME = "shoppinglist";
    public static final String COL_SHOPPINGLIST_NAME = "shoppinglist_name";

    private SQLiteDatabase mDb;
    private DatabaseHelper mDbHealper;
    private final Context mCtx;

    public ShoppingListDatabase(Context ctx){
        this.mCtx = ctx;
    }

    /**
     * DatabaseHelper class.
     *
     * Database helper class to manage connections with the database.
     */
    private static class DatabaseHelper extends SQLiteOpenHelper
    {
        DatabaseHelper(Context context){
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            Log.d(TAG, "DatabaseHelper onCreate");
            //db.execSQL(ShoppingListItemTable.CREATE_STATEMENT);
            //db.execSQL(ShoppingListTable.CREATE_STATEMENT);
            String crtsql = "CREATE TABLE If NOT EXISTS " + TBNAME + "(" +
                    COL_SHOPPINGLIST_NAME + " TEXT " +
                    ")";
            db.execSQL(crtsql);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.d(TAG, "DatabaseHelper onUpgrade");

            //db.execSQL("DROP TABLE IF EXISTS " + ShoppingListItemTable.TABLE_NAME);
            //db.execSQL("DROP TABLE IF EXISTS " + ShoppingListTable.TABLE_NAME);
            onCreate(db); //this will recreate the database as if it were new
        }
    }

    public synchronized SQLiteDatabase open(){
        if (mDb == null) {
            mDbHealper = new DatabaseHelper(mCtx);
            mDb = mDbHealper.getReadableDatabase();
        }
        return mDb;
    }

    public void close(){
        mDb = null;
    }

    public long addShoppingList(String name) {
        ContentValues cv = new ContentValues();
        cv.put(COL_SHOPPINGLIST_NAME,name);
        return mDb.insert(TBNAME,null,cv);
    }

    public Cursor getAllShoppingListAsCursor() {
        return mDb.query(TBNAME,
                null,
                null,
                null,
                null,null,
                null
        );
    }

    public void logDBTables() {
        Cursor csr = mDb.query("sqlite_master",null,null,null,null,null,null);
        while (csr.moveToNext()) {
            Log.d(TAG,"Item " +
                    csr.getString(csr.getColumnIndex("name")) +
                    " was created using " +
                    csr.getString(csr.getColumnIndex("sql"))
            );
        }
    }
}

Here's code from an Activity (that uses the Database connection in various ways) :-

public class MainActivity extends AppCompatActivity {

    ShoppingListDatabase mSL;

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

        mSL = new ShoppingListDatabase(this);
        mSL.open();

        SO50312618();
        SO50312618_other();
        Cursor csr = mSL.getAllShoppingListAsCursor();
        while(csr.moveToNext()) {
            Log.d("SL INFO","Shopping List " + String.valueOf(csr.getPosition() + 1) +
                    " is " +
                    csr.getString(csr.getColumnIndex(ShoppingListDatabase.COL_SHOPPINGLIST_NAME))
            );
        }
    }

    private void SO50312618() {
        ShoppingListDatabase databaseConnection = new ShoppingListDatabase(this);
        SQLiteDatabase db = databaseConnection.open();
        databaseConnection.logDBTables();
    }

    private void SO50312618_other() {
        mSL.addShoppingList("List001");
        mSL.addShoppingList("List002");
    }
}

The results being :-

05-13 05:36:24.290 4245-4245/soanswers.soanswers D/ShoppingListDatabase: Item android_metadata was created using CREATE TABLE android_metadata (locale TEXT)
    Item shoppinglist was created using CREATE TABLE shoppinglist(shoppinglist_name TEXT )
05-13 05:36:24.302 4245-4245/soanswers.soanswers D/SL INFO: Shopping List 1 is List001
    Shopping List 2 is List002

All the various uses will use the same single connection.

Hi, Thx so much for the reply, sorry for my writing skill English isn't my first language =(, what I'm trying to make a connection happen only once in onCreate then I can use it in other methods within the class Please see the question I will add more detail and error
You can't use onCreate to make a connection as it only gets called once when the database is created (and actually connected i.e. by the time it is called the database has actually been created albiet without any user defined tables (sqlite_master and for android, android_metadata will exist)) and is then only called when getReadableDatabase or getWritableDatabase is called (either directly or implicitly).

Collectives™ on Stack Overflow

Static database connection SQLite

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related