24. 코틀린 데이터베이스 조회(리플렉션)

package com.example.common   interface ISqlData { }

1. 조회용 제한으로 사용할 인터페이스를 만든다.

 

package com.example.common import android.content.Context import android.database.Cursor import android.database.sqlite.SQLiteDatabase import android.database.sqlite.SQLiteOpenHelper import android.util.Log import androidx.core.database.getBlobOrNull import androidx.core.database.getFloatOrNull import androidx.core.database.getIntOrNull import androidx.core.database.getStringOrNull import java.lang.Exception import kotlin.reflect.KMutableProperty import kotlin.reflect.KProperty1 import kotlin.reflect.full.*   class SqlHelper(context: Context): SQLiteOpenHelper(context, "Quiz", null, 1) {       //https://developer.android.com/training/data-storage/sqlite     companion object{         private const val DATABASE_VERSION  = 1         private const val DATABASE_NAME = "Quiz.db"           private val quizTable = SqlQuizTable();     }       override fun onCreate(db: SQLiteDatabase?) {         try{             db!!.execSQL(quizTable.SQL_CREATE_ENTRIES);         }         catch(ex:Exception){             Log.d("onCreate", ex.message);         }     }       override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {         db!!.execSQL(quizTable.SQL_DELETE_ENTRIES);         onCreate(db);     }       override fun onDowngrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {         onUpgrade(db, oldVersion, newVersion)     }       //https://kotlinlang.org/docs/reference/generics.html     //https://blog.kotlin-academy.com/creating-a-random-instance-of-any-class-in-kotlin-b6168655b64a     //https://kotlinlang.org/docs/reference/reflection.html     //CRUD     public inline fun <reified T: ISqlData> getQuizList(db: SQLiteDatabase                                               , tableName:String                                               , columns:Array<String>?                                               , selection:String?                                               , selectionArgs:Array<String>?                                               , groupBy:String?                                               , having:String?                                               , orderBy:String?): MutableList<T> {         val list = mutableListOf<T>();           try{             //https://developer.android.com/reference/kotlin/android/database/sqlite/SQLiteDatabase             val cursor = db.query(                 tableName,   // The table to query                 columns,             // The array of columns to return (pass null to get all)                 selection,              // The columns for the WHERE clause                 selectionArgs,          // The values for the WHERE clause                 groupBy,                   // don't group the rows                 having,                   // don't filter by row groups                 orderBy               // The sort order             )               with(cursor) {                 while (moveToNext()) {                     val sqlDataClass= T::class;  //T::class.constructors.first { it.parameters.isEmpty() }.call();                       sqlDataClass.memberProperties.forEach{                         val index = getColumnIndexOrThrow(it.name);                           //https://stackoverflow.com/questions/44304480/how-to-set-delegated-property-value-by-reflection-in-kotlin                         val property = T::class.memberProperties.find<KProperty1<T, *>>{ prop -> prop.name == it.name };                           if (property is KMutableProperty<*>) {                             //https://developer.android.com/reference/android/database/Cursor#FIELD_TYPE_BLOB                             when(cursor.getType(index)){                                 Cursor.FIELD_TYPE_BLOB -> { property.setter.call(sqlDataClass, cursor.getBlobOrNull(index));}                                 Cursor.FIELD_TYPE_FLOAT -> { property.setter.call(sqlDataClass, cursor.getFloatOrNull(index));}                                 Cursor.FIELD_TYPE_INTEGER -> { property.setter.call(sqlDataClass, cursor.getIntOrNull(index));}                                 Cursor.FIELD_TYPE_STRING -> { property.setter.call(sqlDataClass, cursor.getStringOrNull(index));}                                 Cursor.FIELD_TYPE_NULL -> { property.setter.call(sqlDataClass, cursor.isNull(index));}                             }                         }                           //https://stackoverflow.com/questions/47675033/how-do-i-use-kotlin-object-by-reflection                         /*val s:String = "name : ${it.name}, getterName : ${it.getter.name}, property : ${it.getter.property}";                         Log.i("class data", s);*/                     }                       list.add(sqlDataClass.createInstance());                 }             }         }         catch (ex:Exception){             Log.d("getQuizList exception", ex.message);             throw ex;         }           return list;     } }

 

2. SqlHelper를 만든다. (SqlHelper를 하나 만들고, 중간에 관리하는 클래스를 만들어도 되긴하는데 큰 규모가 아니면 신경 안써도 될듯하다.)

 

package com.example.common   class SqlQuizData: ISqlData {     public var quizIndex: Int? = 0;     public var quizText:String? = "";     public var quizImage: Array<Byte?> = emptyArray();       public var example1:String? = "";     public var example2:String? = "";     public var example3:String? = "";     public var example4:String? = "";       public var correct:Int? = 0;     public var hint:String? = "";     public var score:Int? = 0;     public var time:Int? = 0; }

 

3. ORM 매핑할 테이블 구조를 만든다. 이때 ISqlData를 상속받게 한다.

 

package com.example.common   class SqlQuizTable {     //https://developer.android.com/training/data-storage/sqlite?hl=ko#kotlin       internal object SqliteQuizTableContract {         //Table         internal const val TABLE_NAME = "QuizTable"           //Column         internal const val COLUMN_QUIZ_INDEX = "QuizIndex"         internal const val COLUMN_QUIZ_TEXT = "QuizText"         internal const val COLUMN_QUIZ_IMAGE = "QuizImage"           internal const val COLUMN_EXAMPLE1 = "Example1"         internal const val COLUM_NEXAMPLE2 = "Example2"         internal const val COLUM_NEXAMPLE3 = "Example3"         internal const val COLUM_NEXAMPLE4 = "Example4"           internal const val COLUMN_CORRECT = "Correct"         internal const val COLUMN_HINT = "Hint"         internal const val COLUMN_SCORE = "Score"         internal const val COLUMN_TIME = "Time"     }       lateinit var SQL_CREATE_ENTRIES: String         private set       lateinit var SQL_DELETE_ENTRIES:String         private set       //not use     //private lateinit var projection:Array<String>       // Filter results WHERE "title" = 'My Title'  /*   val selection = "${FeedEntry.COLUMN_NAME_TITLE} = ?"     val selectionArgs = arrayOf("My Title")*/       // How you want the results sorted in the resulting Cursor     //val sortOrder = "${FeedEntry.COLUMN_NAME_SUBTITLE} DESC"       constructor(){         SQL_CREATE_ENTRIES = ("Create Table ${SqliteQuizTableContract.TABLE_NAME}" +                 "(${SqliteQuizTableContract.COLUMN_QUIZ_INDEX} INTEGER PRIMARY KEY " +                 ", ${SqliteQuizTableContract.COLUMN_QUIZ_TEXT} TEXT" +                 ", ${SqliteQuizTableContract.COLUMN_QUIZ_IMAGE} BLOB" +                 ", ${SqliteQuizTableContract.COLUMN_EXAMPLE1} TEXT" +                 ", ${SqliteQuizTableContract.COLUM_NEXAMPLE2} TEXT" +                 ", ${SqliteQuizTableContract.COLUM_NEXAMPLE3} TEXT" +                 ", ${SqliteQuizTableContract.COLUM_NEXAMPLE4} TEXT" +                 ", ${SqliteQuizTableContract.COLUMN_CORRECT} INTEGER" +                 ", ${SqliteQuizTableContract.COLUMN_HINT} TEXT" +                 ", ${SqliteQuizTableContract.COLUMN_SCORE} INTEGER" +                 ", ${SqliteQuizTableContract.COLUMN_TIME} INTEGER)");           SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS ${SqliteQuizTableContract.TABLE_NAME}";           //projection = arrayOf(SqliteQuizTableContract.COLUMN_QUIZ_INDEX);     } }

 

4. 새로 만들때 사용할 정보를 담고 있는 테이블

 

private fun databaseInit() : Unit{         //데이터 베이스에 정보가 있는지 찾는다.         this.sqlHelper = SqlHelper(this);         val readDb = this.sqlHelper.readableDatabase;           //writeDb를 오픈해둔다.         this.writeDb = this.sqlHelper.writableDatabase;           //데이터 가져오기.         try{             this.quizList = sqlHelper.getQuizList<SqlQuizData>(readDb                 , SqlQuizTable.SqliteQuizTableContract.TABLE_NAME                 , null, null, null                 , null, null, null);         }         catch(ex:Exception){             Log.e("QuizCreator.onInit", ex.message);         }     }

 

5. 내가 실제로 콜한 부분.

 

안타깝게 리플렉션이 되는 부분은 만들어 놓고 테스트를 못 했다.

딴거보다 디버그를 하며 확인하는데 시간이 너무 걸려서 -..-

공부하고 싶은 부분에 대해서는 어느정도 끝났기에, 이제 다른 작업을 해봐야겠다.