What you learn:You will learn how tocreatedatabases andtables,insertandquerydatasets in the Android-built-inSQLite-DataBase-Server.
Difficulty:1 of 5Questions/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: |
publicclassDataBaseWorkextendsListActivity{
privatefinalStringMY_DATABASE_NAME ="myCoolUserDB"; privatefinalStringMY_DATABASE_TABLE ="t_Users"; /** Called when the activity is first created. */ @Override publicvoidonCreate(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: |
/* 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. */
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)); } } |