[Android] SQLite 외부 DB파일 이용해서 안드로이드 내 DB로 사용하기

• sqliteBrowser로 DB 파일 생성
• 안드로이드 프로젝트에서 Assets 폴더 생성 : File - new -Folder - Assets folder

• assets 내 폴더에 DB파일 넣기
• SQLiteOpenHelper class 활용해서 assets 내 폴더에 있는 DB를 data/data/패키지이름/databases 폴더로 복붙하는 코드 작성

import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.util.Log
import java.io.File
import java.io.FileOutputStream
import java.io.IOException
import java.io.InputStream
import java.io.OutputStream

class DBHelper(private val mContext: Context) : SQLiteOpenHelper(mContext, DB_NAME, null, 1) {

    companion object {
        private const val TAG = "DataBaseHelper"
        private var DB_PATH = ""
        private const val DB_NAME = "디비파일이름.db"
    }

    init {
        DB_PATH = "/data/data/" + mContext.packageName + "/databases/"
        dataBaseCheck()
    }

    private fun dataBaseCheck() {
        val dbFile = File(DB_PATH + DB_NAME)
        if (!dbFile.exists()) {
            dbCopy()
            Log.d(TAG, "Database is copied.")
        }
    }

    override fun onCreate(db: SQLiteDatabase) {
        Log.d(TAG, "onCreate()")
    }

    override fun onOpen(db: SQLiteDatabase) {
        super.onOpen(db)
        Log.d(TAG, "onOpen() : DB Opening!")
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        Log.d(TAG, "onUpgrade() : DB Schema Modified and Executing onCreate()")
    }

    // assets에 있는 db 복사
    private fun dbCopy() {
        try {
            val folder = File(DB_PATH)
            if (!folder.exists()) {
                folder.mkdir()
            }

            val inputStream: InputStream = mContext.assets.open(DB_NAME)
            val out_filename = DB_PATH + DB_NAME
            val outputStream = FileOutputStream(out_filename)
            val mBuffer = ByteArray(1024)
            var mLength: Int
            while (inputStream.read(mBuffer).also { mLength = it } > 0) {
                outputStream.write(mBuffer, 0, mLength)
            }
            outputStream.flush()
            outputStream.close()
            inputStream.close()

        } catch (e: IOException) {
            e.printStackTrace()
            Log.d("dbCopy", "IOException 발생함")
        }
    }
}

• DAO 작성(sql CRUD)

import android.content.ContentValues
import android.content.Context

class AddrDAO {

    companion object {
        // Create : 저장
        fun insertData(context: Context, data: AddrClass){
            // 컬럼이름과 데이터를 설정하는 객체
            val contentValues = ContentValues()
            // 컬럼 이름, 값을 지정한다.
            contentValues.put("G_NM", data.g_nm)
            contentValues.put("D_NM", data.d_nm)

            val dbHelper = DBHelper(context)

            dbHelper.writableDatabase.insert("ADDR", null, contentValues)
            dbHelper.close()
        }

        // Read Condition : 조건에 맞는 행 하나를 가져온다.
        fun selectData(context: Context, addr_id:Int):AddrClass{

            val dbHelper = DBHelper(context)
            val selection = "addr_id = ?"
            val args = arrayOf("$addr_id")
            val cursor = dbHelper.writableDatabase.query("ADDR", null, selection, args, null, null, null)

            cursor.moveToNext()

            val idx1 = cursor.getColumnIndex("ADDR_ID")
            val idx2 = cursor.getColumnIndex("G_NM")
            val idx3 = cursor.getColumnIndex("D_NM")

            val addr_id = cursor.getInt(idx1)
            val g_nm = cursor.getString(idx2)
            val d_nm = cursor.getString(idx3)


            val AddrClass = AddrClass(addr_id,g_nm,d_nm)

            dbHelper.close()
            return AddrClass
        }

        // Read All : 모든 행을 가져온다
        fun selectAllData(context: Context):MutableList<AddrClass>{

            val dbHelper = DBHelper(context)

            val cursor = dbHelper.writableDatabase.query("ADDR", null, null, null, null, null, null)

            val dataList = mutableListOf<AddrClass>()

            while(cursor.moveToNext()){
                val idx1 = cursor.getColumnIndex("addr_id")
                val idx2 = cursor.getColumnIndex("G_NM")
                val idx3 = cursor.getColumnIndex("D_NM")

                val addr_id = cursor.getInt(idx1)
                val g_nm = cursor.getString(idx2)
                val d_nm = cursor.getString(idx3)


                val AddrClass = AddrClass(addr_id,g_nm,d_nm)

                dataList.add(AddrClass)
            }


            dbHelper.close()

            return dataList
        }
        // Update : 조건에 맞는 행의 컬럼의 값을 수정한다.
        fun updateData(context:Context, obj:AddrClass){
            val cv = ContentValues()
            cv.put("g_nm", obj.g_nm)
            cv.put("d_nm", obj.d_nm)
            val condition = "addr_id = ?"
            val args = arrayOf("${obj.idx}")
            val dbHelper = DBHelper(context)
            dbHelper.writableDatabase.update("ADDR", cv, condition, args)
            dbHelper.close()
        }


        // Delete : 조건 맞는 행을 삭제한다.
        fun deleteData(context:Context, addr_id:Int){
            val condition = "addr_id = ?"
            val args = arrayOf("$addr_id")

            val dbHelper = DBHelper(context)
            dbHelper.writableDatabase.delete("ADDR", condition, args)
            dbHelper.close()
        }
    }

}