Create database with multiple columns using SQLite in Android Studio

sqlite

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?

Best Answer

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.