Android generic SQL database handler

Question

Starting with the fact that creating the classic database handler in Android is really annoying and it usually takes a lot of time since you have to create one handler for each object, I thought at creating a generic one that could avoid this long work.

I'm posting this code for many reasons, the main one being that I would love some suggestions about how to optimize this code way to make it as fast as possible.

This code is working, I don't need a fix, but I will surely need some optimizations because I'm not a senior and surely someone here knows how to perform the same operations with less memory impact.

How to use it:

At app start:

Simply call this code:

    DatabaseHelper db = new DatabaseHelper(this);
    db.OpenDB();
    try {
        db.CreateTable(new myClass1());
        db.CreateTable(new myClass2());
        ...
    } catch (Exception e) {
        e.printStackTrace();
    }

Now all tables are created or updated.

Generic class

(Every class that needs to be a database table, must extend this class)

PS: the id field must be called "id" and must be UUID

import android.content.Context;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

interface IGenericClass {
    ArrayList<?> SelectAll(Class<?> type, Context ctx, String whereClause);

    int SelectCount(Class<?> type, Context ctx, String whereClause);

    boolean SaveAll(List<?> objects, Context ctx);

    boolean Save(Object object, Context ctx);

    boolean Save(Context ctx);

    boolean UpdateObject(Context ctx);

    Integer SumColumn(Class<?> type, Context ctx, String whereClause, String columnName);

    boolean DeleteAll(Class<?> type, Context ctx);

    boolean Delete(Class<?> ciboClass, Context ctx, String whereClause);

    Object SelectById(Class<?> type, Context ctx, UUID id);
}

public class GenericClass implements IGenericClass {

    public ArrayList<?> SelectAll(Class<?> type, Context ctx, String whereClause) {

        DatabaseHelper db = new DatabaseHelper(ctx);
        db.OpenDB();

        ArrayList<?> returnList = new ArrayList<>();
        try {
            return db.SelectAll(this.getClass(), whereClause);
        } catch (Exception ex) {
            return null;
        }
    }

    public int SelectCount(Class<?> type, Context ctx, String whereClause) {

        DatabaseHelper db = new DatabaseHelper(ctx);
        db.OpenDB();

        try {
            return db.SelectCount(this.getClass(), whereClause);
        } catch (Exception ex) {
            return -1;
        }
    }

    public Integer SumColumn(Class<?> type, Context ctx, String whereClause, String columnName) {
        DatabaseHelper db = new DatabaseHelper(ctx);
        db.OpenDB();
        try {
            return db.SumColumn(this.getClass(), whereClause, columnName);
        } catch (Exception ex) {
            return -1;
        }
    }

    public boolean Save(Object object, Context ctx) {
        DatabaseHelper db = new DatabaseHelper(ctx);
        db.OpenDB();
        return db.Save(object);
    }

    public boolean Save(Context ctx) {
        DatabaseHelper db = new DatabaseHelper(ctx);
        db.OpenDB();

        return db.Save(this);
    }

    public boolean SaveAll(List<?> objects, Context ctx) {
        DatabaseHelper db = new DatabaseHelper(ctx);
        db.OpenDB();

        return db.SaveAll(objects);
    }

    public boolean UpdateObject(Context ctx) {
        DatabaseHelper db = new DatabaseHelper(ctx);
        db.OpenDB();
        db.UpdateObject(this);

        return true;

    }

    public boolean DeleteAll(Class<?> type, Context ctx) {
        DatabaseHelper db = new DatabaseHelper(ctx);
        db.OpenDB();
        return db.DeleteAll(type);
    }

    public boolean Delete(Class<?> ciboClass, Context ctx, String whereClause) {
        DatabaseHelper db = new DatabaseHelper(ctx);
        db.OpenDB();
        return db.Delete(ciboClass, whereClause);
    }

    public Object SelectById(Class<?> type, Context ctx, UUID id) {
        DatabaseHelper db = new DatabaseHelper(ctx);
        db.OpenDB();
        return db.SelectById(type, id);
    }
}

Database Helper

