How to properly organize work with the database?

Question

Android

Need advice on how to properly organize the work with the database in the application.
Here is what we have now:
DBHelper class

package com.sherdle.universal.util;

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

import androidx.annotation.Nullable;

import com.sherdle.universal.util.models.habits.Habit;
import com.sherdle.universal.util.models.habits.HabitInterfaceHandler;

import java.util.ArrayList;
import java.util.List;

public class DBHelper extends SQLiteOpenHelper implements HabitInterfaceHandler {

    private static final int DATABASE_VERSION = 2;
    private static final String DATABASE_NAME = "test_habits";


    private static final String TABLE_HABITS = "test_habits";
    private static final String KEY_ID = "id";
    private static final String KEY_NAME = "name";
    private static final String KEY_STATUS = "status";
    private static final String KEY_OPTIONS = "options";

    public DBHelper(@Nullable Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table " + TABLE_HABITS + " ("
                + KEY_ID + " integer primary key,"
                + KEY_NAME + " text,"
                + KEY_STATUS + " text,"
                + KEY_OPTIONS + " json"
                + ")");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("drop table if exists " + TABLE_HABITS);
        onCreate(db);
    }

    @Override
    public Habit addHabit(Habit habit) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(KEY_NAME, habit.getName());
        values.put(KEY_STATUS, habit.getStatus());
        values.put(KEY_OPTIONS, habit.getOptions());

        long id = db.insert(TABLE_HABITS, null, values);

        habit.setID((int)id);

        db.close();

        return habit;
    }

    @Override
    public Habit getHabit(int id) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(
                TABLE_HABITS,
                new String[] { KEY_ID, KEY_NAME, KEY_STATUS, KEY_OPTIONS },
                KEY_ID + "=?",
                new String[] { String.valueOf(id) },
                null, null, null, null);

        if (cursor != null){
            cursor.moveToFirst();
        }

        Habit habit = new Habit(
                Integer.parseInt(cursor.getString(0)),
                cursor.getString(1),
                cursor.getString(2),
                cursor.getString(3)
        );

        return habit;
    }

    @Override
    public List<Habit> getAllHabits() {
        List<Habit> habitList = new ArrayList<Habit>();
        String selectQuery = "SELECT  * FROM " + TABLE_HABITS;

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

        if (cursor.moveToFirst()) {
            do {
                Habit habit = new Habit();
                habit.setID(Integer.parseInt(cursor.getString(0)));
                habit.setName(cursor.getString(1));
                habit.setStatus(cursor.getString(2));
                habit.setOptions(cursor.getString(3));
                habitList.add(habit);
            } while (cursor.moveToNext());
        }

        return habitList;
    }

    @Override
    public int getHabitsCount() {
        String countQuery = "SELECT * FROM " + TABLE_HABITS;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        //cursor.close();
        return cursor.getCount();
    }

    @Override
    public int updateHabit(Habit habit) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_NAME, habit.getName());
        values.put(KEY_STATUS, habit.getStatus());
        values.put(KEY_OPTIONS, habit.getOptions());

        return db.update(TABLE_HABITS, values, KEY_ID + " = ?",
                new String[] { String.valueOf(habit.getID()) });
    }

    @Override
    public void deleteHabit(Habit habit) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_HABITS, KEY_ID + " = ?", new String[] { String.valueOf(habit.getID()) });
        db.close();
    }

    @Override
    public void deleteAllHabits() {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_HABITS, null, null);
        db.close();
    }

    // вывод в лог данных из курсора
    public void logCursor(Cursor c, String title) {
        if (c != null) {
            if (c.moveToFirst()) {
                Log.d("DATABASE", title + ". " + c.getCount() + " rows");
                StringBuilder sb = new StringBuilder();
                do {
                    sb.setLength(0);
                    for (String cn : c.getColumnNames()) {
                        sb.append(cn + " = "
                                + c.getString(c.getColumnIndex(cn)) + "; ");
                    }
                    Log.d("DATABASE", sb.toString());
                } while (c.moveToNext());
            }
        } else
            Log.d("DATABASE", title + ". Cursor is null");
    }
}

Interface:

package com.sherdle.universal.util.models.habits;

import java.util.List;

public interface HabitInterfaceHandler {
    public Habit addHabit(Habit habit);
    public Habit getHabit(int id);
    public List<Habit> getAllHabits();
    public int getHabitsCount();
    public int updateHabit(Habit contact);
    public void deleteHabit(Habit contact);
    public void deleteAllHabits();
}

Entity (if Java says so):

package com.sherdle.universal.util.models.habits;

public class Habit {

    int _id;
    String _name;
    String _status;
    String _options;

    public Habit(){}

    public Habit(int id, String name, String status, String options){
        this._id = id;
        this._name = name;
        this._status = status;
        this._options = options;
    }

    public Habit(String name, String status, String options){
        this._name = name;
        this._status = status;
        this._options = options;
    }

    public int getID(){
        return this._id;
    }

    public void setID(int id){
        this._id = id;
    }

    public String getName(){
        return this._name;
    }

    public void setName(String name){
        this._name = name;
    }

    public String getStatus(){
        return this._status;
    }

    public void setStatus(String status){
        this._status = status;
    }

    public String getOptions(){
        return this._options;
    }

    public void setOptions(String options){
        this._options = options;
    }
}

Everything works perfectly!
But then there was a need for another table. At first I wanted to add methods in the same way as with the first table in DBHelper, but I didn’t like the fact that there would be too much code.
Then I wanted to create a new DBHelper purely for the second table, but I thought that most likely extra connections to the database would be opened (but not exactly, I still don’t understand it so deeply))).

I also wanted to do it like in PHP through traits, but apparently there is no such thing in Java.

Can someone tell me how to structure everything correctly.

Answer 1

To make everything beautiful and neat, it is customary to use ORM, you can, of course, write directly, like you do, but this is bicycle building.
The most fashionable ORM from Google, which is included in the Android SDK, is now Room
https://developer.android.com/jetpack/androidx/rel...
https://developer.android.com/topic/libraries/arch...
But you can google others.
And don't be put off by the need to learn new things, trust me, understanding ORM and using it will be faster than writing your own bicycles.