SQLite ORM 구현하기

ORM 이라는게 있다.  ORM은 이점이 많아 쓰지 않을 이유가 없다. 일단 생산성이 극도로 높아져 코딩의 재미가 있는 것 같다. 사실 Table schema 하나 추가 될 때 마다 귀찮은 db 입력 출력 루틴을 구현하는 것이 너무 귀찮았는데 이번 기회에 공부도 할 겸 ORM을 손수 구현해 보았다. 어제 일요일에는 ORM구현하느라 공부 포스트를 쓰지 못했다. 생각보다 구현해야 하는 코드량이 상당했다. Realm은 일단 걷어냈다. 

기존에 SettingRO를 지우고 SettingDO를 다시 구현했다. 

SettingDO : setting의 값을 저장하는 common_settings TABLE의 스키마를 정의한다. OrmObject를 extends 해야 한다. 

package com.mdiwebma.base.settings;

import java.util.HashSet;
import java.util.Set;

import com.mdiwebma.base.annotation.Table;
import com.mdiwebma.base.db.orm.OrmObject;

/**
 * @author djkim
 */
@Table(name = SettingDO.TABLE)
public class SettingDO extends OrmObject {

   public static final String TABLE = "common_settings";
   public static final String COL_KEY = "key";
   public static final String COL_VALUE = "value";

   @Table.TypeText(name = COL_KEY, primaryKey = true)
   public String id;

   @Table.TypeText(name = COL_VALUE, notNull = true)
   public String value;

   public SettingDO() { //for OrmUtils
   }

   public SettingDO(String id, String value) {
      this.id = id;
      this.value = value;
   }
}

기존의 Realm에 읽고, 쓰고, 지우는 구현은 훨씬 간단해 졌다. 

SettingDO settingDO = new SettingDO();
if (settingDO.select(setting.keyName)) {
   return settingDO.value;
} else {
   return null;
}

//    Realm realm = Realm.getInstance(ApplicationKeeper.get());
//
//    try {
//       SettingRO settingRO = realm.where(SettingRO.class).equalTo(KEY_NAME, setting.keyName).findFirst();
//       if (settingRO != null) {
//          return settingRO.getValue();
//       }
//    } catch (Exception ex) {
//       Dlog.notReached(ex);
//    } finally {
//       realm.close();
//    }
//

// return null;

new SettingDO(setting.keyName, value).insertOrUpdate();

//          Realm realm = Realm.getInstance(ApplicationKeeper.get());
//
//          try {
//             realm.beginTransaction();
//             SettingRO settingRo = realm.where(SettingRO.class).equalTo(KEY_NAME, setting.keyName).findFirst();
//             if (settingRo == null) {
//                settingRo = realm.createObject(SettingRO.class);
//                settingRo.setKeyName(setting.keyName);
//             }
//             settingRo.setValue(value);
//             realm.commitTransaction();
//          } catch (Exception ex) {
//             Dlog.notReached(ex);
//          } finally {
//             realm.close();

// }

SettingDO settingDO = new SettingDO(setting.keyName, null);
settingDO.delete();

//          Realm realm = Realm.getInstance(ApplicationKeeper.get());
//
//          try {
//             realm.beginTransaction();
//             SettingRO settingRo = realm.where(SettingRO.class).equalTo(KEY_NAME, setting.keyName).findFirst();
//             if (settingRo != null) {
//                settingRo.removeFromRealm();
//             }
//             realm.commitTransaction();
//          } catch (Exception ex) {
//             Dlog.notReached(ex);
//          } finally {
//             realm.close();

// }

SettingDO에 선언된 annotation의 구현:  참고로 SQLite는 TEXT, INTEGER, REAL, BLOB 4가지 타입만 있다. 

package com.mdiwebma.base.annotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * @author djkim
 */
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface Table {

   String name();

   @Target(ElementType.FIELD)
   @Retention(RetentionPolicy.RUNTIME)
   public @interface TypeInteger {
      String name();

      boolean primaryKey() default false;

      boolean autoIncrement() default false;

      boolean notNull() default false;

      String defaultValue() default "";
   }

   @Target(ElementType.FIELD)
   @Retention(RetentionPolicy.RUNTIME)
   public @interface TypeText {
      String name();

      boolean primaryKey() default false;

      boolean notNull() default false;

      String defaultValue() default "";
   }

   @Target(ElementType.FIELD)
   @Retention(RetentionPolicy.RUNTIME)
   public @interface TypeReal {
      String name();

      boolean primaryKey() default false;

      boolean notNull() default false;

      String defaultValue() default "";
   }

   @Target(ElementType.FIELD)
   @Retention(RetentionPolicy.RUNTIME)
   public @interface TypeBlob {
      String name();
   }

   // @Target(ElementType.FIELD)
   // @Retention(RetentionPolicy.RUNTIME)
   // public @interface CreateIndex {
   // }

}

OrmObject는 OrmUtils에서 처리하기위한 기본 클래스이다. Table Schema Object는 이 클래스를 extends 하여야 한다. 

package com.mdiwebma.base.db.orm;

import java.util.Set;

