How to implement created_at and updated_at column using Room Persistence ORM tools in android

Question

How can I implement created_at and updated_at columns using Room Persistence ORM tools in Android, that can update the timestamp automatically when creating or updating a row in a table?

Answer 1

I've research many sites, but still not found any results can handle middleware or something like callbacks when we Query, Insert, Update, or Delete,... methods from DAOs.

As @selvin said, RoomDatabase.Callback interface only call when database created in first time. So, use that interface is incorrect way. So, may be the way below is a trick for me, hope this will help you:

1. We need to create a BaseModel

This model to sure that all models in database always available columns creation_date and modification_date.

abstract class BaseModel: Serializable {

    @PrimaryKey(autoGenerate = true) 
    @Expose
    var id: Long = 0

    @ColumnInfo(name = "description")
    @SerializedName(value = "description")
    var description: String? = null

    @ColumnInfo(name = "creation_date")
    @SerializedName(value = "creation_date")
    var creationDate: Date = Date(System.currentTimeMillis())

    @ColumnInfo(name = "modification_date")
    @SerializedName(value = "modification_date")
    var modificationDate: Date = Date(System.currentTimeMillis())

}

2. Create an BaseDAO

Inside BaseDAO, I also create a wrapper class named DAOWrapper, this class will store all helpful methods that we will use to process data like "middleware" before interact model's data to DAO.

So, why we don't create methods inside BaseDAO? -> We cannot do that! use that way will conflict android architecture and also we will have error from compiler (all methods declared in DAO object requires annotation Update, Query,...).

interface BaseDAO<T> where T: BaseModel {

    fun getAll(): List<T>

    @Insert(onConflict = OnConflictStrategy.ABORT)
    fun insert(modelData: T)

    @Update(onConflict = OnConflictStrategy.ABORT)
    fun update(modelData: T)

    companion object {

        open class DAOWrapper<P, T>(private val daoInstance: T) where T: BaseDAO<P>, P: BaseModel {

            fun insertWithTimestapData(modelData: P) {
                modelData.modificationDate = Date(System.currentTimeMillis())
                this@DAOWrapper.daoInstance.insert(modelData)
            }

        }

    }

}

3. To use the DAOWrapper

val appDatabase = // Do something to get RoomDatabase instance...
val exampleDao = appDatabase.exampleDAO()
val exampleDaoWrapper = BaseDAO.Companion.DAOWrapper(exampleDao)

val exampleModel = ExampleModel(name = "Example Name")
exampleDaoWrapper.insertWithTimestapData(exampleModel)

References

Below is example instance of RoomDatabase and example model I used in code above:

/** ExampleModel.kt */
@Entity(
    tableName = "examples",
    indices = [Index(value = arrayOf("name"), unique = true)]
)
class ExampleModel(): BaseModel() {

    @ColumnInfo(name = "name")
    @SerializedName(value = "name")
    var name: String = String()

    @Ignore
    constructor(name: String): this() {
        this@ExampleModel.name = name
    }

}


/** ExampleDAO.kt */
@Dao
interface ExampleDAO: BaseDAO<ExampleModel> {

    @Query("SELECT * FROM `examples`")
    override fun getAll(): List<ExampleModel>

}


/** AppDatabase.kt **/
@Database(entities = [ExampleModel::class], version = 1)
abstract class AppDatabase: RoomDatabase() {

    abstract fun exampleDAO(): ExampleDAO

    companion object {
        private var databaseInstance: AppDatabase? = null
        public const val DatabaseName: String = "app_database"

        fun getDatabase(context: Context): AppDatabase {
            this@Companion.destroyAndCreateNewInstanceIfNeeded(context)
            return this@Companion.databaseInstance!!
        }

        fun destroyAndCreateNewInstanceIfNeeded(context: Context) {
            synchronized(AppDatabase::class) {
                this@Companion.databaseInstance?.close()
                this@Companion.databaseInstance = Room.databaseBuilder(
                    context.applicationContext,
                    AppDatabase::class.java,
                    this@Companion.DatabaseName
                ).build()
            }
        }
    }

}

Answer 2

To follow on the above with a Java example.

The Base entity for all the Room entities This maps to a table that contains an id, created_at and updated_at column.

public abstract class BaseEntity implements Serializable {

    @PrimaryKey(autoGenerate = true)
    private long id;

    @ColumnInfo(name = "created_at", defaultValue = "CURRENT_TIMESTAMP")
    @TypeConverters(DateConverter.class)
    private Date createdAt;

    @ColumnInfo(name = "updated_at", defaultValue = "CURRENT_TIMESTAMP")
    @TypeConverters(DateConverter.class)
    private Date updatedAt;

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public Date getCreatedAt() {
        return createdAt;
    }

    public void setCreatedAt(Date createdAt) {
        this.createdAt = createdAt;
    }

    public Date getUpdatedAt() {
        return updatedAt;
    }

    public void setUpdatedAt(Date updatedAt) {
        this.updatedAt = updatedAt;
    }
}

The Room Date TypeConverter This converts Java Dates into numbers which can be saved in the sqlite database.

public class DateConverter {

    @TypeConverter
    public static Date toDate(Long timestamp) {
        if (timestamp == null) {
            return null;
        }
        return new Date(timestamp);
    }

    @TypeConverter
    public static Long toTimestamp(Date date) {
        if (date == null) {
            return null;
        }

        return date.getTime();
    }
}

The abstract BaseDao This Dao implements all the basic Insert, Update and Delete methods.

@Dao
public abstract class AbstractBaseEntityDao<T extends BaseEntity> {

    @Insert
    public abstract long actualInsert(T t);

    public long insert(T t) {
        t.setCreatedAt(new Date());
        t.setUpdatedAt(new Date());
        return actualInsert(t);
    }

    @Insert
    public abstract List<Long> actualInsertAll(List<T> ts);

    public List<Long> insertAll(List<T> ts) {
        if (ts != null) {
            for (T t : ts) {
                t.setCreatedAt(new Date());
                t.setUpdatedAt(new Date());
            }
        }
        return actualInsertAll(ts);
    }

    @Update
    public abstract void actualUpdate(T t);

    public void update(T t) {
        t.setUpdatedAt(new Date());
        actualUpdate(t);
    }

    @Update
    public abstract void actualUpdateAll(List<T> ts);

    public void updateAll(List<T> ts) {
        if (ts != null) {
            for (T t : ts) {
                t.setUpdatedAt(new Date());
            }
        }
        actualUpdateAll(ts);
    }

    @Delete
    public abstract void delete(T t);

    @Delete
    public abstract void deleteAll(List<T> ts);
}

User and UserDao Here is an example of a concrete Entity and Dao for a User (typical use-case).

@Entity(tableName = "users")
public class User extends BaseEntity {

    @ColumnInfo(name = "name")
    private String name;

    public User() {
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

@Dao
public abstract class UserDao extends AbstractBaseEntityDao<User> {

    @Query("select * from users")
    public abstract List<User> getAllUsers();
}

How to insert a User This will use the AbstractBaseEntityDao which sets the created_at and updated_at timestamps.

Note: do not do this on the UI thread!!

YourDatabase database = YourDatabase.getInstance(getApplicationContext());
UserDao userDao = database.userDao();
long userId = userDao.insert(userToAdd);
userToAdd.setId(userId);

How to update the User

YourDatabase database = YourDatabase.getInstance(getApplicationContext());
UserDao userDao = database.userDao();
userDao.update(userToEdit);