Proper and right way to access and then close all Database connections in Android

Question

I have many databases in a single app, each is used for a specific reason and sometimes I use more than one in a single activity. For an example, in this activity, let's call it Activity A, I define three databases.

DatabaseHandler highscoreDB, db, settingsDB;

and then in the onCreate, I call

settingsDB = new DatabaseHandler(this, "settings");
db = new DatabaseHandler(this, "db");
highscoreDB = new DatabaseHandler(this, "highscore");

I use each separately, like, I may add a new item or update the item by accessing the database.

        highscoreDB.addContact(new Contact("Highscore", 0));

etc..., depending on my needs. My question is, is this a right way to access and create databases?

Then, I started getting crashes when I change the activity, so I looked it up, and I think it is because I dont close my database connections, so I did this in my back button press.

db.close();
highscoreDB.close();
settingsDB.close();
this.finish();
Intent i = new Intent(MainnActivity.this, SecondActivity.class);
startActivity(i);

(Quesiton 2) Is this the right way to close the db connections? Also, in my next activity, let us say Activity B, if I wanna access a database, I do what I have done before which is settingsDB = new DatabaseHandler(this, "settings");, (Question 3) is it fine?

---

Update:

DataBaseHandler.java

public class DatabaseHandler extends SQLiteOpenHelper {

    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
//    private static final String DATABASE_NAME = "contactsManager";

    // Contacts table name
    private static String TABLE_NAME = "Data";

    // Contacts Table Columns names
    private static final String KEY_ID = "id";
    private static final String KEY_NAME = "name";
    private static final String KEY_PH_NO = "phone_number";

    public DatabaseHandler(Context context, String DATABASE_NAME) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_NAME + "("
                + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
                + KEY_PH_NO + " TEXT" + ")";
        db.execSQL(CREATE_CONTACTS_TABLE);
    }

    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);

        // Create tables again
        onCreate(db);
    }

    /**
     * All CRUD(Create, Read, Update, Delete) Operations
     */

    // Adding new contact
    void addContact(Contact contact) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_NAME, contact.getName()); // Contact Name
        values.put(KEY_PH_NO, contact.getPhoneNumber()); // Contact Phone

        // Inserting Row
        db.insert(TABLE_NAME, null, values);
        db.close(); // Closing database connection
    }

    // Getting single contact
    Contact getContact(int id) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(TABLE_NAME, new String[] { KEY_ID,
                        KEY_NAME, KEY_PH_NO }, KEY_ID + "=?",
                new String[] { String.valueOf(id) }, null, null, null, null);
        if (cursor != null)
            cursor.moveToFirst();

        Contact contact = new Contact(Integer.parseInt(cursor.getString(0)),
                cursor.getString(1), cursor.getDouble(2));
        // return contact
        return contact;
    }

    // Getting All Contacts
    public List<Contact> getAllContacts() {
        List<Contact> contactList = new ArrayList<Contact>();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_NAME;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Contact contact = new Contact();
                contact.setID(Integer.parseInt(cursor.getString(0)));
                contact.setName(cursor.getString(1));
                contact.setPhoneNumber(cursor.getDouble(2));
                // Adding contact to list
                contactList.add(contact);
            } while (cursor.moveToNext());
        }

        // return contact list
        return contactList;
    }

    // Updating single contact
    public int updateContact(Contact contact) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_NAME, contact.getName());
        values.put(KEY_PH_NO, contact.getPhoneNumber());

        // updating row
        return db.update(TABLE_NAME, values, KEY_ID + " = ?",
                new String[] { String.valueOf(contact.getID()) });
    }

    public boolean updatedetails(long rowId, String name, double num)
    {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues args = new ContentValues();
        args.put(KEY_ID, rowId);
        args.put(KEY_NAME, name);
        args.put(KEY_PH_NO, num);
        int i =  db.update(TABLE_NAME, args, KEY_ID + "=" + rowId, null);
        return i > 0;
    }

    // Deleting single contact
    public void deleteContact(Contact contact) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_NAME, KEY_ID + " = ?",
                new String[] { String.valueOf(contact.getID()) });
        db.close();
    }


    // Getting contacts Count
    public int getContactsCount() {
        String countQuery = "SELECT  * FROM " + TABLE_NAME;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        cursor.close();

        // return count
        return cursor.getCount();
    }

}

