Kotlin Room Database: How to save result from sum query?

Question

I´m new to Kotlin and trying to learn it by programming an app for work time recording.

I´ve created a room database which works fine when inserting data. In the next step I would like to retrieve the sum of a column and store this value in a variable. And this is the point where I get stuck. Here are the affected snippets of my code.

data class sumPojo(var sumOvertime: Double)
    
@Dao
interface OvertimeDao {
     
    @Query(value = "SELECT SUM(overtime) as sumOvertime FROM TableOvertime")
    fun getSumOvertime(): sumPojo

}
    

class OvertimeRepository(private val overtimeDao: OvertimeDao) {
    
    val getSumOvertime: sumPojo = overtimeDao.getSumOvertime()
    
}


class OvertimeViewModel(application: Application): AndroidViewModel(application) {
    
    private val repository : OvertimeRepository
    val getSumOvertime : sumPojo
    
    init {

    val overtimeDao = OvertimeDatabase.getDatabase(application).overtimeDao()
    repository = OvertimeRepository(overtimeDao)           
    getSumOvertime = repository.getSumOvertime
    
    }
}


class inputWorktimeFragment : Fragment() {

    private lateinit var mOvertimeViewModel : OvertimeViewModel

    override fun onCreateView(
        inflater: LayoutInflater, container: ViewGroup?,
        savedInstanceState: Bundle?
    ): View? {
       
        val view = inflater.inflate(R.layout.fragment_input_worktime, container, false)

        mOvertimeViewModel = ViewModelProvider(this)[OvertimeViewModel::class.java]
        
        val sumOvertime: sumPojo = mOvertimeViewModel.getSumOvertime

        return view
    }
}

Following the error message which I receive.

