Android Room - Get the id of new inserted row with auto-generate

Question

This is how I am inserting data into database using Room Persistence Library:

Entity:

@Entity
class User {
    @PrimaryKey(autoGenerate = true)
    public int id;
    //...
}

Data access object:

@Dao
public interface UserDao{
    @Insert(onConflict = IGNORE)
    void insertUser(User user);
    //...
}

Is it possible to return the id of User once the insertion is completed in the above method itself without writing a separate select query?

Thao

Have you tried using int or long instead of void as the result of the @Insert operation? – Downfall

Not yet. I will give a shot! – Thao

i've added an answer too because i've found the reference in the documentation and i'm pretty confident it will works ;) – Downfall

won't this be done with an aSyncTask? how are you returning the value from your repository function? – Monolayer

Answer 1

Based on the documentation here (below the code snippet)

A method annotated with the @Insert annotation can return:

• long for single insert operation
• long[] or Long[] or List<Long> for multiple insert operations
• void if you don't care about the inserted id(s)

Downfall

why in the documentation does it say int for id type but returns long? is assuming the id will never be big enough to be a long? so the row id and the auto generate id are literally the same thing? – Sim

In SQLite the biggest primary key id you can have is a 64 bit signed integer, so the max value is 9,223,372,036,854,775,807 (only positive because it is an id). In java an int is 32 bit signed number and is max positive value is 2,147,483,647, so is not able to represent all the ids. You need to use a Java long which its max value is 9,223,372,036,854,775,807 to represent all the ids. The documentation is for example only, but the api was designed with this in mind (that's why it is returning long and not int or double) – Downfall

ok so it really should be a long. but maybe for most cases there won't be 9 billion rows in an sqlite db so they use int as an example for the userId since it takes less memory (or it's a mistake). That's what I take from this. Thanks for the explanation about why it returns long. – Sim

You are right, but the APIs of Room should work even in the worst case scenario and must follow the specifications of SQlite. Using an int over a long for this specific case is practically the same thing, the additional memory consumption is negligible – Downfall

@Downfall Your original link no longer included a confirmation of this behaviour (and sadly, neither does the API reference for room's Insert annotation). After a bit of searching I found this and updated the link in your answer. Hopefully it persists a little better than the last one as this is a pretty significant bit of info. – Negotiable

@delicateLatticeworkFever Thank you for your edit, i've made a small change to the link you provided adding the ID of the section to load, in this way it's more specific :) – Downfall

tkanks , u save my time – Esemplastic

Say, using Room, is it possible to get the last inserted ID of a given table, without actually inserting anything? Something that will return a result in case nothing was ever inserted ? – Unfailing

@androiddeveloper You need to use a custom RawQuery with this command SELECT rowid from your_table_name order by ROWID DESC limit 1 which return the value of the last autoincrement inserted id of the table – Downfall

@Downfall But what if I didn't insert any, or if I removed all? How could your idea work in this case? I want to know if the table ever had a row inserted to it. That's why I ask about the auto-increment ID. Also, your idea doesn't work in case of removing the item, as a new ID will always increase upon insertion, but not decrease upon removal. – Unfailing

What IF my PRIMARY KEY IS STRING? – Brundisium

@MichałZiobro If your primary key is a string you can't use autogenerate property. You need to manage it manually maybe using generated UUIDs – Downfall

Two more notes: 1. The return type must be Long even if Int is used as ID in the given entity. 2. If onConflict = OnConflictStrategy.IGNORE is used and the conflict occurs, -1 is returned instead of the ID. – Solidary

As other answers have pointed out, this does not return the primary key as specified in the question, but the rowId. These will usually be the same value since they are both autoincremented by 1, but that is not guaranteed. Either ditch the primary key completely and just use the rowId, or follow the insert with a query on the rowId to get the primary key. – Ssr

