How to Display, Select ,Edit and Delete ListView, with 3 edit text stored on 3 columns on SQLite Database (Java Android Application)

Question

I am trying to build an application where the user will enter 3 data on 3 edit texts which are then stored to 3 columns on the database. The user display them on a listview which allows them to select the whole item to edit either one of the text fields or delete the entire item as a whole. So far the adding of data to the database is still working, it is being stored to 3 different columns . As for the display, it only display the first edit text which is the first column of data in the database which is BodyTemp, and when I try to select the item the whole app crashes.

ViewRecords.java

package mdad.project;
import java.util.ArrayList;
import com.example.manandhowproject.R;
import android.app.Activity;
import android.content.Intent;
import android.database.Cursor;
import android.os.Bundle;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.ListAdapter;
import android.widget.ListView;
import android.widget.Toast;

public class ViewRecords extends Activity {
    DatabaseHelper dbhelper;
    ListView listView;

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

        dbhelper = new DatabaseHelper(this);
        listView = (ListView) findViewById(R.id.listView);
        populateListView();
    }

    private void populateListView()
    {
        Cursor records = dbhelper.getRecords();
        ArrayList<String> listRec = new ArrayList<String>();
        while(records.moveToNext()){
            listRec.add(records.getString(1));
        }
    ListAdapter adapter = new ArrayAdapter<String>(this,android.R.layout.simple_list_item_1,listRec);
    listView.setAdapter(adapter);

    listView.setOnItemClickListener(new AdapterView.OnItemClickListener() {

        @Override
        public void onItemClick(AdapterView<?> adapterView, View view, int i, long l) {
             String BTemp = adapterView.getItemAtPosition(i).toString();
             String HRate = adapterView.getItemAtPosition(i).toString();
             String Bmi = adapterView.getItemAtPosition(i).toString();

             Cursor data = dbhelper.getRecID(BTemp, HRate, Bmi);
             int recID = -1;
             while(data.moveToNext()){
                 recID = data.getInt(0);
             }
             if(recID > -1){

                 Intent editScreenIntent = new Intent(ViewRecords.this, EditDel.class);
                 editScreenIntent.putExtra("id",recID);
                 editScreenIntent.putExtra("Body Temperature",BTemp);
                 editScreenIntent.putExtra("Heart Rate",HRate);
                 editScreenIntent.putExtra("BMI",Bmi);
                 startActivity(editScreenIntent);
             }
             else{
                 Toast.makeText(getApplicationContext(), "No ID Associated", Toast.LENGTH_LONG).show();
             }
         }
     });
 }}

DatabaseHelper.java