import android.support.annotation.Nullable;

/**
 * @author djkim
 */
public abstract class OrmObject {

   public boolean insert() {
      return new OrmHelper(this).insert();
   }

   public boolean insertOrUpdate() {
      return new OrmHelper(this).insertOrUpdate();
   }

   public boolean update() {
      return new OrmHelper(this).update();
   }

   public boolean update(@Nullable Set<String> updateColumns) {
      return new OrmHelper(this).updateColumns(updateColumns).update();
   }

   public boolean delete() {
      return new OrmHelper(this).delete();
   }

   public boolean select(String id) {
      return new OrmHelper(this).id(id).select(this);
   }

   public boolean select(long id) {
      return new OrmHelper(this).id(id).select(this);
   }

   public boolean select(String[] selectColumns, String id) {
      return new OrmHelper(this).selectColumns(selectColumns).id(id).select(this);
   }

   public boolean select(String[] selectColumns, long id) {
      return new OrmHelper(this).selectColumns(selectColumns).id(id).select(this);
   }

   public boolean select(String where, String[] whereArgus) {
      return new OrmHelper(this).where(where, whereArgus).select(this);
   }

   public boolean select(String[] selectColumns, String where, String[] whereArgus) {
      return new OrmHelper(this).selectColumns(selectColumns).where(where, whereArgus).select(this);
   }
}

결국 OrmHelper의 구현은 ..

package com.mdiwebma.base.db.orm;

import java.util.Collections;
import java.util.List;
import java.util.Set;

import android.database.Cursor;
import android.support.annotation.NonNull;
import android.support.annotation.Nullable;

import com.mdiwebma.base.ApplicationKeeper;
import com.mdiwebma.base.helper.StringBuilderPool;
import com.mdiwebma.base.logging.Dlog;
import com.mdiwebma.base.utils.Triple;

/**
 * @author djkim
 */
public class OrmHelper {

   final Class<? extends OrmObject> tableClass;
   @Nullable
   final OrmObject ormObject;

   @Nullable
   String[] selectColumns;
   @Nullable
   String where;
   @Nullable
   String[] whereArgus;
   @Nullable
   Set<String> updateColumns;
   @Nullable
   String orderBy;

   public OrmHelper(@NonNull Class<? extends OrmObject> tableClass) {
      this.tableClass = tableClass;
      ormObject = null;
   }

   public OrmHelper(@NonNull OrmObject ormObject) {
      this.tableClass = ormObject.getClass();
      this.ormObject = ormObject;
   }

   public OrmHelper selectColumns(@Nullable String[] selectColumns) {
      this.selectColumns = selectColumns;
      return this;
   }

   public OrmHelper updateColumns(@Nullable Set<String> updateColumns) {
      this.updateColumns = updateColumns;
      return this;
   }

   public OrmHelper id(long id) {
      this.whereArgus = new String[]{String.valueOf(id)};
      return this;
   }

   public OrmHelper id(@NonNull String id) {
      this.whereArgus = new String[]{id};
      return this;
   }

   public OrmHelper where(@NonNull String where, @NonNull String[] whereArgus) {
      this.where = where;
      this.whereArgus = whereArgus;
      return this;
   }

   public OrmHelper orderBy(@NonNull String columnName) {
      this.orderBy = columnName;
      return this;
   }

   public OrmHelper orderBy(@NonNull String columnName, boolean DESC) {
      this.orderBy = columnName + (DESC ? " DESC" : " ASC");
      return this;
   }

   public boolean insert() {
      Dlog.assertNotNull(ormObject);
      return OrmUtils.insert(ApplicationKeeper.getDbHelper(), ormObject) > 0;
   }

   public boolean insertOrUpdate() {
      Dlog.assertNotNull(ormObject);
      return OrmUtils.insertOrUpdate(ApplicationKeeper.getDbHelper(), ormObject) > 0;
   }

   public boolean update() {
      Dlog.assertNotNull(ormObject);
      return OrmUtils.update(ApplicationKeeper.getDbHelper(), ormObject, updateColumns) > 0;
   }

   public boolean delete() {
      if (ormObject != null) {
         return OrmUtils.delete(ApplicationKeeper.getDbHelper(), ormObject) > 0;

      } else if (whereArgus != null) {
         Triple<String, String, String[]> metaData = OrmUtils.getTableColumns(tableClass);

         try {
            return ApplicationKeeper.getDbHelper().delete_(
               metaData.left, where != null ? where : StringBuilderPool.concat(metaData.middle, "=?"), whereArgus) > 0;
         } catch (Exception ex) {
            Dlog.notReached(ex);
            return false;
         }

      } else {
         throw new RuntimeException("ormObject != null || _id != null");
      }
   }

   @Nullable
   public <T extends OrmObject> T select() {
      Dlog.assertNotNull(whereArgus);

      Triple<String, String, String[]> metaData = OrmUtils.getTableColumns(tableClass);

      try {
         Cursor cursor = ApplicationKeeper.getDbHelper().query_(
            metaData.left,
            selectColumns != null ? selectColumns : metaData.right,
            where != null ? where : StringBuilderPool.concat(metaData.middle, "=?"),
            whereArgus,
            null);

         return (T)OrmUtils.select(cursor, tableClass);
      } catch (Exception ex) {
         Dlog.notReached(ex);
         return null;
      }
   }

