How to better organize my SQLite database class in Android

Question

I'm new to Android development, and I want me to learn decent practice in order to do something. Now this is my database class, which currently allows me to create a new singleton instance, as well as create a profile table, and add / extract from the profile table.

This is my code:

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 (it may or may not be necessary, but I'm sending it just in case), my Profile class, which I use in several other places in the application:

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:

• Is it right to store table field names in a database class like this, or should I move it to my own separate class (for example, a class ProfileSqlthat contains all the names).

• - CRUD ? ? , , , ..? ? CRUD , .

• - Profile, ? , SQL ( /) , , ?

, , . .

:

, , - , , .

Answer 1

. .

DatabaseHelper ( DatabaseController) "" .

, , ( ).

DatabaseController.java

(Threads) (, ).

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
          }
    }
}

DatabaseController db = DatabaseController.getInstance(context);

db.openDatabase();


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

book.save(db);

db.close();