This is the handler for the SQLiteDatabase

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Build;
import android.util.Pair;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Objects;
import java.util.UUID;

interface IDatabaseHelper {
    ArrayList<?> SelectAll(Class<?> tipo, String whereClause);

    int SelectCount(Class<?> type, String whereClause);

    boolean Save(Object object);

    boolean SaveAll(List<?> objects);

    boolean OpenDB();

    boolean CreateTable(Object object);

    void Close();

    Integer SumColumn(Class<?> type, String whereClause, String columnName);

    boolean DeleteAll(Class<?> type);

    Object SelectById(Class<?> type, UUID id);

    boolean Delete(Class<?> type, String whereClause);

    boolean UpdateObject(Object objToUpdate);

}

public class DatabaseHelper implements IDatabaseHelper {
    private static final String DATABASE_NAME = "myDatabase.db";
    private static String DATABASE_FULLPATH = "";
    private static SQLiteDatabase database;
//    private static SimpleDateFormat simpleDateFormat = new SimpleDateFormat("dd/MM/yyyy", Locale.getDefault());


    //constructor
    public DatabaseHelper(Context context) {
        DATABASE_FULLPATH = context.getFilesDir().getPath() + "/" + DATABASE_NAME;
    }

    //returns all object of the given class
    public ArrayList<?> SelectAll(Class<?> type, String whereClause) {
        if (whereClause == null) {
            whereClause = "";
        }
        String query = "select * from " + type.getSimpleName() + " " + whereClause;
        Cursor cursor = database.rawQuery(query, null);
        ArrayList list = new ArrayList();
        try {
            if (cursor.moveToFirst()) {
                while (!cursor.isAfterLast()) {
                    Object o = GetObjectFromCursor(type, cursor);
                    list.add(o);
                    cursor.moveToNext();
                }
            }
            cursor.close();
            return list;
        } catch (Exception ex) {
            return null;
        }
    }

    //returns the count of records of given type
    public int SelectCount(Class<?> type, String whereClause) {
        try {
            if (whereClause == null) {
                whereClause = "";
            }
            String query = "select count(*) from " + type.getSimpleName() + " " + whereClause;
            Cursor cursor = database.rawQuery(query, null);
            cursor.moveToFirst();
            int count = cursor.getInt(0);
            cursor.close();
            return count;
        } catch (Exception ex) {
            return -1;
        }
    }

    //save an object
    public boolean Save(Object object) {

        //we build the query for each object
        String insertQuery = "insert into " + object.getClass().getSimpleName() + "(";
        try {
            ArrayList<Pair<String, Object>> name_value = GetFieldNameValue(object);

            String tableNames = "";
            String tableValues = "";
            //for each record we add the values and the field names
            for (Pair<String, Object> pair : name_value) {
                tableNames += pair.first + ",";
                tableValues += "'" + pair.second.toString() + "'" + ",";
            }

            //remove the last comma
            tableNames = tableNames.substring(0, tableNames.length() - 1);
            tableValues = tableValues.substring(0, tableValues.length() - 1);

            //finished adjusting query
            insertQuery += tableNames + ")values(" + tableValues + ");";
            database.execSQL(insertQuery);
            return true;
        } catch (Exception ex) {
            return false;
        }
    }

    //save multiple objects into db
    public boolean SaveAll(List<?> objects) {
        int saved = 0;
        for (Object object : objects) {
            if (Save(object)) {
                saved++;
            } else {
                return false;
            }
        }
        return saved == objects.size();
    }

    //open db
    public boolean OpenDB() {
        try {
            database = SQLiteDatabase.openOrCreateDatabase(DATABASE_FULLPATH, null, null);
            return true;
        } catch (Exception ex) {
            return false;
        }
    }

