Android學習筆記---Android利用Sqlite,並且用sql語言操作SQLite數據庫

6.對數據庫進行操作:   一下是對數據庫的操作代碼,包括添加,刪除,更新,查詢,和分頁,統計 ------------------------------------------------------------------- /DBSQLIte/src/com/credream/entity/Person.java package com.credream.entity; public class Person { private Integer id; private String name; private String phone; public Person() {   }   public Person(Integer id, String name, String phone) { this.id = id; this.name = name; this.phone = phone; }     public Person(String name, String phone) { this.name = name; this.phone = phone; }         public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone; }             @Override public String toString() { return "Person [id=" + id + ", name=" + name + ", phone=" + phone + "]"; } } ---------------------------------------------------------- /DBSQLIte/src/com/credream/service/DBOpenHelter.java package com.credream.service;     import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.database.sqlite.SQLiteDatabase.CursorFactory;     public class DBOpenHelter extends SQLiteOpenHelper { //父類沒有默認構造器,需要顯示調用 public DBOpenHelter(Context context) { super(context, "credream.db", null, 2); //數據庫創建完成後,默認會保存在<包>/database/文件夾下 //當修改版本號時候,會觸發:onUpgrade方法 //第二個:指定數據庫名稱, //第三個:游標工廠,用來迭代,查詢後的結果集,null代表使用系統默認的     游標工廠 //版本號,大於0   } /**  * 這個方法是在數據庫第一次被創建的時候調用的  */ @Override public void onCreate(SQLiteDatabase db) { //SQLiteDatabase這個類,封裝了增刪改查操作,也叫做數據庫操作實例 db.execSQL("CREATE TABLE person (personid integer primary key      autoincrement, name varchar(20))"); //這裡也可以不寫name的數據類型,因為sqlite是數據類型無關的,就是寫     了varchar(20),也可以寫入超過20的內容     } /**  * 當數據庫的版本號變更的時候被調用  */ @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("alter table person add phone varchar(12) null");     }     } --------------------------------------------- /DBSQLIte/src/com/credream/service/PersonService.java package com.credream.service;     import java.util.ArrayList; import java.util.List;     import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import com.credream.entity.Person;     public class PersonService { private DBOpenHelter dbOpenHelter; public PersonService(Context context){ this.dbOpenHelter=new DBOpenHelter(context); } /** * 添加記錄 * @param person */ public void save (Person person){ SQLiteDatabase db=dbOpenHelter.getWritableDatabase(); //SQLiteDatabase db2=dbOpenHelter.getWritableDatabase(); //這裡獲得的數據庫實例和db這個數據庫實例是一樣的,因為數據庫有緩存     功能 //在源碼裡進行了判斷,如果得到的數據庫實例不為null,並且已經打開,並     且是只讀的,那麼 //就直接返回這個實例         //dbOpenHelter.getWritableDatabase().execSQL(sql);這裡和db.execSQL("作用     是一樣的 //db.execSQL("insert into person (name,phone) values     ('"+person.getName()+"','"+person.getPhone()+"')");    //上面這種寫法是錯誤的,因為當用戶輸入cre'dream的時候那麼sql語句就成了 //insert into person (name,phone) values     ('cre'dream','15066659146')這樣會出現sql語法錯誤 //所以,應該用轉義字符 db.execSQL("insert into person (name,phone) values(?,?)",new      Object[]{person.getName(),person.getPhone()}); //db.close();//數據庫也可以不關閉,因為這樣的話可以提升性能,因為不用頻繁的     開關數據庫   } /** * 刪除記錄 * @param person */ public void delete (Integer id){ SQLiteDatabase db=dbOpenHelter.getWritableDatabase(); db.execSQL("delete from person  where personid=?",new Object[]     {id}); } /** * 更新記錄 * @param person */ public void update(Person person){ SQLiteDatabase db=dbOpenHelter.getWritableDatabase(); db.execSQL("update  person set name=?,phone=?   where      personid=?",new Object[]{person.getName(),person.getPhone(),person.getId()});   } /** * 查找記錄 * @param id * @return */ public Person find (Integer id){ SQLiteDatabase db=dbOpenHelter.getReadableDatabase(); //getReadableDatabase();這個方法裡面調用了getWritableDatabase();     方法,來取得數據庫操作實例,只有調用 //失敗的時候就會發現異常,當數據磁盤滿了,會拋出異常,這時候會打開這     個實例的數據庫 //然後開始讀取,這樣當磁盤滿的時候, //當數據庫磁盤空間沒有滿的時候,返回的數據庫操作實例是一樣的:可讀     可寫,當數據庫磁盤滿了,返回的數據庫 //操作實例是只讀的 Cursor cursor=db.rawQuery("select * from person  where personid=?",new      String[]{id.toString()}); //游標存放結果 if(cursor.moveToFirst()){ int personid=cursor.getInt(cursor.getColumnIndex("personid")); String name=cursor.getString(cursor.getColumnIndex("name")); String phone=cursor.getString(cursor.getColumnIndex("phone")); return new Person(personid,name,phone);   }//當有數據會返回true,否則為false cursor.close(); return null; }   //處理分頁 /** * 分頁獲取記錄 * offset:跳過前面幾條記錄 * maxlength:獲取幾條 */ public List<Person> getScrollData(int offset,int MaxResult){ List<Person> persons=new ArrayList<Person>(); SQLiteDatabase db=dbOpenHelter.getReadableDatabase(); Cursor cursor=db.rawQuery("select * from person order by personid asc      limit ?,?", new String[]{String.valueOf(offset),String.valueOf     (MaxResult)}); while(cursor.moveToNext()){ int personid=cursor.getInt(cursor.getColumnIndex("personid")); String name=cursor.getString(cursor.getColumnIndex("name")); String phone=cursor.getString(cursor.getColumnIndex("phone")); persons.add(new Person(personid,name,phone)); } cursor.close(); return persons; }     public long getCount(){ SQLiteDatabase db=dbOpenHelter.getReadableDatabase(); Cursor cursor=db.rawQuery("select count(*) from person " ,null); //select count(*) from person注意這裡至少會獲得一條數據 cursor.moveToFirst(); long result=cursor.getLong(0); return result; }       } ----------------------------------------------------------- /DBSQLIte/src/com/credream/test/PersonServiceTest.java package com.credream.test;     import java.util.List;     import com.credream.entity.Person; import com.credream.service.DBOpenHelter; import com.credream.service.PersonService;     import android.test.AndroidTestCase; import android.util.Log;     public class PersonServiceTest extends AndroidTestCase { private static final String TAG="PersonServiceTest"; //創建數據庫,在<包>/database/ public void testCreateDB()throws Exception{ DBOpenHelter dbOpenHelter=new DBOpenHelter(getContext()); dbOpenHelter.getWritableDatabase(); } public void testSave()throws Exception{ PersonService service=new PersonService(this.getContext()); for(int i=0;i<20;i++){ Person person=new Person("lidewei"+i,"15163245754"+i); service.save(person); } } public void testUpdate()throws Exception{ PersonService service=new PersonService(this.getContext()); Person person=service.find(1); person.setName("credream"); service.update(person);   }     public void testDelete()throws Exception{ PersonService service=new PersonService(this.getContext()); service.delete(21); }     public void testFind()throws Exception{ PersonService service=new PersonService(this.getContext()); Person person=service.find(1); Log.i(TAG,person.toString());   }     public void testScrollData()throws Exception{ PersonService service=new PersonService(this.getContext()); List<Person> persons= service.getScrollData(5, 5); for(Person person :persons){ Log.i(TAG,person.toString()); } //翻到第2頁   } public void testCount()throws Exception{ PersonService service=new PersonService(this.getContext()); long result=service.getCount(); Log.i(TAG, result+"");   } //除了以上的數據庫操作為,還為我們提供了專門方法:       } ------------------------------------------------------------ 3.代碼寫完後在相應的方法上用android junit 進行測試