đŸ Gestion des donnĂ©es - Room
Note đ: Cette fiche vous permet de comprendre et d'avoir une rapide rĂ©fĂ©rence sur les principes du stockage sur Android via l'utilisation de Room.
Pourquoi Room ?
Avant Room, il fallait faire ses requĂȘtes de crĂ©ation de base de donnĂ©es SQLite, d'insertion, de requĂȘtage, etc. Ă la main.

Petit aperçu de l'enfer đ
Code pour créer sa base de donnée SQLite
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
public class DatabaseHelper extends SQLiteOpenHelper {
public static final int DATABASE_VERSION = 8; //version en prod : 6 // version pre brand: 7 // add brand to shoppingProduct: 8
private static final String TAG = "DatabaseHelper";
private static final String DATABASE_NAME = "coucou.db";
// private variable which is going to store the singlton object of this class and return it to caller
private static DatabaseHelper dbHelper = null;
// private SQLiteDatabase varaible which is going to be responsible for all our db related operation
private static SQLiteDatabase db = null;
private DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
Log.e(TAG, "DatabaseHelper: " + DATABASE_NAME + " " + DATABASE_VERSION);
// storing the object of this class to dbHelper
dbHelper = this;
}
// will return a singleton object of this class will as well open the connection for convinient
public static DatabaseHelper getInstance() {
if (dbHelper == null) {
dbHelper = new DatabaseHelper(ApplicationManager.getContext());
openConnexion();
}
return dbHelper;
}
// will be called only once when singleton is created
private static void openConnexion() {
if (db == null) {
db = dbHelper.getWritableDatabase();
}
}
public static SQLiteDatabase getSQLiteDatabase() {
getInstance();
openConnexion();
return db;
}
private static boolean isTableExists(SQLiteDatabase database, String tableName) {
Cursor cursor = database.rawQuery("select DISTINCT tbl_name from sqlite_master where tbl_name = '" + tableName + "'", null);
if (cursor != null) {
if (cursor.getCount() > 0) {
cursor.close();
return true;
}
cursor.close();
}
return false;
}
public static boolean existsColumnInTable(SQLiteDatabase database, String inTable, String columnToCheck) {
Cursor mCursor = null;
try {
// Query 1 row
mCursor = database.rawQuery("SELECT * FROM " + inTable + " LIMIT 0", null);
// getColumnIndex() gives us the index (0 to ...) of the column - otherwise we get a -1
return mCursor.getColumnIndex(columnToCheck) != -1;
} catch (Exception Exp) {
// Something went wrong. Missing the database? The table?
Log.d(TAG, "When checking whether a column exists in the table, an error occurred: " + Exp.getMessage());
return false;
} finally {
if (mCursor != null) mCursor.close();
}
}
// onDestroy method of application
public synchronized void closeConnexion() {
if (dbHelper != null) {
dbHelper.close();
db.close();
dbHelper = null;
db = null;
}
}
@Override
public void onCreate(SQLiteDatabase database) {
Log.e(TAG, "onCreate: " + "onCreate");
if (!isTableExists(database, CartTable.USER_SHOPPING_CART_TABLE)){
CartTable.onCreate(database);
} else {
if (!existsColumnInTable(database, CartTable.USER_SHOPPING_CART_TABLE, CartTable.TOTAL_PRICE)) {
CartTable.onUpgrade(database, 5, 6);
}
}
if (!isTableExists(database, CartItemTable.SHOPPING_ITEM_CART_TABLE)) {
CartItemTable.onCreate(database);
} else {
if (!existsColumnInTable(database, CartItemTable.SHOPPING_ITEM_CART_TABLE, CartItemTable.COUPON)) {
CartItemTable.onUpgrade(database, 1, 2);
}
}
if (!isTableExists(database, UserItemTable.USER_ITEM_TABLE)) {
UserItemTable.onCreate(database);
} else {
if (!existsColumnInTable(database, UserItemTable.USER_ITEM_TABLE, UserItemTable.IS_FROM_SHARING_LIST)) {
UserItemTable.onUpgrade(database, 1, 2);
}
}
if (!isTableExists(database, ShoppingItemTable.SHOPPING_ITEM_TABLE))
ShoppingItemTable.onCreate(database);
if (!isTableExists(database, ShoppingItemAnalyzedTable.SHOPPING_ITEM_ANALYZED_TABLE))
ShoppingItemAnalyzedTable.onCreate(database);
if (!isTableExists(database, ShoppingListTable.USER_SHOPPING_LIST_TABLE)) {
ShoppingListTable.onCreate(database);
} else {
if (!existsColumnInTable(database, ShoppingListTable.USER_SHOPPING_LIST_TABLE, ShoppingListTable.IS_SHARED) ||
!existsColumnInTable(database, ShoppingListTable.USER_SHOPPING_LIST_TABLE, ShoppingListTable.SHARING_URL)) {
ShoppingListTable.onUpgrade(database, 1, 2);
}
if (!existsColumnInTable(database, ShoppingListTable.USER_SHOPPING_LIST_TABLE, ShoppingListTable.TYPE)) {
ShoppingListTable.onUpgrade(database, 5, 6);
}
}
if (!isTableExists(database, ReceiptTable.RECEIPT_TABLE)){
ReceiptTable.onCreate(database);
} else {
if (!existsColumnInTable(database, ReceiptTable.RECEIPT_TABLE, ReceiptTable.SENT_TIME)) {
ReceiptTable.onUpgrade(database, 5, 6);
}
}
if (!isTableExists(database, UserTable.USER_TABLE))
UserTable.onCreate(database);
if (!isTableExists(database, UserInShoppingListTable.USER_IN_SHOPPING_LIST_TABLE))
UserInShoppingListTable.onCreate(database);
if (!isTableExists(database, UserItemTable.USER_ITEM_TABLE_FTS)) {
database.execSQL("ALTER TABLE " + UserItemTable.USER_ITEM_TABLE + " ADD COLUMN " + UserItemTable.IS_FROM_SHARING_LIST + " INTEGER DEFAULT 0");
database.execSQL("CREATE INDEX IF NOT EXISTS IDX_USER_ITEM_NORMALIZED_NAME ON " + UserItemTable.USER_ITEM_TABLE + " (" + UserItemTable.NORMALIZED_NAME + ")");
database.execSQL("CREATE INDEX IF NOT EXISTS IDX_USER_ITEM_NAME ON " + UserItemTable.USER_ITEM_TABLE + " (" + UserItemTable.NAME + ")");
database.execSQL("CREATE VIRTUAL TABLE IF NOT EXISTS " + UserItemTable.USER_ITEM_TABLE_FTS + " USING fts4(" + UserItemTable.ID + ", " +
UserItemTable.NAME + ", " +
UserItemTable.NORMALIZED_NAME + ", " +
UserItemTable.UNIT_TYPE + ", " +
UserItemTable.COLOR + ", " +
UserItemTable.IS_CATEGORY + ", " +
UserItemTable.LOCALID + ", " +
UserItemTable.PARENT_ITEM + ", " +
UserItemTable.IS_PARENT_IS_SYSTEM_ITEM + ", " +
UserItemTable.IS_SYSTEM_ITEM + ", " +
UserItemTable.ID_SYSTEM_ITEM + ", " +
UserItemTable.STARRED + ", " +
UserItemTable.FREQUENCE + ", " +
UserItemTable.PRICE + ", " +
UserItemTable.IS_FROM_SHARING_LIST + ");");
}
}
@Override
public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
Log.e(TAG, "onUpgrade: oldVersion " + oldVersion + " newVersion " + newVersion);
CartTable.onUpgrade(database, oldVersion, newVersion);
CartItemTable.onUpgrade(database, oldVersion, newVersion);
UserItemTable.onUpgrade(database, oldVersion, newVersion);
ShoppingItemTable.onUpgrade(database, oldVersion, newVersion);
ShoppingItemAnalyzedTable.onUpgrade(database, oldVersion, newVersion);
ShoppingListTable.onUpgrade(database, oldVersion, newVersion);
ReceiptTable.onUpgrade(database, oldVersion, newVersion);
UserTable.onUpgrade(database, oldVersion, newVersion);
UserInShoppingListTable.onUpgrade(database, oldVersion, newVersion);
}
@Override
public void onDowngrade(SQLiteDatabase database, int oldVersion, int newVersion) {
super.onDowngrade(database, oldVersion, newVersion);
}
public void onDrop() {
UserItemTable.onDrop(db);
CartTable.onDrop(db);
CartItemTable.onDrop(db);
UserItemTable.onDrop(db);
ShoppingItemTable.onDrop(db);
ShoppingItemAnalyzedTable.onDrop(db);
ShoppingListTable.onDrop(db);
ReceiptTable.onDrop(db);
UserTable.onDrop(db);
UserInShoppingListTable.onDrop(db);
}
// onDestroy method of application
public synchronized void closeConnecion() {
if (dbHelper != null) {
dbHelper.close();
db.close();
dbHelper = null;
db = null;
}
}
}
Petite table User
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import java.util.ArrayList;
import java.util.List;
public class UserTable {
public static final String USER_TABLE = "USER_TABLE";
private static final String TAG = "UserTable";
private static final String ID = "ID";
private static final String NAME = "NAME";
private static final String FIRSTNAME = "FIRSTNAME";
private static final String URL_PICTURE = "URL_PICTURE";
public static final String DATABASE_CREATE_USER_TABLE = "create table if not exists " + USER_TABLE +
" (" + ID + " INTEGER primary key AUTOINCREMENT NOT NULL, " +
UUID + " TEXT not null, " +
NAME + " TEXT, " +
FIRSTNAME + " TEXT, " +
URL_PICTURE + " TEXT);";
public static void onCreate(SQLiteDatabase database) {
database.execSQL(DATABASE_CREATE_USER_TABLE);
Log.w(TAG, "onCreate: " + DATABASE_CREATE_USER_TABLE);
}
public static void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
Log.w(TAG, "Upgrading database " + USER_TABLE);
switch (oldVersion + 1) {
case 1:
case 2:
case 3:
case 4:
case 5:
database.execSQL(DATABASE_CREATE_USER_TABLE);
}
}
public static void onDrop(SQLiteDatabase database) {
database.execSQL("DROP TABLE IF EXISTS " + USER_TABLE);
database.execSQL(DATABASE_CREATE_USER_TABLE);
}
public static void insert(User user) {
SQLiteDatabase database = DatabaseHelper.getSQLiteDatabase();
if (getOne(user.getUuid()) != null) {
//Log.e(TAG, "UPDATE: " + user.toString());
update(user);
} else {
//Log.e(TAG, "insert: " + user.toString());
ContentValues newValue = new ContentValues();
newValue.put(UUID, user.getUuid());
newValue.put(NAME, user.getName());
newValue.put(FIRSTNAME, user.getFirstName());
newValue.put(URL_PICTURE, user.getUrlPicture());
database.insert(USER_TABLE, null, newValue);
}
}
public static int update(User user) {
SQLiteDatabase database = DatabaseHelper.getSQLiteDatabase();
ContentValues newValue = new ContentValues();
newValue.put(NAME, user.getName());
newValue.put(FIRSTNAME, user.getFirstName());
newValue.put(URL_PICTURE, user.getUrlPicture());
return database.update(USER_TABLE, newValue, UUID +" = \"" + user.getUuid() + "\"", null);
}
public static int updateName(String name, String uuid) {
SQLiteDatabase database = DatabaseHelper.getSQLiteDatabase();
ContentValues newValue = new ContentValues();
newValue.put(NAME, name);
return database.update(USER_TABLE, newValue, UUID + " = \"" + uuid + "\"", null);
}
public static boolean remove(String uuid) {
SQLiteDatabase database = DatabaseHelper.getSQLiteDatabase();
return database.delete(USER_TABLE, UUID + " = \"" + uuid + "\"", null) > 0;
}
public static User getOne(String uuid) {
SQLiteDatabase database = DatabaseHelper.getSQLiteDatabase();
Cursor reponse = database.query(USER_TABLE, new String[]{
UUID, NAME, FIRSTNAME, URL_PICTURE
}, UUID + " = \"" + uuid + "\"", null, null, null, null);
User user = null;
if (reponse.moveToFirst()) {
user = new User(reponse.getString(reponse.getColumnIndex(NAME)),
reponse.getString(reponse.getColumnIndex(FIRSTNAME))
);
user.setUuid(reponse.getString(reponse.getColumnIndex(UUID)));
user.setUrlPicture(reponse.getString(reponse.getColumnIndex(URL_PICTURE)));
}
reponse.close();
return user;
}
public static List<User> getAll() {
SQLiteDatabase database = DatabaseHelper.getSQLiteDatabase();
Cursor reponse = database.query(USER_TABLE, new String[]{
UUID, NAME, FIRSTNAME, URL_PICTURE
}, null, null, null, null, null);
List<User> userList = new ArrayList<>();
if (reponse.moveToFirst()) {
do {
User user = new User(reponse.getString(reponse.getColumnIndex(NAME)),
reponse.getString(reponse.getColumnIndex(FIRSTNAME))
);
user.setUuid(reponse.getString(reponse.getColumnIndex(UUID)));
user.setUrlPicture(reponse.getString(reponse.getColumnIndex(URL_PICTURE)));
userList.add(user);
} while (reponse.moveToNext());
}
reponse.close();
return userList;
}
}

