How to organize work with database?

Need some advice on how to organize the work with the database in the application.
Here is what we have now:
Class DBHelper
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 addHabit Habit(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();
}

 // output to log the data from the cursor
 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 addHabit Habit(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 in Java so they say):
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 there arose a need for another table. First, we wanted to add methods as well as with the first table in DBHelper, but did not like the fact that there is too much code.
Then wanted to create a new DBHelper purely for the second table, but I thought that probably will open an extra connection(but not exactly, just do not understand that deep in it))).

Still wanted to make it in PHP using traits, but apparently in Java it is not.

Can anyone suggest how to structure.
April 19th 20 at 12:39
1 answer
April 19th 20 at 12:41
Solution
To keep things nice and tidy, it is customary to use an ORM, you can of course directly write about you, but this velosipedista.
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 and others.

Do not be confused by the need to explore new, believe me, to deal with the ORM and using it will be faster than writing their bikes.
Vooo thank you!! ORM is great!)
I will explore! - cullen commented on April 19th 20 at 12:44
Room is not included in the android sdk - alvena77 commented on April 19th 20 at 12:47

Find more questions by tags Android