Answer 1

Here is an example of DatabaseOpenHelper that has one database and 5 tables

public class DatabaseOpenHelper extends SQLiteOpenHelper {
private final static String databaseName = "Menu_DB";
private final static int    databaseVersion = 1;

private final static String CatsTable_Name = "tbl_Cats";
private final static String ItemsTable_Name = "tbl_Items";
private final static String PostsTable_Name = "tbl_Posts";
private final static String ContactsTable_Name = "tbl_Contacts";
private final static String FavoriteTable_Name = "tbl_Favorite";

private final static String Record_ID = "_id";
private final static String Record_URL = "URL";

private final static String Cat_Kind = "Cat_Kind";
private final static String Cat_ID = "Cat_ID";
private final static String Cat_Name_en = "Cat_Name_en";
private final static String Cat_Name_ar = "Cat_Name_ar";
private final static String Cat_Image = "Cat_Image";
private final static String Cat_Order = "Cat_Order";

private final static String Item_ID = "Item_ID";
private final static String Item_Name_en = "Item_Name_en";
private final static String Item_Name_ar = "Item_Name_ar";
private final static String Item_About_en = "Item_About_en";
private final static String Item_About_ar = "Item_About_ar";
private final static String Item_Price_en = "Item_Price_en";
private final static String Item_Price_ar = "Item_Price_ar";
private final static String Item_Currency_en = "Item_Currency_en";
private final static String Item_Currency_ar = "Item_Currency_ar";
private final static String Item_Image = "Item_Image";
private final static String Show_Image = "Show_Image";
private final static String Show_Basket = "Show_Basket";
private final static String Item_Order = "Item_Order";

private final static String Item_Qty = "Item_Qty";

public DatabaseOpenHelper(Context context) {
    super(context, databaseName, null, databaseVersion);
}

@Override
public void onCreate(SQLiteDatabase db) {

    String CreateCatsTableSQL = "CREATE TABLE " + CatsTable_Name + " ("     +
            Record_ID   + " INTEGER PRIMARY KEY, "                          +
            Cat_Kind    + " TEXT, "                                         +
            Cat_ID      + " INTEGER, "                                      +
            Cat_Name_en + " TEXT, "                                         +
            Cat_Name_ar + " TEXT, "                                         +
            Cat_Image   + " TEXT, "                                         +
            Show_Image  + " TEXT, "                                         +
            Cat_Order   + " INTEGER);"                                      ;
    db.execSQL(CreateCatsTableSQL);

    String CreateItemsTableSQL = "CREATE TABLE " + ItemsTable_Name + " ("   +
            Record_ID           + " INTEGER PRIMARY KEY, "                  +
            Cat_Kind            + " TEXT, "                                 +
            Cat_ID              + " INTEGER, "                              +
            Item_ID             + " INTEGER, "                              +
            Item_Name_en        + " TEXT, "                                 +
            Item_Name_ar        + " TEXT, "                                 +
            Item_About_en       + " TEXT, "                                 +
            Item_About_ar       + " TEXT, "                                 +
            Item_Price_en       + " TEXT, "                                 +
            Item_Price_ar       + " TEXT, "                                 +
            Item_Currency_en    + " TEXT, "                                 +
            Item_Currency_ar    + " TEXT, "                                 +
            Item_Image          + " TEXT, "                                 +
            Show_Image          + " TEXT, "                                 +
            Show_Basket         + " TEXT, "                                 +
            Item_Order          + " INTEGER);"                              ;
    db.execSQL(CreateItemsTableSQL);

    String CreatePostsTableSQL = "CREATE TABLE " + PostsTable_Name + " ("   +
            Record_ID   + " INTEGER PRIMARY KEY, "                          +
            Record_URL  + " TEXT);"                                         ;
    db.execSQL(CreatePostsTableSQL);

    String CreateContactsTableSQL = "CREATE TABLE " + ContactsTable_Name + " ("     +
            Record_ID   + " INTEGER PRIMARY KEY, "                                  +
            Record_URL  + " TEXT);"                                                 ;
    db.execSQL(CreateContactsTableSQL);

    String CreateFavoriteTableSQL = "CREATE TABLE " + FavoriteTable_Name + " ("     +
            Record_ID       + " INTEGER PRIMARY KEY, "                              +
            Item_ID             + " INTEGER, "                              +
            Item_Name_en        + " TEXT, "                                 +
            Item_Name_ar        + " TEXT, "                                 +
            Item_Price_en       + " TEXT, "                                 +
            Item_Qty            + " INTEGER) "                              ;
    db.execSQL(CreateFavoriteTableSQL);
}

@Override
public void onUpgrade(SQLiteDatabase db, int Old_Version, int New_Version) {
    // TODO Auto-generated method stub
    db.execSQL("DROP TABLE IF EXISTS " + CatsTable_Name);
    db.execSQL("DROP TABLE IF EXISTS " + ItemsTable_Name);
    db.execSQL("DROP TABLE IF EXISTS " + PostsTable_Name);
    db.execSQL("DROP TABLE IF EXISTS " + ContactsTable_Name);
    db.execSQL("DROP TABLE IF EXISTS " + FavoriteTable_Name);
    onCreate(db);
}

}