package mdad.project;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {

        public static final String DATABASE_NAME = "medrec.db";
        public static final String TABLE_NAME = "myrec_data";
        public static final String COL1 = "ID";
        public static final String COL2 = "BodyTemp";
        public static final String COL3 = "HeartRate";
        public static final String COL4 = "BMI";



     public DatabaseHelper(Context context) {
            super(context, TABLE_NAME, null, 1);
        }

    @Override
    public void onCreate(SQLiteDatabase db) {

    String createTable = "CREATE TABLE " + TABLE_NAME + " (ID INTEGER PRIMARY KEY AUTOINCREMENT, " + COL2 + " TEXT, " + COL3 + " TEXT, " + COL4 + " TEXT);";
    db.execSQL(createTable);

    }

    @Override
    public void onUpgrade (SQLiteDatabase db,int i, int i1)
    {
         db.execSQL("DROP IF TABLE EXISTS " + TABLE_NAME);
         onCreate(db);
    }

     public boolean addRecord(String BodyTemp, String HeartRate, String BMI) {
            SQLiteDatabase db = this.getWritableDatabase();
            ContentValues contentValues = new ContentValues();
            contentValues.put(COL2, BodyTemp);
            contentValues.put(COL3, HeartRate);
            contentValues.put(COL4, BMI);

            long result = db.insert(TABLE_NAME, null, contentValues);


            if (result == -1) {
                return false;
            } else {
                return true;
            }
        }


     public Cursor getRecords()
     {
         SQLiteDatabase db = this.getWritableDatabase();
         String query = "SELECT * FROM " + TABLE_NAME;
         Cursor records = db.rawQuery(query, null);
         return records;
     }

     public Cursor getRecID(String BTemp, String Hrate, String Bmi){
            SQLiteDatabase db = this.getWritableDatabase();
            String query = "SELECT " + COL1 + " FROM " + TABLE_NAME +
                    " WHERE " + COL2 + " = '" + BTemp + "AND" + COL3 + " = '" + Hrate + "AND" + COL4 + " = '" + Bmi +"'";
            Cursor data = db.rawQuery(query, null);
            return data;
        }

    public void updateBtemp(String bodytemp, int selectedID,
            String selectedBtemp) {
          SQLiteDatabase db = this.getWritableDatabase();
          String query = "UPDATE " + TABLE_NAME + " SET " + COL2 +
                    " = '" + bodytemp + "' WHERE " + COL1 + " = '" + selectedID + "'" +
                    " AND " + COL2 + " = '" + selectedBtemp + "'";
            db.execSQL(query);

    }

    public void updateHrate(String heartrate, int selectedID,
            String selectedHrate) {
        SQLiteDatabase db = this.getWritableDatabase();
          String query = "UPDATE " + TABLE_NAME + " SET " + COL3 +
                    " = '" + heartrate + "' WHERE " + COL1 + " = '" + selectedID + "'" +
                    " AND " + COL3 + " = '" + selectedHrate + "'";
            db.execSQL(query);

    }

    public void updateBMI(String bmi, int selectedID, String selectedBMI) {
        SQLiteDatabase db = this.getWritableDatabase();
          String query = "UPDATE " + TABLE_NAME + " SET " + COL4 +
                    " = '" + bmi + "' WHERE " + COL1 + " = '" + selectedID + "'" +
                    " AND " + COL4 + " = '" + selectedBMI + "'";
            db.execSQL(query);

    }

    public void deleteBtemp(int selectedID, String selectedBtemp) {
           SQLiteDatabase db = this.getWritableDatabase();
            String query = "DELETE FROM " + TABLE_NAME + " WHERE "
                    + COL1 + " = '" + selectedID + "'" +
                    " AND " + COL2 + " = '" + selectedBtemp + "'";

            db.execSQL(query);

    }

    public void deleteHrate(int selectedID, String selectedHrate) {
           SQLiteDatabase db = this.getWritableDatabase();
            String query = "DELETE FROM " + TABLE_NAME + " WHERE "
                    + COL1 + " = '" + selectedID + "'" +
                    " AND " + COL3 + " = '" + selectedHrate + "'";

            db.execSQL(query);

    }

    public void deleteBMI(int selectedID, String selectedBMI) {
           SQLiteDatabase db = this.getWritableDatabase();
            String query = "DELETE FROM " + TABLE_NAME + " WHERE "
                    + COL1 + " = '" + selectedID + "'" +
                    " AND " + COL4 + " = '" + selectedBMI + "'";

            db.execSQL(query);

    }

}

EditDel.java

