Android-嵌入式SQLite数据库

SQLiteSQLite特点1.Android平台中嵌入了一个关系型数据库SQLite，和其他数据库不同的是SQLite存储数据时不区分类型例如一个字段声明为Integer类型，我们也可以将一个字符串存入，一个字段声明为布尔型，我们也可以存入浮点数.Integer642.创建数据库的表时可以不指定数据类型，例如：CREATETABLEperson（idINTEGERPRIMARYKEY，name）3.SQLite支持大部分标准SQL语句，增删改查语句都是通用的，分页查询语句和MySQL相同SELECT*FROMpersonLIMIT20OFFSET10SELECT*FROMpersonLIMIT20，10创建数据库创建数据库1.SQLiteOpenHelper2.声明构造函数，4个参数onCreate（）方法upGrade（）方法javajava代码： importAndroid.content.Context; importandroid.database.sqlite.SQLiteDatabase; importandroid.database.sqlite.SQLiteOpenHelper; importandroid.database.sqlite.SQLiteDatabase.CursorFactory; publicclassDBOpenHelperextendsSQLiteOpenHelper{ /** *OpenHelper *@paramcontext上下文 *@paramname数据库名 *@paramfactory游标工厂 *@paramversion数据库版本,不要设置为0,如果为0则会每次都创建数据库 */ public DBOpenHelper(Context context, String name,CursorFactoryfactory,intversion){ super(context,name,factory,version); } /** *当数据库第一次创建的时候被调用 */ publicvoidonCreate(SQLiteDatabasedb){ db.execSQL("CREATETABLEperson(id INTEGER PRIMARY KEYAUTOINCREMENT,name)"); } /** *当数据库版本发生改变的时候被调用 */ publicvoidonUpgrade(SQLiteDatabasedb,intoldVersion,intnewVersion){ db.execSQL("ALTERTABLEpersonADDbalance"); } } publicvoidtestCreateDB(){ DBOpenHelper helper = new DBOpenHelper(getContext(),"itcast.db",null,2); helper.getWritableDatabase()//创建数据库 }CRUDCRUD操作JDBCSQLite取连接，直接可以使用SQLiteDatabaseSQLSQLiteDatabase.execSQL（）SQLiteDatabase.rawQuery（）getReadableDatabase（）getWritableDatabase（）的区别查看源代码后我们发现getReadableDatabase（）在通常情况下返回的就是getWritableDatabase（）拿到的数据库只有在抛出异常的时候才会以只读方式打开数据库对象缓存下次打开时判断是否重用SQLiteDatabaseinser（delet（updat（quer（）法也可以对数据库进行操作这些方法封装了部分SQL语句，通过参数进行拼接crudsqlSQLiteDatabase类调用响应的方法执行操作insertdeleteupdateCREATETABLESQL语句；rawQuery（）select语句.javajava代码： importjava.util.ArrayList; importjava.util.List; importAndroid.content.Context; importAndroid.database.Cursor; importAndroid.database.sqlite.SQLiteDatabase; importcn.itcast.sqlite.DBOpenHelper; importcn.itcast.sqlite.domain.Person; publicclassSQLPersonService{ privateDBOpenHelperhelper; publicSQLPersonService(Contextcontext){ helpernewDBOpenHelper(context"itcast.db"null2);//初始化数据库 } /** *Person *@parampPerson */ publicvoidinsert(Personp){ SQLiteDatabasedb=helper.getWritableDatabase();//获取到数据库 db.execSQL("INSERT INTO person(name,phone,balance)VALUES(?,?)",newObject[]{p.getName(),p.getPhone()}); db.close(); } /** *ID删除 *@paramidPERSONID */ publicvoiddelete(Integerid){ SQLiteDatabase db = helper.getWritableDatabase(); 34.db.execSQL("DELETEFROMpersonWHEREid=?",newObject[]{id});db.close(); } /** *Person *@parampPerson */ publicvoidupdate(Personp){ SQLiteDatabasedb=helper.getWritableDatabase(); db.execSQL("UPDATE person SET name=?,phone=?,balance=?WHEREid=?",newObject[]{ p.getName(),p.getPhone(),p.getBalance(),p.getId()}); db.close(); } /** *ID查找 *@paramidID *@return,null */ publicPersonfind(Integerid){ SQLiteDatabasedb=helper.getReadableDatabase(); Cursorcursor=db.rawQuery("SELECTname,phone,balanceFROMpersonWHEREid=?",newString[]{id.toString()}); Personp=null; if(cursor.moveToNext()){ Stringname=cursor.getString(cursor.getColumnIndex("name")); Stringphone=cursor.getString(1); Integerbalance=cursor.getInt(2); p=newPerson(id,name,phone,balance); } cursor.close(); db.close(); returnp; } /** *Person对象 *@returnPerson,如未找到,size()为0List */ publicList<Person>findAll(){ SQLiteDatabasedb=helper.getReadableDatabase(); Cursor cursor = db.rawQuery("SELECT id,name,phone,balanceFROMperson",null); List<Person>persons=newArrayList<Person>(); while(cursor.moveToNext()){ Integerid=cursor.getInt(0);Stringname=cursor.getString(1); Stringphone=cursor.getString(2); Integerbalance=cursor.getInt(3); persons.add(newPerson(id,name,phone,balance)); } cursor.close(); db.close(); returnpersons; } /** *查询某一页数据 *@parampage页码 *@paramsize每页记录数 *@return */ publicList<Person>findPage(intpage,intsize){ SQLiteDatabasedb=helper.getReadableDatabase(); Cursor cursor = db.rawQuery("SELECT id,name,phone,balanceFROMpersonLIMIT?,?"//,newString[]{String.valueOf((1)*size),String.valueOf(size)}); List<Person>persons=newArrayList<Person>(); while(cursor.moveToNext()){ Integerid=cursor.getInt(0); Stringname=cursor.getString(1); Stringphone=cursor.getString(2);Integerbalance=cursor.getInt(3);persons.add(newPerson(id,name,phone,balance));}cursor.close();db.close();returnpersons;}/***获取记录数*@return记录数*/publicintgetCount(){SQLiteDatabasedb=helper.getReadableDatabase();Cursorcursor=db.rawQuery("SELECTCOUNT(*)FROMperson",null);cursor.moveToNext();returncursor.getInt(0);}} /** *Person *@parampPerson */ publicvoidinsert(Personp){ SQLiteDatabasedb=helper.getWritableDatabase();7. ContentValuesvalues=newContentValues(); values.put("name",p.getName()); values.put("phone",p.getPhone()); values.put("balance",p.getBalance()); //第一个参数是表名,第二个参数是如果要插入一条空记录时指定的某一列的名字,第三个参数是数据 db.insert("person",null,values); db.close(); } /** *ID删除 *@paramidPERSONID */ publicvoiddelete(Integerid){ SQLiteDatabasedb=helper.getWritableDatabase(); db.delete("person","id=?",newString[]{id.toString()}); db.close(); } /** *Person *@parampPerson */ publicvoidupdate(Personp){ SQLiteDatabasedb=helper.getWritableDatabase(); ContentValuesvalues=newContentValues(); values.put("id",p.getId()); values.put("name",p.getName()); values.put("phone",p.getPhone()); values.put("balance",p.getBalance()); db.update("person", values, "id=?", new{p.getId().toString()}); db.close(); } /** *ID查找 *@paramidID *@return,null */

String[] publicPersonfind(Integerid){ SQLiteDatabasedb=helper.getReadableDatabase(); Cursorcursor=db.query("person",newString[]{"name","phone","balance"},"id=?",newString[]{id.toString()},null,null,null); Personp=null; if(cursor.moveToNext()){ Stringname=cursor.getString(cursor.getColumnIndex("name")); Stringphone=cursor.getString(1); Integerbalance=cursor.getInt(2); p=newPerson(id,name,phone,balance); } cursor.close(); db.close(); returnp; } /** *Person对象 *@returnPerson,如未找到,size()为0List */ publicList<Person>findAll(){ SQLiteDatabasedb=helper.getReadableDatabase(); Cursorcursor=db.query("person",newString[]{"id","name","phone","balance"},null,null,null,null,"iddesc"); List<Person>persons=newArrayList<Person>(); while(cursor.moveToNext()){ Integerid=cursor.getInt(0); Stringname=cursor.getString(1); Stringphone=cursor.getString(2); Integerbalance=cursor.getInt(3); persons.add(newPerson(id,name,phone,balance)); } cursor.close(); db.close(); returnpersons; } /** *查询某一页数据 *@parampage页码 *@paramsize每页记录数 *@return */ publicList<Person>findPage(intpage,intsize){ SQLiteDatabasedb=helper.getReadableDatabase(); Cursorcursor=db.query("person",newString[]{"id","name","phone","balance"},null,null,null,null,null,(1)*size+","+size); List<Person>persons=newArrayList<Person>(); while(cursor.moveToNext()){ Integerid=cursor.getInt(0); Stringname=cursor.getString(1); Stringphone=cursor.getString(2); Integerbalance=cursor.getInt(3); persons.add(newPerson(id,name,phone,balance)); } cursor.close(); db.close(); returnpersons; } /** *获取记录数*@return记录数*/