You should create a DatabaseAdapter, this will contain all methods and functions to control your database and tables, here is an example

public class DatabaseAdapter {

private Context context;
private SQLiteDatabase db;
private DatabaseOpenHelper dbHelper;

private final static String FavoriteTable_Name = "tbl_Favorite";
private final static String CatsTable_Name = "tbl_Cats";
private final static String ItemsTable_Name = "tbl_Items";

private final static String Item_Name_en = "Item_Name_en";
private final static String Item_Name_ar = "Item_Name_ar";
private final static String Item_Price_en = "Item_Price_en";
private final static String Item_Qty = "Item_Qty";

private final static String Record_ID = "_id";

public DatabaseAdapter(Context context) {
    this.context = context;
}

public DatabaseAdapter open() throws SQLException {
    dbHelper = new DatabaseOpenHelper(context);
    db = dbHelper.getWritableDatabase();
    return this;
}

public DatabaseAdapter openForRead() throws SQLException {
    dbHelper = new DatabaseOpenHelper(context);
    db = dbHelper.getReadableDatabase();
    return this;
}

public void close() {
    if (dbHelper != null) {
        dbHelper.close();
    }
}

public long dbCreateCatsRecord(ContentValues contentValue) {
    return db.insert(CatsTable_Name, null, contentValue);
}

public long dbCreateItemsRecord(ContentValues contentValue) {
    return db.insert(ItemsTable_Name, null, contentValue);
}

public Cursor GetCatsTableData(String catKind) {
    return db.rawQuery("SELECT Cat_ID, Cat_Name_en, Cat_Name_ar, Cat_Image, Show_Image FROM tbl_Cats Where Cat_Kind = ? Order By Cat_Order", new String[]{catKind});
}

public Cursor GetCatsImages() {
    return db.rawQuery("SELECT Cat_Name_en, Cat_Image FROM tbl_Cats Where Show_Image = 'True'", new String[]{});
}

public Cursor GetItemsImages() {
    return db.rawQuery("SELECT Item_Name_en, Item_Image FROM tbl_Items Where Show_Image = 'True'", new String[]{});
}

public Cursor GetItemsTableData(String catKind, String catID) {
    return db.rawQuery("SELECT * FROM tbl_Items Where Cat_Kind = ? AND Cat_ID = ? Order By Item_Order", new String[]{catKind, catID});
}

public void dbCreateFavoriteRecord(int Rec_ID, int Qty) {
    Cursor c = db.rawQuery("SELECT * FROM tbl_Items Where " + Record_ID + " = ?", new String[]{String.valueOf(Rec_ID)});
    if (!(c.moveToFirst()) || c.getCount() == 0) {
        Log.i("Insert New Favorite", "No Item Found");
    } else {
        c.moveToLast();
        ContentValues contentValue = new ContentValues();
        contentValue.put(Record_ID, GetNewRecordID(FavoriteTable_Name, Record_ID));
        contentValue.put(Item_Name_en, c.getString(c.getColumnIndex("Item_Name_en")));
        contentValue.put(Item_Name_ar, c.getString(c.getColumnIndex("Item_Name_ar")));
        contentValue.put(Item_Price_en, c.getString(c.getColumnIndex("Item_Price_en")));
        contentValue.put(Item_Qty, Qty);
        db.insert(FavoriteTable_Name, null, contentValue);
    }
    c.close();
}

public void dbDeleteFavRecord(String Rec_ID) {
    db.delete(FavoriteTable_Name, "_id=?", new String[]{Rec_ID});
}

public int GetNewRecordID(String Table_Name, String Record_ID) {
    Cursor c = db.query(Table_Name, new String[]{Record_ID}, null, null, null, null, null);
    if (!(c.moveToFirst()) || c.getCount() == 0) {
        return 1;
    } else {
        c.moveToLast();
        int newRecId = c.getInt(c.getColumnIndex(Record_ID)) + 1;
        c.close();
        return (newRecId);
    }
}

public Cursor GetTableData(String Table_Name) {
    return db.query(Table_Name, null, null, null, null, null, null);
}

public int GetTableCount(String TableName) {
    Cursor c = db.rawQuery("SELECT COUNT(*) AS Q_QTY FROM " + TableName, null);
    if (!(c.moveToFirst()) || c.getCount() == 0) {
        return 0;
    } else {
        c.moveToLast();
        int Q_QTY = c.getInt(c.getColumnIndex("Q_QTY"));
        c.close();
        return Q_QTY;
    }
}

public void ClearTable(String Table_Name) {
    db.delete(Table_Name, null, null);
}

}

