Making a better android sqlite query

Question

Currently I have a query that looks at the table and returns the newest row, reads that row and sets the string to the value in the chosen column index.

At the moment I have 9 columns so I have end up making 9 methods for returning each column just to return a string and set it to each individual textview.

Do I make a cursor to return the row and then set the column index when I am setting the textviews. Something like

            cursor.getString(1)

This is my current query

            public String getName() {
    // TODO Auto-generated method stub
    String[] columns = new String[] { KEY_ROWID, KEY_NAME, KEY_COLUMN2,
            KEY_COLUMN3, KEY_COLUMN4, KEY_COLUMN5, KEY_COLUMN6, KEY_COLUMN7, KEY_COLUMN8 };
    Cursor c = mDb.query(true, DATABASE_TABLE, columns, null, null, null,
            null, "_id DESC", "1");

    if (c != null) {
        c.moveToFirst();
        String name = c.getString(1);
        return name;
    }

    return null;
}

What about returning string array, map or custom object? Returning cursor is not a good idea. — barti_ddu
– barti_ddu, Commented Feb 20, 2012 at 19:13

Pedantic · Accepted Answer · 2012-02-21 06:49:03Z

2

You could easily change getName() to something field-agnostic, such as:

public String getField(String fieldName) {
    ...
    String s = cursor.getString(cursor.getColumnIndexOrThrow(fieldName));
}

And use it like:

String s = mDb.getField(mDb.KEY_NAME);

A problem with this approach is that you can't use getString() for a numerical column, so you wind up with multiple methods for each datatype supported in your table. A way to tackle this is to look into the cursor's getType() method or just return a complete data structure from your database access methods. Say you have:

DB Column        Field Type
FOO              varchar
BAR              integer
BAZ              varchar

If you define a class, say, MyRowMapper you can do something like this:

public MyRowMapper getRow(String... queryParameters) {
    //query table based on queryParameters
    MyRowMapper mapper = null;
    if (cursor.moveToFirst()) {
        String foo = cursor.getString(cursor.getColumnIndexOrThrow("FOO"));
        Integer bar = cursor.getInteger(cursor.getColumnIndexOrThrow("BAR"));
        String baz = cursor.getString(cursor.getColumnIndexOrThrow("BAZ"));

        mapper = new MyRowMapper(foo, bar, baz);            
    }
    cursor.close(); // NEVER FORGET TO CLOSE YOUR CURSOR!
    return mapper;
 }

This is just abstract mailercode, so take the syntaxt with a grain of salt. But hopefully you get the idea.

edited Feb 21, 2012 at 6:49

answered Feb 20, 2012 at 19:22

Pedantic

5,0222 gold badges26 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Argyle Over a year ago

@user1088701 If you are only getting a single value out of the query, you should look into using SQLiteStatement prepared statements as they will be faster to execute. However, it's better than that to grab everything en masse like you see in this answer.

Tonious Over a year ago

I'm getting - The method getString(int) in the type Cursor is not applicable for the arguments (String) on String s = cursor.getString(fieldName);

Pedantic Over a year ago

Oh, sorry about that I meant cursor.getString(cursor.getColumnIndexOrThrow(KEY_NAME)); I'll fix the answer.

Collectives™ on Stack Overflow

Making a better android sqlite query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related