   public boolean select(@NonNull OrmObject ormObject) {
      Dlog.assertNotNull(whereArgus);

      Triple<String, String, String[]> metaData = OrmUtils.getTableColumns(tableClass);

      try {
         Cursor cursor = ApplicationKeeper.getDbHelper().query_(
            metaData.left,
            selectColumns != null ? selectColumns : metaData.right,
            where != null ? where : StringBuilderPool.concat(metaData.middle, "=?"),
            whereArgus,
            null);

         return OrmUtils.select(cursor, tableClass, ormObject);
      } catch (Exception ex) {
         Dlog.notReached(ex);
         return false;
      }
   }

   @NonNull
   public <T extends OrmObject> List<T> selectList() {

      Triple<String, String, String[]> metaData = OrmUtils.getTableColumns(tableClass);

      try {
         Cursor cursor = ApplicationKeeper.getDbHelper().query_(
            metaData.left,
            selectColumns != null ? selectColumns : metaData.right,
            where != null ? where : StringBuilderPool.concat(metaData.middle, "=?"),
            whereArgus,
            orderBy);

         return (List<T>)OrmUtils.selectList(cursor, tableClass);
      } catch (Exception ex) {
         Dlog.notReached(ex);
         return Collections.EMPTY_LIST;
      }
   }

}

OrmUtils.java : OrmHelper는 OrmUtils의 static 메쏘드의 구현에 의존한다. ORM의 구현은 OrmUtils가 핵심이 된다. 

package com.mdiwebma.base.db.orm;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Set;
import java.util.concurrent.ConcurrentHashMap;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.annotation.NonNull;
import android.support.annotation.Nullable;
import android.util.Pair;

import com.mdiwebma.base.annotation.Table;
import com.mdiwebma.base.db.BaseSQLiteHelper;
import com.mdiwebma.base.helper.StringBuilderPool;
import com.mdiwebma.base.logging.Dlog;
import com.mdiwebma.base.utils.StringUtils;
import com.mdiwebma.base.utils.Triple;

/**
 * @author djkim
 * @TODO byte byte[] type, remove Float Integer, Double, Boolean 
 */
public class OrmUtils {

   private static final String PRIMARY_KEY = " PRIMARY KEY ";
   private static final String AUTOINCREMENT = " AUTOINCREMENT ";
   private static final String NOT_NULL = " NOT NULL ";
   private static final String DEFAULT = " DEFAULT ";

   //----------------------------------------------------------------------------------------------
   // Schema

   @NonNull
   public static String getCreateTableSQL(@NonNull Class<? extends OrmObject> tableClass) {
      Table table = (Table)tableClass.getAnnotation(Table.class);
      Dlog.assertNotNull(table);

      StringBuilder sb = StringBuilderPool.obtain();
      sb.append(" CREATE TABLE IF NOT EXISTS ");
      sb.append(table.name());
      sb.append(" ( ");

      boolean appendComma = false;
      Field[] fields = tableClass.getFields();

      for (Field field : fields) {
         // check  sqlite 3 types
         Table.TypeText typeText = (Table.TypeText)field.getAnnotation(Table.TypeText.class);
         if (typeText != null) {
            Dlog.assertTrue(field.getType() == String.class);

            if (appendComma) {
               sb.append(" , ");
            }
            sb.append(typeText.name()).append(" TEXT ");

            if (typeText.primaryKey()) {
               sb.append(PRIMARY_KEY);
            }

            if (typeText.notNull()) {
               sb.append(NOT_NULL);
            }

            if (StringUtils.isNotEmpty(typeText.defaultValue())) {
               sb.append(DEFAULT).append(typeText.defaultValue());
            }

            appendComma = true;
            continue;
         }

         Table.TypeInteger typeInteger = (Table.TypeInteger)field.getAnnotation(Table.TypeInteger.class);
         if (typeInteger != null) {
            Dlog.assertTrue(field.getType() == int.class || field.getType() == Integer.class
               || field.getType() == long.class || field.getType() == Long.class
               || field.getType() == boolean.class || field.getType() == Boolean.class
               || field.getType() == short.class || field.getType() == Short.class);

            if (appendComma) {
               sb.append(" , ");
            }
            sb.append(typeInteger.name()).append(" INTEGER ");

            if (typeInteger.primaryKey()) {
               sb.append(PRIMARY_KEY);
            }

            if (typeInteger.autoIncrement()) {
               sb.append(AUTOINCREMENT);
            }

            if (typeInteger.notNull()) {
               sb.append(NOT_NULL);
            }

            if (StringUtils.isNotEmpty(typeInteger.defaultValue())) {
               sb.append(DEFAULT).append(typeInteger.defaultValue());
            }

            appendComma = true;
            continue;
         }

         Table.TypeReal typeReal = (Table.TypeReal)field.getAnnotation(Table.TypeReal.class);
         if (typeReal != null) {
            Dlog.assertTrue(field.getType() == float.class || field.getType() == Float.class
               || field.getType() == double.class || field.getType() == Double.class);

            if (appendComma) {
               sb.append(" , ");
            }
            sb.append(typeReal.name()).append(" REAL ");

            if (typeReal.primaryKey()) {
               sb.append(PRIMARY_KEY);
            }

            if (typeReal.notNull()) {
               sb.append(NOT_NULL);
            }

            if (StringUtils.isNotEmpty(typeReal.defaultValue())) {
               sb.append(DEFAULT).append(typeReal.defaultValue());
            }

            appendComma = true;
            continue;
         }

         Table.TypeBlob typeBlob = (Table.TypeBlob)field.getAnnotation(Table.TypeBlob.class);
         if (typeBlob != null) {
            Dlog.assertTrue(field.getType() == byte[].class);

            if (appendComma) {
               sb.append(" , ");
            }
            sb.append(typeBlob.name()).append(" BLOB ");

            appendComma = true;
            continue;
         }
      }

      sb.append(" ) ");

      return StringBuilderPool.recycle(sb);
   };

