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();
}

Answer 1

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);

Answer 2

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.