android database sql insert data and display -
i following tutorials sqlite database on android. when try insert values database got logcat error.
the database:
todo methods /* * creating todo */ public long createtodo(todo todo, long[] tag_ids) { sqlitedatabase db = this.getwritabledatabase(); contentvalues values = new contentvalues(); values.put(key_todo, todo.getnote()); values.put(key_status, todo.getstatus()); values.put(key_created_at, getdatetime()); // insert row long todo_id = db.insert(table_todo, null, values); // insert tag_ids (long tag_id : tag_ids) { createtodotag(todo_id, tag_id); } return todo_id; } /* * single todo */ public todo gettodo(long todo_id) { sqlitedatabase db = this.getreadabledatabase(); string selectquery = "select * " + table_todo + " " + key_id + " = " + todo_id; log.e(log, selectquery); cursor c = db.rawquery(selectquery, null); if (c != null) c.movetofirst(); todo td = new todo(); td.setid(c.getint(c.getcolumnindex(key_id))); td.setnote((c.getstring(c.getcolumnindex(key_todo)))); td.setcreatedat(c.getstring(c.getcolumnindex(key_created_at))); return td; } /** * getting todos * */ public list<todo> getalltodos() { list<todo> todos = new arraylist<todo>(); string selectquery = "select * " + table_todo; log.e(log, selectquery); sqlitedatabase db = this.getreadabledatabase(); cursor c = db.rawquery(selectquery, null); // looping through rows , adding list if (c.movetofirst()) { { todo td = new todo(); td.setid(c.getint((c.getcolumnindex(key_id)))); td.setnote((c.getstring(c.getcolumnindex(key_todo)))); td.setcreatedat(c.getstring(c.getcolumnindex(key_created_at))); // adding todo list todos.add(td); } while (c.movetonext()); } return todos; } /** * getting todos under single tag * */ public list<todo> getalltodosbytag(string tag_name) { list<todo> todos = new arraylist<todo>(); string selectquery = "select * " + table_todo + " td, " + table_tag + " tg, " + table_todo_tag + " tt tg." + key_tag_name + " = '" + tag_name + "'" + " , tg." key_id + " = " + "tt." + key_tag_id + " , td." + key_id + " = " + "tt." + key_todo_id; log.e(log, selectquery); sqlitedatabase db = this.getreadabledatabase(); cursor c = db.rawquery(selectquery, null); // looping through rows , adding list if (c.movetofirst()) { { todo td = new todo(); td.setid(c.getint((c.getcolumnindex(key_id)))); td.setnote((c.getstring(c.getcolumnindex(key_todo)))); td.setcreatedat(c.getstring(c.getcolumnindex(key_created_at))); // adding todo list todos.add(td); } while (c.movetonext()); } return todos; } /* * getting todo count */ public int gettodocount() { string countquery = "select * " + table_todo; sqlitedatabase db = this.getreadabledatabase(); cursor cursor = db.rawquery(countquery, null); int count = cursor.getcount(); cursor.close(); // return count return count; } /* * updating todo */ public int updatetodo(todo todo) { sqlitedatabase db = this.getwritabledatabase(); contentvalues values = new contentvalues(); values.put(key_todo, todo.getnote()); values.put(key_status, todo.getstatus()); // updating row return db.update(table_todo, values, key_id + " = ?", new string[] { string.valueof(todo.getid()) }); } /* * deleting todo */ public void deletetodo(long tado_id) { sqlitedatabase db = this.getwritabledatabase(); db.delete(table_todo, key_id + " = ?", new string[] { string.valueof(tado_id) }); } // closing database public void closedb() { sqlitedatabase db = this.getreadabledatabase(); if (db != null && db.isopen()) db.close(); } the input:
// creating todos todo todo1 = new todo("iphone 5s", 0); todo todo2 = new todo("galaxy note ii", 0); todo todo3 = new todo("whiteboard", 0); todo todo4 = new todo("riddick", 0); todo todo5 = new todo("prisoners", 0); todo todo6 = new todo("the croods", 0); todo todo7 = new todo("insidious: chapter 2", 0); todo todo8 = new todo("don't forget call mom", 0); todo todo9 = new todo("collect money john", 0); todo todo10 = new todo("post new article", 0); todo todo11 = new todo("take database backup", 0); // inserting todos in db // inserting todos under "shopping" tag long todo1_id = db.createtodo(todo1, new long[] { tag1_id }); long todo2_id = db.createtodo(todo2, new long[] { tag1_id }); long todo3_id = db.createtodo(todo3, new long[] { tag1_id }); // inserting todos under "watchlist" tag long todo4_id = db.createtodo(todo4, new long[] { tag3_id }); long todo5_id = db.createtodo(todo5, new long[] { tag3_id }); long todo6_id = db.createtodo(todo6, new long[] { tag3_id }); long todo7_id = db.createtodo(todo7, new long[] { tag3_id }); // inserting todos under "important" tag long todo8_id = db.createtodo(todo8, new long[] { tag2_id }); long todo9_id = db.createtodo(todo9, new long[] { tag2_id }); // inserting todos under "androidhive" tag long todo10_id = db.createtodo(todo10, new long[] { tag4_id }); long todo11_id = db.createtodo(todo11, new long[] { tag4_id }); log.e("todo count", "todo count: " + db.gettodocount()); // getting todos log.d("get todos", "getting todos"); list<todo> alltodos = db.getalltodos(); (todo todo : alltodos) { log.d("todo", todo.getnote()); } // don't forget close database connection db.closedb();
logcat error:
04-01 16:14:49.582: e/database(273): error inserting id=31 er_tenor=12 er_rate=12.1 used_or_new=baru 04-01 16:14:49.582: e/database(273): android.database.sqlite.sqliteconstraintexception: error code 19: constraint failed 04-01 16:14:49.582: e/database(273): @ android.database.sqlite.sqlitestatement.native_execute(native method) 04-01 16:14:49.582: e/database(273): @ android.database.sqlite.sqlitestatement.execute(sqlitestatement.java:55) 04-01 16:14:49.582: e/database(273): @ android.database.sqlite.sqlitedatabase.insertwithonconflict(sqlitedatabase.java:1549) 04-01 16:14:49.582: e/database(273): @ android.database.sqlite.sqlitedatabase.insert(sqlitedatabase.java:1410) 04-01 16:14:49.582: e/database(273): @ info.androidhive.sqlite.helper.databasehelper.createentryeffectiverate(databasehelper.java:405) 04-01 16:14:49.582: e/database(273): @ info.androidhive.sqlite.mainactivity.oncreate(mainactivity.java:96) 04-01 16:14:49.582: e/database(273): @ android.app.instrumentation.callactivityoncreate(instrumentation.java:1047) 04-01 16:14:49.582: e/database(273): @ android.app.activitythread.performlaunchactivity(activitythread.java:2627) 04-01 16:14:49.582: e/database(273): @ android.app.activitythread.handlelaunchactivity(activitythread.java:2679) 04-01 16:14:49.582: e/database(273): @ android.app.activitythread.access$2300(activitythread.java:125) 04-01 16:14:49.582: e/database(273): @ android.app.activitythread$h.handlemessage(activitythread.java:2033) 04-01 16:14:49.582: e/database(273): @ android.os.handler.dispatchmessage(handler.java:99) 04-01 16:14:49.582: e/database(273): @ android.os.looper.loop(looper.java:123) 04-01 16:14:49.582: e/database(273): @ android.app.activitythread.main(activitythread.java:4627) 04-01 16:14:49.582: e/database(273): @ java.lang.reflect.method.invokenative(native method) 04-01 16:14:49.582: e/database(273): @ java.lang.reflect.method.invoke(method.java:521) 04-01 16:14:49.582: e/database(273): @ com.android.internal.os.zygoteinit$methodandargscaller.run(zygoteinit.java:868) 04-01 16:14:49.582: e/database(273): @ com.android.internal.os.zygoteinit.main(zygoteinit.java:626) 04-01 16:14:49.582: e/database(273): @ dalvik.system.nativestart.main(native method) 04-01 16:14:49.592: e/database(273): error inserting id=32 er_tenor=24 er_rate=12.2 used_or_new=baru 04-01 16:14:49.592: e/database(273): android.database.sqlite.sqliteconstraintexception: error code 19: constraint failed 04-01 16:14:49.592: e/database(273): @ android.database.sqlite.sqlitestatement.native_execute(native method) 04-01 16:14:49.592: e/database(273): @ android.database.sqlite.sqlitestatement.execute(sqlitestatement.java:55) 04-01 16:14:49.592: e/database(273): @ android.database.sqlite.sqlitedatabase.insertwithonconflict(sqlitedatabase.java:1549) 04-01 16:14:49.592: e/database(273): @ android.database.sqlite.sqlitedatabase.insert(sqlitedatabase.java:1410) 04-01 16:14:49.592: e/database(273): @ info.androidhive.sqlite.helper.databasehelper.createentryeffectiverate(databasehelper.java:405) 04-01 16:14:49.592: e/database(273): @ info.androidhive.sqlite.mainactivity.oncreate(mainactivity.java:97) 04-01 16:14:49.592: e/database(273): @ android.app.instrumentation.callactivityoncreate(instrumentation.java:1047) 04-01 16:14:49.592: e/database(273): @ android.app.activitythread.performlaunchactivity(activitythread.java:2627) 04-01 16:14:49.592: e/database(273): @ android.app.activitythread.handlelaunchactivity(activitythread.java:2679) 04-01 16:14:49.592: e/database(273): @ android.app.activitythread.access$2300(activitythread.java:125) 04-01 16:14:49.592: e/database(273): @ android.app.activitythread$h.handlemessage(activitythread.java:2033) 04-01 16:14:49.592: e/database(273): @ android.os.handler.dispatchmessage(handler.java:99) 04-01 16:14:49.592: e/database(273): @ android.os.looper.loop(looper.java:123) 04-01 16:14:49.592: e/database(273): @ android.app.activitythread.main(activitythread.java:4627) 04-01 16:14:49.592: e/database(273): @ java.lang.reflect.method.invokenative(native method) 04-01 16:14:49.592: e/database(273): @ java.lang.reflect.method.invoke(method.java:521) 04-01 16:14:49.592: e/database(273): @ com.android.internal.os.zygoteinit$methodandargscaller.run(zygoteinit.java:868) 04-01 16:14:49.592: e/database(273): @ com.android.internal.os.zygoteinit.main(zygoteinit.java:626) 04-01 16:14:49.592: e/database(273): @ dalvik.system.nativestart.main(native method) 04-01 16:14:49.602: e/database(273): error inserting id=33 er_tenor=36 er_rate=12.3 used_or_new=baru 04-01 16:14:49.602: e/database(273): android.database.sqlite.sqliteconstraintexception: error code 19: constraint failed 04-01 16:14:49.602: e/database(273): @ android.database.sqlite.sqlitestatement.native_execute(native method) 04-01 16:14:49.602: e/database(273): @ android.database.sqlite.sqlitestatement.execute(sqlitestatement.java:55) 04-01 16:14:49.602: e/database(273): @ android.database.sqlite.sqlitedatabase.insertwithonconflict(sqlitedatabase.java:1549) 04-01 16:14:49.602: e/database(273): @ android.database.sqlite.sqlitedatabase.insert(sqlitedatabase.java:1410) 04-01 16:14:49.602: e/database(273): @ info.androidhive.sqlite.helper.databasehelper.createentryeffectiverate(databasehelper.java:405) 04-01 16:14:49.602: e/database(273): @ info.androidhive.sqlite.mainactivity.oncreate(mainactivity.java:98) 04-01 16:14:49.602: e/database(273): @ android.app.instrumentation.callactivityoncreate(instrumentation.java:1047) 04-01 16:14:49.602: e/database(273): @ android.app.activitythread.performlaunchactivity(activitythread.java:2627) 04-01 16:14:49.602: e/database(273): @ android.app.activitythread.handlelaunchactivity(activitythread.java:2679) 04-01 16:14:49.602: e/database(273): @ android.app.activitythread.access$2300(activitythread.java:125) 04-01 16:14:49.602: e/database(273): @ android.app.activitythread$h.handlemessage(activitythread.java:2033) 04-01 16:14:49.602: e/database(273): @ android.os.handler.dispatchmessage(handler.java:99) 04-01 16:14:49.602: e/database(273): @ android.os.looper.loop(looper.java:123) 04-01 16:14:49.602: e/database(273): @ android.app.activitythread.main(activitythread.java:4627) 04-01 16:14:49.602: e/database(273): @ java.lang.reflect.method.invokenative(native method) 04-01 16:14:49.602: e/database(273): @ java.lang.reflect.method.invoke(method.java:521) 04-01 16:14:49.602: e/database(273): @ com.android.internal.os.zygoteinit$methodandargscaller.run(zygoteinit.java:868) 04-01 16:14:49.602: e/database(273): @ com.android.internal.os.zygoteinit.main(zygoteinit.java:626) 04-01 16:14:49.602: e/database(273): @ dalvik.system.nativestart.main(native method) 04-01 16:14:49.612: e/database(273): error inserting id=34 er_tenor=48 er_rate=12.4 used_or_new=baru 04-01 16:14:49.612: e/database(273): android.database.sqlite.sqliteconstraintexception: error code 19: constraint failed 04-01 16:14:49.612: e/database(273): @ android.database.sqlite.sqlitestatement.native_execute(native method) 04-01 16:14:49.612: e/database(273): @ android.database.sqlite.sqlitestatement.execute(sqlitestatement.java:55) 04-01 16:14:49.612: e/database(273): @ android.database.sqlite.sqlitedatabase.insertwithonconflict(sqlitedatabase.java:1549) 04-01 16:14:49.612: e/database(273): @ android.database.sqlite.sqlitedatabase.insert(sqlitedatabase.java:1410) 04-01 16:14:49.612: e/database(273): @ info.androidhive.sqlite.helper.databasehelper.createentryeffectiverate(databasehelper.java:405) 04-01 16:14:49.612: e/database(273): @ info.androidhive.sqlite.mainactivity.oncreate(mainactivity.java:99) 04-01 16:14:49.612: e/database(273): @ android.app.instrumentation.callactivityoncreate(instrumentation.java:1047) 04-01 16:14:49.612: e/database(273): @ android.app.activitythread.performlaunchactivity(activitythread.java:2627) 04-01 16:14:49.612: e/database(273): @ android.app.activitythread.handlelaunchactivity(activitythread.java:2679) 04-01 16:14:49.612: e/database(273): @ android.app.activitythread.access$2300(activitythread.java:125) 04-01 16:14:49.612: e/database(273): @ android.app.activitythread$h.handlemessage(activitythread.java:2033) 04-01 16:14:49.612: e/database(273): @ android.os.handler.dispatchmessage(handler.java:99) 04-01 16:14:49.612: e/database(273): @ android.os.looper.loop(looper.java:123) 04-01 16:14:49.612: e/database(273): @ android.app.activitythread.main(activitythread.java:4627) 04-01 16:14:49.612: e/database(273): @ java.lang.reflect.method.invokenative(native method) 04-01 16:14:49.612: e/database(273): @ java.lang.reflect.method.invoke(method.java:521) 04-01 16:14:49.612: e/database(273): @ com.android.internal.os.zygoteinit$methodandargscaller.run(zygoteinit.java:868) 04-01 16:14:49.612: e/database(273): @ com.android.internal.os.zygoteinit.main(zygoteinit.java:626) 04-01 16:14:49.612: e/database(273): @ dalvik.system.nativestart.main(native method) 04-01 16:14:49.622: e/database(273): error inserting id=35 er_tenor=60 er_rate=12.5 used_or_new=baru 04-01 16:14:49.622: e/database(273): android.database.sqlite.sqliteconstraintexception: error code 19: constraint failed 04-01 16:14:49.622: e/database(273): @ android.database.sqlite.sqlitestatement.native_execute(native method) 04-01 16:14:49.622: e/database(273): @ android.database.sqlite.sqlitestatement.execute(sqlitestatement.java:55) 04-01 16:14:49.622: e/database(273): @ android.database.sqlite.sqlitedatabase.insertwithonconflict(sqlitedatabase.java:1549) 04-01 16:14:49.622: e/database(273): @ android.database.sqlite.sqlitedatabase.insert(sqlitedatabase.java:1410) 04-01 16:14:49.622: e/database(273): @ info.androidhive.sqlite.helper.databasehelper.createentryeffectiverate(databasehelper.java:405) 04-01 16:14:49.622: e/database(273): @ info.androidhive.sqlite.mainactivity.oncreate(mainactivity.java:100) 04-01 16:14:49.622: e/database(273): @ android.app.instrumentation.callactivityoncreate(instrumentation.java:1047) 04-01 16:14:49.622: e/database(273): @ android.app.activitythread.performlaunchactivity(activitythread.java:2627) 04-01 16:14:49.622: e/database(273): @ android.app.activitythread.handlelaunchactivity(activitythread.java:2679) 04-01 16:14:49.622: e/database(273): @ android.app.activitythread.access$2300(activitythread.java:125) 04-01 16:14:49.622: e/database(273): @ android.app.activitythread$h.handlemessage(activitythread.java:2033) 04-01 16:14:49.622: e/database(273): @ android.os.handler.dispatchmessage(handler.java:99) 04-01 16:14:49.622: e/database(273): @ android.os.looper.loop(looper.java:123) 04-01 16:14:49.622: e/database(273): @ android.app.activitythread.main(activitythread.java:4627) 04-01 16:14:49.622: e/database(273): @ java.lang.reflect.method.invokenative(native method) 04-01 16:14:49.622: e/database(273): @ java.lang.reflect.method.invoke(method.java:521) 04-01 16:14:49.622: e/database(273): @ com.android.internal.os.zygoteinit$methodandargscaller.run(zygoteinit.java:868) 04-01 16:14:49.622: e/database(273): @ com.android.internal.os.zygoteinit.main(zygoteinit.java:626) 04-01 16:14:49.622: e/database(273): @ dalvik.system.nativestart.main(native method) 04-01 16:14:49.642: e/database(273): error inserting id=36 er_tenor=12 er_rate=12.6 used_or_new=bekas 04-01 16:14:49.642: e/database(273): android.database.sqlite.sqliteconstraintexception: error code 19: constraint failed 04-01 16:14:49.642: e/database(273): @ android.database.sqlite.sqlitestatement.native_execute(native method) 04-01 16:14:49.642: e/database(273): @ android.database.sqlite.sqlitestatement.execute(sqlitestatement.java:55) 04-01 16:14:49.642: e/database(273): @ android.database.sqlite.sqlitedatabase.insertwithonconflict(sqlitedatabase.java:1549) 04-01 16:14:49.642: e/database(273): @ android.database.sqlite.sqlitedatabase.insert(sqlitedatabase.java:1410) 04-01 16:14:49.642: e/database(273): @ info.androidhive.sqlite.helper.databasehelper.createentryeffectiverate(databasehelper.java:405) 04-01 16:14:49.642: e/database(273): @ info.androidhive.sqlite.mainactivity.oncreate(mainactivity.java:102) 04-01 16:14:49.642: e/database(273): @ android.app.instrumentation.callactivityoncreate(instrumentation.java:1047) 04-01 16:14:49.642: e/database(273): @ android.app.activitythread.performlaunchactivity(activitythread.java:2627) 04-01 16:14:49.642: e/database(273): @ android.app.activitythread.handlelaunchactivity(activitythread.java:2679) 04-01 16:14:49.642: e/database(273): @ android.app.activitythread.access$2300(activitythread.java:125) 04-01 16:14:49.642: e/database(273): @ android.app.activitythread$h.handlemessage(activitythread.java:2033) 04-01 16:14:49.642: e/database(273): @ android.os.handler.dispatchmessage(handler.java:99) 04-01 16:14:49.642: e/database(273): @ android.os.looper.loop(looper.java:123) 04-01 16:14:49.642: e/database(273): @ android.app.activitythread.main(activitythread.java:4627) 04-01 16:14:49.642: e/database(273): @ java.lang.reflect.method.invokenative(native method) 04-01 16:14:49.642: e/database(273): @ java.lang.reflect.method.invoke(method.java:521) 04-01 16:14:49.642: e/database(273): @ com.android.internal.os.zygoteinit$methodandargscaller.run(zygoteinit.java:868) 04-01 16:14:49.642: e/database(273): @ com.android.internal.os.zygoteinit.main(zygoteinit.java:626) 04-01 16:14:49.642: e/database(273): @ dalvik.system.nativestart.main(native method) 04-01 16:14:49.652: e/database(273): error inserting id=37 er_tenor=24 er_rate=12.7 used_or_new=bekas 04-01 16:14:49.652: e/database(273): android.database.sqlite.sqliteconstraintexception: error code 19: constraint failed 04-01 16:14:49.652: e/database(273): @ android.database.sqlite.sqlitestatement.native_execute(native method) 04-01 16:14:49.652: e/database(273): @ android.database.sqlite.sqlitestatement.execute(sqlitestatement.java:55) 04-01 16:14:49.652: e/database(273): @ android.database.sqlite.sqlitedatabase.insertwithonconflict(sqlitedatabase.java:1549) 04-01 16:14:49.652: e/database(273): @ android.database.sqlite.sqlitedatabase.insert(sqlitedatabase.java:1410) 04-01 16:14:49.652: e/database(273): @ info.androidhive.sqlite.helper.databasehelper.createentryeffectiverate(databasehelper.java:405) 04-01 16:14:49.652: e/database(273): @ info.androidhive.sqlite.mainactivity.oncreate(mainactivity.java:103) 04-01 16:14:49.652: e/database(273): @ android.app.instrumentation.callactivityoncreate(instrumentation.java:1047) 04-01 16:14:49.652: e/database(273): @ android.app.activitythread.performlaunchactivity(activitythread.java:2627) 04-01 16:14:49.652: e/database(273): @ android.app.activitythread.handlelaunchactivity(activitythread.java:2679) 04-01 16:14:49.652: e/database(273): @ android.app.activitythread.access$2300(activitythread.java:125) 04-01 16:14:49.652: e/database(273): @ android.app.activitythread$h.handlemessage(activitythread.java:2033) 04-01 16:14:49.652: e/database(273): @ android.os.handler.dispatchmessage(handler.java:99) 04-01 16:14:49.652: e/database(273): @ android.os.looper.loop(looper.java:123) 04-01 16:14:49.652: e/database(273): @ android.app.activitythread.main(activitythread.java:4627) 04-01 16:14:49.652: e/database(273): @ java.lang.reflect.method.invokenative(native method) 04-01 16:14:49.652: e/database(273): @ java.lang.reflect.method.invoke(method.java:521) 04-01 16:14:49.652: e/database(273): @ com.android.internal.os.zygoteinit$methodandargscaller.run(zygoteinit.java:868) 04-01 16:14:49.652: e/database(273): @ com.android.internal.os.zygoteinit.main(zygoteinit.java:626) 04-01 16:14:49.652: e/database(273): @ dalvik.system.nativestart.main(native method) i have more tables havent mentioned,
the code:
//er methods public long createentryeffectiverate(entryeffectiverate eer, long[] as_ids) { sqlitedatabase db = this.getwritabledatabase(); contentvalues valueser = new contentvalues(); valueser.put(key_id, eer.geterid()); valueser.put(key_er_usedornew, eer.geterkondisi()); valueser.put(key_er_tenor, eer.getertenor()); valueser.put(key_er_rate, eer.geterrate()); //values.put(key_created_at, getdatetime()); // insert row long er_id = db.insert(table_effective_rate, null, valueser); // insert tag_ids //for (long as_id : as_ids) { // createtodotag(er_id, as_id); //} return er_id; } //get er public entryeffectiverate getentryeffectiverate(long er_id) { sqlitedatabase db = this.getreadabledatabase(); string selectquery = "select * " + table_effective_rate + " " + key_id + " = " + er_id; log.e(log, selectquery); cursor c = db.rawquery(selectquery, null); if (c != null) c.movetofirst(); entryeffectiverate ergt = new entryeffectiverate(); ergt.seterid(c.getint(c.getcolumnindex(key_id))); ergt.seterkondisi(c.getstring(c.getcolumnindex(key_er_usedornew))); ergt.setertenor(c.getint(c.getcolumnindex(key_er_tenor))); ergt.seterrate(c.getdouble(c.getcolumnindex(key_er_rate))); //ergt.setcreatedat(c.getstring(c.getcolumnindex(key_created_at))); return ergt; } //getting er public list<entryeffectiverate> getalleffectiverates() { list<entryeffectiverate> effectiverates = new arraylist<entryeffectiverate>(); string selectquery = "select * " + table_effective_rate; log.e(log, selectquery); sqlitedatabase db = this.getreadabledatabase(); cursor c = db.rawquery(selectquery, null); // looping through rows , adding list if (c.movetofirst()) { { entryeffectiverate ergt = new entryeffectiverate(); ergt.seterid(c.getint(c.getcolumnindex(key_id))); ergt.seterkondisi(c.getstring(c.getcolumnindex(key_er_usedornew))); ergt.setertenor(c.getint(c.getcolumnindex(key_er_tenor))); ergt.seterrate(c.getdouble(c.getcolumnindex(key_er_rate))); // adding todo list effectiverates.add(ergt); } while (c.movetonext()); } c.close(); return effectiverates; } //get er count public int getentryeffectiveratecount() { string countquery = "select * " + table_effective_rate; sqlitedatabase db = this.getreadabledatabase(); cursor cursor = db.rawquery(countquery, null); int count = cursor.getcount(); cursor.close(); // return count return count; } /* * updating todo */ public int updateentryeffectiverate(entryeffectiverate er) { sqlitedatabase db = this.getwritabledatabase(); contentvalues values = new contentvalues(); values.put(key_id, er.geterid()); values.put(key_er_usedornew, er.geterkondisi()); values.put(key_er_tenor, er.getertenor()); values.put(key_er_rate, er.geterrate()); //values.put(key_created_at, getdatetime()); // updating row return db.update(table_effective_rate, values, key_id + " = ?", new string[] { string.valueof(er.geterid()) }); } // public void deleteentryeffectiverate(long er_id) { sqlitedatabase db = this.getwritabledatabase(); db.delete(table_effective_rate, key_id + " = ?", new string[] { string.valueof(er_id) }); } ////////////////////////////////////////////////////////////////////////////////// inserted values: entryeffectiverate er_id = new entryeffectiverate(31, "baru", 12, 12.1); entryeffectiverate er_id1 = new entryeffectiverate(32, "baru", 24, 12.2); entryeffectiverate er_id2 = new entryeffectiverate(33, "baru", 36, 12.3); entryeffectiverate er_id3 = new entryeffectiverate(34, "baru", 48, 12.4); entryeffectiverate er_id4 = new entryeffectiverate(35, "baru", 60, 12.5); entryeffectiverate er_id5 = new entryeffectiverate(36, "bekas", 12, 12.6); entryeffectiverate er_id6 = new entryeffectiverate(37, "bekas", 24, 12.7); entryeffectiverate er_id7 = new entryeffectiverate(38, "bekas", 36, 12.8); entryeffectiverate er_id8 = new entryeffectiverate(39, "bekas", 48, 12.9); entryeffectiverate er_id9 = new entryeffectiverate(40, "bekas", 60, 12.10); long er = db.createentryeffectiverate(er_id, null); long er1 = db.createentryeffectiverate(er_id1, null); long er2 = db.createentryeffectiverate(er_id2, null); long er3 = db.createentryeffectiverate(er_id3, null); long er4 = db.createentryeffectiverate(er_id4, null); long er5 = db.createentryeffectiverate(er_id5, null); long er6 = db.createentryeffectiverate(er_id6, null); long er7 = db.createentryeffectiverate(er_id7, null); long er8 = db.createentryeffectiverate(er_id8, null); long er9 = db.createentryeffectiverate(er_id9, null); log.d("effectiverate count", " effectiveratecount: " + db.getalleffectiverates().size()); // getting er log.d("get er", "getting er"); list<entryeffectiverate> alleffectiverate = db.getalleffectiverates(); (entryeffectiverate er : alleffectiverate) { log.d("er_ids", string.valueof(er.geterid())); log.d("er_rates", er.geterkondisi()); log.d("er_tenors", string.valueof(er.getertenor())); log.d("er_rates", string.valueof(er.geterrate())); }
the culprit key_id defined integer primary key autoincrement means insert statement violating constraint in new table. primary key constraint, unique constraint, foreign key constraint. make more 2 id's each table , set them integer , kesh1234,cesztoszule, amrut bidri helping me figuring out solution.
the working code:
the databasehelper code
public class databasehelper extends sqliteopenhelper { // logcat tag private static final string log = "databasehelper"; // database version private static final int database_version = 1; // database name private static final string database_name = "rates"; // table names public static final string key_er_id = "er_id"; public static final string key_er_usedornew = "used_or_new"; public static final string key_er_tenor = "er_tenor"; public static final string key_er_rate = "er_rate"; // asuransi rate public static final string key_as_id = "as_id"; public static final string key_as_regional = "regional"; public static final string key_as_tenor = "as_tenor"; public static final string key_as_tlo = "tlo"; public static final string key_as_comprehensive = "comprehensive"; public static final string key_as_combine = "combine"; public static final string table_effective_rate = "effective_rate"; public static final string table_asuransi_rate = "asuransi_rate"; // common column names private static final string key_id = "id"; private static final string key_created_at = "created_at"; // table create statements public static final string create_table_asuransi_rate = "create table " + table_asuransi_rate + " (" + key_as_id + " integer, " + key_as_regional + " integer, " + key_as_tenor + " integer," + key_as_tlo + " real," + key_as_comprehensive + " real," + key_as_combine + " real" +")"; public static final string create_table_effective_rate = "create table " + table_effective_rate + " (" + key_er_id + " integer, " + key_er_usedornew + " text not null, " + key_er_tenor + " integer," + key_er_rate + " real"+ ")"; public databasehelper(context context) { super(context, database_name, null, database_version); } @override public void oncreate(sqlitedatabase db) { // creating required tables db.execsql(create_table_effective_rate); db.execsql(create_table_asuransi_rate); } @override public void onupgrade(sqlitedatabase db, int oldversion, int newversion) { // on upgrade drop older tables db.execsql("drop table if exists " + table_effective_rate); db.execsql("drop table if exists " + table_asuransi_rate); // create new tables oncreate(db); } ///////////////////////////////////////////////////////////////////////////////////// //er methods public long createentryeffectiverate(entryeffectiverate eer/*, long[] as_ids*/) { sqlitedatabase db = this.getwritabledatabase(); contentvalues valueser = new contentvalues(); valueser.put(key_er_id, eer.geterid()); valueser.put(key_er_usedornew, eer.geterkondisi()); valueser.put(key_er_tenor, eer.getertenor()); valueser.put(key_er_rate, eer.geterrate()); //values.put(key_created_at, getdatetime()); // insert row long er_id = db.insert(table_effective_rate, null, valueser); return er_id; } //get er public entryeffectiverate getentryeffectiverate(long er_id) { sqlitedatabase db = this.getreadabledatabase(); string selectquery = "select * " + table_effective_rate + " " + key_er_id + " = " + er_id; log.e(log, selectquery); cursor c = db.rawquery(selectquery, null); if (c != null) c.movetofirst(); entryeffectiverate ergt = new entryeffectiverate(); ergt.seterid(c.getint(c.getcolumnindex(key_er_id))); ergt.seterkondisi(c.getstring(c.getcolumnindex(key_er_usedornew))); ergt.setertenor(c.getint(c.getcolumnindex(key_er_tenor))); ergt.seterrate(c.getdouble(c.getcolumnindex(key_er_rate))); //ergt.setcreatedat(c.getstring(c.getcolumnindex(key_created_at))); c.close(); return ergt; } //getting er public list<entryeffectiverate> getalleffectiverates() { list<entryeffectiverate> effectiverates = new arraylist<entryeffectiverate>(); string selectquery = "select * " + table_effective_rate; log.e(log, selectquery); sqlitedatabase db = this.getreadabledatabase(); cursor c = db.rawquery(selectquery, null); // looping through rows , adding list if (c.movetofirst()) { { entryeffectiverate ergt = new entryeffectiverate(); ergt.seterid(c.getint(c.getcolumnindex(key_er_id))); ergt.seterkondisi(c.getstring(c.getcolumnindex(key_er_usedornew))); ergt.setertenor(c.getint(c.getcolumnindex(key_er_tenor))); ergt.seterrate(c.getdouble(c.getcolumnindex(key_er_rate))); // add effectiverates.add(ergt); } while (c.movetonext()); } c.close(); return effectiverates; } //get er count public int getentryeffectiveratecount() { string countquery = "select * " + table_effective_rate; sqlitedatabase db = this.getreadabledatabase(); cursor cursor = db.rawquery(countquery, null); int count = cursor.getcount(); cursor.close(); // return count return count; } /* * updating todo */ public int updateentryeffectiverate(entryeffectiverate er) { sqlitedatabase db = this.getwritabledatabase(); contentvalues values = new contentvalues(); values.put(key_er_id, er.geterid()); values.put(key_er_usedornew, er.geterkondisi()); values.put(key_er_tenor, er.getertenor()); values.put(key_er_rate, er.geterrate()); //values.put(key_created_at, getdatetime()); // updating row return db.update(table_effective_rate, values, key_er_id + " = ?", new string[] { string.valueof(er.geterid()) }); } // public void deleteentryeffectiverate(long er_id) { sqlitedatabase db = this.getwritabledatabase(); db.delete(table_effective_rate, key_er_id + " = ?", new string[] { string.valueof(er_id) }); } ////////////////////////////////////////////////////////////////////////////////// ////////////////////////////////////////////////////////////////////////////////// public long createentryasuransirate(entryasuransirate ear/*, long[] as_ids*/) { sqlitedatabase db = this.getwritabledatabase(); contentvalues valuesas = new contentvalues(); valuesas.put(key_as_id, ear.getasid()); valuesas.put(key_as_regional, ear.getaszona()); valuesas.put(key_as_tenor, ear.getastenor()); valuesas.put(key_as_tlo, ear.getastlo()); valuesas.put(key_as_comprehensive, ear.getascomp()); valuesas.put(key_as_combine, ear.getascomb()); //values.put(key_created_at, getdatetime()); // insert row long er_id = db.insert(table_asuransi_rate, null, valuesas); return er_id; } //get er public entryasuransirate getentryasuransirate(long as_id) { sqlitedatabase db = this.getreadabledatabase(); string selectquery = "select * " + table_asuransi_rate + " " + key_as_id + " = " + as_id; log.e(log, selectquery); cursor c = db.rawquery(selectquery, null); if (c != null) c.movetofirst(); entryasuransirate asgt = new entryasuransirate(); asgt.setasid(c.getint(c.getcolumnindex(key_as_id))); asgt.setaszona(c.getint(c.getcolumnindex(key_as_regional))); asgt.setastenor(c.getint(c.getcolumnindex(key_as_tenor))); asgt.setastlo(c.getdouble(c.getcolumnindex(key_as_tlo))); asgt.setascomp(c.getdouble(c.getcolumnindex(key_as_comprehensive))); asgt.setascomb(c.getdouble(c.getcolumnindex(key_as_combine))); //ergt.setcreatedat(c.getstring(c.getcolumnindex(key_created_at))); c.close(); return asgt; } //getting er public list<entryasuransirate> getallasuransirates() { list<entryasuransirate> asuransirates = new arraylist<entryasuransirate>(); string selectquery = "select * " + table_asuransi_rate; log.e(log, selectquery); sqlitedatabase db = this.getreadabledatabase(); cursor c = db.rawquery(selectquery, null); // looping through rows , adding list if (c.movetofirst()) { { entryasuransirate asgt = new entryasuransirate(); asgt.setasid(c.getint(c.getcolumnindex(key_as_id))); asgt.setaszona(c.getint(c.getcolumnindex(key_as_regional))); asgt.setastenor(c.getint(c.getcolumnindex(key_as_tenor))); asgt.setastlo(c.getdouble(c.getcolumnindex(key_as_tlo))); asgt.setascomp(c.getdouble(c.getcolumnindex(key_as_comprehensive))); asgt.setascomb(c.getdouble(c.getcolumnindex(key_as_combine))); // add asuransirates.add(asgt); } while (c.movetonext()); } c.close(); return asuransirates; } //get er count public int getentryasuransiratecount() { string countquery = "select * " + table_asuransi_rate; sqlitedatabase db = this.getreadabledatabase(); cursor cursor = db.rawquery(countquery, null); int count = cursor.getcount(); cursor.close(); // return count return count; } /* * updating todo */ public int updateentryasuransirate(entryasuransirate ear) { sqlitedatabase db = this.getwritabledatabase(); contentvalues valuesas = new contentvalues(); valuesas.put(key_as_id, ear.getasid()); valuesas.put(key_as_regional, ear.getaszona()); valuesas.put(key_as_tenor, ear.getastenor()); valuesas.put(key_as_tlo, ear.getastlo()); valuesas.put(key_as_comprehensive, ear.getascomp()); valuesas.put(key_as_combine, ear.getascomb()); //values.put(key_created_at, getdatetime()); // updating row return db.update(table_asuransi_rate, valuesas, key_as_id + " = ?", new string[] { string.valueof(ear.getasid()) }); } // public void deleteentryasuransirate(long as_id) { sqlitedatabase db = this.getwritabledatabase(); db.delete(table_asuransi_rate, key_as_id + " = ?", new string[] { string.valueof(as_id) }); } ////////////////////////////////////////////////////////////////////////////////// // closing database public void closedb() { sqlitedatabase db = this.getreadabledatabase(); if (db != null && db.isopen()) db.close(); } /** * datetime * */ private string getdatetime() { simpledateformat dateformat = new simpledateformat( "yyyy-mm-dd hh:mm:ss", locale.getdefault()); date date = new date(); return dateformat.format(date); } } the main activity insert values:
public class mainactivity extends activity { // database helper databasehelper db; @override protected void oncreate(bundle savedinstancestate) { super.oncreate(savedinstancestate); setcontentview(r.layout.activity_main); db = new databasehelper(getapplicationcontext()); entryeffectiverate er_id = new entryeffectiverate(1, "baru", 12, 12.1); entryeffectiverate er_id1 = new entryeffectiverate(2, "baru", 24, 12.2); entryeffectiverate er_id2 = new entryeffectiverate(3, "baru", 36, 12.3); entryeffectiverate er_id3 = new entryeffectiverate(4, "baru", 48, 12.4); entryeffectiverate er_id4 = new entryeffectiverate(5, "baru", 60, 12.5); entryeffectiverate er_id5 = new entryeffectiverate(6, "bekas", 12, 12.6); entryeffectiverate er_id6 = new entryeffectiverate(7, "bekas", 24, 12.7); entryeffectiverate er_id7 = new entryeffectiverate(8, "bekas", 36, 12.8); entryeffectiverate er_id8 = new entryeffectiverate(9, "bekas", 48, 12.9); entryeffectiverate er_id9 = new entryeffectiverate(10, "bekas", 60, 12.10); long er = db.createentryeffectiverate(er_id); long er1 = db.createentryeffectiverate(er_id1); long er2 = db.createentryeffectiverate(er_id2); long er3 = db.createentryeffectiverate(er_id3); long er4 = db.createentryeffectiverate(er_id4); long er5 = db.createentryeffectiverate(er_id5); long er6 = db.createentryeffectiverate(er_id6); long er7 = db.createentryeffectiverate(er_id7); long er8 = db.createentryeffectiverate(er_id8); long er9 = db.createentryeffectiverate(er_id9); log.d("effectiverate count", " effectiveratecount: " + db.getalleffectiverates().size()); entryasuransirate = new entryasuransirate(1, 1, 12, 0.07, 2.07, 2.14); entryasuransirate as1 = new entryasuransirate(2, 1, 24, 0.08, 2.06, 2.15); entryasuransirate as2 = new entryasuransirate(3, 1, 36, 0.09, 2.05, 2.16); entryasuransirate as3 = new entryasuransirate(4, 1, 48, 0.10, 2.04, 2.17); entryasuransirate as4 = new entryasuransirate(5, 1, 60, 0.11, 2.03, 2.18); entryasuransirate as5 = new entryasuransirate(6, 2, 12, 0.12, 2.02, 2.19); entryasuransirate as6 = new entryasuransirate(7, 2, 24, 0.13, 2.01, 2.20); entryasuransirate as7 = new entryasuransirate(8, 2, 36, 0.14, 2.00, 2.21); entryasuransirate as8 = new entryasuransirate(9, 2, 48, 0.15, 1.99, 2.22); entryasuransirate as9 = new entryasuransirate(10, 2, 60, 0.16, 1.98, 2.23); entryasuransirate as10 = new entryasuransirate(11, 3, 12, 0.17, 1.97, 2.24); entryasuransirate as11 = new entryasuransirate(12, 3, 24, 0.18, 1.96, 2.25); entryasuransirate as12 = new entryasuransirate(13, 3, 36, 0.19, 1.95, 2.26); entryasuransirate as13 = new entryasuransirate(14, 3, 48, 0.20, 1.94, 2.27); entryasuransirate as14 = new entryasuransirate(15, 3, 60, 0.21, 1.93, 2.28); long as_id = db.createentryasuransirate(as); long as_id1 = db.createentryasuransirate(as1); long as_id2 = db.createentryasuransirate(as2); long as_id3 = db.createentryasuransirate(as3); long as_id4 = db.createentryasuransirate(as4); long as_id5 = db.createentryasuransirate(as5); long as_id6 = db.createentryasuransirate(as6); long as_id7 = db.createentryasuransirate(as7); long as_id8 = db.createentryasuransirate(as8); long as_id9 = db.createentryasuransirate(as9); long as_id10 = db.createentryasuransirate(as10); long as_id11 = db.createentryasuransirate(as11); long as_id12 = db.createentryasuransirate(as12); long as_id13 = db.createentryasuransirate(as13); long as_id14 = db.createentryasuransirate(as14); log.d("asuransirate count", " asuransiratecount: " + db.getallasuransirates().size()); // getting er log.d("get er", "getting er"); list<entryeffectiverate> alleffectiverate = db.getalleffectiverates(); (entryeffectiverate er : alleffectiverate) { log.d("er_ids", string.valueof(er.geterid())); log.d("er_rates", er.geterkondisi()); log.d("er_tenors", string.valueof(er.getertenor())); log.d("er_rates", string.valueof(er.geterrate())); } // getting log.d("get as", "getting as"); list<entryasuransirate> allasuransirate = db.getallasuransirates(); (entryasuransirate : allasuransirate) { log.d("as_ids", string.valueof(as.getasid())); log.d("as_tenors", string.valueof(as.getastenor())); log.d("as_tlos", string.valueof(as.getastlo())); log.d("as_comps", string.valueof(as.getascomp())); log.d("as_combs", string.valueof(as.getascomb())); } // don't forget close database connection db.closedb(); } } and model classes:
public class entryasuransirate { int as_id; int aszona; int astenor; double astlo; double ascomp; double ascomb; // string created_at; // constructors public entryasuransirate() { } public entryasuransirate(int aszona, int astenor, double astlo, double ascomp, double ascomb) { this.aszona = aszona; this.astenor = astenor; this.astlo = astlo; this.ascomp = ascomp; this.ascomb = ascomb; } public entryasuransirate(int as_id, int aszona, int astenor, double astlo, double ascomp, double ascomb) { this.as_id = as_id; this.aszona = aszona; this.astenor = astenor; this.astlo = astlo; this.ascomp = ascomp; this.ascomb = ascomb; } // setters public void setasid(int as_id) { this.as_id = as_id; } public void setaszona(int aszona) { this.aszona = aszona; } public void setastenor(int astenor) { this.astenor = astenor; } public void setastlo(double astlo) { this.astlo = astlo; } public void setascomp(double ascomp) { this.ascomp = ascomp; } public void setascomb(double ascomb) { this.ascomb = ascomb; } // public void setcreatedat(string created_at){ // this.created_at = created_at; // } // getters public long getasid() { return this.as_id; } public int getaszona() { return this.aszona; } public int getastenor() { return this.astenor; } public double getastlo() { return this.astlo; } public double getascomp() { return this.ascomp; } public double getascomb() { return this.ascomb; } } another model:
public class entryeffectiverate { int er_id; string erkondisi; int ertenor; double errate; //string created_at; // constructors public entryeffectiverate() { } public entryeffectiverate(string erkondisi, int ertenor, double errate) { this.erkondisi = erkondisi; this.ertenor = ertenor; this.errate = errate; } public entryeffectiverate(int er_id, string erkondisi, int ertenor, double errate) { this.er_id = er_id; this.erkondisi = erkondisi; this.ertenor = ertenor; this.errate = errate; } // setters public void seterid(int er_id) { this.er_id = er_id; } public void seterkondisi(string erkondisi) { this.erkondisi = erkondisi; } public void setertenor(int ertenor) { this.ertenor = ertenor; } public void seterrate(double errate){ this.errate = errate; } //public void setcreatedat(string created_at){ // this.created_at = created_at; //} // getters public long geterid() { return this.er_id; } public string geterkondisi() { return this.erkondisi; } public int getertenor() { return this.ertenor; } public double geterrate(){ return this.errate; } } its working fine result appear in logcat this:
04-02 13:00:19.758: e/databasehelper(360): select * effective_rate 04-02 13:00:19.779: d/effectiverate count(360): effectiveratecount: 20 04-02 13:00:21.068: e/databasehelper(360): select * asuransi_rate 04-02 13:00:21.079: d/asuransirate count(360): asuransiratecount: 30 04-02 13:00:21.079: d/get er(360): getting er 04-02 13:00:21.089: e/databasehelper(360): select * effective_rate 04-02 13:00:21.099: d/er_ids(360): 1 04-02 13:00:21.099: d/er_rates(360): baru 04-02 13:00:21.099: d/er_tenors(360): 12 04-02 13:00:21.099: d/er_rates(360): 12.1 04-02 13:00:21.099: d/er_ids(360): 2 04-02 13:00:21.099: d/er_rates(360): baru 04-02 13:00:21.099: d/er_tenors(360): 24 04-02 13:00:21.099: d/er_rates(360): 12.2 04-02 13:00:21.099: d/er_ids(360): 3 04-02 13:00:21.099: d/er_rates(360): baru 04-02 13:00:21.099: d/er_tenors(360): 36 04-02 13:00:21.099: d/er_rates(360): 12.3 04-02 13:00:21.099: d/er_ids(360): 4 04-02 13:00:21.099: d/er_rates(360): baru 04-02 13:00:21.099: d/er_tenors(360): 48 04-02 13:00:21.099: d/er_rates(360): 12.4 04-02 13:00:21.099: d/er_ids(360): 5 04-02 13:00:21.109: d/er_rates(360): baru 04-02 13:00:21.109: d/er_tenors(360): 60 04-02 13:00:21.119: d/er_rates(360): 12.5 04-02 13:00:21.119: d/er_ids(360): 6 04-02 13:00:21.119: d/er_rates(360): bekas 04-02 13:00:21.119: d/er_tenors(360): 12 04-02 13:00:21.119: d/er_rates(360): 12.6 04-02 13:00:21.119: d/er_ids(360): 7 04-02 13:00:21.129: d/er_rates(360): bekas 04-02 13:00:21.129: d/er_tenors(360): 24 04-02 13:00:21.129: d/er_rates(360): 12.7 04-02 13:00:21.129: d/er_ids(360): 8 04-02 13:00:21.129: d/er_rates(360): bekas 04-02 13:00:21.129: d/er_tenors(360): 36 04-02 13:00:21.129: d/er_rates(360): 12.8 04-02 13:00:21.139: d/er_ids(360): 9 04-02 13:00:21.139: d/er_rates(360): bekas 04-02 13:00:21.139: d/er_tenors(360): 48 04-02 13:00:21.139: d/er_rates(360): 12.9 04-02 13:00:21.139: d/er_ids(360): 10 04-02 13:00:21.149: d/er_rates(360): bekas 04-02 13:00:21.149: d/er_tenors(360): 60 04-02 13:00:21.149: d/er_rates(360): 12.1 04-02 13:00:21.188: d/get as(360): getting 04-02 13:00:21.188: e/databasehelper(360): select * asuransi_rate 04-02 13:00:21.399: d/as_ids(360): 1 04-02 13:00:21.399: d/as_tenors(360): 12 04-02 13:00:21.399: d/as_tlos(360): 0.07 04-02 13:00:21.399: d/as_comps(360): 2.07 04-02 13:00:21.408: d/as_combs(360): 2.14 . . . 04-02 13:00:21.509: d/as_ids(360): 15 04-02 13:00:21.509: d/as_tenors(360): 60 04-02 13:00:21.519: d/as_tlos(360): 0.21 04-02 13:00:21.519: d/as_comps(360): 1.93 04-02 13:00:21.519: d/as_combs(360): 2.28 due body limit characters shorten it
Comments
Post a Comment