   @NonNull
   public static String getDropTableSQL(@NonNull Class<? extends OrmObject> tableClass) {
      String tableName = getTableName(tableClass);
      Dlog.assertNotNull(tableName);

      StringBuilder sb = StringBuilderPool.obtain();
      sb.append("DROP TABLE IF EXISTS ").append(tableName);

      return StringBuilderPool.recycle(sb);
   }

   //----------------------------------------------------------------------------------------------
   // annotation meta data 

   private static final int TYPE_TEXT = 0;
   private static final int TYPE_INTEGER = 1;
   private static final int TYPE_REAL = 2;
   private static final int TYPE_BLOB = 3;

   private static class OrmField {
      OrmField(int type, String name, Field field, boolean primaryKey, boolean autoIncrement) {
         this.type = type;
         this.name = name;
         this.field = field;
         this.primaryKey = primaryKey;
         this.autoIncrement = autoIncrement;
      }

      final int type;
      final String name;
      final Field field;
      final boolean primaryKey;
      final boolean autoIncrement;
   }

   // Triple<TableName, Primaary Field Name,, Columns[]>
   // OrmField List
   private static ConcurrentHashMap<Class, Pair<Triple<String, String, String[]>, List<OrmField>>> ormObjectMetaDataMap = new ConcurrentHashMap<>();

   @NonNull
   private static List<OrmField> getTableFiledList(Class<? extends OrmObject> tableClass) {
      Pair<?, List<OrmField>> metaData = getOrmObjectMetaData(tableClass);
      return metaData.second;
   }

   @NonNull
   private static String getTableName(Class<? extends OrmObject> tableClass) {
      Pair<Triple<String, String, String[]>, List<OrmField>> metaData = getOrmObjectMetaData(tableClass);
      return metaData.first.left;
   }

   @NonNull
   // Table, primary key name, columns[]
   public static Triple<String, String, String[]> getTableColumns(Class<? extends OrmObject> tableClass) {
      Pair<Triple<String, String, String[]>, List<OrmField>> metaData = getOrmObjectMetaData(tableClass);
      return metaData.first;
   }

   @NonNull
   // Triple<TableName, Primaary Field Name,, Columns[]>
   // OrmField List
   private static Pair<Triple<String, String, String[]>, List<OrmField>> getOrmObjectMetaData(
         Class<? extends OrmObject> tableClass) {
      Pair<Triple<String, String, String[]>, List<OrmField>> metaData = ormObjectMetaDataMap.get(tableClass);
      if (metaData != null) {
         return metaData;
      }

      Table table = (Table)tableClass.getAnnotation(Table.class);
      Dlog.assertNotNull(table);
      String tableName = table.name();
      String firstPrimaryKeyName = null;
      List<OrmField> fieldList = new ArrayList<>();

      Field[] fields = tableClass.getFields();
      for (Field field : fields) {
         // check  sqlite 3 types
         Table.TypeText typeText = (Table.TypeText)field.getAnnotation(Table.TypeText.class);
         if (typeText != null) {
            Dlog.assertTrue(field.getType() == String.class, field.getName());

            if (firstPrimaryKeyName == null && typeText.primaryKey()) {
               firstPrimaryKeyName = typeText.name();
            }

            OrmField ormField = new OrmField(TYPE_TEXT, typeText.name(), field, typeText.primaryKey(), false);
            fieldList.add(ormField);
            continue;
         }

         Table.TypeInteger typeInteger = (Table.TypeInteger)field.getAnnotation(Table.TypeInteger.class);
         if (typeInteger != null) {
            Dlog.assertTrue(field.getType() == int.class || field.getType() == Integer.class
               || field.getType() == long.class || field.getType() == Long.class
               || field.getType() == boolean.class || field.getType() == Boolean.class
               || field.getType() == short.class || field.getType() == Short.class, field.getName());

            if (firstPrimaryKeyName == null && typeInteger.primaryKey()) {
               Dlog.assertTrue(field.getType() != boolean.class && field.getType() != Boolean.class);
               firstPrimaryKeyName = typeInteger.name();
            }

            OrmField ormField = new OrmField(TYPE_INTEGER, typeInteger.name(), field, typeInteger.primaryKey(), typeInteger.autoIncrement());
            fieldList.add(ormField);
            continue;
         }

         Table.TypeReal typeReal = (Table.TypeReal)field.getAnnotation(Table.TypeReal.class);
         if (typeReal != null) {
            Dlog.assertTrue(field.getType() == float.class || field.getType() == Float.class
               || field.getType() == double.class || field.getType() == Double.class, field.getName());

            if (firstPrimaryKeyName == null && typeReal.primaryKey()) {
               firstPrimaryKeyName = typeReal.name();
            }

            OrmField ormField = new OrmField(TYPE_REAL, typeReal.name(), field, typeReal.primaryKey(), false);
            fieldList.add(ormField);
            continue;
         }

         Table.TypeBlob typeBlob = (Table.TypeBlob)field.getAnnotation(Table.TypeBlob.class);
         if (typeBlob != null) {
            Dlog.assertTrue(field.getType() == byte[].class, field.getName());

            OrmField ormField = new OrmField(TYPE_BLOB, typeBlob.name(), field, false, false);
            fieldList.add(ormField);
            continue;
         }
      }

      // column array for default query
      String[] columns = new String[fieldList.size()];
      for (int i = 0; i < fieldList.size(); i++) {
         columns[i] = fieldList.get(i).name;
      }

      ormObjectMetaDataMap.putIfAbsent(tableClass, new Pair<>(new Triple<>(tableName, firstPrimaryKeyName, columns), fieldList));
      return ormObjectMetaDataMap.get(tableClass);
   }

