CODEWITHSUNDEEP
javaandroidsqlarrayssqlite
Nima
Nima

Reputation: 6563

How to retrieve a column from sqlite and store it into an array?

Earlier I asked this question ( and after some googling I read Using your own SQLite database for Android app.

So, I have a simple database which contains 2 columns (_id and quotes). What I need to do is to grab all the quotes from the db and show them 1 by 1 in a TextView.

I have this in my DataBaseHelper.java:

private SQLiteDatabase myDataBase; 
public void openDataBase() throws SQLException{

    //Open the database
    String myPath = DB_PATH + DB_NAME;
    myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
    Cursor mCursor = myDataBase.query(true, DB_NAME, null, new String[] + "=" + "_id", null,
              null, null, null, null);
        if (mCursor != null) {
          mCursor.moveToFirst();
        }

What should I do? Thanks

Upvotes: 1

Views: 3686

Answers (3)

WarrenFaith
WarrenFaith

Reputation: 57672

The solution from Johan is ok, but you should think about using logical objects. Mostly one row of your table represents a logical object. You should fetch a database row into one object so that you can easily work with them.

A sample for a Bookmark object:

public static List<Bookmark> getBookmarks(SQLiteDatabase db, long emagId) {
    List<Bookmark> bookmarksList = null;
    Cursor cursor = null;
    try {
        // WHERE clause for the SELECT query
        final String where = Bookmark.EMAG_ID + " = " + emagId;

        cursor = db.query(Bookmark.TABLE, new String[] {}, where, null, null, null, null);

        bookmarksList = new ArrayList<Bookmark>();

        while (cursor.moveToNext()) {
            Bookmark bookmark = new Bookmark();
            bookmark.id = cursor.getLong(cursor.getColumnIndex(Bookmark.ID));
            bookmark.emag_id = cursor.getLong(cursor.getColumnIndex(Bookmark.EMAG_ID));
            bookmark.page_number = cursor.getInt(cursor.getColumnIndex(Bookmark.PAGE_NUMBER));
            bookmark.article_id = cursor.getLong(cursor.getColumnIndex(Bookmark.ARTICLE_ID));

            bookmark.ref_html = cursor.getString(cursor.getColumnIndex(Bookmark.REF_HTML));
            bookmark.ref_text = cursor.getString(cursor.getColumnIndex(Bookmark.REF_TEXT));

            bookmark.ref_html = bookmark.ref_html;
            bookmark.ref_text = "Page: " + bookmark.page_number;

            bookmark.resource_id = cursor.getLong(cursor.getColumnIndex(Bookmark.RESOURCE_ID));
            bookmark.resource_type = cursor.getInt(cursor.getColumnIndex(Bookmark.RESOURCE_TYPE));
            bookmark.source_type = cursor.getInt(cursor.getColumnIndex(Bookmark.SOURCE_TYPE));
            bookmark.content_path = cursor.getString(cursor.getColumnIndex(Bookmark.CONTENT_PATH));

            bookmarksList.add(bookmark);
        }
    } catch (Exception e) {
        Log.e(LOG_TAG, "::getBookmarksForEmagId", e);
    } finally {
        if (cursor != null) {
            cursor.close();
        }
    }
    return bookmarksList;
}

I saw this code in your question new String[] + "=" + "_id". I think this will not work...

Upvotes: 2

Johan Lindkvist
Johan Lindkvist

Reputation: 1784

This function is how I fetch data from a database where "db" is the database instance. This function will return an 2D String array where [0][x] is the first row it fetched and [1][x] is the second, etc; and the x is the columns, and in your case [0][1] would be the first quote in your database. 

public String[][] customQuery(String query) {
    Cursor cur = null;
    try {
        cur = db.rawQuery(query,null);
    } catch(SQLException e) {
        Log.d("DATABASE", e.toString());
    }

    String data[][] = new String[cur.getCount()][cur.getColumnCount()];

    if (cur != null) {
        int i = 0;
        while (cur.moveToNext()) {
            int j = 0;
            while (j < cur.getColumnCount()) {
                data[i][j] = cur.getString(j);
                j++;
            }
            i++;
            cur.moveToNext();
        }
        cur.close();
    }
    return data;
}

Upvotes: 3

gtdevel
gtdevel

Reputation: 1513

I would use a rawQuery to get a cursor and then scroll through it. Find the method in the SQLiteDatabase documentation.

As for the TextView. Declare textview in your xml file. Keep a variable that remembers what position of the cursor you are on. Once your rawQuery gives you the right columns then there are many methods you can use to find elements in it. Check here: Cursor.

Upvotes: 1

Related Questions