SQLite Database Representation

Question

I have a question concerning the handling of SQLite databases in Android.

I am programming an Application, which is doing some Data Reporting out of a SQLite Database. These App consists of one Activity which owns serveral Fragments. These Fragments offer various tools to plot and manipulate data and communicate via Interfaces.

I also need one or several classes to represent my database and offer CRUD functions.

But now I don't know where to implement these classes. Does every Fragment implement its own class, or should I place them central in my Activity and offer the CRUD functions via Interfaces? I would do it with Interfaces, but since i have never done something with Android, im not sure.

Is there a recommended way or a pattern for this case?

Answer 1

you can simply maintain a singleton instance of your SQLiteOpenHelper and use it in your fragments where ever you want.

Singleton instance is recommended for data-centric applications. Even if you have multiple threads working on the same database instance, you don't need to worry about synchronization. Since you have a single instance of SQLiteOpenHelper , you are internally maintaining single instance of SQLiteDatabase object and it is thread safe.

You have just mentioned about multiple fragments inside an activity(Not mentioned about thread used for processing data). So I would like to add upon that aspect. Better do data-centric operations on background threads. When each fragment does some processing on data - it is highly recommended to do in background thread to avoid blocking UI/Main thread.

To organize your code better, create a DAO layer.

Answer 2

It would be best to use the activity to handle the database activities and writing/reading and just have your fragments communicate with the activity (this is recommended by google anyway). You DBOpenHelper could look something like this:

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

/**
 * Created by Dev Paul
 */
public class DataBaseOpenHelper extends SQLiteOpenHelper {



  // Logcat tag
    private static final String LOG = "DatabaseHelper";

// Database Version
private static final int DATABASE_VERSION = 1;

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

//Table Names
public static final String REPS_TABLE = "repsTable";


//Reps Table Columns
public static final String R_COLUMN_ID = "repId";
public static final String R_COLUMN_STRING_DATA = "repStringData";
public static final String R_COLUMN_GOOD_FORM = "repGoodForm";

/**
 * Table create statements for the reps objects.
 */
private static final String CREATE_REPS_TABLE = "CREATE TABLE "
        + REPS_TABLE + " (" + R_COLUMN_ID + " INTEGER, "
        + R_COLUMN_STRING_DATA + " TEXT, "
        + R_COLUMN_GOOD_FORM + " INTEGER " + ")";
/**
 * Default constructor.
 * @param context from the calling activity.
 */
public DataBaseOpenHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(CREATE_REPS_TABLE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    //drop the tables if they exist.
    db.execSQL("DROP TABLE IF EXISTS " + REPS_TABLE);
    // create new tables
    onCreate(db);
}
}

This is part of one of my apps that I'm working on and will eventually probably be open source anyway. Then your DataSource class could look something like this...

/**
 * Created by Dev Paul
 */
public class DataSource {

private Context mContext;
private DataBaseOpenHelper dataBaseOpenHelper;
private SQLiteDatabase db;
private boolean isOpened;

/**
 * Class that helps with obtaining data from the data base. This should do all interfacing to
 * the data base. No calls should be directly made to {@code DataBaseOpenHelper}.
 * @param context the context of the calling activity.
 */
public DataSource(Context context) {
    this.mContext = context;
    dataBaseOpenHelper = new DataBaseOpenHelper(context);
}

/**
 * Checks to see if the database has been openned.
 * @return
 */
public boolean isOpened() {
    return isOpened;
}

/**
 * Get a writeable database. This must be called.
 */
public void open() {
    db = dataBaseOpenHelper.getWritableDatabase();
    isOpened = true;
}

/**
 * Close the database. This must be called.
 */
public void close() {
    dataBaseOpenHelper.close();
    isOpened = false;
}

    //handle getting and creating data methods....
}

Then you just need to handle getting the setting the data using query statements with proper SQL syntax. So in your activity you would just have to create a new DataSource object and call dataSource.open() to open the database. Then in your onDestroy() method of your activity call dataSource.close() so that you don't leak any elements.