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
AndroidJava

Avatar of undefined
Last Comment
derrida

8/22/2022 - Mon
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

ASKER
derrida

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

ASKER
derrida

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
derrida

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
GuruJava

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
derrida

where do i return res?
ASKER
derrida

added it after the try catch ,got a red balb made res available and still getting only the last results several times
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
GuruJava

after finally return res and maybe you need to take res initialization out or try block.

and for wrong value put debug and see.
ASKER
derrida

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?
ASKER
derrida

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

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
derrida

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;
GuruJava

remove static from  setAuthor method

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

great help saved me
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.