Ignore my last comment - the sqlite docs says "The PRIMARY KEY of a rowid table (if there is one) is usually not the true primary key for the table, in the sense that it is not the unique key used by the underlying B-tree storage engine. The exception to this rule is when the rowid table declares an INTEGER PRIMARY KEY. In the exception, the INTEGER PRIMARY KEY becomes an alias for the rowid. " (sqlite.org/rowidtable.html) So if you have a primary key that is a kotlin/java Long then it should always be the same as the rowId, which is returned by the Dao queries. – Ssr

What will room return when there is a composite primary key? – Cortes

BTW, @Upsert also returns a List<Long>. – Cystine

Answer 2

@Insert function can return void, long, long[] or List<Long>. Please try this.

 @Insert(onConflict = OnConflictStrategy.REPLACE)
  long insert(User user);

 // Insert multiple items
 @Insert(onConflict = OnConflictStrategy.REPLACE)
  long[] insert(User... user);

Mucous

return Single.fromCallable(() -> dbService.YourDao().insert(mObject)); – Godfrey

What's Single here? how to import? – Heisel

Answer 3

According to the documentation functions annoted with @Insert can return the rowId.

If the @Insert method receives only 1 parameter, it can return a long, which is the new rowId for the inserted item. If the parameter is an array or a collection, it should return long[] or List<Long> instead.

The problem I have with this is that it returns the rowId and not the id and I still haven't found out how to get the id using the rowId.

Edit: I now know how to get the id from the rowId. Here is the SQL command:

SELECT id FROM table_name WHERE rowid = :rowId

Causality

Answer 4

Get the row ID by the following sniplet. It uses callable on an ExecutorService with Future.

 private UserDao userDao;
 private ExecutorService executorService;

 public long insertUploadStatus(User user) {
    Callable<Long> insertCallable = () -> userDao.insert(user);
    long rowId = 0;

    Future<Long> future = executorService.submit(insertCallable);
     try {
         rowId = future.get();
    } catch (InterruptedException e1) {
        e1.printStackTrace();
    } catch (ExecutionException e) {
        e.printStackTrace();
    }
    return rowId;
 }

Ref: Java Executor Service Tutorial for more information on Callable.

Hydrocellulose

Will the call future.get() block the UI thread? – Oney

Answer 5

In your Dao, the insert query returns Long i.e. the rowId inserted.

 @Insert(onConflict = OnConflictStrategy.REPLACE)
 fun insert(recipes: CookingRecipes): Long

In your Model(Repository) class : (MVVM)

fun addRecipesData(cookingRecipes: CookingRecipes): Single<Long>? {
        return Single.fromCallable<Long> { recipesDao.insertManual(cookingRecipes) }
}

In your ModelView class: (MVVM) Handle LiveData with DisposableSingleObserver.
Working sourcer reference : https://github.com/SupriyaNaveen/CookingRecipes

Akan

Answer 6

The return value of the insertion for one record will be 1 if your statement successfully.

In case you want to insert list of objects, you can go with:

@Insert(onConflict = OnConflictStrategy.REPLACE)
public long[] addAll(List<Object> list);

And execute it with Rx2:

Observable.fromCallable(new Callable<Object>() {
        @Override
        public Object call() throws Exception {
            return yourDao.addAll(list<Object>);
        }
    }).subscribeOn(Schedulers.io()).observeOn(AndroidSchedulers.mainThread()).subscribe(new Consumer<Object>() {
        @Override
        public void accept(@NonNull Object o) throws Exception {
           // the o will be Long[].size => numbers of inserted records.

        }
    });

Maggot

"The return value of the insertion for one record will be 1 if your statement successfully" -> According to this documentation: developer.android.com/training/data-storage/room/accessing-data "If the @Insert method receives only 1 parameter, it can return a long, which is the new rowId for the inserted item. If the parameter is an array or a collection, it should return long[] or List<Long> instead." – Negotiable

Answer 7

After a lot of struggle, I managed to solve this. Here is my solution using MMVM architecture:

Student.kt

