We help IT Professionals succeed at work.

android java how to loop a database results

derrida
derrida asked
on
412 Views
Last Modified: 2016-02-25
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
Comment
Watch Question

Top Expert 2013

Commented:
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

Author

Commented:
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

Author

Commented:
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

Author

Commented:
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?
Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
where do i return res?

Author

Commented:
added it after the try catch ,got a red balb made res available and still getting only the last results several times
Top Expert 2013

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

and for wrong value put debug and see.

Author

Commented:
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?

Author

Commented:
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

Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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;
Top Expert 2013

Commented:
remove static from  setAuthor method

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

Author

Commented:
great help saved me

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.