What you learn:You will learn how tocreatedatabases andtables,insertandquerydatasets in the Android-built-insqlite-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:
- Create a DataBase(generally this is done just once)
- Open the DataBase
- Create a Table(generally this is done just once)
- Insert some Datasets
- Query for some Datasets
- 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
classDataBaseWork
extendsListActivity
{
private final StringMY_DATABASE_NAME = "myCoolUserDB"; private final StringMY_DATABASE_TABLE = "t_Users"; /** Called when the activity is first created. */ @Override public voidonCreate (Bundle icicle ) { super. onCreate (icicle ); /* Will hold the 'Output' we want to display at the end. */ ArrayListresults = newArrayList ( ); |
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: |
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. */
Cursorc = 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 thegetColumnIndex(String);-method of the Cursor:
| Java: |
|
/* Get the indices of the Columns we will need */
intfirstNameColumn = c. getColumnIndex ( "FirstName" ); intageColumn = c. getColumnIndex ( "Age" ); /* Check if our result was valid. */ if (c != null ) { /* Check if at least one Result was returned. */ if (c. first ( ) ) { inti = 0; /* Loop through all Results */ do { i++; /* Retrieve the values of the Entry * the Cursor is pointing to. */ StringfirstName = c. getString (firstNameColumn ); intage = 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*/ StringageColumName = 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
(
FileNotFoundExceptione
)
{
} finally { if (myDB != null ) myDB. close ( ); } |
8.)In the end,display our Entries:
| Java: |
|
this.
setListAdapter
(
newArrayAdapter
(
this, android. R. layout. simple_list_item_1_small,results ) ); } } |
