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