Android-嵌入式SQLite數(shù)據(jù)庫

SQLiteSQLite特點1.Android平臺中嵌入了一個關(guān)系型數(shù)據(jù)庫SQLite，和其他數(shù)據(jù)庫不同的是SQLite存儲數(shù)據(jù)時不區(qū)分類型例如一個字段聲明為Integer類型，我們也可以將一個字符串存入，一個字段聲明為布爾型，我們也可以存入浮點數(shù).Integer642.創(chuàng)建數(shù)據(jù)庫的表時可以不指定數(shù)據(jù)類型，例如：CREATETABLEperson（idINTEGERPRIMARYKEY，name）3.SQLite支持大部分標(biāo)準(zhǔn)SQL語句，增刪改查語句都是通用的，分頁查詢語句和MySQL相同SELECT*FROMpersonLIMIT20OFFSET10SELECT*FROMpersonLIMIT20，10創(chuàng)建數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)庫1.SQLiteOpenHelper2.聲明構(gòu)造函數(shù)，4個參數(shù)onCreate（）方法upGrade（）方法javajava代碼： importAndroid.content.Context; importandroid.database.sqlite.SQLiteDatabase; importandroid.database.sqlite.SQLiteOpenHelper; importandroid.database.sqlite.SQLiteDatabase.CursorFactory; publicclassDBOpenHelperextendsSQLiteOpenHelper{ /** *OpenHelper *@paramcontext上下文 *@paramname數(shù)據(jù)庫名 *@paramfactory游標(biāo)工廠 *@paramversion數(shù)據(jù)庫版本,不要設(shè)置為0,如果為0則會每次都創(chuàng)建數(shù)據(jù)庫 */ public DBOpenHelper(Context context, String name,CursorFactoryfactory,intversion){ super(context,name,factory,version); } /** *當(dāng)數(shù)據(jù)庫第一次創(chuàng)建的時候被調(diào)用 */ publicvoidonCreate(SQLiteDatabasedb){ db.execSQL("CREATETABLEperson(id INTEGER PRIMARY KEYAUTOINCREMENT,name)"); } /** *當(dāng)數(shù)據(jù)庫版本發(fā)生改變的時候被調(diào)用 */ publicvoidonUpgrade(SQLiteDatabasedb,intoldVersion,intnewVersion){ db.execSQL("ALTERTABLEpersonADDbalance"); } } publicvoidtestCreateDB(){ DBOpenHelper helper = new DBOpenHelper(getContext(),"itcast.db",null,2); helper.getWritableDatabase()//創(chuàng)建數(shù)據(jù)庫 }CRUDCRUD操作JDBCSQLite取連接，直接可以使用SQLiteDatabaseSQLSQLiteDatabase.execSQL（）SQLiteDatabase.rawQuery（）getReadableDatabase（）getWritableDatabase（）的區(qū)別查看源代碼后我們發(fā)現(xiàn)getReadableDatabase（）在通常情況下返回的就是getWritableDatabase（）拿到的數(shù)據(jù)庫只有在拋出異常的時候才會以只讀方式打開數(shù)據(jù)庫對象緩存下次打開時判斷是否重用SQLiteDatabaseinser（delet（updat（quer（）法也可以對數(shù)據(jù)庫進(jìn)行操作這些方法封裝了部分SQL語句，通過參數(shù)進(jìn)行拼接crudsqlSQLiteDatabase類調(diào)用響應(yīng)的方法執(zhí)行操作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);//初始化數(shù)據(jù)庫 } /** *Person *@parampPerson */ publicvoidinsert(Personp){ SQLiteDatabasedb=helper.getWritableDatabase();//獲取到數(shù)據(jù)庫 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; } /** *查詢某一頁數(shù)據(jù) *@parampage頁碼 *@paramsize每頁記錄數(shù) *@return */ publicList<Person>findPage(intpage,intsize){ SQLiteDatabasedb=helper.getReadableDatabase(); Cursor cursor = db.rawQuery("SELECT id,name,phone,balanceFROMpersonLIMIT?,?"http://,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;}/***獲取記錄數(shù)*@return記錄數(shù)*/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()); //第一個參數(shù)是表名,第二個參數(shù)是如果要插入一條空記錄時指定的某一列的名字,第三個參數(shù)是數(shù)據(jù) 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; } /** *查詢某一頁數(shù)據(jù) *@parampage頁碼 *@paramsize每頁記錄數(shù) *@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; } /** *獲取記錄數(shù)*@return記錄數(shù)*/