@Entity(tableName = "students")
data class Student(
    @NotNull var name: String,
    @NotNull var password: String,
    var subject: String,
    var email: String

) {

    @PrimaryKey(autoGenerate = true)
    var roll: Int = 0
}

StudentDao.kt

interface StudentDao {
    @Insert
    fun insertStudent(student: Student) : Long
}

StudentRepository.kt

    class StudentRepository private constructor(private val studentDao: StudentDao)
    {

        fun getStudents() = studentDao.getStudents()

        fun insertStudent(student: Student): Single<Long>? {
            return Single.fromCallable(
                Callable<Long> { studentDao.insertStudent(student) }
            )
        }

 companion object {

        // For Singleton instantiation
        @Volatile private var instance: StudentRepository? = null

        fun getInstance(studentDao: StudentDao) =
                instance ?: synchronized(this) {
                    instance ?: StudentRepository(studentDao).also { instance = it }
                }
    }
}

StudentViewModel.kt

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

var status = MutableLiveData<Boolean?>()
private var repository: StudentRepository = StudentRepository.getInstance( AppDatabase.getInstance(application).studentDao())
private val disposable = CompositeDisposable()

fun insertStudent(student: Student) {
        disposable.add(
            repository.insertStudent(student)
                ?.subscribeOn(Schedulers.newThread())
                ?.observeOn(AndroidSchedulers.mainThread())
                ?.subscribeWith(object : DisposableSingleObserver<Long>() {
                    override fun onSuccess(newReturnId: Long?) {
                        Log.d("ViewModel Insert", newReturnId.toString())
                        status.postValue(true)
                    }

                    override fun onError(e: Throwable?) {
                        status.postValue(false)
                    }

                })
        )
    }
}

In the Fragment:

class RegistrationFragment : Fragment() {
    private lateinit var dataBinding : FragmentRegistrationBinding
    private val viewModel: StudentViewModel by viewModels()

 override fun onViewCreated(view: View, savedInstanceState: Bundle?) {
        super.onViewCreated(view, savedInstanceState)
        initialiseStudent()
        viewModel.status.observe(viewLifecycleOwner, Observer { status ->
            status?.let {
                if(it){
                    Toast.makeText(context , "Data Inserted Sucessfully" , Toast.LENGTH_LONG).show()
                    val action = RegistrationFragmentDirections.actionRegistrationFragmentToLoginFragment()
                    Navigation.findNavController(view).navigate(action)
                } else
                    Toast.makeText(context , "Something went wrong" , Toast.LENGTH_LONG).show()
                //Reset status value at first to prevent multitriggering
                //and to be available to trigger action again
                viewModel.status.value = null
                //Display Toast or snackbar
            }
        })

    }

    fun initialiseStudent() {
        var student = Student(name =dataBinding.edName.text.toString(),
            password= dataBinding.edPassword.text.toString(),
            subject = "",
            email = dataBinding.edEmail.text.toString())
        dataBinding.viewmodel = viewModel
        dataBinding.student = student
    }
}

I have used DataBinding.Here is my XML:

