Android中SQLite的增删改查

首先我们创建一个数据库帮助类PersonSQLLiteOpenHelper，单词拼写有点小错误。。
让这个类继承SQLiteOpenHelper

packagecom.example.android03db.db;importandroid.content.Context;importandroid.database.sqlite.SQLiteDatabase;importandroid.database.sqlite.SQLiteDatabase.CursorFactory;importandroid.database.sqlite.SQLiteOpenHelper;/***数据库帮助类，用于创建和管理数据库*@authorAdministrator**/publicclassPersonSQLLiteOpenHelperextendsSQLiteOpenHelper{/***数据库的构造函数*@paramcontext*@paramname数据库名称*@paramfactory游标工程*@paramversion数据库版本*/publicPersonSQLLiteOpenHelper(Contextcontext,Stringname,CursorFactoryfactory,intversion){super(context,"hzg01.db",null,1);//依次是上下文，数据库的名称，版本，不能低于1}publicPersonSQLLiteOpenHelper(Contextcontext){super(context,"hzg01.db",null,1);//依次是上下文，数据库的名称，版本，不能低于1}/***数据库第一次创建时调用此方法*初始化一些表*/@OverridepublicvoidonCreate(SQLiteDatabasedb){//操作数据库Stringsql="createtableperson(idintegerprimarykey,namevarchar(20),ageinteger)";db.execSQL(sql);//创建person表}/***数据库的版本号更新时回调此方法*/@OverridepublicvoidonUpgrade(SQLiteDatabasedb,intoldVersion,intnewVersion){}}

接下来我们写一个DAO

packagecom.example.android03db.dao;importjava.util.ArrayList;importjava.util.List;importandroid.content.Context;importandroid.database.Cursor;importandroid.database.sqlite.SQLiteDatabase;importcom.example.android03db.db.PersonSQLLiteOpenHelper;importcom.example.android03db.entities.Person;publicclassPersonDao{privatePersonSQLLiteOpenHelperpersonsql;//数据库的帮助类publicPersonDao(Contextcontext){personsql=newPersonSQLLiteOpenHelper(context);}/***添加到person表中一条数据*@paramp*/publicvoidinsert(Personp){SQLiteDatabasedb=personsql.getWritableDatabase();if(db.isOpen()){//如果数据库打开执行添加的操作//执行添加到数据库的操作Stringsql="insertintoperson(name,age)values(?,?)";db.execSQL(sql,newObject[]{p.getName(),p.getAge()});db.close();//数据库关闭}}publicvoiddelete(intid){SQLiteDatabasedb=personsql.getWritableDatabase();//获得可写的数据库对象if(db.isOpen()){//如果数据库打开执行添加的操作//执行添加到数据库的操作db.execSQL("deletefrompersonwhereid=?",newInteger[]{id});db.close();//数据库关闭}}publicvoidupdate(intid,Stringname){SQLiteDatabasedb=personsql.getWritableDatabase();if(db.isOpen()){//如果数据库打开执行添加的操作//执行添加到数据库的操作db.execSQL("updatepersonsetname=?whereid=?",newObject[]{name,id});db.close();//数据库关闭}}publicList<Person>queryAll(){SQLiteDatabasedb=personsql.getReadableDatabase();//获得一个只读的数据库对象if(db.isOpen()){//db.execSQL("select*fromperson");Cursorcursor=db.rawQuery("selectid,name,agefromperson",null);if(cursor!=null&&cursor.getCount()>0){List<Person>persons=newArrayList<Person>();while(cursor.moveToNext()){intid=cursor.getInt(0);//取第0列idStringname=cursor.getString(1);intage=cursor.getInt(2);persons.add(newPerson(id,name,age));}db.close();returnpersons;}db.close();}returnnull;}publicPersonqueryIntem(intid){SQLiteDatabasedb=personsql.getReadableDatabase();//获得一个只读的数据库对象if(db.isOpen()){Cursorcursor=db.rawQuery("selectid,name,agefrompersonwhereid=?",newString[]{id+""});if(cursor!=null&&cursor.moveToFirst()){intpid=cursor.getInt(0);//取第0列idStringname=cursor.getString(1);intage=cursor.getInt(2);returnnewPerson(pid,name,age);}}returnnull;}}

定义一个entity类Person

packagecom.example.android03db.entities;publicclassPerson{privateintid;privateStringname;privateintage;publicintgetId(){returnid;}publicvoidsetId(intid){this.id=id;}publicStringgetName(){returnname;}publicvoidsetName(Stringname){this.name=name;}publicintgetAge(){returnage;}publicvoidsetAge(intage){this.age=age;}publicPerson(intid,Stringname,intage){super();this.id=id;this.name=name;this.age=age;}publicPerson(){super();}publicPerson(Stringname,intage){super();this.name=name;this.age=age;}@OverridepublicStringtoString(){return"Person[id="+id+",name="+name+",age="+age+"]";}}

测试阶段

packagecom.example.android03db.test;importjava.util.List;importandroid.test.AndroidTestCase;importandroid.util.Log;importcom.example.android03db.dao.PersonDao;importcom.example.android03db.db.PersonSQLLiteOpenHelper;importcom.example.android03db.entities.Person;publicclassTestextendsAndroidTestCase{privatestaticfinalStringTAG="Test";publicstaticvoidmain(String[]args){//TODOAuto-generatedmethodstub}publicvoidt(){PersonSQLLiteOpenHelperpl=newPersonSQLLiteOpenHelper(getContext());//第一次连接数据库时，创建数据库文件oncreate会被调用pl.getReadableDatabase();}publicvoidtestinsert(){PersonDaodao=newPersonDao(getContext());dao.insert(newPerson("张三",25));}publicvoidtestDelete(){PersonDaodao=newPersonDao(getContext());dao.delete(1);}publicvoidtestUpdate(){PersonDaodao=newPersonDao(getContext());dao.update(2,"李四");}publicvoidtestQueryAll(){PersonDaodao=newPersonDao(getContext());List<Person>persons=dao.queryAll();for(Personps:persons){Log.i(TAG,ps.toString());}}publicvoidtestQueryItem(){PersonDaodao=newPersonDao(getContext());Personp=dao.queryIntem(2);Log.i(TAG,p.toString());}}

更多相关文章

随机推荐