Java Android SQLite duplicated values

Question

I want to know how to check for duplicated fields in my database by updating it. If the day and hour are the same in another row it must give an error.

DBHelper:

package me.wouter.schoolwork;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class Schedule {

public static final String KEY_ROWID = "_id"; //Row Id 
public static final String KEY_HOUR = "schedule_hour"; 
public static final String KEY_DAY = "schedule_day";
public static final String KEY_LOCATION = "schedule_location";
public static final String KEY_SUBJECT = "schedule_subject";
public static final String KEY_START = "schedule_start";
public static final String KEY_END = "schedule_end"; 

private static final String DATABASE_NAME = "PlanYourDay";
private static final String DATABASE_TABLE = "schedule";
private static final int DATABASE_VERSION = 1;

private dBHelper classHelper;
private final Context classContext;
private SQLiteDatabase classDatabase;
public Cursor c;

private static class dBHelper extends SQLiteOpenHelper {

    public dBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);

    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" +
                KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                KEY_HOUR + " TEXT NOT NULL, " +
                KEY_DAY + " TEXT NOT NULL, " +
                KEY_LOCATION + " TEXT NOT NULL, " +
                KEY_SUBJECT + " TEXT NOT NULL, " +
                KEY_START + " TEXT NOT NULL, " +
                KEY_END + " TEXT NOT NULL);"

        );

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
        onCreate(db);
    }

}

public Schedule(Context c){
    classContext = c;
}

public Schedule open() throws SQLException{
    classHelper = new dBHelper(classContext);
    classDatabase = classHelper.getWritableDatabase();
    return this;
}
public Schedule close(){
    classHelper.close();
    return this;
}

public long createEntry(String subject, String day, String hour,
        String location, String start, String end) {
    ContentValues cv = new ContentValues();
    cv.put(KEY_SUBJECT, subject);
    cv.put(KEY_DAY, day);
    cv.put(KEY_HOUR, hour);
    cv.put(KEY_LOCATION, location);
    cv.put(KEY_START, start);
    cv.put(KEY_END, end);
    return classDatabase.insert(DATABASE_TABLE, null, cv);

}

public String getData() {
    String[] columns = new String[]{KEY_ROWID, KEY_SUBJECT, KEY_HOUR, KEY_DAY, KEY_LOCATION, KEY_START, KEY_END};
    c = classDatabase.query(DATABASE_TABLE, columns, null, null, null, null, null);
    String result = "";

    int iRow = c.getColumnIndex(KEY_ROWID);
    final int iSubject = c.getColumnIndex(KEY_SUBJECT);
    int iHour = c.getColumnIndex(KEY_HOUR);
    int iDay = c.getColumnIndex(KEY_DAY);
    int iLocation = c.getColumnIndex(KEY_LOCATION);
    int iStart = c.getColumnIndex(KEY_START);
    int iEnd = c.getColumnIndex(KEY_END);

    for(c.moveToFirst(); !c.isAfterLast(); c.moveToNext()){
        result = result + c.getString(iRow) + " " + c.getString(iSubject) + " " + c.getString(iHour) + " "
                + c.getString(iDay) + " " + c.getString(iLocation) + " " + c.getString(iStart) + " "
                + c.getString(iEnd) + "\n";
    }

    return result;
}

    }

DB Inserter:

package me.wouter.schoolwork;

import android.app.Activity;
import android.app.Dialog;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;

    public class ScheduleTest extends Activity implements OnClickListener {
// object variables
Button bLoadSql, bSaveSql;
TextView viewSubject, viewDay, viewHour, viewLocation, viewStart, viewEnd;
EditText editSubject, editDay, editHour, editLocation, editStart, editEnd;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.sql);
    allTheVariables();
    bLoadSql.setOnClickListener(this);
    bSaveSql.setOnClickListener(this);

}

