Create database with multiple columns using SQLite in Android Studio

Question

I want to create a database with 3 columns (name of point of interest, longitude and latitude). Since I am completely new to databases I am using an example I found online and try to change it to my case. The problem is it works for 1 column but if I add the other 2 it breaks. This is my 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 to 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);
 }
}

MainActivity

and here's my main activity where I add the new values to the 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 a 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 a 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 FROM %s WHERE %s = '%s'",
 TaskContract.POI_NAMES,
 TaskContract.Columns.POI,
 poi);
 helper = new TaskDBHelper(MainActivity.this);
 SQLiteDatabase sqlDB = helper.getWritableDatabase();
 sqlDB.execSQL(sql);
 updateUI();
 }
}

Problem

I think the problem is when I declare LONGITUDES and LATITUDES in the TaskContract as if I change them like this:

public static final String POI_NAMES = "tasks"; 
public static final String LONGITUDES = "tasks"; 
public static final String LATITUDES = "tasks";

it works but it puts everything in the same column.

Can someone explain what exactly means to assign the values "tasks" to my string?

Answer 1

Your issue appears to be in your SQL syntax, you missed multiple commas:

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

You also appear to have more strings in the parameters than you have replacement keys for. This may or may not also be part of your issue.

Answer 2

I agree with EBrown, but I think the problem is spaces.

"CREATE TABLE %s (" +
 "_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
 "%s TEXT" + " %s LONGITUDE" + "%s LATITUDE)",

You don't appear to have spaces between your strings. It's been a while since I've done much coding, but maybe you have a table with one field with a long (and weird) name.

Have you run this code and then looked at your database using the SQLite client?