How would you access the methods in DatabaseAdapter from your activity(s):

Insert Data in a table:

ContentValues contentValue = new ContentValues();
    DatabaseAdapter databaseAdapter = new DatabaseAdapter(context);
    databaseAdapter.open();
    contentValue.put("_id", databaseAdapter.GetNewRecordID("tbl_Cats", "_id"));
    contentValue.put("Cat_Kind", iCat_Kind);
    contentValue.put("Cat_ID", Integer.parseInt(iCat_ID));
    contentValue.put("Cat_Name_en", iCat_Name_en);
    contentValue.put("Cat_Name_ar", iCat_Name_ar);
    contentValue.put("Cat_Image", iCat_Image);
    contentValue.put("Show_Image", iShow_Image);
    contentValue.put("Cat_Order", Integer.parseInt(iCat_Order));
    databaseAdapter.dbCreateCatsRecord(contentValue);
    databaseAdapter.close();

Here how you can retrieve data from a table

            DatabaseAdapter databaseAdapter = new DatabaseAdapter(getApplicationContext());
            databaseAdapter.openForRead();

            //Get Cats Images
            Cursor c = databaseAdapter.GetCatsImages();
            if (c.moveToFirst())
                do {
                    publishProgress("Image for " + c.getString(c.getColumnIndex("Cat_Name_en")));
                    DownLoadImage(ImagePath, c.getString(c.getColumnIndex("Cat_Image")));
                } while (c.moveToNext());

            //Get Item Images
            c = databaseAdapter.GetItemsImages();
            if (c.moveToFirst())
                do {
                    publishProgress("Image for " + c.getString(c.getColumnIndex("Item_Name_en")));
                    DownLoadImage(ImagePath, c.getString(c.getColumnIndex("Item_Image")));
                } while (c.moveToNext());

            c.close();
            databaseAdapter.close();

In Summary:

• Use One Database.
• Use DatabaseOpenHelper to declare your Database and Tables
• Use DatabaseAdapter to Insert, Update, Clear and retrieve data from tables

Good luck

Answer 2

You had better create a SqliteOpenHelper and close the connection by calling helper.close();