sqlite - Android Studio - create database with multiple columns -
i want create database 3 columns (name of point of interest, longitude , latitude). since new databases using example found online , try change case. problem works 1 column if add other 2 breaks. code:
taskcontract
import android.provider.basecolumns; public class taskcontract { public static final string db_name = "com.example.todolist.db.tasks"; public static final int db_version = 1; public static final string poi_names = "tasks"; public static final string longitudes = "longs"; public static final string latitudes = "lats"; public class columns { public static final string poi = "task"; public static final string _id = basecolumns._id; public static final string longitude = "long"; public static final string latitude = "lat"; } } taskdbhelper
import android.content.context; import android.database.sqlite.sqlitedatabase; import android.database.sqlite.sqliteopenhelper; import android.util.log; public class taskdbhelper extends sqliteopenhelper { public taskdbhelper(context context) { super(context, taskcontract.db_name, null, taskcontract.db_version); } @override public void oncreate(sqlitedatabase sqldb) { string sqlquery = string.format("create table %s (" + "_id integer primary key autoincrement, " + "%s text" + " %s longitude" + "%s latitude)", taskcontract.poi_names, taskcontract.columns.poi, taskcontract.longitudes, taskcontract.columns.longitude, taskcontract.latitudes, taskcontract.columns.latitude); log.d("taskdbhelper","query form table: "+sqlquery); sqldb.execsql(sqlquery); } @override public void onupgrade(sqlitedatabase sqldb, int i, int i2) { sqldb.execsql("drop table if exists "+taskcontract.poi_names+taskcontract.longitudes+taskcontract.latitudes); oncreate(sqldb); } } and here's main activity add new values columns:
public class mainactivity extends listactivity { private listadapter listadapter; private taskdbhelper helper; @override public void oncreate(bundle savedinstancestate) { super.oncreate(savedinstancestate); setcontentview(r.layout.main); updateui(); } @override public boolean oncreateoptionsmenu(menu menu) { getmenuinflater().inflate(r.menu.menu,menu); return true; } @override public boolean onoptionsitemselected(menuitem item) { switch (item.getitemid()) { case r.id.action_add_task: alertdialog.builder builder = new alertdialog.builder(this); builder.settitle("add new point of interest"); linearlayout layout = new linearlayout(this); layout.setorientation(linearlayout.vertical); final textview inputstring1 = new textview(this); inputstring1.settext("enter name:"); final textview inputstring2 = new textview(this); inputstring2.settext("enter longitude:"); final textview inputstring3 = new textview(this); inputstring3.settext("enter latitude:"); final textview inputstring4 = new textview(this); inputstring4.settext("enter new point of interest:"); final edittext inputfield1 = new edittext(this); final edittext inputfield2 = new edittext(this); final edittext inputfield3 = new edittext(this); layout.addview(inputstring4); layout.addview(inputstring1); layout.addview(inputfield1); layout.addview(inputstring2); layout.addview(inputfield2); layout.addview(inputstring3); layout.addview(inputfield3); builder.setview(layout); builder.setpositivebutton("add", new dialoginterface.onclicklistener() { @override public void onclick(dialoginterface dialoginterface, int i) { string poi = inputfield1.gettext().tostring(); string longit = inputfield2.gettext().tostring(); string lat = inputfield3.gettext().tostring(); helper = new taskdbhelper(mainactivity.this); sqlitedatabase db = helper.getwritabledatabase(); contentvalues values = new contentvalues(); values.clear(); values.put(taskcontract.columns.poi,poi); db.insertwithonconflict(taskcontract.poi_names,null,values,sqlitedatabase.conflict_ignore); values.put(taskcontract.columns.longitude,longit); db.insertwithonconflict(taskcontract.longitudes,null,values,sqlitedatabase.conflict_ignore); values.put(taskcontract.columns.latitude,lat); db.insertwithonconflict(taskcontract.latitudes,null,values,sqlitedatabase.conflict_ignore); updateui(); } }); builder.setnegativebutton("cancel",null); builder.create().show(); return true; default: return false; } } private void updateui() { helper = new taskdbhelper(mainactivity.this); sqlitedatabase sqldb = helper.getreadabledatabase(); cursor cursor = sqldb.query(taskcontract.poi_names, new string[]{taskcontract.columns._id, taskcontract.columns.poi}, null, null, null, null, null); listadapter = new simplecursoradapter( this, r.layout.task_view, cursor, new string[]{taskcontract.columns.poi}, new int[]{r.id.tasktextview}, 0 ); this.setlistadapter(listadapter); } public void ondonebuttonclick(view view) { view v = (view) view.getparent(); textview tasktextview = (textview) v.findviewbyid(r.id.tasktextview); string poi = tasktextview.gettext().tostring(); string sql = string.format("delete %s %s = '%s'", taskcontract.poi_names, taskcontract.columns.poi, poi); helper = new taskdbhelper(mainactivity.this); sqlitedatabase sqldb = helper.getwritabledatabase(); sqldb.execsql(sql); updateui(); } } i think problem when declare longitudes , latitudes in taskcontract if change them this:
public static final string poi_names = "tasks";
public static final string longitudes = "tasks";
public static final string latitudes = "tasks";
it works puts in same column.
can explain means assign values "tasks" string?
there few errors in code - future reference check each method here.
string.format("create table %s (" + "_id integer primary key autoincrement, " + "%s text" + " %s longitude" + "%s latitude)", taskcontract.poi_names, taskcontract.columns.poi, taskcontract.longitudes, taskcontract.columns.longitude, taskcontract.latitudes, taskcontract.columns.latitude); here "text" refers sqlite data type text. sqlite not have data type longitude / latitude. list of data types available here. in case use real floating point numbers or text if want store coordinates strings (at moment inserting strings, i'll give example it, keep in mind real better approach). besides have 4 placeholders, provide 6 parameters. try this:
string.format("create table %s (" + "_id integer primary key autoincrement, " + "%s text" + " %s text" + "%s text)", taskcontract.poi_names, taskcontract.columns.poi, taskcontract.columns.longitude, taskcontract.columns.longitude); this needs changed too:
sqldb.execsql("drop table if exists "+taskcontract.poi_names+taskcontract.longitudes+taskcontract.latitudes); when drop table, need specify name - in case taskcontract.poi_names. adding column names make sql code incorrect , statement rejected (i.e. return error). make this:
sqldb.execsql("drop table if exists " + taskcontract.poi_names); a few issues here too:
contentvalues values = new contentvalues(); values.clear(); values.put(taskcontract.columns.poi,poi); db.insertwithonconflict(taskcontract.poi_names,null,values,sqlitedatabase.conflict_ignore); values.put(taskcontract.columns.longitude,longit); db.insertwithonconflict(taskcontract.longitudes,null,values,sqlitedatabase.conflict_ignore); values.put(taskcontract.columns.latitude,lat); db.insertwithonconflict(taskcontract.latitudes,null,values,sqlitedatabase.conflict_ignore); when calling insertwithonconflict provide table name , collection of key-values represent each of columns. in short need call insertwithonconflict once each record want insert (not each column in record). try code:
contentvalues values = new contentvalues(); values.put(taskcontract.columns.poi,poi); values.put(taskcontract.columns.longitude,longit); values.put(taskcontract.columns.latitude,lat); db.insertwithonconflict(taskcontract.poi_names,null,values,sqlitedatabase.conflict_ignore); after these changes delete these fields - won't needed:
public static final string longitudes = "longs"; public static final string latitudes = "lats"; let me know if works or need more assistance! :)