Et c'Ă©tait mĂȘme pas de la faute des dĂ©veloppeurs, Google n'avait pas de solution pour ça.
Heureusement aujourd'hui, il y a Room.
C'est quoi Room ?
Room est une couche d'abstraction à SQLite créée en 2017, c'est ce qu'on appelle un ORM

Aujourd'hui c'est la façon fortement recommandée pour stocker des données sur une application Android par Google.
Voir : https://developer.android.com/training/data-storage/room

Comment utiliser Room
Les grands concepts de base :
Room se base sur les annotations pour la création de base de données.
Voici les principales annotations đ

Entities
@Entity
est une annotation utilisĂ©e pour mapper une classe modĂšle en tant que table dans la base de donnĂ©es. Chaque champ de la classe modĂšle est reprĂ©sentĂ© par un nom de colonne. Si vous avez besoin d'un nom de colonne spĂ©cifique, alors cela peut ĂȘtre fait via l'annotation @ColumnInfo
(nom = "nom_colonne"). Pour définir le nom de la table, l'attribut tableName est utilisé. Il doit y avoir au moins un champ de clé primaire dans la classe annoté avec @PrimaryKey
.
@Entity
data class User(
@PrimaryKey val uid: Int,
@ColumnInfo(name = "first_name") val firstName: String?,
@ColumnInfo(name = "last_name") val lastName: String?
)
Dao
@Dao
ou Data Access Object est une interface ou une classe abstraite avec l'annotation @Dao
. Cette classe contient des mĂ©thodes dĂ©finissant la requĂȘte qui doit ĂȘtre effectuĂ©e sur les donnĂ©es en utilisant des d'autres annotations comme @Query
, @Insert
, @Delete
ou @Update
. Habituellement, les requĂȘtes SQL retournent des objets curseur (Cursor
) mais les méthodes du dao font la conversion du curseur en objet entité.
@Dao
interface UserDao {
@Query("SELECT * FROM user")
fun getAll(): List<User>
@Query("SELECT * FROM user WHERE uid IN (:userIds)")
fun loadAllByIds(userIds: IntArray): List<User>
@Query("SELECT * FROM user WHERE first_name LIKE :first AND " +
"last_name LIKE :last LIMIT 1")
fun findByName(first: String, last: String): User
@Insert
fun insertAll(vararg users: User)
@Delete
fun delete(user: User)
}
Database
L'annotation @Database
représente la base de données qui contient toutes les tables. Elle est créée avec une classe abstraite via l'annotation @Database
. L'attribut version
est utilisé pour définir la version de la base de données. entities
est utilisé pour définir la liste des entités que cette base de données contiendra sous forme de table.
@Database(entities = arrayOf(User::class), version = 1)
abstract class AppDatabase : RoomDatabase() {
abstract fun userDao(): UserDao
}
Exercice pratique
En vous inspirant de ce codelab rĂ©alisez une vue capable d'afficher une liste de personnage (les donnĂ©es de la liste pourront ĂȘtre insĂ©rĂ© "Ă la main").
En se basant sur la classe modĂšle ci-dessous :
data class Character(val id: Int, val name: String)