Android应用中内嵌SQLite数据库的基本操作指南
一、首先写一个类继承SQLiteOpenHelper类
重写他的方法指定db的名称、版本，重写oncreat和onUpgrade方法，写SQL语句创建表
publicclassMySQLiteOpenhelperextendsSQLiteOpenHelper{
privatestaticStringname="person.db";
privatestaticintversion=1;
publicMySQLiteOpenhelper(Contextcontext){
super(context,name,null,version);
}
/*
*数据库第一次被创建时调用的方法
*db是被创建的数据库
*/
@Override
publicvoidonCreate(SQLiteDatabasedb){
db.execSQL("createtableperson(idintegerprimarykeyautoincrement,namevarchar(20),numbervarchar(20))");
}
/*当数据库版本更新时调用此方法*/
@Override
publicvoidonUpgrade(SQLiteDatabasedb,intoldVersion,intnewVersion){
}
二、创建一个DAO类对外提供增删改查接口
其中执行增删改查的方法可以用SQL语句也可以使用系统给出的API,下面的代码中把两种方法都写了出来
publicclassPersondao{
privateMySQLiteOpenhelperhelper;
publicPersondao(){
}
publicPersondao(Contextcontext){
helper=newMySQLiteOpenhelper(context);
}
publicvoidadd(Stringname,Stringnumber){
SQLiteDatabasedb=helper.getWritableDatabase();
db.execSQL("insertintoperson(name,number)values(?,?)",newObject[]{name,number});
/*ContentValuesvalues=newContentValues();
values.put("number",number);
values.put("name",name);
longid=db.insert("Person",null,values);*/
db.close();
}
publicbooleanfind(Stringname){
SQLiteDatabasedb=helper.getWritableDatabase();
//Cursorcursor=db.rawQuery("select*frompersonwherename=?",newString[]{name});
Cursorcursor=db.query("person",null,"name=?",newString[]{name},null,null,null);
booleanresult=cursor.moveToNext();
cursor.close();
db.close();
returnresult;
}
publicintupdate(Stringname,Stringnewnumber){
SQLiteDatabasedb=helper.getWritableDatabase();
//db.execSQL("updatepersonsetnumber=?wherename=?",newObject[]{newnumber,name});
ContentValuesvalues=newContentValues();
values.put("number",newnumber);
intnumber=db.update("person",values,"name=?",newString[]{newnumber});
db.close();
returnnumber;
}
publicintdelet(Stringname){
SQLiteDatabasedb=helper.getWritableDatabase();
//db.execSQL("deletefrompersonwherename=?",newString[]{name});
intnumber=db.delete("person","name=?",newString[]{name});
db.close();
returnnumber;
}
publicList<Person>findAll(){
List<Person>persons=newArrayList<Person>();
SQLiteDatabasedb=helper.getWritableDatabase();
//Cursorcursor=db.rawQuery("select*fromperson",null);
Cursorcursor=db.query("person",newString[]{"id","name","number"},null,null,null,null,null);
while(cursor.moveToNext()){
intid=cursor.getInt(cursor.getColumnIndex("id"));
Stringname=cursor.getString(cursor.getColumnIndex("name"));
Stringnumber=cursor.getString(cursor.getColumnIndex("number"));
Personp=newPerson();
persons.add(p);
}
db.close();
cursor.close();
returnpersons;
}
}
三、增删改查操作
importandroid.content.ContentValues;
importandroid.content.Context;
importandroid.database.Cursor;
importandroid.database.sqlite.SQLiteDatabase;
importandroid.database.sqlite.SQLiteOpenHelper;
publicclassToDoDBextendsSQLiteOpenHelper{
privatefinalstaticStringDATABASE_NAME="todo_db";
privatefinalstaticintDATABASE_VERSION=1;
privatefinalstaticStringTABLE_NAME="todo_table";
publicfinalstaticStringFIELD_id="_id";
publicfinalstaticStringFIELD_TEXT="todo_text";
publicToDoDB(Contextcontext){
super(context,DATABASE_NAME,null,DATABASE_VERSION);
}
@Override
publicvoidonCreate(SQLiteDatabasedb){
/*建立table*/
Stringsql="CREATETABLE"+TABLE_NAME+"("+FIELD_id
+"INTEGERprimarykeyautoincrement,"+""+FIELD_TEXT
+"text)";
db.execSQL(sql);
}
@Override
publicvoidonUpgrade(SQLiteDatabasedb,intoldVersion,intnewVersion){
Stringsql="DROPTABLEIFEXISTS"+TABLE_NAME;
db.execSQL(sql);
onCreate(db);
}
publicCursorselect(){
SQLiteDatabasedb=this.getReadableDatabase();
Cursorcursor=db
.query(TABLE_NAME,null,null,null,null,null,null);
returncursor;
}
publiclonginsert(Stringtext){
SQLiteDatabasedb=this.getWritableDatabase();
/*将新增的值放入ContentValues*/
ContentValuescv=newContentValues();
cv.put(FIELD_TEXT,text);
longrow=db.insert(TABLE_NAME,null,cv);
returnrow;
}
publicvoiddelete(intid){
SQLiteDatabasedb=this.getWritableDatabase();
Stringwhere=FIELD_id+"=?";
String[]whereValue={Integer.toString(id)};
db.delete(TABLE_NAME,where,whereValue);
}
publicvoidupdate(intid,Stringtext){
SQLiteDatabasedb=this.getWritableDatabase();
Stringwhere=FIELD_id+"=?";
String[]whereValue={Integer.toString(id)};
/*将修改的值放入ContentValues*/
ContentValuescv=newContentValues();
cv.put(FIELD_TEXT,text);
db.update(TABLE_NAME,cv,where,whereValue);
}
}
四、写一个JavaBean设置他的get、set方法
publicclassPerson{
privateintid;
privateStringname;
privateStringnumber;
publicPerson(){
}
publicPerson(intid,Stringname,Stringnumber){
this.id=id;
this.name=name;
this.number=number;
}
publicintgetId(){
returnid;
}
publicvoidsetId(intid){
this.id=id;
}
publicStringgetName(){
returnname;
}
publicvoidsetName(Stringname){
this.name=name;
}
publicStringgetNumber(){
returnnumber;
}
publicvoidsetNumber(Stringnumber){
this.number=number;
}
}
五、mainactivity中使用它
privateSQLiteOpenHelperhelper; Persondaodao=newPersondao(); helper=newMySQLiteOpenhelper(this); helper.getWritableDatabase(); SQLiteDatabasedb=helper.getWritableDatabase();
六、关于数据库的事务处理
android开发中数据库的操作非常慢，将所有操作打包成一个事务能够大大的提高处理速度，其中最重要的是保证了数据的一致性，让事务中的所有操作都能成功执行，或者失败，或者这所有操作都回滚。
SQLiteDatabasedb=helper.getWritableDatabase();
db.beginTransaction();
try{
//在这里执行多个数据库操作，执行过程中可能会抛出异常
db.execSQL("updatepersonsetnumber=?wherename=?",newObject[]{"1",jacky});
db.execSQL("updatepersonsetnumber=?wherename=?",newObject[]{"2","sunny"});
db.setTransactionSuccessful();
}catch{
//捕获异常
throwe；
}finally{
//所有操作完成结束一个事务
db.endTransaction();
db.close;
}