1

I am developing an application where the user inputs title and the date. I want to prevent the duplicated titles being inputted on the same day in to database. I am checking if the title exists on the selected date. However my query seems not to work and i don't know why, the application just crashes.Is this query correct? Can someone help?

public boolean checkExist(String title, String date) {
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor c = db.rawQuery("SELECT * FROM "+TABLE_NAME+" WHERE "+TITLE+"=?" +"AND" + DATE+"=?", new String[] {title,date});
    boolean exists = c.moveToFirst();
    c.close();
    return exists;
}
Phantômaxx
  • 37,901
  • 21
  • 84
  • 115
doejogn
  • 19
  • 5
  • It doesn't work, How? Does it fail or does the cursor return no data (i.e. checkExist always returns false)? If it fails then include the log. If it returns do data then I'd suggest putting a breakpoint at `boolean exists = c.moveToFirst();` then from the debugger have a look at the cursor. especially the generated query. – MikeT Apr 07 '17 at 20:05
  • the application crashes – doejogn Apr 07 '17 at 20:09
  • 1
    please include the logcat. Ooops sorry missed that you had said that it crashes. Uhhm at a guess it's a message along the lines of INDEX 1 from 0 rows because the cursor has no rows and you are trying to move. You should check the number of rows in the cursor using c.getCount() and return true if greater than 0, else false. – MikeT Apr 07 '17 at 20:10
  • is the query correct ? – doejogn Apr 07 '17 at 20:16
  • `boolean exists = cursor.getCount() > 0` – OneCricketeer Apr 07 '17 at 20:16
  • i did that still crashes – doejogn Apr 07 '17 at 20:20
  • It would help us if you provided the actual reason your app crashes – OneCricketeer Apr 07 '17 at 20:22

3 Answers3

3

One issue that you have is that c.moveToFirst will always fail if a match does not exist as you are trying to move to a row in an empty cursor.

The resolution is to not use c.moveToFirst and instead get the count of the rows and then set the return value accordingly.

e.g. 

public boolean checkExist(String title, String date) {
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor c = db.rawQuery("SELECT * FROM "+TABLE_NAME+" WHERE "+TITLE+"=?" +"AND" + DATE+"=?", new String[] {title,date});
    boolean exists = c.getCount() > 0;
    c.close();
    return exists;
}

The second issue is that the query itself is wrong as you do not have spaces either side of the AND keyword. That is instead of

Cursor c = db.rawQuery("SELECT * FROM "+TABLE_NAME+" WHERE "+TITLE+"=?" +"AND" + DATE+"=?", new String[] {title,date});

You should have

Cursor c = db.rawQuery("SELECT * FROM "+TABLE_NAME+" WHERE "+TITLE+"=?" +" AND " + DATE+"=?", new String[] {title,date});

Personally, I setup constants for SQL keywords that include the space and then use these. So I'd have something along the lines of +TITLE+"=?" + SQLAND + DATE+"=?". Where SQLAND would be defined along the lines of String SQLAND=" AND ";

PS look at Cricket_007's answer, the code is neater/better it's easier to read.

MikeT
  • 51,415
  • 16
  • 49
  • 68
0

Your spacing is off. TITLE+"=?" +"AND" + DATE becomes TITLE=?ANDDATE=?

I would suggest this. See DatabaseUtils.queryNumEntries

public boolean checkExist(String title, String date) {
    SQLiteDatabase db = getReadableDatabase();

    String[] args = new String[] {title,date};
    String filter = String.format("%s=? AND %s=?", TITLE, DATE);

    return DatabaseUtils.queryNumEntries(db, TABLE_NAME, filter, args) > 0;
}
OneCricketeer
  • 179,855
  • 19
  • 132
  • 245
-1

you should be using c.getCount() instead of c.moveToFirst()

if the value is greater than 0, then it exists

buradd
  • 1,271
  • 1
  • 13
  • 19
  • love it when people take your answer, re-post it with some more verbiage, then downvote your answer. – buradd Apr 07 '17 at 23:23