Android SQLite數(shù)據(jù)庫操作

1、Android SQLite數(shù)據(jù)庫操作package com.example.sqlitedemo.db; import android.content.Context;importandroid.database.sqlite.SQLiteDatabase;importandroid.database.sqlite.SQLiteOpenHelper;import android.util.Log; /數(shù)據(jù)庫幫助類 用于數(shù)據(jù)庫的創(chuàng)建和管理/用于創(chuàng)建鏈接數(shù)據(jù)庫 public class PersonSQLiteOpenHelper extendsSQLit

2、eOpenHelper          privatestatic final String TGA = "PersonSQLiteOpenHelper"          /數(shù)據(jù)庫的構造方法         /context上下文Activity    &

3、#160;    /name 數(shù)據(jù)庫名稱         /factory 游標工程默認值給空         /version 數(shù)據(jù)庫版本號不能小于1         publicPersonSQLiteOpenHelper(Context context)

4、                    super(context,"wkk.db", null, 1);                    /數(shù)據(jù)庫第一次創(chuàng)建時回調此方法 &

5、#160;       /初始化一些表         publicvoid onCreate(SQLiteDatabase db) / 連接到數(shù)據(jù)庫                   /*    

6、                * SQLite的數(shù)據(jù)類型：Typelessness(無類型), 可以保存任何類型的數(shù)據(jù)到你所想要保存的任何表的任何列中.                    * 但它又支持常見的類型比

7、如: NULL(空),VARCHAR(char), TEXT(文本), INTEGER(int), BLOB,                    * CLOB.等. 唯一的例外：integerprimary key 此字段只能存儲64位整數(shù)           &#

8、160;        */                    /操作數(shù)據(jù)庫                   /creat

9、e table person(_id integer primary key,name varchar(20)/*長度不起作用                   /定義20 存100位也可以*/,age integer )              

10、0;    Stringsql = "create table person(_id integer primary key,name varchar(20),ageinteger )"                   db.execSQL(sql);        

11、;           /取得對象之后可以直接用下面的方法對數(shù)據(jù)庫執(zhí)行操作                   /db.insert(table, nullColumnHack, values)         

12、;          /insert() 增加數(shù)據(jù)                   /delete() 刪除數(shù)據(jù)              

13、60;    /update() 修改數(shù)據(jù)                   /query() 查詢數(shù)據(jù)                   /也可以這樣寫&

14、#160;                  /db.execSQL("create table person(_id integer primary key autoincremnet,namevarchar(20),age integer ");            

15、60;       /數(shù)據(jù)庫的版本號更新時回調此方法         /更新數(shù)據(jù)庫的內容（刪除，添加，修改表）         publicvoid onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) /傳入舊版本號 新報本號   

16、0;               if(oldVersion=1&&newVersion=2)/如果 舊版本號為1且新版本號為2時 執(zhí)行的方法                      

17、0;     Log.i(TGA,"數(shù)據(jù)庫更新");                            db.execSQL("altertable person add balance integer");/ person表

18、中添加一列 列名為 balance(余額)                   elseif(oldVersion=2&&newVersion=3)                   

19、;                           elseif(oldVersion=3&&newVersion=4)                

20、;                                            package com.example.sqlitedemo

21、.entities;  /用于給數(shù)據(jù)庫的各個屬性賦值public classPerson    private int id;   private String name;   private int age;   public int getId()       return id; &

22、#160;    public void setId(int id)       this.id = id;      public String getName()       return name;      public void 

23、setName(String name)        = name;      public int getAge()       return age;      public void setAge(int age)     

24、  this.age = age;      public Person()       super();      / TODO Auto-generated constructor stub      public Person(int id, String name,

25、60;int age)       super();      this.id = id;       = name;      this.age = age;      Override   public

26、60;String toString()       return "Person id="+ id + ", name="+ name+ ", age="+ age+ ""         package com.example.sqlitedemo.dao; import j

27、ava.util.ArrayList;import java.util.List; importcom.example.sqlitedemo.db.PersonSQLiteOpenHelper;importcom.example.sqlitedemo.entities.Person; import android.content.Context;import android.database.Cursor;importandroid.database.sqlite.SQLiteDatabase; /用于執(zhí)行數(shù)據(jù)庫的增刪改查操作public class Person

