Room Database - Compare values in the database to userinput for validation

Question

I am creating an app where the user can log in or register for an account. I have created the register screen and it's successfully saving data into the database. However, I am now trying to integrate some validation. For example, the usernames would have to be unique and the email can't already exist.

I tried to write a custom query of-course to print out all the rows in the username column like this:

SELECT userName from cx_table

and I also tried to write a separate custom query of-course to print out all the rows in the email column like this:

SELECT email from cx_table

Then my approach was to take the user input and compare it to the values returned by that column, if it exists, print an error message. But when I run the app, I get the following error message

The columns returned by the query does not have the fields [id,firstName,lastName,password,address,city,postalcode,email,phone] in com.cxpro.data.Customer even though they are annotated as non-null or primitive. Columns returned by the query: [userName]

here is all my code for the Room Database:

Customer.kt

@Entity(tableName = "cx_table")
data class Customer(
    @PrimaryKey(autoGenerate = true)
    val id: Int,
    val firstName: String,
    val lastName: String,
    val userName: String,
    val password: String,
    val address: String,
    val city: String,
    val postalcode: String,
    val email: String,
    val phone: String
)

CustomerDao.kt

@Dao
interface CustomerDao {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun addCustomers(customer: Customer)

    @Query("SELECT * FROM cx_table ORDER BY id ASC")
    fun readAllData(): LiveData<List<Customer>>

    @Query("SELECT userName FROM cx_table")
    fun readUserName(): LiveData<List<Customer>>
}

CustomerDatabase.kt

@Database(entities = [Customer::class],version = 1, exportSchema = false)
abstract class CustomerDatabase: RoomDatabase() {

    abstract fun customerDao(): CustomerDao

    companion object{
        @Volatile
        private var INSTANCE: CustomerDatabase? = null

        fun getDatabase(context: Context): CustomerDatabase{
            val tempInstance = INSTANCE
            if(tempInstance != null){
                return tempInstance
            }
            synchronized(this){
                val instance = Room.databaseBuilder(
                    context.applicationContext,
                    CustomerDatabase::class.java,
                    "customer_database"
                ).build()
                INSTANCE = instance
                return instance
            }
        }
    }
}

CustomerRepository.kt

class CustomerRepository(private val customerDao: CustomerDao) {
    val readAllData: LiveData<List<Customer>> = customerDao.readAllData()
    val readUserName: LiveData<List<Customer>> = customerDao.readUserName()

    suspend fun addCustomer(customer: Customer){
        customerDao.addCustomers(customer)
    }

}

CustomerViewModel.kt

class CustomerViewModel(application: Application): AndroidViewModel(application) {

    val readAllData: LiveData<List<Customer>>
    val readUserName: LiveData<List<Customer>>
    private val repository: CustomerRepository

    init {
        val customerDao = CustomerDatabase.getDatabase(application).customerDao()
        repository = CustomerRepository(customerDao)
        readAllData = repository.readAllData
        readUserName = repository.readUserName
    }

    fun addCustomer(customer: Customer){
        viewModelScope.launch(Dispatchers.IO){
            repository.addCustomer(customer)
        }
    }
    
}

How can I validate that the username and/or email doesn't already exist in the table?

Does [this](https://stackoverflow.com/questions/48962106/add-unique-constraint-in-room-database-to-multiple-column) answer your question? — Arpit Shukla, Nov 16 '21 at 18:31

MikeT · Accepted Answer · 2021-11-17T00:18:06.383

Then my approach was to take the user input and compare it to the values returned by that column, if it exists, print an error message. But when I run the app, I get the following error message

This is because there are insufficient values to build a Customer object. As you just returning a single value per row you can use List<String> instead of List<Customer>. If multiple values then you need an object probably a POJO. and that the object field/variable names match the columns names

However, rather then having to loop through 2 lists you may wish to consider changing the Customer Entity to :-

@Entity(tableName = "cx_table",
    indices = [
        Index(value = ["userName"],unique = true),
        Index(value = ["email"],unique = true)]
)
data class Customer(
    @PrimaryKey(autoGenerate = true)
    val id: Int,
    val firstName: String,
    val lastName: String,
    val userName: String,
    val password: String,
    val address: String,
    val city: String,
    val postalcode: String,
    val email: String,
    val phone: String
)

and also changing the insert Dao to be :-

@Insert(onConflict = OnConflictStrategy.IGNORE)
fun addCustomers(customer: Customer): Long //<<<<< ADDED Long

You can the check the return value if it is greater than 0 then the row was inserted, otherwise the row wasn't inserted and thus was invalid.

That is, as the indexes on username and email are UNIQUE then attempting to insert whilst duplicating either would result in a conflict, which is ignored. However, the row is not inserted and hence -1 being returned.

Yet another option could be to test the values e.g.

@Query("SELECT count(*) FROM customer WHERE userName=:userNameToCheck OR email=emailToCheck")
fun validateNewCustomer(userNameToCheck: String,emailToCheck): Int

If the result is 0 then OK to insert. You could split into two checks if you wanted to check them individually.

You could ascertain whether it is userName or email that is resulting in the invalid (non-zero) result using something like:-

@Query("SELECT ((SELECT count(*) FROM customer WHERE username=:userNameToCheck) + (SELECT count(*) * 1000 FROM customer WHERE email=:emailToCheck));")
fun validateNewCustomer(userNameToCheck: String,emailToCheck): Int

If the returned value is 0 then valid, if less than 1000 (if greater than 1 then duplicates exist) then the userName is invalid, if greater 1000 then the email is invalid, if greater than 1000 but not an exact multiple of 1000 then both are invalid.

- 1000 caters for up to 998 userName duplicates (if there is a UNIQUE index on userName then there should only be 1, similar for email)

God bless your amazing soul and I hope you receive eternal happiness and success! — , Nov 16 '21 at 23:17

Room Database - Compare values in the database to userinput for validation

1 Answers1