2022-07-06 16:24:03.067 7338-7338/com.example.workingtimerecorder E/AndroidRuntime: FATAL EXCEPTION: main
    Process: com.example.workingtimerecorder, PID: 7338
    java.lang.RuntimeException: Cannot create an instance of class com.example.workingtimerecorder.data.OvertimeViewModel
        at androidx.lifecycle.ViewModelProvider$AndroidViewModelFactory.create(ViewModelProvider.kt:320)
        at androidx.lifecycle.ViewModelProvider$AndroidViewModelFactory.create(ViewModelProvider.kt:278)
        at androidx.lifecycle.SavedStateViewModelFactory.create(SavedStateViewModelFactory.kt:128)
        at androidx.lifecycle.ViewModelProvider.get(ViewModelProvider.kt:187)
        at androidx.lifecycle.ViewModelProvider.get(ViewModelProvider.kt:153)
        at com.example.workingtimerecorder.fragments.inputWorktimeFragment.onCreateView(InputWorktimeFragment.kt:55)
        at androidx.fragment.app.Fragment.performCreateView(Fragment.java:3104)
        at androidx.fragment.app.FragmentStateManager.createView(FragmentStateManager.java:524)
        at androidx.fragment.app.FragmentStateManager.moveToExpectedState(FragmentStateManager.java:261)
        at androidx.fragment.app.FragmentStore.moveToExpectedState(FragmentStore.java:113)
        at androidx.fragment.app.FragmentManager.moveToState(FragmentManager.java:1424)
        at androidx.fragment.app.FragmentManager.dispatchStateChange(FragmentManager.java:2968)
        at androidx.fragment.app.FragmentManager.dispatchViewCreated(FragmentManager.java:2879)
        at androidx.fragment.app.Fragment.performViewCreated(Fragment.java:3129)
        at androidx.fragment.app.FragmentStateManager.createView(FragmentStateManager.java:552)
        at androidx.fragment.app.FragmentStateManager.moveToExpectedState(FragmentStateManager.java:261)
        at androidx.fragment.app.FragmentStore.moveToExpectedState(FragmentStore.java:113)
        at androidx.fragment.app.FragmentManager.moveToState(FragmentManager.java:1424)
        at androidx.fragment.app.FragmentManager.dispatchStateChange(FragmentManager.java:2968)
        at androidx.fragment.app.FragmentManager.dispatchActivityCreated(FragmentManager.java:2886)
        at androidx.fragment.app.FragmentController.dispatchActivityCreated(FragmentController.java:263)
        at androidx.fragment.app.FragmentActivity.onStart(FragmentActivity.java:351)
        at androidx.appcompat.app.AppCompatActivity.onStart(AppCompatActivity.java:246)
        at android.app.Instrumentation.callActivityOnStart(Instrumentation.java:1455)
        at android.app.Activity.performStart(Activity.java:8076)
        at android.app.ActivityThread.handleStartActivity(ActivityThread.java:3660)
        at android.app.servertransaction.TransactionExecutor.performLifecycleSequence(TransactionExecutor.java:221)
        at android.app.servertransaction.TransactionExecutor.cycleToPath(TransactionExecutor.java:201)
        at android.app.servertransaction.TransactionExecutor.executeLifecycleState(TransactionExecutor.java:173)
        at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:97)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2210)
        at android.os.Handler.dispatchMessage(Handler.java:106)
        at android.os.Looper.loopOnce(Looper.java:201)
        at android.os.Looper.loop(Looper.java:288)
        at android.app.ActivityThread.main(ActivityThread.java:7839)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:548)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1003)
     Caused by: java.lang.reflect.InvocationTargetException
        at java.lang.reflect.Constructor.newInstance0(Native Method)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:343)
        at androidx.lifecycle.ViewModelProvider$AndroidViewModelFactory.create(ViewModelProvider.kt:312)
        at androidx.lifecycle.ViewModelProvider$AndroidViewModelFactory.create(ViewModelProvider.kt:278) 
        at androidx.lifecycle.SavedStateViewModelFactory.create(SavedStateViewModelFactory.kt:128) 
        at androidx.lifecycle.ViewModelProvider.get(ViewModelProvider.kt:187) 
        at androidx.lifecycle.ViewModelProvider.get(ViewModelProvider.kt:153) 
        at com.example.workingtimerecorder.fragments.inputWorktimeFragment.onCreateView(InputWorktimeFragment.kt:55) 
        at androidx.fragment.app.Fragment.performCreateView(Fragment.java:3104) 
        at androidx.fragment.app.FragmentStateManager.createView(FragmentStateManager.java:524) 
        at androidx.fragment.app.FragmentStateManager.moveToExpectedState(FragmentStateManager.java:261) 
        at androidx.fragment.app.FragmentStore.moveToExpectedState(FragmentStore.java:113) 
        at androidx.fragment.app.FragmentManager.moveToState(FragmentManager.java:1424) 
        at androidx.fragment.app.FragmentManager.dispatchStateChange(FragmentManager.java:2968) 
        at androidx.fragment.app.FragmentManager.dispatchViewCreated(FragmentManager.java:2879) 
        at androidx.fragment.app.Fragment.performViewCreated(Fragment.java:3129) 
        at androidx.fragment.app.FragmentStateManager.createView(FragmentStateManager.java:552) 
        at androidx.fragment.app.FragmentStateManager.moveToExpectedState(FragmentStateManager.java:261) 
        at androidx.fragment.app.FragmentStore.moveToExpectedState(FragmentStore.java:113) 
        at androidx.fragment.app.FragmentManager.moveToState(FragmentManager.java:1424) 
        at androidx.fragment.app.FragmentManager.dispatchStateChange(FragmentManager.java:2968) 
        at androidx.fragment.app.FragmentManager.dispatchActivityCreated(FragmentManager.java:2886) 
        at androidx.fragment.app.FragmentController.dispatchActivityCreated(FragmentController.java:263) 
        at androidx.fragment.app.FragmentActivity.onStart(FragmentActivity.java:351) 
        at androidx.appcompat.app.AppCompatActivity.onStart(AppCompatActivity.java:246) 
        at android.app.Instrumentation.callActivityOnStart(Instrumentation.java:1455) 
        at android.app.Activity.performStart(Activity.java:8076) 
        at android.app.ActivityThread.handleStartActivity(ActivityThread.java:3660) 
        at android.app.servertransaction.TransactionExecutor.performLifecycleSequence(TransactionExecutor.java:221) 
        at android.app.servertransaction.TransactionExecutor.cycleToPath(TransactionExecutor.java:201) 
        at android.app.servertransaction.TransactionExecutor.executeLifecycleState(TransactionExecutor.java:173) 
        at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:97) 
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2210) 
        at android.os.Handler.dispatchMessage(Handler.java:106) 
        at android.os.Looper.loopOnce(Looper.java:201) 
        at android.os.Looper.loop(Looper.java:288) 
        at android.app.ActivityThread.main(ActivityThread.java:7839) 
        at java.lang.reflect.Method.invoke(Native Method) 
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:548) 
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1003) 
     Caused by: java.lang.IllegalStateException: Cannot access database on the main thread since it may potentially lock the UI for a long period of time.
        at androidx.room.RoomDatabase.assertNotMainThread(RoomDatabase.java:469)
        at androidx.room.RoomDatabase.query(RoomDatabase.java:525)
        at androidx.room.util.DBUtil.query(DBUtil.java:86)
