Update a Record if exists, else insert a new record in Room

Question

I am new to Room, Rxjava and other android architecture components. I am trying to update/insert 2 records (If row already exists,update it. otherwise insert a new row.) I have tried to do it in following way. But, I didn't work.

GameActivity:

public class GameActivity extends AppCompatActivity {

...

    public void onGameWinnerChanged(Player winner) {


    mDisposable.add(gameViewModel.updateDb(winner)
                .subscribeOn(Schedulers.io())
                .observeOn(AndroidSchedulers.mainThread())
            .subscribe(() -> Log.e("Success!!!", "updated records")
                    , throwable -> {
                throwable.printStackTrace();
            }));        
      }
}

GameViewModel:

public class GameViewModel extends ViewModel {

    ...

    public Completable updateDb(Player winner) {

        return Completable.fromAction(() -> {
            updateWinner(winner);
            Player loser = game.player1 == winner ? game.player2 : game.player1;
            updateLoser(loser);
        });
    }

    private void updateLoser(Player loser) {

           User user = userDataSource.getSingleRecordFromName(loser.name);
        if (user != null) {
            user.loss++;
            userDataSource.updateRecord(user);
        } else {
            user = new User(loser.name, "", 0, 0, 1);
            userDataSource.insertOrUpdateUser(user);
        }
    }

    private void updateWinner(Player winner) {

      User user = userDataSource.getSingleRecordFromName(winner.name);
      if (user != null) {
            user.wins++;
            userDataSource.updateRecord(user);
      } else {
            user = new User(winner.name, "", 0, 1, 0);
            userDataSource.insertOrUpdateUser(user);
      }
   }
}

LocalUserDataSource

public class LocalUserDataSource implements UserDataSource {

    private DaoAccess daoAccess;

    public LocalUserDataSource(DaoAccess daoAccess){
        this.daoAccess=daoAccess;
    }

    @Override
    public Flowable<List<User>> getUsers() {
        return daoAccess.fetchAllData();
    }

    @Override
    public void insertOrUpdateUser(User user) {
        Log.e("local user ds","insertOrUpdateUser");
        daoAccess.insertOnlySingleRecord(user);
    }

    @Override
    public User getSingleRecordFromName(String strName) {
        return daoAccess.getSingleRecord(strName);
    }


    @Override
    public void updateRecord(User user) {
        daoAccess.updateRecord(user);
    }

    @Override
    public void deleteRecord(User user) {
        daoAccess.deleteRecord(user);
    }
}

DaoAccess

@Dao
public interface DaoAccess  {

    @Insert
    void insertOnlySingleRecord(User user);

    @Query("SELECT * FROM User")
    Flowable<List<User>> fetchAllData();

    @Query("SELECT * FROM User WHERE name =:strName")
    User getSingleRecord(String strName);

    @Update
    void updateRecord(User user);

    @Delete
    void deleteRecord(User user);
}

The problem is, when I try to run,NullPointer exceptions occurs if I try to insert any user which is not in database in following line of GameViewModel class.

User user = userDataSource.getSingleRecordFromName(loser.name);

Can anyone tell me where I am wrong or what should I do?

Edit

What I want to do is first fetch a user if it is there in database, update increment or decrement the score (if winner then +1 the wins column if loser then +1 the loss column). In case the user does not exists in the table, then create a new entry.

Discoverer

@Insert(onConflict = OnConflictStrategy.REPLACE) void insertOnlySingleRecord(User user); you can update or replace like this. in your ` DaoAccess` – Taoism

@KuLdipPaTel Please see the edited question – Discoverer

@KuLdipPaTel then, what should be the query to fetch a single record in that case? – Discoverer

first you have prevent insert duplicate entry, second if your player name contain space you have to change in your getSingleRecord query. – Taoism

can you explain more? how will I achieve this? @KuLdipPaTel – Discoverer

@Riddhi How did you solved it? – Scotch

Answer 1

your user Dao should be like below

@Dao
public interface DaoAccess  {

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    void insertOnlySingleRecord(User user);

    @Query("SELECT * FROM User")
    Flowable<List<User>> fetchAllData();

    @Query("SELECT * FROM User WHERE lower(name) = lower(:strName) limit 1")
    User getSingleRecord(String strName);

    @Update
    void updateRecord(User user);

    @Delete
    void deleteRecord(User user);
}

your user Entity should be like

 @Entity(tableName = "user_table", indices = @Index(value = {"name"}, unique = true))
public class UserEntity {

    @PrimaryKey(autoGenerate = true)
    private int id;

    @ColumnInfo(name = "name")
    private String name;
 }

Taoism

thank you. I will do it. But, My question is different in this case. I want to first fetch a user if it is there in database, update increment or decrement the score (if winner then +1 the wins column if loser then +1 the loss column). In case the user does not exists in the table, then create a new entry. – Discoverer

here, indices = @Index(value = {"name"} what does it mean? – Discoverer

it will insert unique user name if it conflict it will replace or update with previous entry. – Taoism

Ok. Thanks again. – Discoverer

so you dont need to check that user already exist or not. just insert entry – Taoism

Ok. Got your point. But,In my case I wanted to update another field(wins/loss) too.(kind of increment in scores). then without fetching user from table how will I know current score and increment it. – Discoverer

Check it out here : developer.android.com/training/data-storage/room/… Very clearly! – Larceny

Answer 2

You can check simply if data exist in DB using this query

// It will return **TRUE** if user exist in User Table else return **FALSE** 

@Query("SELECT * FROM User WHERE name == :strName")
    fun isUserExist(strName: String): Boolean

// It will return **user count** if user exist in User Table else return **0**

@Query("SELECT * FROM User WHERE name == :strName")
    fun isUserExist(strName: String): Int

// It will return **User** if user exist in User Table else return **null**

@Query("SELECT * FROM User WHERE name == :strName")
    fun isUserExist(strName: String): User

Klayman

we cant query on main thread but when I use thread or coroutine or async so it used to take bit time so my if else condition failing because I'm checking item is exists or not in db if exists so not store the value if not exists then store the value in DB. how can I get rid from this issue? – Tonguing