💾 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

*Exemple d'un développeur ayant confondu un ORL et 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)

Voir ici le modèle complet

Question 1 👉 A quoi sert un repository ?

Question 2 👉 Comment empêcher l'application d'instancier plusieurs fois la base de données ?

Question 3 👉 Que fait getDatabase ?

Question 4 👉 Faites valider par l'enseignant la réalisation des codelabs et les réponses aux questions

🔗 Continuer le cours principal ➡️