private void allTheVariables() {
    // defines variables in xml file
            // buttons
            bLoadSql = (Button) findViewById(R.id.bLoadSql);
            bSaveSql = (Button) findViewById(R.id.bSaveSql);
            // textviews
            viewSubject = (TextView) findViewById(R.id.viewSubject);
            viewDay = (TextView) findViewById(R.id.viewDay);
            viewHour = (TextView) findViewById(R.id.viewHour);
            viewLocation = (TextView) findViewById(R.id.viewLocation);
            viewStart = (TextView) findViewById(R.id.viewStart);
            viewEnd = (TextView) findViewById(R.id.viewEnd);
            // edittext
            editSubject = (EditText) findViewById(R.id.editSubject);
            editDay = (EditText) findViewById(R.id.editDay);
            editHour = (EditText) findViewById(R.id.editHour);
            editLocation = (EditText) findViewById(R.id.editLocation);
            editStart = (EditText) findViewById(R.id.editStart);
            editEnd = (EditText) findViewById(R.id.editEnd);

}

@Override
public void onClick(View arg0) {
    switch (arg0.getId()) {
    case R.id.bLoadSql:
        Intent i = new Intent("me.wouter.schoolwork.SCHEDULEVIEW");
        startActivity(i);
        break;
    case R.id.bSaveSql:

        boolean didItWork = true;
        try{
        String subject = editSubject.getText().toString();
        String day = editDay.getText().toString();
        String hour = editHour.getText().toString();
        String location = editLocation.getText().toString();
        String start = editStart.getText().toString();
        String end = editEnd.getText().toString();

        Schedule entry = new Schedule(this);
        entry.open();
        entry.createEntry(subject, day, hour, location, start, end);
        entry.close();
        }catch (Exception e){
            didItWork = false;
            String error = e.toString();
            Dialog d = new Dialog(this);
            d.setTitle("Failed");
            TextView tv = new TextView(this);
            tv.setText(error);
            d.setContentView(tv);
            d.show();

        }finally{
            if(didItWork){
                Dialog d = new Dialog(this);
                d.setTitle("Inserted");
                TextView tv = new TextView(this);
                tv.setText("Succes");
                d.setContentView(tv);
                d.show();
            }
        }

        break;
    }

}

  }

And Viewer/Loader:

package me.wouter.schoolwork;

import android.app.Activity;
import android.os.Bundle;
import android.widget.TextView;

public class ScheduleView extends Activity {

TextView subject;

@Override
protected void onCreate(Bundle savedInstanceState) {
    // TODO Auto-generated method stub
    super.onCreate(savedInstanceState);
    setContentView(R.layout.sqlview);
    subject = (TextView) findViewById(R.id.subject);
    Schedule info = new Schedule(this);
    info.open();
    String data =  info.getData();
    subject.setText(data);
    info.close();
}


    }

Thanks in advance!

thkala · Accepted Answer · 2013-01-01 20:03:59Z

1

It seems to me that what you need is a multi-column uniqueness constraint on your DB schema, with a suitable conflict clause:

public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" +
            KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            KEY_HOUR + " TEXT NOT NULL, " +
            KEY_DAY + " TEXT NOT NULL, " +
            KEY_LOCATION + " TEXT NOT NULL, " +
            KEY_SUBJECT + " TEXT NOT NULL, " +
            KEY_START + " TEXT NOT NULL, " +
            KEY_END + " TEXT NOT NULL, " +
            "UNIQUE (" + KEY_DAY + ", " + KEY_HOUR + ") ON CONFLICT ROLLBACK);"
    );

This will generate a constraint violation error if the day/hour combination is not unique, without you having to write an explicit check.

answered Jan 1, 2013 at 20:03

thkala

86.9k24 gold badges167 silver badges205 bronze badges

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

3 Comments

wouterdz Over a year ago

But this will only work on the onCreate method. Not on the onUpgrade method?

thkala Over a year ago

@user1923618: the constraint will be part of the DB schema. SQLite will validate all operations against it and will act as instructed by the conflict clause if the constraint is violated - in this case it will throw an error and rollback the current transaction.

thkala Over a year ago

@user1923618: you need to alter the schema of the DB for the constraint to take effect. You can do that either by re-creating the table in question from scratch, or using the ALTER TABLE SQL directive. For your apparent level of DB experience, I'd recommend creating a new DB and starting with a clean slate - ALTER TABLE and its intricacies are not nearly as simple to handle...

Collectives™ on Stack Overflow

Java Android SQLite duplicated values

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related