package mdad.project;
import com.example.manandhowproject.R;
import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class EditDel extends Activity {

    Button btnSave,btnDelete;
    EditText etBT, etHR, etBMI;

    DatabaseHelper dbhelper;

    private String selectedBtemp, selectedHrate, selectedBMI;
    private int selectedID;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.editdel);
        btnSave = (Button) findViewById(R.id.btnSave);
        btnDelete = (Button) findViewById(R.id.btnDelete);
        etBT = (EditText) findViewById(R.id.etBT);
        etHR = (EditText) findViewById(R.id.etHR);
        etBMI = (EditText) findViewById(R.id.etBMI);
        dbhelper = new DatabaseHelper(this);


        Intent receivedIntent = getIntent();


        selectedID = receivedIntent.getIntExtra("id",-1); 
        selectedBtemp = receivedIntent.getStringExtra("BTemp");
        selectedHrate = receivedIntent.getStringExtra("Hrate");
        selectedBMI = receivedIntent.getStringExtra("Bmi");



        etBT.setText(selectedBtemp);
        etHR.setText(selectedHrate);
        etBMI.setText(selectedBMI);

        btnSave.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                String bodytemp = etBT.getText().toString();
                String heartrate = etHR.getText().toString();
                String bmi = etBMI.getText().toString();
                if(!bodytemp.equals("") && !heartrate.equals("") && !bmi.equals("")){
                    dbhelper.updateBtemp(bodytemp,selectedID,selectedBtemp);
                    dbhelper.updateHrate(heartrate,selectedID,selectedHrate);
                    dbhelper.updateBMI(bmi,selectedID,selectedBMI);

                }else{
                    toastMessage("Please ensure all fields are filled.");
                }
            }
        });

        btnDelete.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
               dbhelper.deleteBtemp(selectedID,selectedBtemp);
               dbhelper.deleteHrate(selectedID,selectedHrate);
               dbhelper.deleteBMI(selectedID,selectedBMI);
                etBT.setText("");
                etHR.setText("");
                etBMI.setText("");
                toastMessage("removed from database");
            }
        });

    }

    /**
     * customizable toast
     * @param message
     */
    private void toastMessage(String message){
        Toast.makeText(this,message, Toast.LENGTH_SHORT).show();
    }
}

Crash Log

02-07 01:14:44.373: E/AndroidRuntime(17899): FATAL EXCEPTION: main
02-07 01:14:44.373: E/AndroidRuntime(17899): android.database.sqlite.SQLiteException: unrecognized token: "666666ANDBMI" (code 1): , while compiling: SELECT ID FROM myrec_data WHERE BodyTemp = '666666ANDHeartRate = '666666ANDBMI = '666666'
02-07 01:14:44.373: E/AndroidRuntime(17899):    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
02-07 01:14:44.373: E/AndroidRuntime(17899):    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
02-07 01:14:44.373: E/AndroidRuntime(17899):    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
02-07 01:14:44.373: E/AndroidRuntime(17899):    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
02-07 01:14:44.373: E/AndroidRuntime(17899):    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
02-07 01:14:44.373: E/AndroidRuntime(17899):    at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
02-07 01:14:44.373: E/AndroidRuntime(17899):    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
02-07 01:14:44.373: E/AndroidRuntime(17899):    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
02-07 01:14:44.373: E/AndroidRuntime(17899):    at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1253)
02-07 01:14:44.373: E/AndroidRuntime(17899):    at mdad.project.DatabaseHelper.getRecID(DatabaseHelper.java:69)
02-07 01:14:44.373: E/AndroidRuntime(17899):    at mdad.project.ViewRecords$1.onItemClick(ViewRecords.java:47)
02-07 01:14:44.373: E/AndroidRuntime(17899):    at android.widget.AdapterView.performItemClick(AdapterView.java:298)
02-07 01:14:44.373: E/AndroidRuntime(17899):    at android.widget.AbsListView.performItemClick(AbsListView.java:1100)
02-07 01:14:44.373: E/AndroidRuntime(17899):    at android.widget.AbsListView$PerformClick.run(AbsListView.java:2788)
02-07 01:14:44.373: E/AndroidRuntime(17899):    at android.widget.AbsListView$1.run(AbsListView.java:3463)
02-07 01:14:44.373: E/AndroidRuntime(17899):    at android.os.Handler.handleCallback(Handler.java:730)
02-07 01:14:44.373: E/AndroidRuntime(17899):    at android.os.Handler.dispatchMessage(Handler.java:92)
02-07 01:14:44.373: E/AndroidRuntime(17899):    at android.os.Looper.loop(Looper.java:137)
02-07 01:14:44.373: E/AndroidRuntime(17899):    at android.app.ActivityThread.main(ActivityThread.java:5103)
02-07 01:14:44.373: E/AndroidRuntime(17899):    at java.lang.reflect.Method.invokeNative(Native Method)
02-07 01:14:44.373: E/AndroidRuntime(17899):    at java.lang.reflect.Method.invoke(Method.java:525)
02-07 01:14:44.373: E/AndroidRuntime(17899):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:737)
02-07 01:14:44.373: E/AndroidRuntime(17899):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
02-07 01:14:44.373: E/AndroidRuntime(17899):    at dalvik.system.NativeStart.main(Native Method)

