Find duplicate entries in SQLite database with Android

Question

I am using a SQLite database in Android and the user has to input name, ip and url which is then saved in the db. I want to be able to recognize if there are double entries in the name column but I do not know how to program it. I think making the column UNIQUE would be the correct way...

My SQLAdapter:

public class ProjectsDBAdapter {

 public static final String KEY_ROWID = "_id";
 public static final String KEY_PROJECTNAME = "projectname";
 public static final String KEY_ROUTERIP = "routerip";
 public static final String KEY_URL = "url";
 public static final String KEY_CALIMERO = "calimero";

 private static final String TAG = "ProjectsDBAdapter";
 private DatabaseHelper mDbHelper;
 private SQLiteDatabase mDb;

 private static final String DATABASE_NAME = "KNXTable";
 private static final String SQLITE_TABLE = "Project";
 private static final int DATABASE_VERSION = 1;

 private final Context mCtx;

 private static final String DATABASE_CREATE =
  "CREATE TABLE if not exists " + SQLITE_TABLE + " (" +
  KEY_ROWID + " integer PRIMARY KEY autoincrement," +
  KEY_ROUTERIP + "," +
  KEY_PROJECTNAME + "," +
  KEY_URL + "," +
  KEY_CALIMERO + "," +
  "UNIQUE("+KEY_PROJECTNAME+")"+");";

 private static class DatabaseHelper extends SQLiteOpenHelper {

  DatabaseHelper(Context context) {
   super(context, DATABASE_NAME, null, DATABASE_VERSION);
  }

  @Override
  public void onCreate(SQLiteDatabase db) {
   //invoked when the database is created, 
   //this is where we can create tables and columns to them, create views or triggers.
   Log.w(TAG, DATABASE_CREATE);
   db.execSQL(DATABASE_CREATE);
  }

  public long createProject(String ip, String name, 
   String url, String calimero) {

  ContentValues initialValues = new ContentValues();
  initialValues.put(KEY_ROUTERIP, ip);
  initialValues.put(KEY_PROJECTNAME, name);
  initialValues.put(KEY_URL, url);
  initialValues.put(KEY_CALIMERO, calimero);

  return mDb.insert(SQLITE_TABLE, null, initialValues);
  }

I hope you can give me some hints, I already tried to use UNIQUE when creating the table but without success...

UPDATE

The method in the SQLiteAdapter:

 public long createProject(String ip, String name, 
   String url, String calimero) {

  ContentValues initialValues = new ContentValues();
  initialValues.put(KEY_ROUTERIP, ip);
  initialValues.put(KEY_PROJECTNAME, name);
  initialValues.put(KEY_URL, url);
  initialValues.put(KEY_CALIMERO, calimero);

  return mDb.insertOrThrow(SQLITE_TABLE, null, initialValues);
 }

The call in my activity:

 //DATABASE
 // Add project to Database
 dbHelper = new ProjectsDBAdapter(this);
 dbHelper.open();

 //Add Strings to database
 dbHelper.insertSomeProjects(IP, Name, URL, Calimero);
 //Generate ListView from SQLite Database
 displayListView();

and logcat output:

01-27 00:02:56.555: E/AndroidRuntime(28526): FATAL EXCEPTION: main
01-27 00:02:56.555: E/AndroidRuntime(28526): java.lang.RuntimeException: Unable to start activity ComponentInfo{de.bertrandt.bertrandtknx/de.bertrandt.bertrandtknx.ProjectList}: android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed
01-27 00:02:56.555: E/AndroidRuntime(28526):    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1970)
01-27 00:02:56.555: E/AndroidRuntime(28526):    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:1995)
01-27 00:02:56.555: E/AndroidRuntime(28526):    at android.app.ActivityThread.access$600(ActivityThread.java:128)
01-27 00:02:56.555: E/AndroidRuntime(28526):    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1161)
01-27 00:02:56.555: E/AndroidRuntime(28526):    at android.os.Handler.dispatchMessage(Handler.java:99)
01-27 00:02:56.555: E/AndroidRuntime(28526):    at android.os.Looper.loop(Looper.java:137)
01-27 00:02:56.555: E/AndroidRuntime(28526):    at android.app.ActivityThread.main(ActivityThread.java:4514)
01-27 00:02:56.555: E/AndroidRuntime(28526):    at java.lang.reflect.Method.invokeNative(Native Method)
01-27 00:02:56.555: E/AndroidRuntime(28526):    at java.lang.reflect.Method.invoke(Method.java:511)
01-27 00:02:56.555: E/AndroidRuntime(28526):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:993)
01-27 00:02:56.555: E/AndroidRuntime(28526):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:760)
01-27 00:02:56.555: E/AndroidRuntime(28526):    at dalvik.system.NativeStart.main(Native Method)
01-27 00:02:56.555: E/AndroidRuntime(28526): Caused by: android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed
01-27 00:02:56.555: E/AndroidRuntime(28526):    at android.database.sqlite.SQLiteStatement.native_executeInsert(Native Method)
01-27 00:02:56.555: E/AndroidRuntime(28526):    at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:113)
01-27 00:02:56.555: E/AndroidRuntime(28526):    at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1839)
01-27 00:02:56.555: E/AndroidRuntime(28526):    at android.database.sqlite.SQLiteDatabase.insertOrThrow(SQLiteDatabase.java:1738)
01-27 00:02:56.555: E/AndroidRuntime(28526):    at SQLite.ProjectsDBAdapter.createProject(ProjectsDBAdapter.java:89)
01-27 00:02:56.555: E/AndroidRuntime(28526):    at SQLite.ProjectsDBAdapter.insertSomeProjects(ProjectsDBAdapter.java:140)
01-27 00:02:56.555: E/AndroidRuntime(28526):    at de.bertrandt.bertrandtknx.ProjectList.setup(ProjectList.java:120)
01-27 00:02:56.555: E/AndroidRuntime(28526):    at de.bertrandt.bertrandtknx.ProjectList.onCreate(ProjectList.java:55)
01-27 00:02:56.555: E/AndroidRuntime(28526):    at android.app.Activity.performCreate(Activity.java:4562)
01-27 00:02:56.555: E/AndroidRuntime(28526):    at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1053)
01-27 00:02:56.555: E/AndroidRuntime(28526):    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1934)
01-27 00:02:56.555: E/AndroidRuntime(28526):    ... 11 more

