Example
Here is an example of a method that would live inside a SQLiteOpenHelper subclass. It uses the searchTerm String to filter the results, iterates through the Cursor's contents, and returns those contents in a List of Product Objects.
First, define the Product POJO class that will be the container for each row retrieved from the database:
public class Product {
long mId;
String mName;
String mDescription;
float mValue;
public Product(long id, String name, String description, float value) {
mId = id;
mName = name;
mDescription = description;
mValue = value;
}
}
Then, define the method that will query the database, and return a List of Product Objects:
public List<Product> searchForProducts(String searchTerm) {
// When reading data one should always just get a readable database.
final SQLiteDatabase database = this.getReadableDatabase();
final Cursor cursor = database.query(
// Name of the table to read from
TABLE_NAME,
// String array of the columns which are supposed to be read
new String[]{COLUMN_NAME, COLUMN_DESCRIPTION, COLUMN_VALUE},
// The selection argument which specifies which row is read.
// ? symbols are parameters.
COLUMN_NAME + " LIKE ?",
// The actual parameters values for the selection as a String array.
// ? above take the value from here
new String[]{"%" + searchTerm + "%"},
// GroupBy clause. Specify a column name to group similar values
// in that column together.
null,
// Having clause. When using the GroupBy clause this allows you to
// specify which groups to include.
null,
// OrderBy clause. Specify a column name here to order the results
// according to that column. Optionally append ASC or DESC to specify
// an ascending or descending order.
null
);
// To increase performance first get the index of each column in the cursor
final int idIndex = cursor.getColumnIndex(COLUMN_ID);
final int nameIndex = cursor.getColumnIndex(COLUMN_NAME);
final int descriptionIndex = cursor.getColumnIndex(COLUMN_DESCRIPTION);
final int valueIndex = cursor.getColumnIndex(COLUMN_VALUE);
try {
// If moveToFirst() returns false then cursor is empty
if (!cursor.moveToFirst()) {
return new ArrayList<>();
}
final List<Product> products = new ArrayList<>();
do {
// Read the values of a row in the table using the indexes acquired above
final long id = cursor.getLong(idIndex);
final String name = cursor.getString(nameIndex);
final String description = cursor.getString(descriptionIndex);
final float value = cursor.getFloat(valueIndex);
products.add(new Product(id, name, description, value));
} while (cursor.moveToNext());
return products;
} finally {
// Don't forget to close the Cursor once you are done to avoid memory leaks.
// Using a try/finally like in this example is usually the best way to handle this
cursor.close();
// close the database
database.close();
}
}