    //create a table if not exists
    public boolean CreateTable(Object object) {
        try {
            String query = GetCreateQueryFromObject(object);
            database.execSQL(query);

            //we check for each field if it exists, if not it create the field on the database
            String className = object.getClass().getSimpleName();
            try {
                List<Pair<String, Object>> fields = GetFieldNameValue(object);
                for (Pair<String, Object> c : fields) {
                    if (!CheckColumnExistInTable(className, c.first)) {
                        String addColumnSql = "alter table ";
                        addColumnSql += className;
                        addColumnSql += " add ";
                        addColumnSql += c.first;
                        String columnType = GetSQLFieldType(c.first, c.second.getClass().getSimpleName());
                        addColumnSql += " " + columnType;
                        database.execSQL(addColumnSql);
                    }
                }
                return true;
            } catch (Exception ex) {
                return false;
            }
        }catch(Exception ex){
            return false;
        }
    }

    //closes db
    public void Close() {
        database.close();
    }

    //sum a column value for a given table
    public Integer SumColumn(Class<?> type, String whereClause, String columnName) {
        String sql = "select sum(" + columnName + ") as total from " + type.getSimpleName() + " " + whereClause;
        Cursor cursor = database.rawQuery(sql, null);
        int columnIndex = cursor.getColumnIndex("total");
        if (columnIndex == -1) {
            return 0;
        }
        if (cursor.moveToFirst()) {
            int value = cursor.getInt(columnIndex);
            cursor.close();
            return value;
        } else {
            cursor.close();
            return 0;
        }
    }

    //delete all record in a table
    public boolean DeleteAll(Class<?> type)  {
        String sql = "delete from " + type.getSimpleName();
        database.execSQL(sql);
        return true;
    }

    //select a record from id
    public Object SelectById(Class<?> type, UUID id) {

        String query = "select * from " + type.getSimpleName() + " where id='" + id.toString() + "'";
        Cursor cursor = database.rawQuery(query, null);
        if (cursor.moveToFirst()) {
            try {
                Object object = GetObjectFromCursor(type, cursor);
                cursor.close();
                return object;
            } catch(Exception ex){
                return null;
            }
        } else {
            return null;
        }
    }

    //delete all records with a given condition
    public boolean Delete(Class<?> type, String whereClause) {
        try {
            String sql = "delete from " + type.getSimpleName() + " " + whereClause;
            database.execSQL(sql);
            return true;
        } catch (Exception ex) {
            return false;
        }
    }

    //update an object from his id
    public boolean UpdateObject(Object objToUpdate) {
        try {
            Field field = objToUpdate.getClass().getField("id");
            int id = (int) field.get(objToUpdate);
            String whereClause = "where id = " + id;

            String sqlQuery = "update " + objToUpdate.getClass().getSimpleName() + " set ";
            ArrayList<Pair<String, Object>> name_value = GetFieldNameValue(objToUpdate);

            //for each field we add name and value
            for (Pair<String, Object> pair : name_value) {
                if (!pair.first.equals("id")) {
                    sqlQuery += pair.first + "=";
                    sqlQuery += "'" + pair.second.toString() + "'" + ",";
                }
            }

            sqlQuery = sqlQuery.substring(0, sqlQuery.length() - 1);

            sqlQuery += " ";
            sqlQuery += whereClause;

            database.execSQL(sqlQuery);
            return true;


        } catch (Exception ex) {
            return false;
        }
    }

    private boolean CheckColumnExistInTable(String tableName, String columnName) {
        Cursor mCursor = null;
        try {
            // Query 1 row
            mCursor = database.rawQuery("SELECT * FROM " + tableName + " LIMIT 0", null);

            // getColumnIndex() gives us the index (0 to ...) of the column - otherwise we get a -1
            return mCursor.getColumnIndex(columnName) != -1;

        } catch (Exception Exp) {
            return false;
        } finally {
            if (mCursor != null) mCursor.close();
        }
    }

    //given a cursor and a class, it returns the object from the cursor
    private Object GetObjectFromCursor(Class<?> tipo, Cursor cursor) throws Exception {
        Field[] fields = tipo.getFields();
        Object o = tipo.newInstance();
        for (int i = 0; i < fields.length; i++) {
            Object fieldValue = GetCursorFieldValue(cursor, i);
            if (fieldValue != null) {
                o = SetUnknownFieldValue(o, cursor.getColumnName(i), fieldValue);
            }
        }
        return o;
    }

