Update Some Specific Field of an Entity in Android Room

Update some specific field of an entity in android Room

I want to know how can I update some field(not all) like method 1 where id = 1

Use @Query, as you did in Method 2.

is too long query in my case because I have many field in my entity

Then have smaller entities. Or, do not update fields individually, but instead have more coarse-grained interactions with the database.

IOW, there is nothing in Room itself that will do what you seek.

UPDATE 2020-09-15: Room now has partial entity support, which can help with this scenario. See this answer for more.

How do I update certain columns within an entity in a Room database?

Try:

"UPDATE media SET name = :name, description = :description," +
        " uri = :uri, text = :text, media_type = :mediaType" +
        " WHERE id = :id"

See the SQLite documentation for more about UPDATE syntax.

Update parameter inside child table room database

The first query doesn't work, because you probably don't have the ConnectionWithPassenger entity defined as an entity in the entities = [] in the class annotated with @Database.

• if not the @Entity annotation is useless (even damaging/frustating as such as it allows you to code single_connection as if it were a table).

The second query is trying to reference columns that are not in the connections_new table. That table has just two columns rideId and connectionsWithPassengers.

The data held in the connectionsWithPassengers column may well contain the lastStatus value (that depends upon the TypeConverter) but the lastStatus is not a column (likewise for the connectionIdNearBy column).

Using what you have you would have to either:-

• locate and change the value within the connectionsWithPassengers column (which SQL wise could be a nightmare but is dependant upon the TypeConverter as the TypeConverter determines how the data is saved in the column), or
• extract the connectionsWithPassengers list then extract/locate the respective connectionsWithPassengers object(s) from the list and then update the column with the amended list of connectionsWithPassengers objects.

Example

As much depends upon the TypeConverters in this example they are :-

@TypeConverter
fun fromConnectionWithPassengerListHolder(cwplh: ConnectionWithPassengerListHolder): String {
    return Gson().toJson(cwplh)
}
@TypeConverter
fun toConnectionWithPassengerListHolder(json: String): ConnectionWithPassengerListHolder {
    return Gson().fromJson(json,ConnectionWithPassengerListHolder::class.java)
} 

A new class ConnectionWithPassengerListHolder has been added, as you there are issue with trying to use var connectionsWithPassengers: MutableList<ConnectionWithPassenger>

So ConnectionWithPassengerListHolder is :-

data class ConnectionWithPassengerListHolder (
    val connectionWithPassengerList: MutableList<ConnectionWithPassenger>
    )

Yo utilise the above LocalConnectionsNearbyList has been changed to be:-

@TypeConverters(ConnectionConverter::class)
@Entity(tableName = "connections_new")
data class LocalConnectionsNearbyList(
    @PrimaryKey
    var rideId: String,
    //var connectionsWithPassengers: MutableList<ConnectionWithPassenger>,
    var connectionsWithPassengers: ConnectionWithPassengerListHolder
)

The AllDao abstract class (rather than an interface) :-

@Dao
abstract class AllDao {

    @Insert(onConflict = IGNORE)
    abstract fun insert(localConnectionsNearbyList: LocalConnectionsNearbyList): Long

    @Query("SELECT * FROM connections_new WHERE rideId=:rideId")
    abstract fun getNewConnectionByRideId(rideId: String): LocalConnectionsNearbyList

    @Update
    abstract fun update(localConnectionsNearbyList: LocalConnectionsNearbyList): Int

    @Query("")
    @Transaction
    fun updateLastStatusOfConnectionNearby(rideId: String, connectionNearBy: String, newStatus: String) {
        var update_needed = false
        var lcnl: LocalConnectionsNearbyList = getNewConnectionByRideId(rideId)
        for (cwp: ConnectionWithPassenger in lcnl.connectionsWithPassengers.connectionWithPassengerList) {
            if (cwp.connectionIdNearBy == connectionNearBy) {
                cwp.lastStatus = newStatus
                update_needed = true
            }
        }
        if (update_needed) {
            update(lcnl)
        }
    }
}

