「Android/kotlin/db/room」の版間の差分
(→likeの%をアノテーション定義に書く場合) |
|||
(同じ利用者による、間の34版が非表示) | |||
行1: | 行1: | ||
==Roomとは== | ==Roomとは== | ||
SQLiteのマッパーライブラリ | SQLiteのマッパーライブラリ | ||
+ | |||
+ | 公式:https://developer.android.com/training/data-storage/room | ||
+ | |||
+ | Androidでも公式が、強くおすすめすると書いてる。 | ||
==準備== | ==準備== | ||
行17: | 行21: | ||
} | } | ||
</pre> | </pre> | ||
+ | |||
+ | https://codelabs.developers.google.com/codelabs/android-room-with-a-view-kotlin/#2 | ||
==サンプル== | ==サンプル== | ||
− | + | schema1.usersのテーブルを置く時のサンプル | |
− | + | ||
+ | Schema1Database.kt | ||
+ | <pre> | ||
import android.content.Context | import android.content.Context | ||
import androidx.room.Database | import androidx.room.Database | ||
行28: | 行35: | ||
@Database(entities = arrayOf(UserEntity::class), version = 1) | @Database(entities = arrayOf(UserEntity::class), version = 1) | ||
− | + | abstract class Schema1Database: RoomDatabase() { | |
abstract fun userDao(): UserDao | abstract fun userDao(): UserDao | ||
companion object { | companion object { | ||
@Volatile | @Volatile | ||
− | private var INSTANCE: | + | private var INSTANCE: Schema1Database? = null |
− | fun getDatabase(context: Context): | + | fun getDatabase(context: Context): Schema1Database { |
val tempInstance = INSTANCE | val tempInstance = INSTANCE | ||
if (tempInstance != null) { | if (tempInstance != null) { | ||
行41: | 行48: | ||
val instance = Room.databaseBuilder( | val instance = Room.databaseBuilder( | ||
context.applicationContext, | context.applicationContext, | ||
− | + | Schema1Database::class.java, | |
− | " | + | "schema1.db" |
).build() | ).build() | ||
INSTANCE = instance | INSTANCE = instance | ||
行76: | 行83: | ||
interface UserDao { | interface UserDao { | ||
@Query("SELECT * from users") | @Query("SELECT * from users") | ||
− | fun | + | 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) | @Insert(onConflict = OnConflictStrategy.REPLACE) | ||
fun insert(user: UserEntity) | fun insert(user: UserEntity) | ||
+ | |||
+ | @Insert | ||
+ | fun insertAll(vararg users: UserEntity) | ||
@Update | @Update | ||
fun update(user: UserEntity) | fun update(user: UserEntity) | ||
+ | |||
+ | @Update | ||
+ | fun updateUsers(vararg users: UserEntity) | ||
@Delete | @Delete | ||
行89: | 行108: | ||
@Delete | @Delete | ||
fun deleteUsers(users: List<UserEntity>) | fun deleteUsers(users: List<UserEntity>) | ||
+ | |||
+ | @Query("DELETE FROM users") | ||
+ | fun deleteAll() | ||
} | } | ||
</pre> | </pre> | ||
+ | |||
+ | @Insert(onConflict = OnConflictStrategy.REPLACE) は同一idの場合に置き換えされる | ||
db呼び出し | db呼び出し | ||
行98: | 行122: | ||
class MainActivity : AppCompatActivity() { | class MainActivity : AppCompatActivity() { | ||
val scope = CoroutineScope(Dispatchers.Default) | val scope = CoroutineScope(Dispatchers.Default) | ||
− | + | lateinit var db: Schema1Database | |
− | + | ||
− | + | ||
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) | ||
− | + | db = Schema1Database.getDatabase(applicationContext) | |
scope.launch { | scope.launch { | ||
dbTask() | dbTask() | ||
行117: | 行139: | ||
} | } | ||
private fun dbExec() { | private fun dbExec() { | ||
− | val dao = | + | val dao = db.userDao() |
dao.insert(UserEntity(0, "rei")) | dao.insert(UserEntity(0, "rei")) | ||
dao.insert(UserEntity(1, "taro")) | dao.insert(UserEntity(1, "taro")) | ||
行154: | 行176: | ||
</pre> | </pre> | ||
idが0のinsertはauto_incrementのmax値が入る? | idが0のinsertはauto_incrementのmax値が入る? | ||
+ | |||
+ | ==更新処理== | ||
+ | 上記例では新規UserEntityを作って入れたが、 | ||
+ | dao.update(UserEntity(user.id, "siro")) | ||
+ | 変更するプロパティをvalからvarに変えて、更新していれることもできる | ||
+ | |||
+ | UserEntity.kt | ||
+ | <pre> | ||
+ | data class UserEntity constructor( | ||
+ | @PrimaryKey(autoGenerate = true) | ||
+ | val id: Int, | ||
+ | var name: String | ||
+ | ) | ||
+ | </pre> | ||
+ | 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を設定すれば良い。 | ||
+ | <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://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://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