Getting data from Sqlite using custom object ArrayList?

Question

This is code for Employee class.

public class Employee {
String name;
String email;
byte[] picture;

public Employee(String name, String email, byte[] picture) {
    this.name = name;
    this.email = email;
    this.picture = picture;
}
}

And this is my code to get data from Database which is currently not working because i could'nt figure out how to get these three things (String name, email and byte array image) from database. However i have successfully inserted all three things into Sqlite database.

**Edit : ** This is my database class complete code.

package com.cplusplusapp.rashidfaheem.hybridsoftwaresolutions.hbss.rashidfaheem.image2database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import java.util.ArrayList;
import java.util.List;



public class Sqlite_database extends SQLiteOpenHelper {


private static final String DATABASE_NAME="myCustomerDatabase";
private static final String DATABASE_TABLE="Customers";
private static final int DATABASE_VERSION=1;

public static final String KEY_ROWID="id";
public static final String KEY_Name="customer_name";
public static final String KEY_Email="customer_email";
public static final String KEY_IMAGE="image";

private SQLiteDatabase ourDatabase;

ArrayList<Employee> elist;

public Sqlite_database(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
    sqLiteDatabase.execSQL("CREATE TABLE " + DATABASE_TABLE + "(" +
            KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            KEY_Name + " TEXT, " +
            KEY_Email + " TEXT, " +
            KEY_IMAGE + " BLOB);"
    );

}

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



public List<Employee> getImage(){

    List<Employee> employee_list = new ArrayList<>();
    ourDatabase = this.getReadableDatabase();
    String[] field = {KEY_Name,KEY_Email,KEY_IMAGE};
    Cursor c = ourDatabase.query(DATABASE_TABLE, field, null, null, null, null, null);

    int iname = c.getColumnIndex(KEY_Name);
    int iemail = c.getColumnIndex(KEY_Email);
    int image = c.getColumnIndex(KEY_IMAGE);


    for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()){
        String name = c.getString(iname);
        String email = c.getString(iemail);
        byte[] pic = c.getBlob(image);
        employee_list.add(new Employee(name, email, pic));

    }

    return employee_list;
}




public long createImageEntry(String name, String email, byte[] image) {
    ourDatabase=this.getWritableDatabase();
    ContentValues cv=new ContentValues();
    cv.put(KEY_Name, name);
    cv.put(KEY_Email, email);
    cv.put(KEY_IMAGE, image);

    return ourDatabase.insert(DATABASE_TABLE, null, cv);

}
}

And this is code on button click to get name and email from that return arraylist and show it through textviews. But getting error. java.lang.IllegalStateException: Couldn't read row 0, col 0 from CursorWindow. Make sure the Cursor is initialized correctly before accessing data from it.

 btnView.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View view) {
            sql = new Sqlite_database(MainActivity.this);
            List<Employee> employees = sql.getImage();

            for (Employee e : employees) {
                r_name = r_name + e.name;
                r_email = r_email + e.email;

            }

            tvName.setText(r_name);
            tvEmail.setText(r_email);
        }
    });

I have added three things in my Sqlite database. Employee name, Employee email and employee picture (by converting pic into byte[]). Now i want to retrieve these three things using Employee class. I know its possible but don't know how. — Rashid Faheem, Jun 20 '17 at 14:54
`I have added three things ...` Which "things"?! I guess you added three new columns (or fields, if you prefer) — Phantômaxx, Jun 20 '17 at 16:15
Three things = 2 String values + 1 image in form of byte array. — Rashid Faheem, Jun 20 '17 at 17:14
And you don't really want to tell us what these mysterious things are? Row values? Columns? Tables? Sandwiches? — Phantômaxx, Jun 20 '17 at 17:35
Maybe i am not good at english when it comes to make someone understand. Sorry for that. Maybe you can understand better with code. Updated my code. — Rashid Faheem, Jun 20 '17 at 17:59

0xDEADC0DE · Accepted Answer · 2017-06-20T17:17:33.603

3

You are assigning a new Employee object to employee_list each and every time you go through the for-loop

Change your employee_list to this

List<Employee> employee_list = new ArrayList<Employee>();

You can add an object to that list like this at the end of your for-loop

employee_list.add(new Employee(name, email, pic));

The complete code would look like this

public List<Employee> getImage(){

    List<Employee> employee_list = new ArrayList<Employee>();
    ourDatabase = this.getReadableDatabase();
    String[] field = {KEY_Name,KEY_Email,KEY_IMAGE};
    Cursor c = ourDatabase.query(DATABASE_TABLE, field, null, null, null, null, null);

    int iname = c.getColumnIndex(KEY_Name);
    int iemail = c.getColumnIndex(KEY_Email);
    int image = c.getColumnIndex(KEY_IMAGE);

    for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()){
        String name = c.getString(iname);
        String email = c.getString(iemail);
        byte[] pic = c.getBlob(image);
        employee_list.add(new Employee(name, email, pic));

    }

    return employee_list;
}

You can then use it like this

List<Employee> employees = getImage();

for (Employee employee : employees) {
    // You can use employee here that represents an Employee entry in the list
}

edited Jun 20 '17 at 17:17

answered Jun 20 '17 at 14:42

0xDEADC0DE

2,453
1
17
22

Can you please edit my full code to achieve my goal? This is what i want to do. I have added three things in my Sqlite database. Employee name, Employee email and employee picture (by converting pic into byte[]). Now i want to retrieve these three things using Employee class. I know its possible but don't know how. – Rashid Faheem Jun 20 '17 at 14:56
Updated my answer. From the looks of it, you are already selecting the name, email & picture, but you are not adding it to a list. Instead you override the same variable and only return the last thing – 0xDEADC0DE Jun 20 '17 at 14:59
Thanx. Can u please tell me how to extract name, email as string and image as byte array from this returned arraylist? – Rashid Faheem Jun 20 '17 at 15:04
You can use a for-loop to loop over your list and access the variables in the loop – 0xDEADC0DE Jun 20 '17 at 15:45
Care to add it in complete code? – Rashid Faheem Jun 20 '17 at 17:15
Added some code – 0xDEADC0DE Jun 20 '17 at 17:17
I am getting this error while trying to get data from table. java.lang.IllegalStateException: Couldn't read row 0, col 0 from CursorWindow. Make sure the Cursor is initialized correctly before accessing data from it. Added code in my question above. – Rashid Faheem Jun 20 '17 at 18:10
In that case there’s nothing in your database – 0xDEADC0DE Jun 20 '17 at 18:44
I used this code to add data in database and it says inserted successfully. name = etName.getText().toString(); email = etEmail.getText().toString(); byte[] pic_array = getBytes(bmp); sql = new Sqlite_database(MainActivity.this); result_local = sql.createImageEntry(name, email, pic_array); if (result_local != -1) { Toast.makeText(MainActivity.this, "inserted successfully", Toast.LENGTH_SHORT).show(); } – Rashid Faheem Jun 20 '17 at 18:52

Getting data from Sqlite using custom object ArrayList?

1 Answers1