2022-07-06 16:24:03.068 7338-7338/com.example.workingtimerecorder E/AndroidRuntime:     at com.example.workingtimerecorder.data.OvertimeDao_Impl.getSumOvertime(OvertimeDao_Impl.java:124)
        at com.example.workingtimerecorder.data.OvertimeRepository.<init>(OvertimeRepository.kt:16)
        at com.example.workingtimerecorder.data.OvertimeViewModel.<init>(OvertimeViewModel.kt:24)
            ... 40 more

I would appreciate your help or any hints which could lead me into the rigth direction. I`ve read about several similar issues here on Stackoverflow but none of them could help me.

Your problem is that you can't access to the database from the main thread. This question already has an answer [here](https://stackoverflow.com/q/44167111/11055151). Check it out. — Masoud Karimi, Jul 06 '22 at 16:29
Thank´s a lot Masoud! While debugging I see that it get stuck at this line of code: mOvertimeViewModel = ViewModelProvider(this)[OvertimeViewModel::class.java]. It doesn´t get stuck and works fine when I only use the insert query to insert data into the database. It only get stuck when I use the code snippets above for getting the sum from a column. Any idea how this difference can be explained? — diniska, Jul 06 '22 at 17:05

David · Accepted Answer · 2022-07-06T20:53:44.250

1

Caused by: java.lang.IllegalStateException: Cannot access database on the main thread since it may potentially lock the UI for a long period of time.

I'd try returning either flow or live data and using view model scope to start a co routine.

in the dao

//change
fun getSumOvertime(): sumPojo
to

fun getSumOvertime(): LiveData<sumPojo>
//or
suspend fun getSumOvertime(): Flow<sumPojo>

in the repo

//livedata
fun getSumPojo():LiveData<sumPojo>{return yourdb.yourdao.getSumovertime}
//flow
suspend fun getSumPojo():Flow<sumPojo>{return yourdb.yourdao.getSumovertime}

in the view model id gain access to the values like such

// create private and public accessors
private var _sumPojo = MutableLiveData<SumPojo> //use Int as mutable type if you do not have an object to map to
val sumPojo: LiveData<SumPojo> // again if you do not have an object for sumPojo use Int as live data type
get() = _sumPojo
private fun refreshSumPojo()=viewModelScope.launch{return yourRepo.getSumPojo()}
init {_sumPojo.value = refreshSumPojo}

in the view I would use databinding and bind the text view to the public accessor

in your view.xml file

<TextView
            android:text="@{viewModel.sumPojo.toString()}"
            " />

edited Jul 06 '22 at 20:53

answered Jul 06 '22 at 18:09

David

150
10

Thank you very much David! I´m trying to implement your suggestion. I´m getting errors when using the lines "private fun refreshSumPojo()=viewModelScope.launch{return yourRepo.getSumPojo()}" and "init {_sumPojo.value = refreshSumPojo}". While trying to solve the issues I´m getting into an endless chain off errors. Do you may have an other option how to implement this two line of code? – diniska Jul 07 '22 at 10:09
The code I posted is just an example, I may have some typos in there but try one line of code at a time, run it and see what the error says. I think you may need to start with the kotlin fundamentals code labs. – David Jul 07 '22 at 11:48
You can also try manually testing values on the private val by doing init {_sumPojo.value ="TEST"} Then check if your view displays the word test. If that works then any other problems should be listed in the error log. Dont forget to use Log.i(TAG,"YOUR LOG MESSSAGE") often. Watching the log cat trigger your logs may give you better perspective – David Jul 07 '22 at 12:23
Thanks a lot David! You‘re answer and comments were very helpful. I could resolve the error messages. – diniska Jul 10 '22 at 07:18
No problem, glad I could be of some help! – David Jul 10 '22 at 14:37

Kotlin Room Database: How to save result from sum query?

1 Answers1