Matan Matan - 21 days ago 4
Java Question

Cant insert values in my sqlite database

i am very new to databases so i was looking at some guides online and started to try making my own database. For some reason my add method isnt working and i cant figure out why

This is my database class

public class DBHandler extends SQLiteOpenHelper{

// Database Version
private static final int DATABASE_VERSION = 2;
// Database Name
private static final String DATABASE_NAME = "DictionaryInfo";
// Contacts table name
private static final String TABLE_WORDS = "Words";
// Shops Table Columns names
private static final String KEY_ID ="id";
private static final String KEY_WORD = "word";
private static final String KEY_MEANING = "meaning";
private static final String CREATE_WORDS_TABLE = "CREATE TABLE " + TABLE_WORDS + "("
+ KEY_ID + " INTEGER PRIMARY KEY," + KEY_WORD + " TEXT," + KEY_MEANING + " TEXT " + ")";
public DBHandler(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {

db.execSQL(CREATE_WORDS_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

db.execSQL("DROP TABLE IF EXISTS " + TABLE_WORDS);

onCreate(db);
}

public void addWord(Word word){

SQLiteDatabase db = this.getReadableDatabase();
ContentValues values = new ContentValues();

values.put(KEY_ID,word.getId());
values.put(KEY_WORD,word.getWord());
values.put(KEY_MEANING,word.getMeaning());

db.insert(TABLE_WORDS,null,values);
db.close();
}

public Word getWord(int id){

SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.query(TABLE_WORDS,null,KEY_ID + " = ?",new String[]{String.valueOf(id)},null,null,null);
if (cursor != null)
cursor.moveToFirst();

Word word = new Word(Integer.parseInt(cursor.getString(0)),cursor.getString(1),cursor.getString(2));
return word;

}


}

This is my "word" class

public class Word {
int _id;
String word;
String meaning;


public Word(int id,String word, String meaning)
{
this._id = id;
this.word = word;
this.meaning = meaning;
}


public int getId() {
return this._id;
}

public void setId(int id) {
this._id = id;
}

public String getMeaning() {
return this.meaning;
}

public void setMeaning(String meaning) {
this.meaning = meaning;
}

public String getWord() {
return this.word;
}

public void setWord(String word) {
this.word = word;
}


}

This is my mainActivity

public class MainActivity extends AppCompatActivity {

TextView tv1;
TextView tv2;
TextView tv3;
TextView tv4;
Button btn1;



@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);

tv1 = (TextView)findViewById(R.id.tv1);
tv2 = (TextView)findViewById(R.id.tv2);
tv3 = (TextView)findViewById(R.id.tv3);
tv4 = (TextView)findViewById(R.id.tv4);
btn1 = (Button)findViewById(R.id.btn1);

DBHandler db = new DBHandler(this);
db.addWord(new Word(1,"abc","def"));
Word word = db.getWord(1);
tv1.setText(word.getWord());
tv2.setText(word.getMeaning());

}


}

I tried pulling out the values out of the database and insert them into a textview to check if its working but by the logcat i can obviously see that its caused somewhere in the insert process.

My logcat


com.example.android.psydictionary E/SQLiteLog: (1555) abort at 10 in [INSERT INTO Words(meaning,word,id) VALUES (?,?,?)]: UNIQUE constraint failed: Words.id
11-19 12:34:44.875 17853-17853/com.example.android.psydictionary E/SQLiteDatabase: Error inserting meaning=def word=abc id=1
android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: Words.id (code 1555)
at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:784)
at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1471)
at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1341)
at com.example.android.psydictionary.DBHandler.addWord(DBHandler.java:51)
at com.example.android.psydictionary.MainActivity.onCreate(MainActivity.java:31)
at android.app.Activity.performCreate(Activity.java:5990)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1106)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2311)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2420)
at android.app.ActivityThread.access$900(ActivityThread.java:154)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1321)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:135)
at android.app.ActivityThread.main(ActivityThread.java:5294)
at java.lang.reflect.Method.invoke(Native Method)
at java.lang.reflect.Method.invoke(Method.java:372)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:904)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:699)

Answer

UNIQUE constraint failed says what the problem is. You have a UNIQUE constraint on a column (meaning,words,id) and try to INSERT the same value twice in that column.

It is most likely the id that fails. Maybe you can just leave it and it gets calculated during insert. Otherwise, check your testdata.