   //----------------------------------------------------------------------------------------------
   // insert update elete 

   public static long insert(@NonNull BaseSQLiteHelper db, @NonNull OrmObject ormObject) {
      try {
         ContentValues contentValues = convertToContentValues(ormObject, true, null);
         return db.insert_(getTableName(ormObject.getClass()), contentValues);
      } catch (Exception ex) {
         Dlog.notReached(ex);
      }
      return -1;
   }

   @NonNull
   public static ContentValues convertToContentValues(@NonNull OrmObject ormObject, boolean forInsertMode,
         @Nullable Set<String> updateColumns) {
      List<OrmField> fieldList = getTableFiledList(ormObject.getClass());
      ContentValues contentValues = new ContentValues();

      for (OrmField ormField : fieldList) {
         if (forInsertMode) {
            if (ormField.autoIncrement) {
               continue;
            }
         } else if (ormField.primaryKey) { // for Update
            continue;
         }

         if (forInsertMode == false && updateColumns != null && updateColumns.contains(ormField.name) == false) {
            continue;
         }

         try {
            Class klass = ormField.field.getType();

            ormField.field.setAccessible(true);

            if (ormField.type == TYPE_INTEGER) {
               if (klass == int.class) {
                  contentValues.put(ormField.name, (Integer)ormField.field.getInt(ormObject));
               } else if (klass == long.class) {
                  contentValues.put(ormField.name, (Long)ormField.field.getLong(ormObject));
               } else if (klass == boolean.class) {
                  contentValues.put(ormField.name, ormField.field.getBoolean(ormObject) ? (Integer)1 : (Integer)0);
               } else if (klass == short.class) {
                  contentValues.put(ormField.name, (Short)ormField.field.getShort(ormObject));
               } else if (klass == Integer.class) {
                  contentValues.put(ormField.name, (Integer)ormField.field.get(ormObject));
               } else if (klass == Long.class) {
                  contentValues.put(ormField.name, (Long)ormField.field.get(ormObject));
               } else if (klass == Boolean.class) {
                  Boolean value = (Boolean)ormField.field.get(ormObject);
                  contentValues.put(ormField.name, (value != null && value.booleanValue()) ? (Integer)1
                     : (Integer)0);
               } else if (klass == Short.class) {
                  contentValues.put(ormField.name, (Short)ormField.field.get(ormObject));
               }

            } else if (ormField.type == TYPE_TEXT) {
               contentValues.put(ormField.name, (String)ormField.field.get(ormObject));

            } else if (ormField.type == TYPE_REAL) {
               if (klass == float.class) {
                  contentValues.put(ormField.name, (Float)ormField.field.getFloat(ormObject));
               } else if (klass == double.class) {
                  contentValues.put(ormField.name, (Double)ormField.field.getDouble(ormObject));
               } else if (klass == Float.class) {
                  contentValues.put(ormField.name, (Float)ormField.field.get(ormObject));
               } else if (klass == Double.class) {
                  contentValues.put(ormField.name, (Double)ormField.field.get(ormObject));
               }
            } else if (ormField.type == TYPE_BLOB) {
               if (klass == byte[].class) {
                  contentValues.put(ormField.name, (byte[])ormField.field.get(ormObject));
               }
            }

         } catch (IllegalAccessException | IllegalArgumentException ex) {
            Dlog.assertException(ex);
         }
      }

      return contentValues;
   }

