đŸ’Ÿ Gestion des donnĂ©es - Room

đŸ’Ÿ 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 âžĄïž

Commentaires

Connectez vous ou devenez un membre de Async pour rejoindre la conversation.
Entrez un mail ici pour recevoir un lien de connexion, super simple âšĄïž