28、Dao          privatePersonSQLiteOpenHelper mOpenHelper;/ 創(chuàng)建一個 數(shù)據(jù)庫的幫助類的對象          publicPersonDao(Context context) / 構造方法當創(chuàng)建的這類的對象時需要傳入一個上下文對象        

29、           mOpenHelper= new PersonSQLiteOpenHelper(context);/ new一個 當創(chuàng)建此類對象時 會初始化                         

30、0;                                                 

31、0;                                                 

32、0;              /PersonSQliteOpenHelper類的對象                   /添加到person表一條數(shù)據(jù)增         pub

33、licvoid insert(Person person)                    SQLiteDatabasedb = mOpenHelper.getWritableDatabase();/ 取出 得到              &#

34、160;                                                 &#

35、160;                                                 &#

36、160;                                  /SQLiteDatabase對象進行操作            

37、;       /SQLiteDatabase db1=mOpenHelper.getReadableDatabase();/只讀的對象                   /SQLiteDatabase db2=mOpenHeLper.getWritableDatabase();/可寫的對象    

38、;               if(db.isOpen() / 如果數(shù)據(jù)庫打開執(zhí)行添加的操作                            /執(zhí)行添加

39、到數(shù)據(jù)庫的操作                            /db.execSQL("insert into person(name,age) values ('ff',19);");       

40、60;                    db.execSQL("insertinto person(name,age) values (?,?);",                  

41、0;                            newObject person.getName(), person.getAge() );/ ?占位符             

42、                                                  

43、                                                  

44、                                                  

45、     /在后面的Object數(shù)組中賦給對應的值                            db.close();/數(shù)據(jù)庫關閉           

46、;                           publicvoid delete(int id) / 刪除                 

47、  SQLiteDatabasedb = mOpenHelper.getWritableDatabase();/ 獲得可寫的數(shù)據(jù)庫對象                   if(db.isOpen() / 如果數(shù)據(jù)庫打開執(zhí)行添加的操作           &#

48、160;                db.execSQL("deletefrom person where _id = ?;",                        

49、                       newInteger id );/ 根據(jù)id刪除相應的數(shù)據(jù)                     &#

50、160;      db.close();/數(shù)據(jù)庫關閉                                      publicvoid dete

51、le(Integer. _ids)/ 獲得一個不確定長度的Integer型數(shù)組 (Integer _ids)                            if(_ids.length > 0)        

52、60;                    StringBuffersb = new StringBuffer();                       

53、60;    for(int i = 0; i < _ids.length; i+)                                      sb.append(&#

54、39;?').append(',');/將 char 參數(shù)的字符串表示形式追加到此序列。                                       

55、                 sb.deleteCharAt(sb.length() - 1);/ 移除此序列指定位置的char                        

56、    SQLiteDatabasedb = mOpenHelper.getWritableDatabase();/ 將最后個位置的，去掉                            db.execSQL("deletefrom person whe

57、re _id in (" + sb + ")",                                           

58、60;   (Object)_ids);/ 將sb中的?替換為_ids中的具體數(shù)字                                      publicvoid

59、update(int id, String name) / 改 修改數(shù)據(jù) 根據(jù)id修改數(shù)據(jù)                   SQLiteDatabasedb = mOpenHelper.getWritableDatabase();            &

60、#160;      if(db.isOpen() / 如果數(shù)據(jù)庫打開執(zhí)行添加的操作                            db.execSQL("update person set name = ? where _id =

61、 ?;",                                               newObjec

62、t name, id );                            db.close();/數(shù)據(jù)庫關閉                 

63、                    publicList<Person> queryAll() / 查 查詢所有                   SQLiteDataba

64、sedb = mOpenHelper.getReadableDatabase();/ 獲得一個只讀的數(shù)據(jù)庫對象                   if(db.isOpen()                   

65、60;         /db.execSQL("select * from person;");/此方法沒有返回值                            Cursorcursor = db.r

66、awQuery("select * from person;", null);                            if(cursor != null && cursor.getCount() > 0) /cursor != null 是判斷curs

67、or這個對象是否為空cursor.getCount() > 0判斷cursor值是否為空                                     List<Person>personList =

68、new ArrayList<Person>();                                     intid;       

69、                              Stringname;                  &

70、#160;                  intage;                              

71、       while(cursor.moveToNext() / 如果移動到最后一位返回false                                   

72、;            id= cursor.getInt(0);/ 取第0列的數(shù)據(jù) _id                              

73、60;                name= cursor.getString(1);                             &

74、#160;                 age= cursor.getInt(2);                            

75、60;                  personList.add(newPerson(id, name, age);/ 將數(shù)據(jù)添加到ArrayList集合中                    

76、0;                                                 

77、0;   db.close();                                     returnpersonList;/ 返回ArrayList集合 

78、0;                                                 

79、0;    db.close();                                      returnnull;    

80、               /*          * 獲取學生信息          *          * param start&

81、#160;起始位置          * param count 學生數(shù)量          * return          */         publicList<Person>

82、 getScrollData(int start, int count)                    List<Person>students = new ArrayList<Person>();               

83、    SQLiteDatabasedb = mOpenHelper.getWritableDatabase();                   Cursorcursor = db.rawQuery("select * from person limit ?,?",      

84、60;                              newString String.valueOf(start), String.valueOf(count) );/ String.valueOf(start)      &

85、#160;                                                 &

86、#160;                                                 &

87、#160;                                                 &

88、#160;                              /將int型start轉化成String型               

89、0;   while(cursor.moveToNext()/ 游標下移返回false時結束查詢                   /取得信息賦值到ArrayList集合中                &#

90、160;           students.add(newPerson(                                  

91、60;            cursor.getInt(cursor.getColumnIndex("_id"),cursor                           

92、0;                                     .getString(cursor.getColumnIndex("name"),cursor   &

93、#160;                                                 &

94、#160;           .getShort(cursor.getColumnIndex("age");                   /* cursor.getColumnIndex("sid") 返回sid的序列號 */&#

95、160;                  returnstudents;                   publicPerson queryItem(int id) / 根據(jù)id查詢單條  &

96、#160;                SQLiteDatabasedb = mOpenHelper.getReadableDatabase();/ 獲得一個只讀的數(shù)據(jù)庫對象                   if(db.isOpen(

97、) / 判斷數(shù)據(jù)庫是否打開                            Cursorcursor = db.rawQuery("select * from person where _id = ?;",      

98、60;                                        newString id + "" );    

99、60;                       if(cursor != null && cursor.moveToFirst()                   &

100、#160;                  /int _id = cursor.getInt(0);                          

101、60;          Stringname = cursor.getString(1);                                  

102、   intage = cursor.getInt(2);                                     db.close();    

103、;                                 returnnew Person(cursor.getInt(cursor.getColumnIndex("_id"),     &

104、#160;                                                 &

105、#160;name,age);                                               

106、60;        db.close();                                      returnnull

107、;                   publiclong getCount()                    SQLiteDatabasedb = mOpenHelper.getWritableDatabas

108、e();                   Cursorcursor = db.rawQuery("select count(_id) from person", null);/ 獲取便簽信息的記錄數(shù)，后面本應是Object數(shù)組的位置            

109、                                                  

110、                                                  

111、                                                  

112、               /因為我們要查詢所有，所以給null                   if(cursor.moveToNext()/ 判斷Cursor中是否有數(shù)據(jù)      

113、;                                         returncursor.getLong(0);/ 返回總記錄數(shù)  

114、0;                                   return0;          package com.example.

115、sqlitedemo.test; import java.util.List; importcom.example.sqlitedemo.dao.PersonDao;importcom.example.sqlitedemo.db.PersonSQLiteOpenHelper;importcom.example.sqlitedemo.entities.Person;   importandroid.database.sqlite.SQLiteDatabase;/import android.content.Context;/importandro

116、id.database.sqlite.SQLiteDatabase;import android.test.AndroidTestCase;import android.util.Log;/測試類public class TestCase extendsAndroidTestCase          privatestatic final String TAG = "TestCase"        &#

117、160;        publicvoid test()                    /數(shù)據(jù)庫什么時候創(chuàng)建                 

118、0; PersonSQLiteOpenHelperopenHelper = new PersonSQLiteOpenHelper(                                     getConte

119、xt();                   /第一次連接數(shù)據(jù)庫時創(chuàng)建數(shù)據(jù)庫文件 onCreate會被調用                   openHelper.getReadableDatabase()

120、;                   Log.i(TAG,"數(shù)據(jù)庫創(chuàng)建成功");                   publicvoid testInsert()   

121、0;                PersonDaodao = new PersonDao(getContext();                   dao.insert(newPerson(0, "王五", 213);&

122、#160;                  dao.insert(newPerson(0, "張三", 23);                   dao.insert(newPerson(0, "

123、;王二", 12);                   publicvoid testDelete()                    PersonDaodao = new PersonDao(getC

124、ontext();                   dao.delete(0);                   publicvoid testUpdate()     &

125、#160;              PersonDaodao = new PersonDao(getContext();                   dao.update(1,"如花");    &#

126、160;              publicvoid testQueryAll()                    PersonDaodao = new PersonDao(getContext();     

127、              List<Person>personList= dao.queryAll();                   for(Personperson:personList)     

128、;                       Log.i("查詢所有",person.toString();                    

129、                                             publicvoid testqueryItem()

130、60;                  PersonDaodao = new PersonDao(getContext();                   Personperson = dao.queryItem(1);&

131、#160;                                    Log.i("查詢單條",person.toString();      &#

132、160;            /事務 尚未解決         /事務:         /db.beginTransaction();         /標記事務成功  

133、60;      /db.setTransactionSuccessful();         /停止事務         /db.endTransaction();         publicvoid testTransaction()   &

134、#160;                PersonSQLiteOpenHelperopenHelper = new PersonSQLiteOpenHelper(                       

135、;              getContext();                   SQLiteDatabasedb = openHelper.getWritableDatabase();      

136、             if(db.isOpen()                             try      

137、                               /開啟事務                  &

138、#160;                  db.beginTransaction();                           

139、60;         /1. 從張三賬戶中扣1000塊錢                                     

140、;db.execSQL("updateperson set balance = balance - 1000 where name = 'zhangsan'");                                 

141、60;   / ATM機, 掛掉了.                                     /int result = 10 / 0;   &

142、#160;                                 /2. 向李四賬戶中加1000塊錢            

143、60;                        db.execSQL("updateperson set balance = balance + 1000 where name = 'lisi'");          

144、;                           /標記事務成功                     

145、0;               db.setTransactionSuccessful();                            finally &#

146、160;                                    /停止事務            

147、60;                        db.endTransaction();                      &

148、#160;                                 db.close();              &#

149、160;                       publicvoid testTransactionInsert()                    PersonSQL

150、iteOpenHelperopenHelper = new PersonSQLiteOpenHelper(                                     getContext();  

151、                 SQLiteDatabasedb = openHelper.getWritableDatabase();                   if(db.isOpen()    

152、;                         /1. 記住當前的時間                      &

153、#160;     longstart = System.currentTimeMillis();                            /2. 開始添加數(shù)據(jù)       &

154、#160;                    try                            

155、0;        db.beginTransaction();                                     for(in

156、t i = 0; i < 10000; i+)                                              

157、60; db.execSQL("insertinto person(name, age, balance) values('wang"                                   

158、0;                             +i + "', " + (10 + i) + ", " + (10000 + i) + ")");      

159、0;                                                 

160、0;                 db.setTransactionSuccessful();                           

161、0;finally                                      db.endTransaction();        

162、                                                /3. 記住結

163、束時間, 計算耗時時間                            longend = System.currentTimeMillis();             &

164、#160;              longdiff = end - start;                            Log.i(TAG,"耗時:

165、" + diff + "毫秒");                            db.close();               

166、;                 package com.example.sqlitedemo.dao; import java.util.ArrayList;import java.util.List; import android.content.ContentValues;import android.content.Context;import&#

167、160;android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.util.Log; import com.example.sqlitedemo.db.PersonSQLiteOpenHelper;import com.example.sqlitedemo.entities.Person; public classPersonDao2     private sta

168、tic final String TAG = "PersonDao2"   private PersonSQLiteOpenHelper mOpenHelper; / 數(shù)據(jù)庫的幫助類對象    public PersonDao2(Contextcontext)       mOpenHelper = newPersonSQLit

169、eOpenHelper(context);       /*    * 添加到person表一條數(shù)據(jù)    *    * param person    */   public void insert(Person person)      

170、; SQLiteDatabasedb = mOpenHelper.getWritableDatabase();/此處可以將db定義為全局變量      if (db.isOpen()  / 如果數(shù)據(jù)庫打開, 執(zhí)行添加的操作         ContentValuesvalues = newContentValues();     

171、;    values.put("name",person.getName(); / key作為要存儲的列名, value對象列的值         values.put("age",person.getAge();         / nullColumnHack：當values參數(shù)為空或者里面沒有內容的時候，         / 我們insert是會失敗的（底層數(shù)據(jù)庫不允許插入一個空行），         / 為了防止這種情況，我們要在這里指定一個列名，    &#