   public static long insertOrUpdate(@NonNull BaseSQLiteHelper db, @NonNull OrmObject ormObject) {
      try {
         ContentValues contentValues = convertToContentValues(ormObject, true, null);

         return db.getWritableDatabase().insertWithOnConflict(
            getTableName(ormObject.getClass()), null, contentValues, SQLiteDatabase.CONFLICT_REPLACE);

      } catch (Exception ex) {
         Dlog.notReached(ex);
      }
      return -1;
   }

   public static int update(@NonNull BaseSQLiteHelper db, @NonNull OrmObject ormObject) {
      return update(db, ormObject, null);
   }

   public static int update(@NonNull BaseSQLiteHelper db, @NonNull OrmObject ormObject,
         @Nullable Set<String> updateColumns) {
      try {
         // TableName, PrimaryKey Fieldname, value
         Triple<String, String, String> data = getTablePrimaryKeyValue(ormObject);
         Dlog.assertNotNull(data);

         ContentValues contentValues = convertToContentValues(ormObject, false, updateColumns);
         if (contentValues.size() == 0) {
            Dlog.notReached();
         }

         return db.update_(data.left, contentValues, StringBuilderPool.concat(data.middle, "=?"), new String[]{data.right});
      } catch (Exception ex) {
         Dlog.notReached(ex);
      }

      return -1;
   }

   // <TableName, PrimaryKeyName, IDValue>
   public static Triple<String, String, String> getTablePrimaryKeyValue(@NonNull OrmObject ormObject) {
      Pair<Triple<String, String, String[]>, List<OrmField>> metaData = getOrmObjectMetaData(ormObject.getClass());
      String tableName = metaData.first.left;
      List<OrmField> fieldList = metaData.second;

      for (OrmField ormField : fieldList) {
         if (ormField.primaryKey) {
            try {
               ormField.field.setAccessible(true);
               String idValue = null;

               if (ormField.type == TYPE_INTEGER) {
                  Class klass = ormField.field.getType();
                  if (klass == int.class) {
                     int value = ormField.field.getInt(ormObject);
                     idValue = String.valueOf(value);
                  } else if (klass == long.class) {
                     long value = ormField.field.getLong(ormObject);
                     idValue = String.valueOf(value);
                  } else if (klass == short.class) {
                     short value = ormField.field.getShort(ormObject);
                     idValue = String.valueOf(value);
                  } else if (klass == Integer.class) {
                     Integer value = (Integer)ormField.field.get(ormObject);
                     if (value != null) {
                        idValue = String.valueOf(value.intValue());
                     }
                  } else if (klass == Long.class) {
                     Long value = (Long)ormField.field.get(ormObject);
                     if (value != null) {
                        idValue = String.valueOf(value.longValue());
                     }
                  } else if (klass == Short.class) {
                     Short value = (Short)ormField.field.get(ormObject);
                     if (value != null) {
                        idValue = String.valueOf(value.shortValue());
                     }
                  }

               } else if (ormField.type == TYPE_TEXT) {
                  idValue = (String)ormField.field.get(ormObject);
               }

               return new Triple<>(tableName, ormField.name, idValue);

            } catch (IllegalAccessException | IllegalArgumentException ex) {
               Dlog.assertException(ex);
            } catch (Exception ex) {
               Dlog.notReached(ex);
            }
            break;
         }
      }

      return null;
   }

   public static int delete(@NonNull BaseSQLiteHelper db, @NonNull OrmObject ormObject) {
      // TableName, PrimaryKey Fieldname, value
      Triple<String, String, String> data = getTablePrimaryKeyValue(ormObject);
      Dlog.assertNotNull(data);

      try {
         return db.delete_(data.left, StringBuilderPool.concat(data.middle, "=?"), new String[]{data.right});
      } catch (Exception ex) {
         Dlog.notReached(ex);
      }

      return -1;
   }

   //----------------------------------------------------------------------------------------------
   // select , query

   @Nullable
   public static <T extends OrmObject> T select(@NonNull Cursor cursor, Class<T> klass) {
      Dlog.assertNotNull(cursor);

      T data = null;

      try {
         if (cursor.moveToNext()) {
            try {
               data = klass.getDeclaredConstructor().newInstance();
            } catch (NoSuchMethodException | IllegalArgumentException | IllegalAccessException
                  | InstantiationException | InvocationTargetException ex) {
               Dlog.assertException(ex);
               return null;
            }

            List<OrmField> fieldList = getTableFiledList(klass);

            for (OrmField ormField : fieldList) {
               int columnIndex = cursor.getColumnIndex(ormField.name);
               if (columnIndex >= 0) {
                  putFieldObject(ormField.field, ormField.type, data, cursor, columnIndex);
               }
            }
         }
      } finally {
         cursor.close();
      }
      return data;
   }

   @Nullable
   public static <T extends OrmObject> boolean select(@NonNull Cursor cursor, Class<T> klass, OrmObject ormObject) {
      Dlog.assertNotNull(cursor);

      try {
         if (cursor.moveToNext()) {
            List<OrmField> fieldList = getTableFiledList(klass);

            for (OrmField ormField : fieldList) {
               int columnIndex = cursor.getColumnIndex(ormField.name);
               if (columnIndex >= 0) {
                  putFieldObject(ormField.field, ormField.type, ormObject, cursor, columnIndex);
               }
            }

            return true;
         }
      } finally {
         cursor.close();
      }

      return false;
   }

