How to better arrange my SQLite database class in Android

Question

I am new to Android development and I want to ensure I'm learning decent practices for doing things. Right now this is my database class, which currently allows me to make a new instance of the singleton, as well as create a profiles table, as well as add/retrieve from the profiles table.

This is my code so far:

public class DatabaseHelper extends SQLiteOpenHelper {
    private static volatile SQLiteDatabase mDatabase;
    private static DatabaseHelper mInstance = null;
    private static Context mContext;

    private static final String DB_NAME = "database.db";
    private static final int DB_VERSION = 1; 

    public static final String PROFILES_TABLE = "PROFILES";
    public static final String PROFILES_COLUMN_ID = "_ID";
    public static final String PROFILES_COLUMN_NAME = "NAME";

    private static final String DB_CREATE_PROFILES_TABLE =  
            "CREATE TABLE " + PROFILES_TABLE + " ("
                    + PROFILES_COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                    + PROFILES_COLUMN_NAME + " TEXT UNIQUE NOT NULL)";


    public static synchronized DatabaseHelper getInstance(Context context) {

        if (mInstance == null) {
            mInstance = new DatabaseHelper(context.getApplicationContext());
            try {
                mInstance.open();
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return mInstance;
    }

    private DatabaseHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
        mContext = context;
    }

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

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 

    }

    @Override
    public void onConfigure(SQLiteDatabase db){
        super.onConfigure(db);
        db.setForeignKeyConstraintsEnabled(true);
    }

    public synchronized void open() throws SQLException {
        mDatabase = getWritableDatabase();
    }

    public synchronized void close() {
        mDatabase.close();
    }

    public synchronized long addNewProfile(String name) {
        ContentValues values = new ContentValues();
        values.put(DatabaseHelper.PROFILES_COLUMN_NAME, name);
        return mDatabase.insertWithOnConflict(DatabaseHelper.PROFILES_TABLE, null, values, SQLiteDatabase.CONFLICT_IGNORE);
    }

    public synchronized Profile getProfileById(long profileId) {
        Cursor cursor = mDatabase.query(
                DatabaseHelper.PROFILES_TABLE, // table
                new String[]{DatabaseHelper.PROFILES_COLUMN_ID, DatabaseHelper.PROFILES_COLUMN_NAME}, // column names
                DatabaseHelper.PROFILES_COLUMN_ID + " = ?", // where clause
                new String[]{profileId + ""}, // where params
                null, // groupby
                null, // having
                null);  // orderby
        cursor.moveToFirst();
        Profile profile = null;
        if (!cursor.isAfterLast()) {
            String profileName = getStringFromColumnName(cursor, DatabaseHelper.PROFILES_COLUMN_NAME);
            profile = new Profile(profileId, profileName);
            cursor.moveToNext();
        }
        cursor.close();
        return profile;
    }

    public synchronized List<Profile> getAllProfiles() {
        List<Profile> profiles = new ArrayList<>();
        Cursor cursor = mDatabase.query(
                DatabaseHelper.PROFILES_TABLE, // table
                new String[]{DatabaseHelper.PROFILES_COLUMN_ID, DatabaseHelper.PROFILES_COLUMN_NAME}, // column names
                null, // where clause
                null, // where params
                null, // groupby
                null, // having
                DatabaseHelper.PROFILES_COLUMN_NAME); // orderby
        cursor.moveToFirst();
        while (!cursor.isAfterLast()) {
            long profileId = getLongFromColumnName(cursor, DatabaseHelper.PROFILES_COLUMN_ID);
            String profileName = getStringFromColumnName(cursor, DatabaseHelper.PROFILES_COLUMN_NAME);
            profiles.add(new Profile(profileId, profileName));
            cursor.moveToNext();
        }
        cursor.close();
        return profiles;
    }

    private synchronized long getLongFromColumnName(Cursor cursor, String columnName) {
        int columnIndex = cursor.getColumnIndex(columnName);
        return cursor.getLong(columnIndex);
    }

    private synchronized String getStringFromColumnName(Cursor cursor, String columnName) {
        int columnIndex = cursor.getColumnIndex(columnName);
        return cursor.getString(columnIndex);
    }

}

For reference (this may or may not be necessary, but I am posting it just in case), my Profile class, which is something I use in several other places in the app:

public class Profile {
    private long mId;
    private String mName;

    public Profile(long id, String name) {
        mId = id;
        mName = name;
    }

    public long getId() {
        return mId;
    }

    public void setId(long id) {
        mId = id;
    }

    public String getName() {
        return mName;
    }

    public void setName(String name) {
        mName = name;
    }

}

My questions:

1. Is it proper to be storing the field names of the table in the database class like this, or should I be moving it to its own separate class (for example a ProfileSql class of some kind that holds all the names).

2. Should I be decoupling the CRUD query logic from this class somehow? How do I do this? What if I have several tables, queries, thread methods, etc? Do these all go in their own separate classes, too? If I add CRUD functions for several tables, this class could get very large very quickly.

3. Should I be somehow tying any of this stuff into my Profile class itself, which I use in several other places in my app? For instance should I be including the profile table SQL stuff (the create table string and the table/column names) in the Profile class, or is this meddling things together that shouldn't be?

