Update with parameter using room persistent library

Question

How will I update an entire row using room library, the @Update take a @Entity annotated object and updates it via referencing primary key but how will I update via some other parameter like update where certain value matches a value in cell in a row.

//Simple update
@Update
int updateObject(ObjectEntity... objectEntities);

//Custom Update
@Query(UPDATE TABLENAME ????)
int updateObject(ObjetEntity objectEntity,String field);

What should I pass in place of ???? such that the new objectEntity is replaced by old one where the field value matches.

Similar question and answer also here https://stackoverflow.com/a/49562132/9381524 — Jurij Pitulja, Mar 29 '18 at 21:23

score 30 · Answer 1 · answered Jul 10 '18 at 04:50

30

to update multiple columns use comma , to separate columns. like following

@Query("UPDATE DailyConsumption SET quantity = :quantity ,date_time= :dateTime,date= :date WHERE id LIKE :id ")
int updateItem(int id,int quantity,long dateTime,String date);

answered Jul 10 '18 at 04:50

Shahkar Raza

355
3
11

5

Thank you! I was using AND instead of comma and this made my query not working, thanks a lot for specifying this detail, you saved my day! – Mattia Ruggiero May 13 '19 at 09:44
I cannot do it since this is not a question asked by me.. I can only upvote this answer. – Mattia Ruggiero May 20 '19 at 10:11
Made my head turn for hours. Thanks a lot! – S. Gissel May 31 '21 at 09:34
1

What should I be passing as `Id` if the `Id` is automatically generated for the table. – oyeraghib Nov 03 '22 at 19:30

score 20 · Answer 2 · answered Oct 07 '17 at 20:17

You have to know which column you are matching against ahead of time as Room doesn't let you dynamically define the column. Let's say you've got an entity Person as follows:

@Entity(tableName = "people")
public final class Person {

    @PrimaryKey
    @ColumnInfo(name = "uuid")
    public final String uuid;

    @ColumnInfo(name = "name")
    public final String name;

    @ColumnInfo(name = "is_alive")
    public final Boolean isAlive;

    public Person(String uuid, String name, Boolean isAlive) {
        this.uuid = uuid;
        this.name = name;
        this.isAlive = isAlive;
    }
}

And you wanted to update the column is_alive depending on the name. You could write the method as:

@Query("UPDATE people SET is_alive= :alive WHERE name = :name")
public abstract int setIsAliveByName(String name, int alive);

This of course could get quite tedious if you have an entity which has many fields because you have to pass in each field as a separate parameter and write the entire UPDATE query by hand.

The alternative is to do a SELECT query to fetch the items first, update the objects with the new data and then save them back into the db.

It's at this point you start to wonder if using an ORM is actually making anything easier and is worth your time...

For an entity with multiple columns, how would I write the SQL statement for update within the @Query annotation? Because using "AND" in-between each param like @Query("UPDATE people SET is_alive = :alive AND sample = :sample WHERE name = :name") doesn't work in my sample — DaveNOTDavid, Mar 14 '18 at 16:37
"The alternative is to do a SELECT query to fetch the items first, update the objects with the new data and then save them back into the db" ---- hey just want to ask if there is still not a definite way to update columns dynamically without fetching the data first. — Kashif K., Jul 13 '18 at 13:37

Upendra Shah · Answer 3 · 2022-02-21T09:37:14.797

If you want to update a single or two field then use

@Query("UPDATE user SET first_name =:fname ,last_name=:lname WHERE email =:email")
int updateUser(String email,String fname, String lname);

And if you want to update too many field then there is no any custom update function in room to update multiple column data till now. But you can use some logic like.

Assume that you need to update last_name

@Entity(tableName = "user")
public class User {
    @NonNull
    @PrimaryKey
    private int id;

    private String user_id;

    private String first_name;

    private String last_name;

    private String email;
}

First you get ObjectModel using select query.

@Query("SELECT * FROM user WHERE email = :email")
User getUser(String email);

then get id from that ObjectModel and set that id to your new ObjectModel

then update simple as room query

@Update
void updateUser(User user);

For example:

//here you get whole object and update whichever field you want. In this firstname and email
User userObject = getUser(String email);

userObject.setFirstName("name")
userObject.setEmail("abc@gkj.com")

//now update query
updateUser(User user);

This is useful when you want to update more number of field

score 3 · Answer 4 · answered Nov 23 '18 at 13:02

To update on the basis of composite primary key in Room

@Query("UPDATE RecentDestinations SET readStatus=:readStatus WHERE name = :name AND street = :street AND state = :state AND postCode = :postcode")
void updateStatus(boolean readStatus,String name,String street,String suburb,String state,String postcode);

Update with parameter using room persistent library

4 Answers4

Linked