How to avoid duplicate rows in SQLite database in Android?

Question

I am stuck at how do I avoid duplicate rows in Sqlite Database.I tried every possible answer on StackOverflow but none of them worked for me.I tried using unique constraint, db.insertWithOnConflict() but both of them was of no use. Can anyone please help me to figure out my mistake? Here is code from my Android studio:

Params.java:

public class Params {
public static final int DB_VERSION=1;
public static final String DB_NAME="PDF_NAME";
public static final String TABLE_NAME="PDF_TABLE";

public static final String KEY_ID="ID";
public static final String KEY_NAME="NAME";
public static final String KEY_PAGE="PAGE";

}

MyDbHandler.java

public class MyDbHandler extends SQLiteOpenHelper {
  public static SQLiteDatabase database;
public MyDbHandler(Context context) {
    super(context, Params.DB_NAME, null, Params.DB_VERSION);
}

@Override
   public void onCreate(SQLiteDatabase db) {
    String create="CREATE TABLE "+Params.TABLE_NAME+"("+Params.KEY_ID+" INTEGER PRIMARY KEY,"+Params.KEY_NAME+" TEXT UNIQUE, "
            +Params.KEY_PAGE+" INTEGER"+")";
    db.execSQL(create);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

}
public void addPDF(File file)
{
    SQLiteDatabase db=this.getWritableDatabase();
    ContentValues values=new ContentValues();
    values.put(Params.KEY_NAME,file.getName());
    values.put(Params.KEY_PAGE, ViewPdf.pagenumber);
    db.insertWithOnConflict(Params.TABLE_NAME,null,values,SQLiteDatabase.CONFLICT_REPLACE);
    Log.d("pa","pagenumber"+file.getName() + ViewPdf.pagenumber);
    Log.d("data","Inserted");
    db.close();

}
public Cursor getALlfile()
{
    database=this.getReadableDatabase();
    String select="SELECT * FROM "+Params.TABLE_NAME;
    Cursor cursor=database.rawQuery(select,null);
   return cursor;
 }


}

DocumentsFragment.java:

 public void onItemClick(int position) {

    Intent intent = new Intent(getContext(), ViewPdf.class);
    intent.putExtra("Position", position);
    startActivity(intent);
    if (!HistoryFragment.pdfHistory.contains(pdf.get(position))) {
        HistoryFragment.pdfHistory.add(0, pdf.get(position));
    } else {
        File newpdf = pdf.get(position);
        int pos = HistoryFragment.pdfHistory.indexOf(newpdf);
        HistoryFragment.pdfHistory.remove(pos);
        HistoryFragment.pdfHistory.add(0, newpdf);
    }
     Cursor cursor=MyDbHandler.database.rawQuery("Select * from" + Params.TABLE_NAME + "where"+Params.KEY_NAME+ "=" +(pdf.get(position).getName()) ,null);
    if(cursor.moveToFirst())
        Toast.makeText(getContext(),"already exist", Toast.LENGTH_SHORT).show();
    else{
       db.addPDF(pdf.get(position));
    }

I am attaching my logcat too if its of any use:

UPDATE

     2021-05-08 17:54:15.181 11873-11873/com.flashxpdfreader.flash2021 E/SQLiteLog: (1) no such column: ghty.pdf
2021-05-08 17:54:15.182 11873-11873/com.flashxpdfreader.flash2021 D/AndroidRuntime: Shutting down VM
2021-05-08 17:54:15.183 11873-11873/com.flashxpdfreader.flash2021 E/AndroidRuntime: FATAL EXCEPTION: main
    Process: com.flashxpdfreader.flash2021, PID: 11873
    android.database.sqlite.SQLiteException: no such column: ghty.pdf (code 1 SQLITE_ERROR): , while compiling: Select * from PDF_TABLE where NAME = ghty.pdf
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:986)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:593)
        at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:590)
        at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:61)
        at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
        at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:46)
        at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1443)
        at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1382)
        at com.flashxpdfreader.flash2021.Fragment.DocumentsFragment.onItemClick(DocumentsFragment.java:156)
        at com.flashxpdfreader.flash2021.RecylerAdapter$ViewHolder.lambda$new$0$RecylerAdapter$ViewHolder(RecylerAdapter.java:137)
        at com.flashxpdfreader.flash2021.-$$Lambda$RecylerAdapter$ViewHolder$8a1BheGsPwqyn4c_IFEij433NiM.onClick(Unknown Source:2)
        at android.view.View.performClick(View.java:7185)
        at android.view.View.performClickInternal(View.java:7162)
        at android.view.View.access$3500(View.java:819)
        at android.view.View$PerformClick.run(View.java:27684)
        at android.os.Handler.handleCallback(Handler.java:883)
        at android.os.Handler.dispatchMessage(Handler.java:100)
        at android.os.Looper.loop(Looper.java:224)
        at android.app.ActivityThread.main(ActivityThread.java:7562)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:539)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:950)

