CODEWITHSUNDEEP
javaandroiddatabasesqlite
Tonious
Tonious

Reputation: 53

Making a better android sqlite query

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

Upvotes: 0

Views: 369

Answers (1)

Pedantic
Pedantic

Reputation: 5022

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.

Upvotes: 2

Related Questions