and the following in an activity ( .allowMainThreadQueries used for brevity when building the database)

    db = TheDatabase.getInstance(this)
    dao = db.getAllDao()

    var lc1 = LocalConnectionsNearbyList("ride1",
        ConnectionWithPassengerListHolder(
            mutableListOf(
                ConnectionWithPassenger("S1","n1","NEW",0,false,"Fred","i1"),
                ConnectionWithPassenger("S2","n2","NEW",0,false,"Mary","i2"),
                ConnectionWithPassenger("S3","n3","NEW",0,false,"Jane","i3"),
                ConnectionWithPassenger("S4","n4","NEW",0,false,"Tom","i4")
            )
        )
    )
    dao.insert(lc1)
    dao.updateLastStatusOfConnectionNearby(lc1.rideId,"n3","CHANGED")
    dao.updateLastStatusOfConnectionNearby(lc1.rideId,"n1","ALTERED")

}

This will :-

1. get the database instance
2. get the AllDao instance
3. add a single LocalConnectionsNearbyList with 4 ConnectionsWithPassengers (note the ConnectionWithPassengerListHolder is to circumvent the use of a List/Array for a column) to the database.
4. Change the 3rd ConnectionWithPassenger's lastSatatus from NEW to CHANGED in the database.
5. Change the 1st ConnectionWithPassenger's lastSatatus from NEW to ALTERED in the database.

Note the above is designed/intended to run only the once.

Result

After running the example above then using Android Studio's App Inspection :-

As can be seen not all the data in the connectionsWithPassengers is displayed. Copying the data shows the data as :-

{"connectionWithPassengerList":[
    {"connectionIdNearBy":"n1","image":"i1","isSent":false,"lastStatus":"ALTERED","lastTimeStamp":0,"name":"Fred","serverId":"S1"},
    {"connectionIdNearBy":"n2","image":"i2","isSent":false,"lastStatus":"NEW","lastTimeStamp":0,"name":"Mary","serverId":"S2"},
    {"connectionIdNearBy":"n3","image":"i3","isSent":false,"lastStatus":"CHANGED","lastTimeStamp":0,"name":"Jane","serverId":"S3"},
    {"connectionIdNearBy":"n4","image":"i4","isSent":false,"lastStatus":"NEW","lastTimeStamp":0,"name":"Tom","serverId":"S4"}
    ]
}

• note the data has been split using line-feeds and tabs to make it more presentable.

• As can be seen the lastStatus for the 1st connectionWithPassengers has been changed to ALTERED and the 3rd has been changed to CHANGED with the other two unaltered as NEW.

Android Room Database what is the correct syntax to perform bulk update?

@Update
abstract fun update(myTuple : MyTuple)

//This function is under a transaction so it will be commited to the database
//all at the same time.
@Transaction
fun update(list : List<MyTuple>){
   list.forEach{
      update(it)
   }
}

This is just one way to handle it but you could go the upsert approach that will most likely serve you better in the long run. Android Room Persistence Library: Upsert

Android room how to increment count field and updating specific row fields

I believe the following could do what you want:-

@Query("UPDATE ClickEntity SET clickCount = clickCount + 1, clickTime = :clickTime WHERE type=:type")
fun incrementClick(type: String, clickTime: Long)

For example the following :-

    dao.insert(ClickEntity("Apple",0,0))
    dao.incrementClick("Apple",System.currentTimeMillis());

results in :-

i.e. when inserted count and time were 0 after the update count has been incremented and the current time has been set accordingly. So no need to get the data and then apply (as long as you are aware of the type which you should if it equates to the button).

Additional re comment

I would still need to insert once before updating and incrmenting ! Is there a shorter version to insertorupdate at the same time.

Yes, perhaps. However, if you have a button then I'd suggest inserting when you know that you should have the button.