    //returns from a given object a fieldName - fieldValue map
    private ArrayList<Pair<String, Object>> GetFieldNameValue(Object object) throws Exception {
        Field[] fields = object.getClass().getFields();
        ArrayList<Pair<String, Object>> pairs = new ArrayList<>();
        for (Field f : fields) {
            Object value = GetUnknownObjectFieldValue(f, object);
            String fieldName = f.getName();

            if (value == null || fieldName.isEmpty()) {
                continue;
            }
            pairs.add(new Pair(fieldName, value));
        }
        return pairs;
    }

    //returns from an object and a field name, the value
    private Object GetUnknownObjectFieldValue(Field field, Object object) throws Exception {
            field.setAccessible(true);
            Object o = field.get(object);

            //we save dates as longs
            Date date = new Date();
            UUID uuid = UUID.randomUUID();
            if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.KITKAT) {
                if (o != null && Objects.equals(o.getClass(), date.getClass())) {
                    return new Date((long) o);
                }
                if (o != null && Objects.equals(o.getClass(), uuid.getClass())) {
                    return o.toString();
                }
            } else {
                if (o != null && o.getClass().equals(date.getClass())) {
                    return new Date((long) o);
                }
                if (o != null && o.getClass().equals(uuid.getClass())) {
                    return o.toString();
                }
            }
            return o;
    }

    //we set the value "val" on the field "field". we use this to value an object without having its name
    private Object SetUnknownFieldValue(Object object, String fieldName, Object fieldValue) throws Exception {
        Class<?> clazz = object.getClass();
            Field field = clazz.getDeclaredField(fieldName);
            field.setAccessible(true);
            Object fieldCasted = CastField(field.getType(), fieldValue);
            field.set(object, fieldCasted);
            return object;

    }

    //we take the field type and the object way to convert the object in the required field
    private Object CastField(Class fieldType, Object fieldValue) throws Exception {
            switch (fieldType.getSimpleName().toLowerCase()) {
                case "boolean":
                    if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.KITKAT) {
                        if (Objects.equals(fieldValue.getClass().getSimpleName(), "String")) {
                            return Objects.equals(fieldValue, "true");
                        }
                    } else {
                        if (fieldValue.getClass().getSimpleName().equals("String")) {
                            return fieldValue.equals("true");
                        }
                    }
                    break;
                case "double":
                    if (fieldValue == null) {
                        return null;
                    }
                    return (double) Float.parseFloat(fieldValue.toString());
                case "date":
                    return new Date((long) fieldValue);
                case "uuid":
                    return UUID.fromString((String) fieldValue);
                default:
                    return fieldValue;

            }
            return fieldValue;
    }

    //return a generic field value from cursor
    private Object GetCursorFieldValue(Cursor cursor, int i) {
        switch (cursor.getType(i)) {
            /*  FIELD_TYPE_NULL
                FIELD_TYPE_INTEGER
                FIELD_TYPE_FLOAT
                FIELD_TYPE_STRING
                FIELD_TYPE_BLOB  */
            case 0:
                return null;
            case 1:
                return cursor.getInt(i);
            case 2:
                return cursor.getFloat(i);
            case 3:
                return cursor.getString(i);
            case 4:
                return cursor.getBlob(i);
            default:
                cursor.close();
                return null;
        }
    }

    //prende un oggetto e ne crea la query di creazione tabella
    private String GetCreateQueryFromObject(Object object) throws Exception {
        String fullQuery = "create table if not exists ";
        String objectName = GetTableName(object);
        fullQuery += objectName + " ";
        String properties = GetPropertiesFromObject(object);
        fullQuery += "(" + properties + ");";
        return fullQuery;
    }

    //returns object properties as: (fieldName fieldType, fieldName fieldType,..)
    private String GetPropertiesFromObject(Object object) throws Exception {
        Class<?> tClass = object.getClass();
        Field[] fieldsArray = tClass.getFields();
        ArrayList<Pair<String, String>> fieldMap = GetFields(fieldsArray);//1 field name, 2 field type
        String fields = "";
        for (Pair<String, String> field : fieldMap) {
            fields += field.first + " ";
            fields += GetSQLFieldType(field.first, field.second) + ", ";
        }
        return fields.substring(0, fields.length() - 2);
    }

    //returns from a given type, the sql type required
    private String GetSQLFieldType(String fieldName, String fieldType) throws Exception {

        if (fieldName.toLowerCase().equals("id")) {
            return "TEXT PRIMARY KEY UNIQUE";
        }
        switch (fieldType.toLowerCase()) {
            case "uuid":
                return "TEXT";
            case "string":
                return "TEXT";
            case "int":
                return "INT";
            case "double":
                return "DOUBLE";
            case "boolean":
                return "BOOLEAN";
            case "float":
                return "FLOAT";
            case "integer":
                return "INT";
            case "date":
                return "INT";
            default:
                return "BLOB";
        }
    }

    //returns from an object the table name
    private String GetTableName(Object object) {
        return object.getClass().getSimpleName();
    }

    //returns a map with an object properties as <fieldType-fieldName>
    private ArrayList<Pair<String, String>> GetFields(Field[] fields) {
        ArrayList<Pair<String, String>> pairs = new ArrayList<>();
        for (Field f : fields) {
            if (!f.getName().equals("shadow$_klass_") && !f.getName().equals("shadow$_monitor_") && !f.getName().equals("$change")) {
                pairs.add(new Pair<>(f.getName(), f.getType().getSimpleName()));
            }
        }
        return pairs;
    }
}