<?xml version="1.0" encoding="utf-8"?>
<layout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools">

    <data>

        <variable
            name="student"
            type="com.kgandroid.studentsubject.data.Student" />

        <variable
            name="listener"
            type="com.kgandroid.studentsubject.view.RegistrationClickListener" />

        <variable
            name="viewmodel"
            type="com.kgandroid.studentsubject.viewmodel.StudentViewModel" />

    </data>


    <androidx.core.widget.NestedScrollView
        android:id="@+id/nestedScrollview"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:fillViewport="true"
        tools:context="com.kgandroid.studentsubject.view.RegistrationFragment">

        <androidx.constraintlayout.widget.ConstraintLayout
            android:id="@+id/constarintLayout"
            android:layout_width="match_parent"
            android:layout_height="match_parent"
            android:isScrollContainer="true">

            <TextView
                android:id="@+id/tvRoll"
                android:layout_width="0dp"
                android:layout_height="wrap_content"
                android:layout_marginStart="16dp"
                android:layout_marginTop="16dp"
                android:layout_marginEnd="16dp"
                android:gravity="center_horizontal"
                android:text="Roll : 1"
                android:textColor="@color/colorPrimary"
                android:textSize="18sp"
                app:layout_constraintEnd_toEndOf="parent"
                app:layout_constraintStart_toStartOf="parent"
                app:layout_constraintTop_toTopOf="parent" />

            <EditText
                android:id="@+id/edName"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_marginTop="24dp"
                android:layout_marginEnd="16dp"
                android:ems="10"
                android:inputType="textPersonName"
                android:text="Name"
                app:layout_constraintEnd_toEndOf="parent"
                app:layout_constraintTop_toBottomOf="@+id/tvRoll" />

            <TextView
                android:id="@+id/tvName"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_marginStart="16dp"
                android:layout_marginEnd="16dp"
                android:text="Name:"
                android:textColor="@color/colorPrimary"
                android:textSize="18sp"
                app:layout_constraintBaseline_toBaselineOf="@+id/edName"
                app:layout_constraintEnd_toStartOf="@+id/edName"
                app:layout_constraintStart_toStartOf="parent" />

            <TextView
                android:id="@+id/tvEmail"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="Email"
                android:textColor="@color/colorPrimary"
                android:textSize="18sp"
                app:layout_constraintBaseline_toBaselineOf="@+id/edEmail"
                app:layout_constraintEnd_toStartOf="@+id/edEmail"
                app:layout_constraintStart_toStartOf="parent" />

            <EditText
                android:id="@+id/edEmail"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_marginTop="24dp"
                android:layout_marginEnd="16dp"
                android:ems="10"
                android:inputType="textPersonName"
                android:text="Name"
                app:layout_constraintEnd_toEndOf="parent"
                app:layout_constraintTop_toBottomOf="@+id/edName" />

            <TextView
                android:id="@+id/textView6"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="Password"
                android:textColor="@color/colorPrimary"
                android:textSize="18sp"
                app:layout_constraintBaseline_toBaselineOf="@+id/edPassword"
                app:layout_constraintEnd_toStartOf="@+id/edPassword"
                app:layout_constraintStart_toStartOf="parent" />

            <EditText
                android:id="@+id/edPassword"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_marginTop="24dp"
                android:layout_marginEnd="16dp"
                android:ems="10"
                android:inputType="textPersonName"
                android:text="Name"
                app:layout_constraintEnd_toEndOf="parent"
                app:layout_constraintTop_toBottomOf="@+id/edEmail" />

            <Button
                android:id="@+id/button"
                android:layout_width="0dp"
                android:layout_height="wrap_content"
                android:layout_marginStart="32dp"
                android:layout_marginTop="24dp"
                android:layout_marginEnd="32dp"
                android:background="@color/colorPrimary"
                android:text="REGISTER"
                android:onClick="@{() -> viewmodel.insertStudent(student)}"
                android:textColor="@android:color/background_light"
                app:layout_constraintEnd_toEndOf="parent"
                app:layout_constraintHorizontal_bias="0.0"
                app:layout_constraintStart_toStartOf="parent"
                app:layout_constraintTop_toBottomOf="@+id/edPassword" />
        </androidx.constraintlayout.widget.ConstraintLayout>


    </androidx.core.widget.NestedScrollView>
</layout>

I have struggled a lot to accomplish this with asynctask as room insert and delete operation must be done in a separate thread. Finally able to do this with Single type observable in RxJava.

Here is Gradle dependencies for rxjava:

implementation 'io.reactivex.rxjava2:rxandroid:2.0.1'
implementation 'io.reactivex.rxjava2:rxjava:2.0.3' 

Typecast

Answer 8

If the @Insert method receives a single parameter, it can return a long value, which is the new rowId for the inserted item. enter link description here

@Insert
suspend fun insert(myEntity: MyEntity):Long

Pyrometer