If the App is targeted at android versions that have SQLite version 3.24.0 (Android API 30+ version 11+) then you could use an UPSERT (INSERT with an ON CONFLICT() DO UPDATE clause).

e.g.

@Query("INSERT INTO clickEntity VALUES(:type,0,:clickTime) ON CONFLICT(type) DO UPDATE SET clickCount = clickCount +1, clickTime = :clickTime ")
fun insertOrUpdate(type: String, clickTime: Long)

• Note the above has not been tested BUT shows a syntax error (perhaps because of the targeted android versions).

The alternative would be to utilise INSERT OR REPLACE which would simply be a matter of using:-

@Insert(onConflictStategy = OnConflictStrategy.REPLACE)
fun insert(ClickEntity("the_type",the_new_count,the_current_timestamp)

BUT you would have to retrieve the count.

HOWEVER a more complex solution could be to get the count + 1 for the type with a subquery. This is complicated as when first inserting there is no count. This can be resolved using the SQLite coalesce function. This would require @Query as @Insert expects an Entity object. So a solution that Inserts or replaces (updates but actually deletes and inserts to perform the update) could be :-

@Query("INSERT OR REPLACE INTO clickentity VALUES(:type,coalesce((SELECT clickCount FROM clickEntity WHERE type = :type),-1) + 1,strftime('%s','now') * 1000)")
fun insertOrReplace(type: String)

So:-

• If the type doesn't exist it will insert a new row in doing so it:-
  
  • tries to get the current value of clickCount column for the type, but as no such row exists null is returned, so the coalesce function returns -1 and 1 is added so the count is 0 for a new row.
  • gets the current time (to the second) using SQLite's strftime function and multiplies it by 1000 to bring it inline with an Android TimeStamp (adds milliseconds).
• If the type exists it will replace the row with another row in doing so it:-
  
  • finds the count from the existing row and adds 1

Example (of both INSERT OR REPLACE options) :-

Following on from the above code the following was added in addition to the changes/additions of the Dao's as above (NOT the UPSERT) :-

    // Adds new
    dao.insert(ClickEntity("Grape",0, System.currentTimeMillis()))
    // Updates (cheated with clickCount knowing it would be 1)
    dao.insert(ClickEntity("Grape",1,System.currentTimeMillis()))
    // Adds new
    dao.insertOrReplace("Apricot")
    // Updates
    dao.insertOrReplace("Apricot")

The database then looks like (all of Android Studio Show) :-

• Banana shows how CURRENT_TIMESTAMP is stored which would cause issues.
• Pear is just using strftime('%s','now') i.e. no millisecs
• as you can see both grape and apricot are as expected i.e. the count has been incremented.

Android Room using Update but not set All Column in entity

Try to define your own DAO.

@Dao
public interface IDaoAccessStuff {

    @Query("UPDATE SET COLUMN_TITLE=:text WHERE id=:id")
    void UpdateColumnById (String text, int id);
    ...
    @Insert
    void insertStuff (Stuffstuff);

    @Update
    void updateExercise (Exercises exercise);
}

Then:

@Database (entities = {Stuff.class}, version = 1, exportSchema = false)
public abstract class AStuffDatabase extends RoomDatabase {
    public abstract IDaoStuffExercise daoAccess();
}

Finally:

private AStuffDatabase aStuffDatabase;
...
RoomDatabase.Builder<AExerciseDatabase> builder;
builder = Room.databaseBuilder(getApplicationContext(),
           AStuffDatabase.class,
            YOUR_DATABASE_NAME);
aStuffDatabase = builder.fallbackToDestructiveMigration().build();
... 
aStuffDatabase.daoAccess().updateStuff(yourString, theId);

Also, unless you specify that the column should not accept null values, as shown below:

@ColumnInfo(name = "COLUMN_TITLE")
@NonNull 
private String title;

---

Related Topics