Peter Taylor · Accepted Answer · 2019-08-29 09:05:10Z

Simply call this code:

    DatabaseHelper db = new DatabaseHelper(this);
    db.OpenDB();
    try {
        db.CreateTable(new myClass1());
        db.CreateTable(new myClass2());
        ...
    } catch (Exception e) {
        e.printStackTrace();
    }

Be aware that there are database upgrades which can't be automated so straightforwardly. E.g. the initial values of a new column might need to be calculated based on some older column.

interface IGenericClass {
    ArrayList<?> SelectAll(Class<?> type, Context ctx, String whereClause);

    int SelectCount(Class<?> type, Context ctx, String whereClause);

    Integer SumColumn(Class<?> type, Context ctx, String whereClause, String columnName);

    boolean DeleteAll(Class<?> type, Context ctx);

    boolean Delete(Class<?> ciboClass, Context ctx, String whereClause);

    Object SelectById(Class<?> type, Context ctx, UUID id);
}

Why do all of those methods have Class<?> type parameters? An object knows its own type, and if these aren't operating on the type of this then they probably should be in a different class altogether.

interface IGenericClass {
    ArrayList<?> SelectAll(Class<?> type, Context ctx, String whereClause);

    boolean SaveAll(List<?> objects, Context ctx);
}

This is not typesafe, although fixing it requires a moderately advanced technique called f-bounded polymorphism. Also, code to the interface (List) rather than the instance (ArrayList).

interface IGenericClass<T extends IGenericClass<T>> {
    List<T> SelectAll(Context ctx, String whereClause);
    boolean SaveAll(List<T> objects, Context ctx);
}

    public ArrayList<?> SelectAll(Class<?> type, Context ctx, String whereClause) {

        DatabaseHelper db = new DatabaseHelper(ctx);
        db.OpenDB();

        ArrayList<?> returnList = new ArrayList<>();
        try {
            return db.SelectAll(this.getClass(), whereClause);
        } catch (Exception ex) {
            return null;
        }
    }

Database connections should either be closed or (preferably) returned to a cache. OpenDB() with neither in sight is a red flag. There are extremely advanced techniques to return it to the cache automatically, but they require getting intimate with the garbage collector, so it's better to use a standard pattern.

This implementation illustrates my point above about Class<?> type: it's not even used!

Catching the exception and returning null without logging anything is a classic "worst practice".

