Link to home
Start Free TrialLog in
Avatar of derrida
derrida

asked on

android java how to loop a database results

Hi
i'm new to java and android. i have a sqlite database, i have this code in my database class, to get all records:
    public Cursor getAllQuotes(){
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor res = db.rawQuery("SELECT * FROM quotes a LEFT JOIN authors b ON b.author_id=a.quote_author", null);
        return res;
    }

Open in new window


then in my main activity i have a list i need to populate with the results:
    private ArrayList<DataObject> getTheData(){
        ArrayList res = new ArrayList<DataObject>();
        Cursor dbresults = Qdb.getAllQuotes();

    }

Open in new window


now i don't understand how to loop the dbresults and send author name and quote to the dataobject. i need to pass the author name and quote so seem to me i need something like:
DataObject obj = new DataObject("author name","the quote");
but that should be within a loop and somehow can't figure out how exactly to do it.

best regards
Avatar of GuruJava
GuruJava

Hi Derrida,

Here you go ...

public void getData() {
    public String[] getData() {
        String selectQuery = "SELECT * FROM quotes a LEFT JOIN authors b ON b.author_id=a.quote_author;
              SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
                 String[] data = null;
        if (cursor.moveToFirst()) {
            do {
               // get  the  data into array,or class variable
            } while (cursor.moveToNext());
        }
        db.close();
        return data;
    }

}

Open in new window

Avatar of derrida

ASKER

hi
i have tried to adjust what you suggested to my needs: i need to return and arraylist of my dataobject. but i get this error message:
Unable to start activity ComponentInfo{com.example.ronsh.sharequotes/com.example.ronsh.sharequotes.MainActivity}: java.lang.IllegalStateException: Couldn't read row 0, col -1 from CursorWindow.  Make sure the Cursor is initialized correctly before accessing data from it.

on my onCreate method i have this to initialize my db class:
Qdb = new DbHelper(this);

and this is how i tried to adjust your code with my needs:
    public ArrayList<DataObject> getData() {

            ArrayList res = new ArrayList<DataObject>();
            String selectQuery = "SELECT * FROM quotes a LEFT JOIN authors b ON b.author_id=a.quote_author";
            SQLiteDatabase db = Qdb.getReadableDatabase();
            Cursor cursor = db.rawQuery(selectQuery, null);

            if (cursor.moveToFirst()) {
                do {
                    // get  the  data into array,or class variable
                    DataObject obj = new DataObject(
                            cursor.getString(cursor.getColumnIndex("first_name")),
                            cursor.getString(cursor.getColumnIndex("quote"))
                    );
                    res.add(obj);
                } while (cursor.moveToNext());
            }
            db.close();
            return res;


    }

Open in new window

Avatar of derrida

ASKER

i have moved the method to my database helper class and noticed i didn't used the right column name in the database, so fixed it. now i get this error:
Unable to start activity ComponentInfo{com.example.ronsh.sharequotes/com.example.ronsh.sharequotes.MainActivity}: android.database.CursorIndexOutOfBoundsException: Index -1 requested, with a size of 2

when i try the query in the firefox addon for sqlite i do get the results
Avatar of derrida

ASKER

ok moved this line :
ArrayList res = new ArrayList<DataObject>();

after the: getReadableDatabase

but on the app i see the second name twice. so my card views show only the last results twice

so in the loop i put:
                Log.d("CHECKDB",cursor.getString(cursor.getColumnIndex("first_name")));

and i see both authors name.

any idea?
SOLUTION
Avatar of GuruJava
GuruJava

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of derrida

ASKER

where do i return res?
Avatar of derrida

ASKER

added it after the try catch ,got a red balb made res available and still getting only the last results several times
after finally return res and maybe you need to take res initialization out or try block.

and for wrong value put debug and see.
Avatar of derrida

ASKER

hi
so this is what i have now:
    public ArrayList<DataObject> getData() {
        SQLiteDatabase db = this.getReadableDatabase();
        ArrayList res = null;
        try {
            res = new ArrayList<DataObject>();
            String selectQuery = "SELECT * FROM quotes a LEFT JOIN authors b ON b.author_id=a.quote_author";

            Cursor cursor = db.rawQuery(selectQuery, null);

            if (cursor != null) {
                if (cursor.moveToFirst()) {
                    do {

                        Log.d("CHECKDB",cursor.getString(cursor.getColumnIndex("first_name")) + " " + cursor.getString(cursor.getColumnIndex("last_name")));
                        Log.d("CHECKDB2",cursor.getString(cursor.getColumnIndex("quote_text")));
                        DataObject obj = new DataObject(
                                cursor.getString(cursor.getColumnIndex("first_name")) + " " + cursor.getString(cursor.getColumnIndex("last_name")),
                                cursor.getString(cursor.getColumnIndex("quote_text"))
                        );
                        res.add(obj);

                    } while (cursor.moveToNext());

                }

            }
        } catch (SQLiteException se) {
            Log.e(getClass().getSimpleName(), "Could not create or Open the database");
        } finally {
            if (db != null)
                db.close();
        }

        return res;
    }

Open in new window


the log show all the right results. but on my cardviews i get the last results the right amount of cards.

now, before i connected to the database and entered values with a simple loop i had this:
        for (int index = 0; index < 5; index++){
            DataObject obj = new DataObject("the name" + index ,"the quote" + index);
            res.add(index,obj);
        }
        return res;

Open in new window


what am i missing?
Avatar of derrida

ASKER

this is my dataobject:
public class DataObject {


    private static String author;
    private static String thequote;

    public DataObject(String authorName,String quote) {
        author = authorName;
        thequote = quote;
    }


    public static String getAuthor() {
        return author;
    }

    public static void setAuthor(String author) {
        DataObject.author = author;
    }

    public static String getThequote() {
        return thequote;
    }

    public static void setThequote(String thequote) {
        DataObject.thequote = thequote;
    }


}

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of derrida

ASKER

i try your dataobject and get:
Error:(23, 9) error: non-static variable this cannot be referenced from a static context

and point to this line:
this.author = author;
remove static from  setAuthor method

 public void setAuthor(String author) {
        this.author = author;
    }
Avatar of derrida

ASKER

great help saved me