NullPointerException on MAX value retrieval from Dao in Room database

Question

From the Room database, I want to get the MAX value for a specific column returned as a Long value, but it throws the NullPointerException error below when I add the very first item into the shopping list. How can I fix this?

Exception

java.lang.NullPointerException: Attempt to invoke virtual method 'long java.lang.Number.longValue()' on a null object reference

ShoppingListEntity

@Entity(tableName = "shopping_lists")
data class ShoppingListEntity(
    @PrimaryKey(autoGenerate = true)
    var id: Long = 0L,
    @ColumnInfo(name = "name")
    val name: String,
    @ColumnInfo(name = "number_of_items")
    val numberOfItems: Long,
    @ColumnInfo(name = "list_total")
    val listTotal: String,
    @ColumnInfo(name = "sort_order")
    val sortOrder: String
)

ShoppingListItemEntity

@Entity(
    tableName = "shopping_list_items",
    foreignKeys = [ForeignKey(
        entity = ShoppingListEntity::class,
        parentColumns = arrayOf("id"),
        childColumns = arrayOf("shopping_list_id"),
        onUpdate = ForeignKey.CASCADE,
        onDelete = ForeignKey.CASCADE
    )]
)
data class ShoppingListItemEntity(
    @PrimaryKey(autoGenerate = true)
    var id: Long = 0L,
    @ColumnInfo(name = "shopping_list_id")
    var shoppingListId: Long = 0L,
    
    ...

    @ColumnInfo(name = "custom_sort_index")
    val customSortIndex: Long
)

Dao

@Query("SELECT MAX(custom_sort_index) FROM shopping_list_items WHERE shopping_list_id = :listId")
suspend fun getMaxCustomSortIndex(listId: Long): Long

Repository

override suspend fun getMaxCustomSortIndex(listId: Long): Long {
    return withContext(Dispatchers.IO) {
        shoppingListItemDao.getMaxCustomSortIndex(listId)
    }
}

Use Case

class GetMaxCustomSortIndexInItemsTableUseCase@Inject constructor(
    private val shoppingListItemsRepository: ShoppingListItemsRepository
) {
    suspend operator fun invoke(listId: Long): Long {
        return shoppingListItemsRepository.getMaxCustomSortIndex(listId)
    }
}

ViewModel

suspend fun addShoppingListItemToDb() {
    if (!_shoppingListScreenState.value.isNameError && !_shoppingListScreenState.value.isCategoryError) {
        viewModelScope.launch {
            val listId = _shoppingListState.value!!.id

            //Throws Null Pointer Exception here
            val maxCustomSortIndex = getMaxCustomSortIndexInItemsTableUseCase(listId)

            val item = _shoppingListItemState.value
            addShoppingListItemUseCase(item.copy(customSortIndex = maxCustomSortIndex + 1))
            _listItemsLoadingState.value = ListItemsState.Success(_shoppingListItemsState.value)

            setStateValue(IS_SCROLL_TO_LAST_ITEM_IN_LAZYCOLUMN_STR, true)
        }
    }
}

MikeT · Accepted Answer · 2022-12-31T00:36:41.610

You could use:-

@Query("SELECT coalesce(MAX(custom_sort_index),0) FROM shopping_list_items WHERE shopping_list_id = :listId")

Then in the case of null it will be replaced with 0, if the max value is not null, then as it appears first in the list of parameters passed to the coalesce function it will return that value.

this assumes that 0 would be an acceptable/usable value in this scenario. (0, as the 2nd parameter, could be any suitable value).

See https://www.sqlite.org/lang_corefunc.html#coalesce

Instead of coalesce you could use the ifnull function, they basically do the same thing in this use (2 parameters).

NullPointerException on MAX value retrieval from Dao in Room database

1 Answers1