SOLUTION

//Add Strings to database
try {
     dbHelper.insertSomeProjects(IP, Name, URL, Calimero);
} catch (SQLiteException exception) {
     Log.d("SQLite", "Error"+exception.toString());
     Toast.makeText(getApplicationContext(),
        "Name is duplicated", Toast.LENGTH_SHORT).show();
            exception.printStackTrace();
}

score 2 · Answer 1 · edited May 23 '17 at 11:51

2

Put the UNIQUE declaration in the column definition section as shown in this related question:

SQLite table constraint - unique on multiple columns

Your example would only have a single column in the UNIQUE clause and you may want to use a different policy when there is a conflict (maybe reject the insert rather than replace, for example)

CREATE TABLE a (i INT, j INT, UNIQUE(i) ON CONFLICT REPLACE);

edited May 23 '17 at 11:51

Community

1
1

answered Sep 26 '12 at 13:54

PANCAKES

361
1
3
11

I now tried it [this](http://stackoverflow.com/a/7821484/1582279) way whuch seems nearly the same but it still does not work... – Mokkapps Sep 26 '12 at 13:59
So the table is created, but you are able to insert duplicate project names with no error thrown on insert? What behaviour are you looking for? Do you want to prevent the duplicate insert with an exception thrown, or have a policy applied (REPLACE, etc) upon attempting a duplicate insert? – PANCAKES Sep 26 '12 at 14:03
yes my create table looks like this private static final String `DATABASE_CREATE = "CREATE TABLE if not exists " + SQLITE_TABLE + " (" + KEY_ROWID + " integer PRIMARY KEY autoincrement," + KEY_ROUTERIP + "," + KEY_PROJECTNAME + "," + KEY_URL + "," + KEY_CALIMERO + "," + "UNIQUE("+KEY_PROJECTNAME+")"+");";` but there is a error thrown if the routerip is duplicated??? – Mokkapps Sep 26 '12 at 14:06
I want to create a Toast or Dialog with a hint that this name is already saved in the database. Can I do this with a thrown exception? – Mokkapps Sep 26 '12 at 14:15
Just going off the c ode you have posted, I do not see why the ROUTERIP column would be throwing the error. Maybe post the offending insert code and logcat? – PANCAKES Sep 26 '12 at 14:18
If you want to check for the name prior to insert to give a hint to the user, just do a SELECT and COUNT on the name from the table. SELECT COUNT(KEY_PROJECTNAME) FROM TABLE_NAME WHERE KEY_PROJECTNAME = 'the_name' GROUP BY KEY_PROJECTNAME – PANCAKES Sep 26 '12 at 14:20
The COUNT is not really needed. The column should be unique, so if you get a row back in the select on the name column, the name is taken. – PANCAKES Sep 26 '12 at 14:27

score 1 · Accepted Answer · answered Sep 26 '12 at 13:51

1

You can create the column as a primary key. For example:

"CREATE TABLE mytable ("
"field1 text,"
"field2 text,"
"field3 integer,"
"PRIMARY KEY (field1)"
");"

Now when you try to insert as duplicate value in this column (field1), your code with throw an SQLException. You need to catch the SQLException and take appropriate action.

If you don't have too many rows to deal with, you should consider populating an ArrayList containing all the names and use myArrayList.contains(myNameVariable) to check if a name has already been processed earlier. Then, you will be able to avoid actually executing an SQL statement to see if it throws an Exception.

answered Sep 26 '12 at 13:51

zeiger

700
5
16

this works also but I still have the same problem as described in PANCAKES post – Mokkapps Sep 26 '12 at 14:33
You can obviously show a Toast message to the user in your catch block. All you need is the application context – zeiger Sep 26 '12 at 14:35
ok I will try but at the moment there my porgram throws a exception in the worng columnd and I do not know why... i have edited my main post, maybe you can find the problem – Mokkapps Sep 26 '12 at 14:38
1

You need to put mDb.insertOrThrow(SQLITE_TABLE, null, initialValues); in a try catch block. It is evident from your log that the constraint is failing. Catch the exception and check if it is due to the constraint failing, then display your toast! – zeiger Sep 26 '12 at 14:41
ok now it work, I have added the code to my main post. Thank you! – Mokkapps Sep 26 '12 at 15:06

Find duplicate entries in SQLite database with Android

2 Answers2