Displaying SQLite DB arraylist in RecyclerView keeps crashing

Question

I'm setting up a RecyclerView to view the ArrayList of data in the database (empty initially). However, it keeps on crashing in the Android Virtual Device. I'm new to Android Studio and I'm doing a personal project. I've used different books and videos to create what I have.

I'm trying to list all the data that has a common category in alphabetical order. But if data does not exist, then to show the id/emptyView textView.

I thought my mistake might be in my adapter by choosing incorrect textViews in ViewHolder or incorrect inflation layout in onCreateViewHolder as I am a bit confused which they're supposed to indicate. Or mistakes in my db.query cursor.

Logcat:

FATAL EXCEPTION: main
Process: com.HSC.calendarfit, PID: 10164
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.HSC.calendarfit/com.HSC.calendarfit.ExerciseList}: android.database.sqlite.SQLiteException: unrecognized token: ""Arms ORDER BY exerciseName ASC" (code 1 SQLITE_ERROR): , while compiling: SELECT exerciseName FROM exerciseList WHERE muscleGroup = "Arms ORDER BY exerciseName ASC
    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2913)
    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3048)
    at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:78)
    at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:108)
    at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:68)
    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1808)
    at android.os.Handler.dispatchMessage(Handler.java:106)
    at android.os.Looper.loop(Looper.java:193)
    at android.app.ActivityThread.main(ActivityThread.java:6669)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:493)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858)
 Caused by: android.database.sqlite.SQLiteException: unrecognized token: ""Arms ORDER BY exerciseName ASC" (code 1 SQLITE_ERROR): , while compiling: SELECT exerciseName FROM exerciseList WHERE muscleGroup = "Arms ORDER BY exerciseName ASC
    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:903)
    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:514)
    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
    at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:46)
    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1408)
    at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1255)
    at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1126)
    at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1294)
    at com.HSC.calendarfit.dbExerciseListHandler$override.getExercises(dbExerciseListHandler.java:65)
    at com.HSC.calendarfit.dbExerciseListHandler$override.access$dispatch(Unknown Source:64)
    at com.HSC.calendarfit.dbExerciseListHandler.getExercises(Unknown Source:15)
    at com.HSC.calendarfit.ExerciseList.onCreate(ExerciseList.java:43)
    at android.app.Activity.performCreate(Activity.java:7136)
    at android.app.Activity.performCreate(Activity.java:7127)
    at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1271)
    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2893)
    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3048) 
    at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:78) 
    at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:108) 
    at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:68) 
    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1808) 
    at android.os.Handler.dispatchMessage(Handler.java:106) 
    at android.os.Looper.loop(Looper.java:193) 
    at android.app.ActivityThread.main(ActivityThread.java:6669) 
    at java.lang.reflect.Method.invoke(Native Method) 
    at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:493) 
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858)

Calling adapter and methods in activity:

       dbHandler = new dbExerciseListHandler(this, null, null, 1);
        muscleCategory = "Arms";
        List<dbExerciseList> list = dbHandler.getExercises(muscleCategory);
        recyclerView = (RecyclerView) findViewById(R.id.rvExerciseList);
        emptyView = (TextView) findViewById(R.id.emptyView);

        if (list.isEmpty()){
            recyclerView.setVisibility(View.GONE);
            emptyView.setVisibility(View.VISIBLE);
        }else{
            recyclerView.setVisibility(View.VISIBLE);
            emptyView.setVisibility(View.GONE);
        }

        recyclerView.setHasFixedSize(true);
        mAdapter = new ExerciseListAdapter(list);

        layoutManager = new LinearLayoutManager(this);
        recyclerView.setLayoutManager(layoutManager);
        recyclerView.setAdapter(mAdapter);

Method in database handler to get data and create Arraylist:

public List<dbExerciseList> getExercises(String muscleGroup){
        List<dbExerciseList> list = new ArrayList<>();
        SQLiteDatabase db = this.getWritableDatabase();
        String[] columnsToUse = {COLUMN_EXERCISENAME};
        Cursor cursor = db.query(TABLE_EXERCISE, columnsToUse, COLUMN_MUSCLEGROUP + " = \"" +
                        muscleGroup, null, null, null, COLUMN_EXERCISENAME + "ASC");
        while (cursor.moveToNext()){
            String exerciseName = cursor.getString(0);
            dbExerciseList name = new dbExerciseList(exerciseName);
            list.add(name);
        }
        return list;

    }

dbExerciseList has my getters & setters.

RecyclerView Adapter:

public class ExerciseListAdapter extends RecyclerView.Adapter<ExerciseListAdapter.ExerciseListViewHolder>{

    private List<dbExerciseList> mDataset;


    public class ExerciseListViewHolder extends RecyclerView.ViewHolder {
        public TextView tvExerciseName;
        public ExerciseListViewHolder(@NonNull View itemView) {
            super(itemView);
            tvExerciseName = itemView.findViewById(R.id.tvExerciseName);
        }
    }

    public ExerciseListAdapter(List<dbExerciseList> mDataset) {
        this.mDataset = mDataset;
    }

    @Override
    public ExerciseListViewHolder onCreateViewHolder( ViewGroup viewGroup, int i) {
        TextView v = (TextView) LayoutInflater.from(viewGroup.getContext()).
                inflate(R.layout.activity_exercise_list, viewGroup, false);
        return new ExerciseListViewHolder(v);
    }

    @Override
    public void onBindViewHolder(ExerciseListViewHolder exerciseListViewHolder, int position) {
        dbExerciseList item = mDataset.get(position);
        exerciseListViewHolder.tvExerciseName.setText(item.get_exerciseName());
    }

    @Override
    public int getItemCount() {
        return mDataset.size();
    }
}

Thank you.

Share the error log.. Also, Try to add a space here: COLUMN_EXERCISENAME + " ASC".. Maybe, ASC is being append directly to end of String EXERCISENAME without any spaces... Add a space in the " ASC" — guipivoto, May 20 '19 at 17:45
Possible duplicate of [Unfortunately MyApp has stopped. How can I solve this?](https://stackoverflow.com/questions/23353173/unfortunately-myapp-has-stopped-how-can-i-solve-this) — Zoe, May 20 '19 at 17:59

score 0 · Accepted Answer · answered May 20 '19 at 19:03

Inside getExercises(), in this line:

Cursor cursor = db.query(TABLE_EXERCISE, columnsToUse, COLUMN_MUSCLEGROUP + " = \"" +
    muscleGroup, null, null, null, COLUMN_EXERCISENAME + "ASC");

what is this: \"???
This makes this string:

SELECT exerciseName FROM exerciseList WHERE muscleGroup = "Arms ORDER BY exerciseName ASC

which is syntactically wrong because of the double quotes.
Change to:

Cursor cursor = db.query(TABLE_EXERCISE, columnsToUse, COLUMN_MUSCLEGROUP 
    + " = '" + muscleGroup + "'", null, null, null, COLUMN_EXERCISENAME + "ASC");

(Was this that you wanted to do?)
But as this way is prone to sql injection, beter use this way of passing parameters to an sql statement:

String strQuery = "SELECT " + COLUMN_EXERCISENAME + " FROM " + TABLE_EXERCISE + 
    " WHERE " + COLUMN_MUSCLEGROUP + " = ? ORDER BY " + COLUMN_EXERCISENAME + " ASC";
Cursor cursor = db.rawQuery(strQuery, new String[] {muscleGroup});

This way you don't have to worry about the type of the argument you pass.

Displaying SQLite DB arraylist in RecyclerView keeps crashing

1 Answers1