interface IDatabaseHelper {
    ArrayList<?> SelectAll(Class<?> tipo, String whereClause);
    int SelectCount(Class<?> type, String whereClause);
    boolean Save(Object object);
    boolean SaveAll(List<?> objects);
    Integer SumColumn(Class<?> type, String whereClause, String columnName);
    boolean DeleteAll(Class<?> type);
    Object SelectById(Class<?> type, UUID id);
    boolean Delete(Class<?> type, String whereClause);
    boolean UpdateObject(Object objToUpdate);
}

You say about IGenericClass that

(Every class that needs to be a database table, must extend this class)

Why? The IDatabaseHelper API doesn't care. If you're serious about that statement, IDatabaseHelper should enforce it. And while we're at it, let's fix the generics:

interface IDatabaseHelper {
    ArrayList<T> SelectAll<T extends IGenericClass<T>>(Class<T> tipo, String whereClause);
    int SelectCount(Class<? extends IGenericClass<?>> type, String whereClause);
    boolean Save(IGenericClass<?> object);
    boolean SaveAll(List<? extends IGenericClass<?>> objects);
    Integer SumColumn(Class<? extends IGenericClass<?>> type, String whereClause, String columnName);
    boolean DeleteAll(Class<? extends IGenericClass<?>> type);
    T SelectById<T extends IGenericClass<T>>(Class<T> type, UUID id);
    boolean Delete(Class<? extends IGenericClass<?>> type, String whereClause);
    boolean UpdateObject(IGenericClass<?> objToUpdate);
}

interface IDatabaseHelper {
    boolean CreateTable(Object object);
}

Shouldn't that take a Class<? extends IGenericClass<?>> instead of an instance?

interface IDatabaseHelper {
    void Close();
}

So it does exist and is just never used?

Why does IDatabaseHelper have DeleteAll(Class) and Delete(Class, String) but not Delete(Object) (to be corrected to Delete(IGenericClass<?>))?

public class DatabaseHelper implements IDatabaseHelper {
    private static final String DATABASE_NAME = "myDatabase.db";
    private static String DATABASE_FULLPATH = "";
    private static SQLiteDatabase database;
//    private static SimpleDateFormat simpleDateFormat = new SimpleDateFormat("dd/MM/yyyy", Locale.getDefault());


    //constructor
    public DatabaseHelper(Context context) {
        DATABASE_FULLPATH = context.getFilesDir().getPath() + "/" + DATABASE_NAME;
    }

    ...

    //open db
    public boolean OpenDB() {
        try {
            database = SQLiteDatabase.openOrCreateDatabase(DATABASE_FULLPATH, null, null);
            return true;
        } catch (Exception ex) {
            return false;
        }
    }

This speaks to me of confusion about the meaning of static. Setting a static field in an instance constructor is a big red flag. Similarly, setting a static field in an instance method. The only exceptions I can think of are things like instance counting, and they should be made thread-safe.

IMO the database helper used at app start should be conserved and passed around, and no further helpers should need to be constructed.

Also, that path is non-standard. It should be context.getDatabasePath(DATABASE_NAME).

    public boolean Save(Object object) {

        //we build the query for each object
        String insertQuery = "insert into " + object.getClass().getSimpleName() + "(";
        try {
            ArrayList<Pair<String, Object>> name_value = GetFieldNameValue(object);

            String tableNames = "";
            String tableValues = "";
            //for each record we add the values and the field names
            for (Pair<String, Object> pair : name_value) {
                tableNames += pair.first + ",";
                tableValues += "'" + pair.second.toString() + "'" + ",";
            }

That is a classic SQL injection attack waiting to happen. Use parameterised queries. Always. This also applies to all of the methods which take a queryString.

Stack Exchange Network

current community

your communities

more stack exchange communities

Android generic SQL database handler

At app start:

Generic class

Database Helper

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged java performance android sqlite or ask your own question.

Hot Network Questions

Android generic SQL database handler

At app start:

Generic class

Database Helper

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged java performance android sqlite or ask your own question.

Related

Hot Network Questions