Working with the SQLite-Database - Cursors

What you learn: You will learn how to create databases and tables, insert and query datasets in the Android-built-in SQLite-DataBase-Server.

Difficulty: 1 of 5 

 Questions/Problems: Simply post below...

What it will look like:

Description:
We'll need to to the following things:

1. Create a DataBase (generally this is done just once)
2. Open the DataBase
3. Create a Table (generally this is done just once)
4. Insert some Datasets
5. Query for some Datasets
6. Close the Database

0.) So lets work it out:
We first do some setup. Declaring the DataBases/Tables we are using as final should always be preferred before typing the name to every single statement. (Changes are a lot easier !).

Java:

public class DataBaseWork extends ListActivity { private final String MY_DATABASE_NAME = "myCoolUserDB"; private final String MY_DATABASE_TABLE = "t_Users"; /** Called when the activity is first created. */ @Override public void onCreate(Bundle icicle) { super.onCreate(icicle); /* Will hold the 'Output' we want to display at the end. */ ArrayList results = new ArrayList();

1.) So lets create the DataBase:

Java:

SQLiteDatabase myDB = null; try { /* Create the Database (no Errors if it already exists) */  this.createDatabase(MY_DATABASE_NAME, 1, MODE_PRIVATE, null);

2.) Having created the DataBase we want to open it:

Java:

/* Open the DB and remember it */ myDB = this.openDatabase(MY_DATABASE_NAME, null);

3.) Now we create a simple Table with just four columns:

Java:

/* Create a Table in the Database. */  myDB.execSQL("CREATE TABLE IF NOT EXISTS " + MY_DATABASE_TABLE + " (LastName VARCHAR, FirstName VARCHAR," + " Country VARCHAR, Age INT(3));");

4.) Put two DataSets to the recently created Table:

Java:

/* Add two DataSets to the Table. */ myDB.execSQL("INSERT INTO " + MY_DATABASE_TABLE + " (LastName, FirstName, Country, Age)" + " VALUES ('Gramlich', 'Nicolas', 'Germany', 20);"); myDB.execSQL("INSERT INTO " + MY_DATABASE_TABLE + " (LastName, FirstName, Country, Age)" + " VALUES ('Doe', 'John', 'US', 34);");

5.) Having written some DataSets to the Table, we would want to receive them back somewhen. Thr result of a query is a Cursor that can move over all the results returned by the query. We apply Projection (Just the Specified Columns) and Selection (WHERE ...) to it and a LIMIT. Just as we would do in any other SQL-"Dialect":

Java:

/* Query for some results with Selection and Projection. */ Cursor c = myDB.query("SELECT FirstName,Age" + " FROM " + MY_DATABASE_TABLE  + " WHERE Age > 10 LIMIT 7;", null);

6.) Now having queried, we retrieve the ColumIndexes of two Columns calling the getColumnIndex(String);-method of the Cursor:

Java:

/* Get the indices of the Columns we will need */ int firstNameColumn = c.getColumnIndex("FirstName"); int ageColumn = c.getColumnIndex("Age"); /* Check if our result was valid. */ if (c != null) { /* Check if at least one Result was returned. */  if (c.first()) { int i = 0; /* Loop through all Results */  do { i++; /* Retrieve the values of the Entry * the Cursor is pointing to. */ String firstName = c.getString(firstNameColumn); int age = c.getInt(ageColumn); /* We can also receive the Name  * of a Column by its Index. * Makes no sense, as we already  * know the Name, but just to shwo we can  */ String ageColumName = c.getColumnName(ageColumn); /* Add current Entry to results. */ results.add("" + i + ": " + firstName  + " (" + ageColumName + ": " + age + ")"); } while (c.next()); } }

7.) Finally close the DataBase (if it has been opened):

Java:

} catch (FileNotFoundException e) { } finally { if (myDB != null) myDB.close(); }

8.) In the end, display our Entries:

Java:

this.setListAdapter(new ArrayAdapter(this, android.R.layout.simple_list_item_1_small, results)); } }