   private static void putFieldObject(Field field, int type, OrmObject ormObject, Cursor cursor, int columnIndex) {
      try {
         Class klass = field.getType();
         field.setAccessible(true);

         if (type == TYPE_TEXT) {
            field.set(ormObject, cursor.getString(columnIndex));
         } else if (type == TYPE_INTEGER) {
            if (klass == int.class) {
               field.set(ormObject, cursor.getInt(columnIndex));
            } else if (klass == long.class) {
               field.set(ormObject, cursor.getLong(columnIndex));
            } else if (klass == boolean.class) {
               field.set(ormObject, cursor.getInt(columnIndex) == 1);
            } else if (klass == short.class) {
               field.set(ormObject, cursor.getShort(columnIndex));
            } else if (klass == Integer.class) {
               field.set(ormObject, (Integer)cursor.getInt(columnIndex));
            } else if (klass == Long.class) {
               field.set(ormObject, (Long)cursor.getLong(columnIndex));
            } else if (klass == Boolean.class) {
               field.set(ormObject, Boolean.valueOf(cursor.getInt(columnIndex) == 1));
            } else if (klass == Short.class) {
               field.set(ormObject, (Short)cursor.getShort(columnIndex));
            }
         } else if (type == TYPE_REAL) {
            if (klass == double.class) {
               field.set(ormObject, cursor.getDouble(columnIndex));
            } else if (klass == float.class) {
               field.set(ormObject, cursor.getFloat(columnIndex));
            } else if (klass == Double.class) {
               field.set(ormObject, (Double)cursor.getDouble(columnIndex));
            } else if (klass == Float.class) {
               field.set(ormObject, (Float)cursor.getFloat(columnIndex));
            }
         } else if (type == TYPE_BLOB) {
            if (klass == byte[].class) {
               field.set(ormObject, cursor.getBlob(columnIndex));
            }
         }
      } catch (IllegalArgumentException | IllegalAccessException ex) {
         Dlog.assertException(ex);
      }
   }

   @NonNull
   public static <T extends OrmObject> List<T> selectList(@NonNull Cursor cursor, Class<T> klass) {
      Dlog.assertNotNull(cursor);
      List<T> list = Collections.EMPTY_LIST;

      try {
         if (cursor.getCount() > 0) {
            list = new ArrayList<>(cursor.getCount());
            List<OrmField> fieldList = getTableFiledList(klass);

            while (cursor.moveToNext()) {
               T data;

               try {
                  data = klass.getDeclaredConstructor().newInstance();
               } catch (NoSuchMethodException | IllegalArgumentException | IllegalAccessException
                     | InstantiationException | InvocationTargetException ex) {
                  cursor.close();
                  Dlog.assertException(ex);
                  return list;
               }

               for (OrmField ormField : fieldList) {
                  int columnIndex = cursor.getColumnIndex(ormField.name);
                  if (columnIndex >= 0) {
                     putFieldObject(ormField.field, ormField.type, data, cursor, columnIndex);
                  }
               }

               list.add(data);
            }
         }
      } finally {
         cursor.close();
      }

      return list;
   }

}

구현의 단계별 테스트 코드: 

void test3() {

   String _id = "test_key333";
   new OrmHelper(SettingDO.class).id(_id).delete();

   String[] cols = new String[]{SettingDO.COL_KEY, SettingDO.COL_VALUE};
   Cursor cursor = DBHelper.getInstance().query_(SettingDO.TABLE, cols, SettingDO.COL_KEY + "=?", new String[]{_id}, null);
   SettingDO setting = OrmUtils.select(cursor, SettingDO.class);
   Dlog.test("setting == null", setting == null);

   setting = new SettingDO(_id, "testval");
   long rowId = OrmUtils.insert(DBHelper.getInstance(), setting);
   Dlog.test("rowId != -1", rowId != -1);

   cursor = DBHelper.getInstance().query_(SettingDO.TABLE, cols, SettingDO.COL_KEY + "=?", new String[]{_id}, null);
   setting = OrmUtils.select(cursor, SettingDO.class);
   Dlog.test("setting != null", setting != null);
   Dlog.test("setting.value == testval", setting.value.equals("testval"));

   setting.value = "testval2";
   int rowCount = OrmUtils.update(DBHelper.getInstance(), setting);
   Dlog.test("rowCount == 1", rowCount == 1);

   cursor = DBHelper.getInstance().query_(SettingDO.TABLE, cols, SettingDO.COL_KEY + "=?", new String[]{_id}, null);
   setting = OrmUtils.select(cursor, SettingDO.class);
   Dlog.test("setting != null", setting != null);
   Dlog.test("setting.value == testval2", setting.value.equals("testval2"));

   setting.value = "testval3";
   rowId = OrmUtils.insertOrUpdate(DBHelper.getInstance(), setting);
   Dlog.test("rowId != -1", rowId != -1);

   cursor = DBHelper.getInstance().query_(SettingDO.TABLE, cols, SettingDO.COL_KEY + "=?", new String[]{_id}, null);
   setting = OrmUtils.select(cursor, SettingDO.class);
   Dlog.test("setting != null", setting != null);
   Dlog.test("setting.value == testval3", setting.value.equals("testval3"));

   rowCount = OrmUtils.delete(DBHelper.getInstance(), setting);
   Dlog.test("rowCount == 1", rowCount == 1);

   cursor = DBHelper.getInstance().query_(SettingDO.TABLE, cols, SettingDO.COL_KEY + "=?", new String[]{_id}, null);
   setting = OrmUtils.select(cursor, SettingDO.class);
   Dlog.test("setting == null", setting == null);
}

