Working with the SQLite-Database
What you learn:You will learn how tocreatedatabases andtables,insertandquerydatasets in the Android-built-inSQLite-DataBase-Server.Difficulty:1 of 5http://gdb.web.daili.my/browse.php?u=5b55c0fbccad0307Oi8vd3d3LmFuZGRldi5vcmcvaW1hZ2VzL3NtaWxlcy9pY29uX3NtaWxlLmdpZg%3D%3D&b=1
http://gdb.web.daili.my/browse.php?u=5b55c0fbccad0307Oi8vd3d3LmFuZGRldi5vcmcvaW1hZ2VzL3NtaWxlcy9pY29uX2lkZWEuZ2lm&b=1Questions/Problems:Simply post below...
What it will look like:
http://gdb.web.daili.my/browse.php?u=5b55c0fbccad0307Oi8vd3d3LmFuZGRldi5vcmcvaW1hZ2VzL3R1dC9iYXNpYy93b3JraWdud2l0aHNxbGl0ZWRiL3F1ZXJ5X291dHB1dC5wbmc%3D&b=1
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 canhttp://gdb.web.daili.my/browse.php?u=5b55c0fbccad0307Oi8vd3d3LmFuZGRldi5vcmcvaW1hZ2VzL3NtaWxlcy9pY29uX3dpbmsuZ2lm&b=1*/
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));
}
}
页:
[1]