@ScaryWombat 666666 was the value I enter on the edit text when I trying to add the database. — Kelvin Magic How MAGIC, Feb 07 '18 at 06:39
Look up SQL injection attacks, when you get a chance... Anyway, Android documentation recommends using this instead of SQLiteOpenHepler https://developer.android.com/training/data-storage/room/index.html — OneCricketeer, Feb 07 '18 at 06:54
Also, how were you planning on showing three columns using only a list containing `records.getString(1)`? — OneCricketeer, Feb 07 '18 at 07:06
@cricket_007 do you mind showing me how I should change it so I can showing 3 columns? Thanks a lot — Kelvin Magic How MAGIC, Feb 07 '18 at 07:10
I've rolled back your edit because that's a separate error, and deserves to be a separate question. Plenty of tutorials online exists for "listview sqlite delete" — OneCricketeer, Feb 07 '18 at 07:10
Using a SimpleCursorAdapter would be a good start to map each column in the database to its own views. https://stackoverflow.com/questions/12077955/android-using-simplecursoradapter-to-get-data-from-database-to-listview — OneCricketeer, Feb 07 '18 at 07:12

score 1 · Answer 1 · answered Feb 07 '18 at 06:43

1

You need to add some quotes and spaces to your sql

from

WHERE " + COL2 + " = '" + BTemp + "AND" ...

to

WHERE " + COL2 + " = '" + BTemp + "' AND " ....

Although it would be better to use JDBC or JPA and use the set methods

answered Feb 07 '18 at 06:43

Scary Wombat

44,617
6
35
64

Just not using rawQuery would be preferred https://stackoverflow.com/questions/20865511/android-how-to-write-the-query-with-a-proper-projection – OneCricketeer Feb 07 '18 at 06:56
@cricket_007 Thanks, was not sure of the android specific way, but as the question is tagged java, I will leave it here – Scary Wombat Feb 07 '18 at 06:58
Hi currently I am able to select and it will prompt the EditDel page, however still the ListView does not display 3 columns as what I wanted it only shows the first column which is body temp and even when I select it , it does not have the values carry forward to EditDel page which I implemented using the intent function (See codes above) . Also when I want try to delete it says "records deleted from database" which is a toast I added in my code when the records is deleted but when I check its still there. Please Advise. – Kelvin Magic How MAGIC Feb 07 '18 at 06:59
It's Android code, though. Those libraries exist directly without JDBC – OneCricketeer Feb 07 '18 at 07:00
1

Hi Kelvin, Please consider to edit your question (or make a new one) with the relevant information. I have solved the exception that was being thrown – Scary Wombat Feb 07 '18 at 07:01
1

@cricket_007 I agree the OP should use them – Scary Wombat Feb 07 '18 at 07:01
@Kelvin 1) CursorAdapter should be used to display sqlite information directly from a Cursor object, not an Arraylist in a ArrayAdapter. 2) You must call notifyDataSetChanged on the adapter instance after anything about the list is modified or at the very least, `populateListView()` – OneCricketeer Feb 07 '18 at 07:04
@cricket_007 Sorry I am not very good at this is it ok if you can give me a sample of how my code should look like or where and how do I add the methods you mentioned – Kelvin Magic How MAGIC Feb 07 '18 at 07:08

How to Display, Select ,Edit and Delete ListView, with 3 edit text stored on 3 columns on SQLite Database (Java Android Application)

1 Answers1