0

I've created a method where the app checks if a number exists in the DB:

    Cursor player = playerDatabase.rawQuery("select * from playerTable where playerNumber ="+number,null);
  try {
      if (player.getCount() >= 0) {
          player.close();
          return true;
      }
      else{
          return false;
      }
  }finally {
      if(player != null){
          player.close();
      }
  }

But the problem is, this always returns true? The reason I am using it this way, is from the previous answers on Stack Overflow. What is the optimal way of being able to check if a search query returns a row/check if this number exits?

Improve this question
5
  • While I don't know the perfect answer for that, I'd in general add a limiting limit 1 to your query. That way the engine will be able to drop out after the first (potentially only possible) hit rather then iterating over all entries. This might be non-relevant if the column is set to be unique, but you never know. :) Commented May 13, 2018 at 21:15
  • This is a logical error, You said you just want to check if the number exists but you use greater or equal to zero instead just greater than zero change this if (player.getCount() >= 0) to if (player.getCount() >0) Commented May 13, 2018 at 21:20
  • Can you provide datatype definitions of DB and the code of "player.getCount()" method. Commented May 13, 2018 at 21:28
  • @Xenolion I did try this but it does not work if the row exists Commented May 13, 2018 at 21:30
  • you are using "=" thats why it is returning always true, Commented May 13, 2018 at 23:25

1 Answer 1

Reset to default
1

This is the most efficient way to find if a row exists or not.

SELECT EXISTS(SELECT 1 FROM playerTable WHERE playerNumber="Here comes the player number");

It will return 1 if there is a row with playerNumber in the table or will return 0 if there is no row in the table.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thank you, will give this a go
@YassarHaq Give it a try and dont forget to create index on playerTable.playerNumber.
When you say create index, what do you mean? Can you possibly provide some documentation/example on this, please. Also, tried this and it looks to have worked thanks
Here is what index and how to create it . sqlitetutorial.net/sqlite-index
Dont use the random index commands without reading about them.