facebook twitter hatena line email

「Android/kotlin/db/room」の版間の差分

提供: 初心者エンジニアの簡易メモ
移動: 案内検索
(サンプル)
(likeの%をアノテーション定義に書く場合)
 
(同じ利用者による、間の28版が非表示)
行21: 行21:
 
}
 
}
 
</pre>
 
</pre>
 +
 +
https://codelabs.developers.google.com/codelabs/android-room-with-a-view-kotlin/#2
  
 
==サンプル==
 
==サンプル==
UserRoomDatabase.kt
+
schema1.usersのテーブルを置く時のサンプル
 +
 
 +
Schema1Database.kt
 
<pre>
 
<pre>
 
import android.content.Context
 
import android.content.Context
行31: 行35:
  
 
@Database(entities = arrayOf(UserEntity::class), version = 1)
 
@Database(entities = arrayOf(UserEntity::class), version = 1)
abstract class UserRoomDatabase: RoomDatabase() {
+
abstract class Schema1Database: RoomDatabase() {
 
     abstract fun userDao(): UserDao
 
     abstract fun userDao(): UserDao
 
     companion object {
 
     companion object {
 
         @Volatile
 
         @Volatile
         private var INSTANCE: UserRoomDatabase? = null
+
         private var INSTANCE: Schema1Database? = null
         fun getDatabase(context: Context): UserRoomDatabase {
+
         fun getDatabase(context: Context): Schema1Database {
 
             val tempInstance = INSTANCE
 
             val tempInstance = INSTANCE
 
             if (tempInstance != null) {
 
             if (tempInstance != null) {
行44: 行48:
 
                 val instance = Room.databaseBuilder(
 
                 val instance = Room.databaseBuilder(
 
                     context.applicationContext,
 
                     context.applicationContext,
                     UserRoomDatabase::class.java,
+
                     Schema1Database::class.java,
                     "users_database"
+
                     "schema1.db"
 
                 ).build()
 
                 ).build()
 
                 INSTANCE = instance
 
                 INSTANCE = instance
行79: 行83:
 
interface UserDao {
 
interface UserDao {
 
     @Query("SELECT * from users")
 
     @Query("SELECT * from users")
     fun getAllUserData(): List<UserEntity>
+
     fun getAll(): List<UserEntity>
  
     @Query("SELECT * FROM users WHERE uid IN (:userIds)")
+
     @Query("SELECT * FROM users WHERE id IN (:userIds)")
     fun loadAllByIds(userIds: IntArray): List<UserEntity>
+
     fun loadAllByIds(vararg userIds: Int): List<UserEntity>
 
+
    @Query("SELECT * FROM users WHERE first_name LIKE :first AND " +
+
              "last_name LIKE :last LIMIT 1")
+
    fun findByName(first: String, last: String): UserEntity
+
  
 
     @Query("SELECT * FROM users WHERE name LIKE :name")
 
     @Query("SELECT * FROM users WHERE name LIKE :name")
行101: 行101:
  
 
     @Update
 
     @Update
     fun updateUser(vararg user: UserEntity)
+
     fun updateUsers(vararg users: UserEntity)
  
 
     @Delete
 
     @Delete
行113: 行113:
 
}
 
}
 
</pre>
 
</pre>
 +
 +
@Insert(onConflict = OnConflictStrategy.REPLACE) は同一idの場合に置き換えされる
  
 
db呼び出し
 
db呼び出し
行120: 行122:
 
class MainActivity : AppCompatActivity() {
 
class MainActivity : AppCompatActivity() {
 
     val scope = CoroutineScope(Dispatchers.Default)
 
     val scope = CoroutineScope(Dispatchers.Default)
     companion object {
+
     lateinit var db: Schema1Database
        lateinit var database: UserRoomDatabase
+
    }
+
 
     override fun onCreate(savedInstanceState: Bundle?) {
 
     override fun onCreate(savedInstanceState: Bundle?) {
 
         super.onCreate(savedInstanceState)
 
         super.onCreate(savedInstanceState)
 
         setContentView(R.layout.activity_main)
 
         setContentView(R.layout.activity_main)
         database = UserRoomDatabase.getDatabase(applicationContext)
+
         db = Schema1Database.getDatabase(applicationContext)
 
         scope.launch {
 
         scope.launch {
 
             dbTask()
 
             dbTask()
行139: 行139:
 
     }
 
     }
 
     private fun dbExec() {
 
     private fun dbExec() {
         val dao = database.userDao()
+
         val dao = db.userDao()
 
         dao.insert(UserEntity(0, "rei"))
 
         dao.insert(UserEntity(0, "rei"))
 
         dao.insert(UserEntity(1, "taro"))
 
         dao.insert(UserEntity(1, "taro"))
行192: 行192:
 
  user.name = "siro"
 
  user.name = "siro"
 
  dao.update(user)
 
  dao.update(user)
==参考==
 
  
https://re-engines.com/2019/10/24/%E3%80%90kotlin%E3%80%91room%E3%81%A7db%E7%AE%A1%E7%90%86%E3%82%92%E3%81%99%E3%82%8B/
+
==以下のような修飾子を用意==
 +
@Insert(onConflict = OnConflictStrategy.REPLACE)
 +
@Insert(onConflict = OnConflictStrategy.ROLLBACK)
 +
@Insert(onConflict = OnConflictStrategy.ABORT)
 +
 
 +
参考:https://tech.recruit-mp.co.jp/mobile/post-12311/
 +
 
 +
==insertしたときにidを返す場合==
 +
戻り値の型にLongを設定すれば良い。
 +
<pre>
 +
@Dao
 +
interface UserDao {
 +
    @Insert(onConflict = OnConflictStrategy.REPLACE)
 +
    fun insert(user: UserEntity): Long
 +
}
 +
</pre>
 +
呼び出しサンプル
 +
<pre>
 +
var id = dao.insert(UserEntity(2, "jiro"))
 +
Log.i("db", "id=" + id.toString()) // 2
 +
</pre>
 +
 
 +
==updateした行数を返す場合==
 +
updateの戻り値の型にIntを設定する
 +
<pre>
 +
@Dao
 +
interface UserDao {
 +
    @Update
 +
    fun update(user: UserEntity): Int
 +
}
 +
</pre>
 +
呼び出しサンプル
 +
<pre>
 +
var cnt = dao.update(user)
 +
Log.i("db", "cnt=" + cnt.toString()) // 1
 +
</pre>
 +
 
 +
==sql失敗時の処理==
 +
<pre>
 +
try {
 +
    var ret = dao.insert(UserEntity(9223372036854775807, "jiro"))
 +
    Log.i("test", "ret insert id=" + ret.toString())
 +
} catch (e: SQLiteException) {
 +
    Log.i("test", "error message=" + e.message)
 +
}
 +
</pre>
 +
sqliteのintの上限値(9223372036854775807)で一度データを入れ、次にidを0指定してauto_incrementさせると以下エラーがe.messageで発生した。
 +
cannot rollback - no transaction is active (code 1 SQLITE_ERROR)
 +
 
 +
==insert失敗時の処理==
 +
isnertをIGNOREにしてみて、同じidを入れてみた所、失敗として、-1が返ってきた。
 +
 
 +
UserDao.kt
 +
<pre>
 +
@Dao
 +
interface UserDao {
 +
    @Insert(onConflict = OnConflictStrategy.IGNORE)
 +
    fun insert(user: UserEntity): Int
 +
}
 +
</pre>
 +
 
 +
MainActivity.kt
 +
<pre>
 +
var ret = dao.insert(UserEntity(1234, "jiro"))
 +
Log.i("test", "ret insert id=" + ret.toString()) // 1234
 +
var ret2 = dao.insert(UserEntity(1234, "jiro"))
 +
Log.i("test", "ret2 insert id=" + ret2.toString()) // -1
 +
</pre>
 +
参考:https://tutorialmore.com/questions-756651.htm
 +
 
 +
==likeの%をアノテーション定義に書く場合==
 +
@Query("SELECT * FROM users WHERE name LIKE '%' || :date || '%'")
 +
fun findByLikeDate(date: String): UserEntity
 +
 
 +
参考:https://stackoverflow.com/questions/44184769/android-room-select-query-with-like
 +
 
 +
==db定義変更時==
 +
以下エラーが発生する場合
 +
<pre>
 +
Caused by: java.lang.IllegalStateException: Room cannot verify the data integrity. Looks like you've changed schema but forgot to update the version number. You can simply fix this by increasing the version number.
 +
</pre>
 +
未リリースのときはアプリのデータを削除するだけで直る
 +
 
 +
参考:https://translate.google.com/translate?sl=en&tl=ja&u=https://stackoverflow.com/questions/44197309/room-cannot-verify-the-data-integrity
 +
 
 +
==参考==
 +
* https://re-engines.com/2019/10/24/%E3%80%90kotlin%E3%80%91room%E3%81%A7db%E7%AE%A1%E7%90%86%E3%82%92%E3%81%99%E3%82%8B/
 +
* https://developer.android.com/training/data-storage/room/accessing-data
 +
* https://developer.android.com/training/data-storage/room
 +
* https://qiita.com/niusounds/items/fff5e83489e69d7924fd
 +
* https://android.jlelse.eu/android-room-using-kotlin-f6cc0a05bf23
 +
* https://gabrieltanner.org/blog/android-room

2020年3月24日 (火) 19:45時点における最新版

Roomとは

SQLiteのマッパーライブラリ

公式:https://developer.android.com/training/data-storage/room

Androidでも公式が、強くおすすめすると書いてる。

準備

app/build.gradle

apply plugin: 'kotlin-android'
apply plugin: 'kotlin-android-extensions'
apply plugin: 'kotlin-kapt'
dependencies {
    implementation 'android.arch.persistence.room:runtime:1.0.0'
    annotationProcessor  'android.arch.persistence.room:compiler:1.0.0'
    kapt 'android.arch.persistence.room:compiler:1.0.0'
    
    implementation "org.jetbrains.kotlinx:kotlinx-coroutines-core:1.3.3"
    implementation "org.jetbrains.kotlinx:kotlinx-coroutines-android:1.3.3"
}

https://codelabs.developers.google.com/codelabs/android-room-with-a-view-kotlin/#2

サンプル

schema1.usersのテーブルを置く時のサンプル

Schema1Database.kt

import android.content.Context
import androidx.room.Database
import androidx.room.Room
import androidx.room.RoomDatabase

@Database(entities = arrayOf(UserEntity::class), version = 1)
abstract class Schema1Database: RoomDatabase() {
    abstract fun userDao(): UserDao
    companion object {
        @Volatile
        private var INSTANCE: Schema1Database? = null
        fun getDatabase(context: Context): Schema1Database {
            val tempInstance = INSTANCE
            if (tempInstance != null) {
                return tempInstance
            }
            synchronized(this) {
                val instance = Room.databaseBuilder(
                    context.applicationContext,
                    Schema1Database::class.java,
                    "schema1.db"
                ).build()
                INSTANCE = instance
                return instance
            }
        }
    }
}

UserEntity.kt

import androidx.room.Entity
import androidx.room.PrimaryKey
@Entity(tableName = "users")
data class UserEntity constructor(
    @PrimaryKey(autoGenerate = true)
    val id: Int,
    val name: String
)

UserDao.kt

import androidx.room.Dao
import androidx.room.Insert
import androidx.room.Query
import androidx.room.Update
import androidx.room.Delete
import androidx.room.OnConflictStrategy

@Dao
interface UserDao {
    @Query("SELECT * from users")
    fun getAll(): List<UserEntity>

    @Query("SELECT * FROM users WHERE id IN (:userIds)")
    fun loadAllByIds(vararg userIds: Int): List<UserEntity>

    @Query("SELECT * FROM users WHERE name LIKE :name")
    fun findByLikeName(name: String): UserEntity

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insert(user: UserEntity)

    @Insert
    fun insertAll(vararg users: UserEntity)

    @Update
    fun update(user: UserEntity)

    @Update
    fun updateUsers(vararg users: UserEntity)

    @Delete
    fun delete(user: UserEntity)

    @Delete
    fun deleteUsers(users: List<UserEntity>)

    @Query("DELETE FROM users")
    fun deleteAll()
}

@Insert(onConflict = OnConflictStrategy.REPLACE) は同一idの場合に置き換えされる

db呼び出し

MainActivity.kt

class MainActivity : AppCompatActivity() {
    val scope = CoroutineScope(Dispatchers.Default)
    lateinit var db: Schema1Database
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        db = Schema1Database.getDatabase(applicationContext)
        scope.launch {
            dbTask()
        }
    }
    private suspend fun dbTask() {
        try {
            dbExec()
        } catch (e: Exception) {
            Log.e(localClassName, "onCancelled", e)
        }
    }
    private fun dbExec() {
        val dao = db.userDao()
        dao.insert(UserEntity(0, "rei"))
        dao.insert(UserEntity(1, "taro"))
        dao.insert(UserEntity(2, "jiro"))
        var users: List<UserEntity> = dao.getAll()
        for (user: UserEntity in users) {
            Log.i("db", "id=" + user.id.toString() + " name=" + user.name);
        }
        for (user: UserEntity in users) {
            if (user.id == 2) {
                Log.i("db", "upd id=" + user.id.toString() +" name=" + user.name);
                dao.update(UserEntity(user.id, "siro"))
            } else {
                Log.i("db", "del id=" + user.id.toString() +" name=" + user.name);
                dao.delete(UserEntity(user.id, user.name));
            }
        }
        users = dao.getAll()
        for (user: UserEntity in users) {
            Log.i("db", "del id=" + user.id.toString() +" name=" + user.name);
            dao.delete(UserEntity(user.id, user.name));
        }
    }
}

出力

2020-01-14 16:16:51.109 24379-24416/? I/db: id=1 name=taro
2020-01-14 16:16:51.109 24379-24416/? I/db: id=2 name=jiro
2020-01-14 16:16:51.109 24379-24416/? I/db: id=21 name=rei
2020-01-14 16:16:51.109 24379-24416/? I/db: del id=1 name=taro
2020-01-14 16:16:51.110 24379-24416/? I/db: upd id=2 name=jiro
2020-01-14 16:16:51.111 24379-24416/? I/db: del id=21 name=rei
2020-01-14 16:16:51.114 24379-24416/? I/db: del id=2 name=siro

idが0のinsertはauto_incrementのmax値が入る?

更新処理

上記例では新規UserEntityを作って入れたが、

dao.update(UserEntity(user.id, "siro"))

変更するプロパティをvalからvarに変えて、更新していれることもできる

UserEntity.kt

data class UserEntity constructor(
    @PrimaryKey(autoGenerate = true)
    val id: Int,
    var name: String
)
user.name = "siro"
dao.update(user)

以下のような修飾子を用意

@Insert(onConflict = OnConflictStrategy.REPLACE)
@Insert(onConflict = OnConflictStrategy.ROLLBACK)
@Insert(onConflict = OnConflictStrategy.ABORT)

参考:https://tech.recruit-mp.co.jp/mobile/post-12311/

insertしたときにidを返す場合

戻り値の型にLongを設定すれば良い。

@Dao
interface UserDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insert(user: UserEntity): Long
}

呼び出しサンプル

var id = dao.insert(UserEntity(2, "jiro"))
Log.i("db", "id=" + id.toString()) // 2

updateした行数を返す場合

updateの戻り値の型にIntを設定する

@Dao
interface UserDao {
    @Update
    fun update(user: UserEntity): Int
}

呼び出しサンプル

var cnt = dao.update(user)
Log.i("db", "cnt=" + cnt.toString()) // 1

sql失敗時の処理

try {
    var ret = dao.insert(UserEntity(9223372036854775807, "jiro"))
    Log.i("test", "ret insert id=" + ret.toString())
} catch (e: SQLiteException) {
    Log.i("test", "error message=" + e.message)
}

sqliteのintの上限値(9223372036854775807)で一度データを入れ、次にidを0指定してauto_incrementさせると以下エラーがe.messageで発生した。

cannot rollback - no transaction is active (code 1 SQLITE_ERROR)

insert失敗時の処理

isnertをIGNOREにしてみて、同じidを入れてみた所、失敗として、-1が返ってきた。

UserDao.kt

@Dao
interface UserDao {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(user: UserEntity): Int
}

MainActivity.kt

 var ret = dao.insert(UserEntity(1234, "jiro"))
 Log.i("test", "ret insert id=" + ret.toString()) // 1234
 var ret2 = dao.insert(UserEntity(1234, "jiro"))
 Log.i("test", "ret2 insert id=" + ret2.toString()) // -1

参考:https://tutorialmore.com/questions-756651.htm

likeの%をアノテーション定義に書く場合

@Query("SELECT * FROM users WHERE name LIKE '%' || :date || '%'")
fun findByLikeDate(date: String): UserEntity

参考:https://stackoverflow.com/questions/44184769/android-room-select-query-with-like

db定義変更時

以下エラーが発生する場合

Caused by: java.lang.IllegalStateException: Room cannot verify the data integrity. Looks like you've changed schema but forgot to update the version number. You can simply fix this by increasing the version number.

未リリースのときはアプリのデータを削除するだけで直る

参考:https://translate.google.com/translate?sl=en&tl=ja&u=https://stackoverflow.com/questions/44197309/room-cannot-verify-the-data-integrity

参考