void test4() {
   String _id = "test_key5";
   new OrmHelper(SettingDO.class).id(_id).delete();

   SettingDO setting = new OrmHelper(SettingDO.class).id(_id).select();
   Dlog.test("setting == null", setting == null);

   setting = new SettingDO(_id, "testval");
   boolean rowUpdated = new OrmHelper(setting).insert();
   Dlog.test("rowUpdated = true", rowUpdated == true);

   setting = new OrmHelper(SettingDO.class).id(_id).select();
   Dlog.test("setting != null", setting != null);
   Dlog.test("setting.value == testval", setting.value.equals("testval"));

   setting.value = "testval2";
   rowUpdated = new OrmHelper(setting).update();
   Dlog.test("rowUpdated == true", rowUpdated == true);

   setting = new OrmHelper(SettingDO.class).id(_id).select();
   Dlog.test("setting != null", setting != null);
   Dlog.test("setting.value == testval2", setting.value.equals("testval2"));

   setting.value = "testval3";
   rowUpdated = new OrmHelper(setting).insertOrUpdate();
   Dlog.test("rowUpdated == true", rowUpdated == true);

   setting = new OrmHelper(SettingDO.class).id(_id).select();
   Dlog.test("setting != null", setting != null);
   Dlog.test("setting.value == testval3", setting.value.equals("testval3"));

   List<SettingDO> settingList = new OrmHelper(SettingDO.class).id(_id).selectList();
   Dlog.test("settingList != null", settingList != null);
   Dlog.test("settingList.size() == 1", settingList.size() >= 1);
   Dlog.test("settingList.get(0).value == testval3", settingList.get(0).value.equals("testval3"));

   rowUpdated = new OrmHelper(setting).delete();
   Dlog.test("rowUpdated == true", rowUpdated == true);

   setting = new OrmHelper(SettingDO.class).id(_id).select();
   Dlog.test("setting == null", setting == null);
}

void test5() {
   Dlog.l("test_key6");
   String _id = "test_key6";
   new OrmHelper(SettingDO.class).id(_id).delete();

   SettingDO setting = new SettingDO();
   Dlog.test("setting == null", setting.select(_id) == false);

   setting = new SettingDO(_id, "testval");
   Dlog.test("rowUpdated = true", setting.insert());

   setting = new SettingDO();
   Dlog.test("setting != null", setting.select(new String[]{SettingDO.COL_VALUE}, _id));
   Dlog.test("setting.id == null", setting.id == null);
   Dlog.test("setting.value == testval", setting.value.equals("testval"));

   setting = new SettingDO();
   Dlog.test("setting != null", setting.select(_id));
   Dlog.test("setting.id == test_key6", setting.id.equals("test_key6"));
   Dlog.test("setting.value == testval", setting.value.equals("testval"));

   setting.value = "testval2";
   Dlog.test("rowUpdated == true", setting.update());

   setting = new SettingDO();
   Dlog.test("setting != null", setting.select(_id));
   Dlog.test("setting.value == testval2", setting.value.equals("testval2"));

   setting.value = "testval3";
   Dlog.test("rowUpdated == true", setting.update());

   setting = new SettingDO();
   Dlog.test("setting != null", setting.select(_id));
   Dlog.test("setting.value == testval3", setting.value.equals("testval3"));

   setting.value = "testval4";
   Dlog.test("rowUpdated == true", setting.update(SettingDO.UPDATE_COLUMNS));

   setting = new SettingDO();
   Dlog.test("setting != null", setting.select(_id));
   Dlog.test("setting.value == testval4", setting.value.equals("testval4"));

   Dlog.test("rowUpdated == true", setting.delete());
   Dlog.test("setting == null", setting.select(_id) == false);

   List<SettingDO> list = new OrmHelper(SettingDO.class).where("key like '%key%'", null).orderBy(SettingDO.COL_KEY, false).selectList();
   for (SettingDO s : list) {
      Dlog.l(s.id);
   }

   List<SettingDO> list2 = new OrmHelper(SettingDO.class).selectList();
   for (SettingDO s : list2) {
      Dlog.l(s.value);
   }
}

모든 구현이 다 테스트 된 것은 아니지만, 미구현이나 오류는 개발하면서 수정할 생각이다.. 포린키나 인덱스 키를 생성하는 것도 TODO