上一篇文章中介紹了手工拼寫sql語句進行數據庫的CRUD操作,本文將介紹調用sqlite內置的方法實現CRUD操作,其實質也是通過拼寫sql語句.

首先,創建一個新的android項目:

其次,查看代碼實現增刪查改:

1.創建DB工具類

MyDBHelper.java(創建數據庫的操作)

packagecom.amos.android_db;importandroid.content.Context;importandroid.database.sqlite.SQLiteDatabase;importandroid.database.sqlite.SQLiteOpenHelper;/*** Created by amosli on 14-6-12.*/

public class MyDBHelper extendsSQLiteOpenHelper{/***

*@paramcontext*/

publicMyDBHelper(Context context) {super(context, "sqlitedb", null, 1);

}/*** 數據庫第一次創建的時候調用此方法

*@paramdb*/@Overridepublic voidonCreate(SQLiteDatabase db) {

db.execSQL("create table if not exists person (personid integer primary key autoincrement ,name varchar(30) ,age integer(3) )");

}

@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, intnewVersion) {

}

}

2.配置測試環境

AndroidManifest.xml

3.PersonDao.java(實現增刪查改的方法)

packagecom.amos.android_db.dao;importandroid.content.ContentValues;importandroid.content.Context;importandroid.database.Cursor;importandroid.database.sqlite.SQLiteDatabase;importandroid.util.Log;importcom.amos.android_db.MyDBHelper;importjava.util.ArrayList;importjava.util.List;/*** Created by amosli on 14-6-12.*/

public classPersonDao {privateContext context;

MyDBHelper dbHelper;publicPersonDao(Context context) {this.context =context;

dbHelper= newMyDBHelper(context);

}/*** 添加一條記錄*/

public void add(String name, intage) {

SQLiteDatabase db=dbHelper.getWritableDatabase();if(db.isOpen()) {

ContentValues values= newContentValues();

values.put("age", age);

values.put("name", name);//不允許插入一個空值,如果contentvalue,一般第二個參

db.insert("person", null, values);//通過組拼完成的添加的操作

}

db.close();

}

}

１)測試add方法:

packagecom.amos.android_db.test;importandroid.test.AndroidTestCase;importcom.amos.android_db.dao.PersonDao;/*** Created by amosli on 14-6-13.*/

public class TestPersonDao extendsAndroidTestCase{public void testAdd() throwsException{

PersonDao personDao= new PersonDao(this.getContext());

personDao.add("amosli",10);

personDao.add("amosli",10);for(int i=0;i<10;i++){

personDao.add("amos"+i,10+i);

}

}

}

查看結果:

打開新創建的數據庫sqlitedb

./adb shell

#cd/data/data/com.amos.android_db/databases

#lssqlitedb

# sqlite3 sqlitedb

SQLite version3.6.22Enter".help" forinstructions

Enter SQL statements terminated with a";"sqlite>.databaseseq name file

--- --------------- ----------------------------------------------------------

0 main /data/data/com.amos.android_db/databases/sqlitedb

sqlite>.table

android_metadata person

查看寫入的值:

sqlite> select *from person;1|amosli|10

2|amosli|10

3|amos0|10

4|amos1|11

5|amos2|12

6|amos3|13

7|amos4|14

8|amos5|15

9|amos6|16

10|amos7|17

11|amos8|18

12|amos9|19

２)刪除數據

delete方法,主要是調用了SQLiteDatabase的delete方法.其實質上也是在拼sql語句.

public voiddelete(String name) {

SQLiteDatabase db=dbHelper.getWritableDatabase();if(db.isOpen()) {

db.delete("person", "name=?", newString[]{name});

db.close();

}

}

測試delete方法:

public void testDelete() throwsException{

PersonDao personDao= new PersonDao(this.getContext());

personDao.delete("amosli");

}

查看結果:

sqlite> select *from person;3|amos0|10

4|amos1|11

5|amos2|12

6|amos3|13

7|amos4|14

8|amos5|15

9|amos6|16

10|amos7|17

11|amos8|18

12|amos9|19

3)更新數據

public void update(String name, String newname, intnewage) {

SQLiteDatabase db=dbHelper.getWritableDatabase();if(db.isOpen()) {

ContentValues contentValues= newContentValues();

contentValues.put("name", newname);

contentValues.put("age", newage);

db.update("person", contentValues, "name=?", newString[]{name});

db.close();

}

}

