Wednesday, 21 September 2011

Cursor - Making query in the SQLite database in Android

1. Intoduction
As I have explained in the post about the manage of SQLite database in this post, the SELECT sentence of SQL could be done with the method query or rawQuery. All them returns a Cursor object. The purpose of this post is to manage and obtain the value from it.

2. Moving between values
The cursor works like a collection of values. It could be empty or it could contain one or more values. The first thing to do is move between the values of a cursor. Here are several methods to do it:
  • boolean moveToFirst()
Move to the first element of the cursor. Returns false if the cursor is empty.
  • boolean moveToLast()
Move to the last element of the cursor. Returns false if the cursor is empty.
  • boolean moveToNext()
Move to the next element of the cursor. Returns false if it is in the last position
  • boolean moveToPrevious()
Move to the previous element of the cursor. Returns false if it is in the first position
  • boolean moveToPosition(int position)
Move to a position indicated. Returns false if the position is not reachable.
Notice that the position of the cursor starts with 0.

3. Query information
  • int getColumnCount()
Obtain the total number of columns. Notice that the columns sta
  • int getColumnIndex(String columnName)
Obtain the index of the column by a give column name. This method is very important since all the methods to obtain the values of the raws depends on the columnIndex
  • String getColumnName (int columnIndex)
Obtain the name of the column by the index of the column. Note that the column starts with zero.
  • String[] getColumnNames ()
Obtain the name of all the columns of the raw.
  • int getCount()
Returns the total number of the rows of the cursor
  • int getPosition()
Get the actual position of the cursor.

4 Obtain a values
There is a set of get methods that returns the value of a column regarding the actual cursor. Notice if the content of the column is null or the type don't match, it will throw an exception.
  • byte[] getBlob (int columnIndex)
  • double getDouble (int columnIndex)
  • float getFloat (int columnIndex)
  • int getInt (int columnIndex)
  • long getLong (int columnIndex)
  • String getString (int columnIndex)
  • int getType (int columnIndex)
Notice that the method getBool (int columnIndex) doesn't exist. This is because SQLite save booleans as integer (1 = true, 0 = false).

A good way to get boolean is use the getInt (int columnIndex) method and then check if the valus is bigger than 0.
  • boolean value = cursor.getInt (boolean_column_index) > 0;

5. Other useful methods
  • boolean isFirst()
Check if the position of the cursor is the first one.
  • boolean isBeforeFirst()
Check if the position of the cursor is before the first one. This is possible because when the cursor is set for the first time, its position is "-1".
    • boolean isLast()
    Check if the position of the cursor is the last one.
    • boolean isAfterLast()
    Check if the position of the cursor is after the last one.
    • boolean isClosed()
    Check if the cursor has been closed. If the cursor is closed, all the resources will be released, thus the cursor is completely invalid.
    • boolean isNull (int columnIndex)
    Check if the content of column index is null or not.

    Additionally the user can close the cursor to make it completely invalid or desactivate it until it is requeried, but they are not necessary to manage the cursor in a simple way.

    For more information:
    Android developers official guide: http://developer.android.com/reference/android/database/Cursor.html

    No comments:

    Post a Comment