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; }