測試方法:

public void testUpdate() throwsException{

PersonDao personDao= new PersonDao(this.getContext());

personDao.update("amos0","0amos",35);

}

查看結果:

sqlite> select *from person;3|0amos|35

4|amos1|11

5|amos2|12

6|amos3|13

7|amos4|14

8|amos5|15

9|amos6|16

10|amos7|17

11|amos8|18

12|amos9|19

4)查找數據

public booleanfind(String name) {boolean status_result = false;

SQLiteDatabase db=dbHelper.getReadableDatabase();//public android.database.Cursor query(//String table,//String[] columns,//String selection,//String[] selectionArgs,//String groupBy,//String having,//String orderBy)

if(db.isOpen()) {

Cursor cursor= db.query("person", null, "name=?", new String[]{name}, null, null, null);if(cursor.moveToFirst()) {

status_result= true;

}

cursor.close();

db.close();

}returnstatus_result;

}

測試方法:

public void testFind() throwsException{

PersonDao personDao= new PersonDao(this.getContext());

assertEquals(true,personDao.find("amos1"));

}

5)查找所有數據

public ListfindAll(){

List persons = null;

SQLiteDatabase db=dbHelper.getReadableDatabase();if(db.isOpen()){

persons= new ArrayList();

Cursor cursor= db.query("person", null, null, null, null, null, null);while(cursor.moveToNext()){

Person person= newPerson();

person.setName(cursor.getString(cursor.getColumnIndex("name")));

person.setAge(cursor.getInt(cursor.getColumnIndex("age")));

persons.add(person);

}

cursor.close();

db.close();

}returnpersons;

}

測試方法:

public void testFindAll() throwsException{

PersonDao personDao= newPersonDao(getContext());

List personList =personDao.findAll();for(Person person:personList){

Log.d("person:",person.toString());

}

}

輸出結果:

4.擴展--SQLite中的事務

這里以amos1向amos2轉錢200元為例:

1),amos1賬戶初始1000元,amos2賬戶初始0元.

2),從amos1中減去200元,amos2中加上200元,這兩個步驟要么同時成功,要么同時失敗,不能一方成功,另一主失敗,這就是事務.

代碼實現:

packagecom.amos.android_db;importandroid.content.Context;importandroid.database.sqlite.SQLiteDatabase;importandroid.database.sqlite.SQLiteOpenHelper;/*** Created by amosli on 14-6-12.*/

public class MyDBHelper extendsSQLiteOpenHelper{

publicMyDBHelper(Context context) {super(context, "sqlitedb", null, 2);

}/*** 數據庫第一次創建的時候調用此方法

*@paramdb*/@Overridepublic voidonCreate(SQLiteDatabase db) {

db.execSQL("create table if not exists person (personid integer primary key autoincrement ,name varchar(30) ,age integer(3) )");

}

@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, intnewVersion) {

db.execSQL("alter table person add account integer null");

}

}

在初始化時更新表的結構,添加account一列,用來表示賬戶余額.

在PersonDao中添加如下方法:

public voidtransferMoney() {

SQLiteDatabase db=dbHelper.getWritableDatabase();if(db.isOpen()){try{

db.beginTransaction();//給amos1賬戶里設置1000元,amost account=0;

db.execSQL("update person set account=? where name = ?",new Object[]{1000,"amos1"});

db.execSQL("update person set account=? where name = ?",new Object[]{0,"amos2"});//從amos1賬戶里扣除200元

db.execSQL("update person set account=account-? where name = ?",new Object[]{200,"amos1"});//把amos1的錢轉給amos2

db.execSQL("update person set account=account+? where name=?",new Object[]{200,"amos2"});

}catch(Exception e){

e.printStackTrace();

}finally{//顯示的設置數據事務是否成功

db.setTransactionSuccessful();

db.endTransaction();

db.close();

}

}

}

和hibernate里的事務調用很類似,這里先beginTransaction,然后要注意的是setTransactionSuccessful和endTransaction.

測試方法:

public void testTransaction() throwsException{

PersonDao personDao= newPersonDao(getContext());

personDao.transferMoney();

}

測試結果:

sqlite> select *from person;3|0amos|35|

4|amos1|11|800

5|amos2|12|200

6|amos3|13|

7|amos4|14|

8|amos5|15|

9|amos6|16|

10|amos7|17|

11|amos8|18|

12|amos9|19|