As you can see I am mostly asking where stuff should go. Right now I am just kind of lumping it all together into one database class.

My main intent here:

I am hoping that this example is short enough to where someone can show me the proper way to restructure all of this so I can take those skills and apply them going forward as things get more complex.

Answer 1

You should sperate the different tables into so called model classes. Where each Model has its own set of database functions.

The DatabaseHelper (or DatabaseController) only exist to provide a "handle" to work with.

I don't have time to write a very detailed answer but I did write you some example code (might not directly compile).

DatabaseController.java

This controller also handles multiple database users (Threads) and enables foreign key constraints (you might need those).

public final class DatabaseController extends SQLiteOpenHelper {

    public static abstract class LocalDatabaseModel {

        public LocalDatabaseModel(){

        }

        public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion){

        }
        public abstract void onCreate(SQLiteDatabase database);
    }

    private SQLiteDatabase database;
    private int openConnections = 0;

    private static final String DATABASE = "database-name.db";
    private static final int VERSION = 1;
    private static DatabaseController instance = null;

    // Add you LocalDatabaseModels here.
    private final LocalDatabaseModel[] models = new LocalDatabaseModel[]{new Book.Model(), new Writer.Model()};


    public synchronized static DatabaseController getInstance(Context context) {
        if (instance == null) {
            instance = new DatabaseController(context.getApplicationContext());
        }
        return instance;
    }

    private DatabaseController(Context context) {
        super(context, DATABASE, null, VERSION);
    }

    /**
     * Must be called from the same thread as the original openDatabase call.
     */
    @Override
    public synchronized void close() {
        if(database == null || openConnections == 0){
            throw new IllegalStateException("Database already closed or has never been opened.");
        }
        openConnections--;
        if(openConnections != 0){
            return;
        }
        database = null;
        super.close();
    }

    /**
     * Do not manually call this method! Use openDatabase(), database() and close()!
     *
     * Opens the SQLiteDatabase if not already opened.
     * This implementation does the exact same thing as getWritableDatabase and thus will return a writable database.
     *
     * @return the newly opened database or the existing database.
     */
    @Override
    public synchronized SQLiteDatabase getReadableDatabase() {
        return getWritableDatabase();
    }

    /**
     *
     * Do not manually call this method! Use openDatabase(), database() and close()!
     *
     * Opens the SQLiteDatabase if not already opened.
     *
     * @return the newly opened database or the existing database.
     */
    @Override
    public synchronized SQLiteDatabase getWritableDatabase() {
        if(database == null){
            database = super.getWritableDatabase();
        }
        openConnections++;
        return database;
    }

    /**
     * Open the database. Always pair this call with close() and use database() to get the opened database!
     */
    public synchronized void openDatabase(){
        getWritableDatabase();
    }

    /**
     * Returns the opened database. Throws an exception if the database has not been opened yet!
     * @return the database.
     */
    public synchronized SQLiteDatabase database(){
        if(database == null){
            throw new IllegalStateException("Database has not been opened yet!");
        }
        return database;
    }

    @Override
    public synchronized void onCreate(SQLiteDatabase db) {
        setForeignKeyConstraintsEnabled(db);
        for(LocalDatabaseModel model: models){
            model.onCreate(db);
        }
    }

    @Override
    public synchronized void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        setForeignKeyConstraintsEnabled(db);
        for(LocalDatabaseModel model: models){
            model.onUpgrade(db, oldVersion, newVersion);
        }
    }

    @Override
    public synchronized void onOpen(SQLiteDatabase db) {
        setForeignKeyConstraintsEnabled(db);
    }

    @TargetApi(Build.VERSION_CODES.JELLY_BEAN)
    @Override
    public synchronized void onConfigure(SQLiteDatabase db) {
        db.setForeignKeyConstraintsEnabled(true);
    }

    private void setForeignKeyConstraintsEnabled(SQLiteDatabase db){
        //Skip for Android 4.1 and newer as this is already handled in onConfigure
        if(Build.VERSION.SDK_INT < Build.VERSION_CODES.JELLY_BEAN && !db.isReadOnly()) {
            db.execSQL("PRAGMA foreign_keys=ON;");
        }
    }

    /* I often have some utility methods in this class too. */
    public long getCount(String table){
        return DatabaseUtils.queryNumEntries(database(), table);
    }
}

Book.java

public final class Book {

    private long id = -1;
    private String title;

    public Book(String title){
        this.title = title;
    }

    private Book(long id, String title){
        this.title = title;
        this.id = id;
    }


    public void save(DatabaseController db){
        //save or update the book, throw an exception on failure.
    }

    //More non static methods (getters, setters, database methods) here

    public static Book getById(DatabaseController db, long id){
        //Do select query and get an existing book from the database.
    }

    //More static methods here

    public static class Model extends LocalDatabaseModel {

        public Model(){
        }

        @Override
        public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion){
             //Implement update logic for this model/table
         }
         @Override
         public void onCreate(SQLiteDatabase database){
             //Implement create logic for this model/table
          }
    }
}

Usage

DatabaseController db = DatabaseController.getInstance(context);

db.openDatabase();


Book book = new Book("Alice in Wonderland");

book.save(db);

db.close();