Here is screenshot from my Sqlite database Browser:

You can notice that I actually have that same file name in my Database but it still says no such column.

The exception has nothing to do with duplicate rows. You have a SQL syntax error (missing spaces around keywords) in the `select` query in the `onItemClick` method. — Federico klez Culloca, May 08 '21 at 10:56
Please don't change your post in a way that makes it a completely different question. Also, how is that error message not clear? You're missing quotes around `ghty.pdf` in your query. — Federico klez Culloca, May 08 '21 at 12:41
Sorry Federico actually i am new so i am not familiar with rules of Stackoverflow,Btw your answer did worked.I added quotes and now its working fine. Thanks alot dude. — Knowledge hub, May 08 '21 at 13:02

score -1 · Accepted Answer · edited May 08 '21 at 12:43

-1

android.database.sqlite.SQLiteException: near "fromPDF_TABLEwhereNAME": syntax error (code 1 SQLITE_ERROR): , while compiling: Select * fromPDF_TABLEwhereNAME=fhg.pdf

Currently, your sql statement is like this:

Select * fromPDF_TABLEwhereNAME=fhg.pdf

You need to add space. Your sql statement need to be like this:

Select * from PDF_TABLE where NAME = fhg.pdf

In DocumentsFragment.java, try changing the code from

Cursor cursor=MyDbHandler.database.rawQuery("Select * from" + Params.TABLE_NAME + "where"+Params.KEY_NAME+ "=" +(pdf.get(position).getName()) ,null);

To

Cursor cursor=MyDbHandler.database.rawQuery("Select * from " + Params.TABLE_NAME + " where "+Params.KEY_NAME+ " = " +(pdf.get(position).getName()) ,null);

edited May 08 '21 at 12:43

Federico klez Culloca

26,308
17
56
95

answered May 08 '21 at 11:29

eric

34
3

You need a space after `from` as well – Federico klez Culloca May 08 '21 at 11:33
Anyway, hello and welcome. For the future, we don't usually answer questions whose solution is just fixing a typo. Especially when a comment in that sense has already been made. – Federico klez Culloca May 08 '21 at 11:34
Thank you for indication! I didn't know the rule,and failed to notice others' comments. How embarrassed! – eric May 08 '21 at 12:09
I tried your answer it did work but there is new error in my logcat.I have updated my logcat please check it. – Knowledge hub May 08 '21 at 12:27

Bristol · Answer 2 · 2021-05-08T11:17:56.870

-2

Rather check if your cursor returns something. if the itemcount is != 0 the the pdf exists.

Cursor cursor=MyDbHandler.database.rawQuery("Select * from " + Params.TABLE_NAME + " where "+Params.KEY_NAME+ "=" +(pdf.get(position).getName()) ,null);    
if(cursor.getCount() != 0)
    Toast.makeText(getContext(),"already exist",Toast.LENGTH_SHORT).show();
else{
   db.addPDF(pdf.get(position));
}

edited May 08 '21 at 11:17

answered May 08 '21 at 11:05

Bristol

82
5

The OP's query throws an error and crashes the app before the `if` block is reached. Also `if(cursor.getCount() != 0)` is not better than `if(cursor.moveToFirst())` in this case. – forpas May 08 '21 at 11:10
loking a your log, have you corrected this?? android.database.sqlite.SQLiteException: near "fromPDF_TABLEwhereNAME": syntax error (code 1 SQLITE_ERROR): , while compiling: Select * fromPDF_TABLEwhereNAME=fhg.pdf at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method) ?? it is the reason for the crash. and spaces before and after WHERE clause – Bristol May 08 '21 at 11:13
Please recheck my question.I have attached few more details to it. – Knowledge hub May 08 '21 at 12:38

How to avoid duplicate rows in SQLite database in Android?

2 Answers2