SQLite "select * from table" doesnt work in android studio

Question

I create a DatabaseHelper class that extends SQLiteOpenHelper in Android Studio. The getBuildingData function doesn't return any data. The data table creates and all data inserts into table successfully, but the cursor in the getBuildingData function doesn't return data.

Here is the code I have in the class

package com.example.campus_map;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.content.Context;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.os.Build;

import androidx.annotation.RequiresApi;

import java.io.ByteArrayOutputStream;
import java.util.ArrayList;
import java.util.Base64;

public class DatabaseHelper extends SQLiteOpenHelper {
    public static final String DATABASE_NAME = "NDSUMap.db";
    public static final String TABLE_NAME = "Building_table";
    public static final String Building_col1 = "ID";
    public static final String Building_col2 = "Name";
    public static final String Building_col3 = "Image";
    public static final String Building_col4 = "AltName";
    public static final String Building_col5 = "Dept";
    public static final String Building_col6 = "Info";
    public static Context context;

public DatabaseHelper(Context context) {
    super(context, DATABASE_NAME, null, 1);
    int imageRes[] = {R.drawable.aghill, R.drawable.library, R.drawable.minard, R.drawable.qbb, R.drawable.union};
    this.insertData("A. Glenn Hill Center", convertImg(context, imageRes[0]), "AGHill, STEM Building", "Computer Labs", "The state-funded building houses " +
            "classrooms, labs, and study areas, with a focus on science, technology, engineering and mathematics-related courses.");
    this.insertData("Library", convertImg(context, imageRes[1]), "", "Center for Writers, Computer Labs, Libraries, Office of the Ombudsperson", "");
    this.insertData("Minard Hall", convertImg(context, imageRes[2]), "", "Anthropology, College of Arts, Humanities and Social Sciences, College of Science and Mathematics," +
            "Department of Communication, Computer Labs, Emergency Management, English, History, Mathematics, Modern Language, Philogophy, Psychology, Sociology","");
    this.insertData("Quentin Burdick Building", convertImg(context, imageRes[3]), "QBB", "Computer Labs, Computer Science, Scomputer Systems Institute, Information Technology Services," +
            "ITS Help Desk, Technology Learning and Media Center, Upper Great Plains Transportation Institute", "");
    this.insertData("Memorial Union", convertImg(context, imageRes[4]), "", "Bookstore, Card Center, Design&Sign, One Stop, Student Activities Office, " +
            "Student Government, Thundar's Game Room, Union Dining Center, US Bank", "");
}

@Override
public void onCreate(SQLiteDatabase db) {
    String query = "create table " + TABLE_NAME + "(" + Building_col1 + " INTEGER PRIMARY KEY AUTOINCREMENT, " + Building_col2 + " TEXT, "
            + Building_col3 + " BLOB, " + Building_col4 + " TEXT, " + Building_col5 + " TEXT, " + Building_col6 + " TEXT)";
    db.execSQL(query);

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    String query = "DROP TABLE IF EXISTS " + TABLE_NAME;
    db.execSQL(query);
    onCreate(db);
}

public boolean insertData(String name, byte[] img, String altName, String dept, String info){
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(Building_col2, name);
    contentValues.put(Building_col3, img);
    contentValues.put(Building_col4, altName);
    contentValues.put(Building_col5, dept);
    contentValues.put(Building_col6, info);
    long result = db.insert(TABLE_NAME, null, contentValues);
    if(result == -1)
        return false;
    else
        return true;
}

@RequiresApi(api = Build.VERSION_CODES.O)
public ArrayList<ArrayList<String>> getBuildingData()
{
    SQLiteDatabase db = this.getReadableDatabase();
    ArrayList<ArrayList<String>> arrayList = new ArrayList<>();
    ArrayList<String> colData = new ArrayList<>();
    //create cursor to select all value
    Cursor cursor = db.rawQuery("select * from " + TABLE_NAME, null);
    //cursor.moveToFirst();
    while(cursor.moveToNext())
    {
        colData.add(String.valueOf(cursor.getInt(cursor.getColumnIndex(Building_col1))));
        colData.add(cursor.getString(cursor.getColumnIndex(Building_col2)));
        colData.add(Base64.getEncoder().encodeToString(cursor.getBlob(cursor.getColumnIndex(Building_col3))));
        colData.add(cursor.getString(cursor.getColumnIndex(Building_col4)));
        colData.add(cursor.getString(cursor.getColumnIndex(Building_col5)));
        colData.add(cursor.getString(cursor.getColumnIndex(Building_col6)));
        arrayList.add(colData);
    }
    cursor.close();
    return arrayList;
}

private byte[] convertImg(Context context, int res)
{
    Bitmap bitmap = BitmapFactory.decodeResource(context.getResources(), res);
    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    bitmap.compress(Bitmap.CompressFormat.PNG, 100, bos);
    return bos.toByteArray();
}
}

I did google a lot, but I cannot find the correct solution for this.

Why don't you use Room library? It's much easier to handle such things plus you get for free all the refresh logic with LiveData and Observables. It may look hard in the beginning but after its way more easier! https://developer.android.com/codelabs/android-room-with-a-view-kotlin — madlymad, Dec 07 '20 at 08:20

IntelliJ Amiya · Answer 1 · 2020-12-07T08:21:49.450

moveToFirst() - This method will return false if the cursor is empty.

moveToNext() - This method will return false if the cursor is already past the last entry in the result set.

Try with

if (cursor.moveToFirst()) {
            
            do 
            {
            
                colData.add(String.valueOf(cursor.getInt(cursor.getColumnIndex(Building_col1))));
                colData.add(cursor.getString(cursor.getColumnIndex(Building_col2)));
                colData.add(Base64.getEncoder().encodeToString(cursor.getBlob(cursor.getColumnIndex(Building_col3))));
                colData.add(cursor.getString(cursor.getColumnIndex(Building_col4)));
                colData.add(cursor.getString(cursor.getColumnIndex(Building_col5)));
                colData.add(cursor.getString(cursor.getColumnIndex(Building_col6)));
                arrayList.add(colData);
            } 
            
            while (cursor.moveToNext());
 }

Right after the rawQuer() is executed there is no difference between the use of: `while(cursor.moveToNext()) {...}` and `if (cursor.moveToFirst()) { do {...} while (cursor.moveToNext());}`. so your code is equivalent to the OP's code. — forpas, Dec 07 '20 at 17:07

SQLite "select * from table" doesnt work in android studio

1 Answers1