android数据存储<三>-----Sqlite操作

1.本文采用单元测试的方式来测试Sqlite数据的增删改查操作。因此在清单文件中需要注册单元测试环境

       <uses-library android:name="android.test.runner" />                  <activity            android:name="com.example.database.MainActivity"            android:label="@string/app_name" >            <intent-filter>                <action android:name="android.intent.action.MAIN" />                               <category android:name="android.intent.category.LAUNCHER" />            </intent-filter>        </activity>     </application><instrumentation android:name="android.test.InstrumentationTestRunner"  android:targetPackage="com.example.database" android:label="Tests for My App" /></manifest>

2.创建一个数据库文件dbservice.java 用来建立一个数据库和更新数据库表，并创建一个记类contact类  

package com.example.databaseService;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;public class Dbservice extends SQLiteOpenHelper{public Dbservice(Context context) {super(context, "contacts.db", null,2);}/*创建一个数据库表格,并定义字段*/@Overridepublic void onCreate(SQLiteDatabase db) {db.execSQL("CREATE TABLE contacts (contactID integer primary key autoincrement,name varchar(20),phonenum vachar(12) NULL)");}/*新增加一个字段，并更新数据库*/@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {// TODO Auto-generated method stubdb.execSQL("ALTER TABLE contacts ADD amount vachar(12) NULL");} }

package com.example.databaseService;public class contacts {  private Integer contactID;  private String name;  private String phonenum;  private Integer amount;  public contacts( String name, String phonenum, Integer amount) {this.name = name;this.phonenum = phonenum;this.amount = amount;}public contacts(Integer contactId, String name, String phonenum, Integer amount) {this.contactID = contactId;this.name = name;this.phonenum = phonenum;this.amount = amount;} public contacts() {}public Integer getContactId() {return contactID;  } public void SetContactId(Integer ID) {this.contactID=ID;  } public String  getName() {return name;  } public void SetName(String name) {this.name=name;  } public String  getphonenum() {return phonenum;  } public void Setphonename(String num) {this.phonenum=num;  }  public Integer getAmount() {return amount;  } public void SetAmount(Integer amount) {this.amount=amount;  }@Overridepublic String toString() {   return "contact [contactID=" + contactID + ", name=" + name + ", phonenum=" + phonenum+ ", amount=" +amount + "]";}}

3.创建一个数据库操作类 dbOperation.Java用来完成数据库记录的增删改查

/*以下是采用sql语句来完成操作，常规做法是这样的，但也可以采用googleAPI来完成*/package com.example.databaseService;import java.util.ArrayList;import java.util.List;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.util.Log;public class dataOperation {Context context;private Dbservice db;final String mytag="mydb";   public dataOperation(Context context) {   this.db = new Dbservice(context);}/*增加一个记录*/public void adddata(contacts ct)   {   SQLiteDatabase sqdb = db.getWritableDatabase();sqdb.execSQL("insert into contacts(name, phonenum, amount) values(?,?,?)",new Object[]{ct.getName(), ct.getphonenum(), ct.getAmount()});      }/*删除一个记录*/public void deletedata(Integer ID){          SQLiteDatabase sqdb = db.getWritableDatabase();sqdb.execSQL("delete from contacts where contactID=?", new Object[]{ID});Log.i(mytag,ID.toString());Log.i(mytag,"hello,hello");   }/*修改一个记录*/public void changedata(contacts ct,Integer ID){ SQLiteDatabase sqdb = db.getWritableDatabase(); sqdb.execSQL("update contacts set name=?, phonenum=?, amount=? where contactID=?",new Object[]{ct.getName(), ct.getphonenum(), ct.getAmount(),ID});}/*查询一个记录*/public contacts querydata(Integer ID){ SQLiteDatabase sqdb = db.getReadableDatabase(); Cursor cursor =sqdb.rawQuery("select * from contacts where contactID=?", new String[]{ID.toString()}); if(cursor.moveToFirst()) { String name=cursor.getString(cursor.getColumnIndex("name"));String phonenum= cursor.getString(cursor.getColumnIndex("phonenum")); Integer amount=cursor.getInt(cursor.getColumnIndex("amount")); return new contacts(ID,name,phonenum,amount); } cursor.close(); return null;  }/*统计记录的个数*/public  long countdata(){ SQLiteDatabase sqdb = db.getReadableDatabase(); Cursor cursor =sqdb.rawQuery("select count(*) from contacts", null); cursor.moveToFirst(); return cursor.getLong(0);}/** * 分页获取记录 * @param offset 跳过前面多少条记录 * @param maxResult 每页获取多少条记录 * @return */public List<contacts> getScrollData(Integer offset, Integer maxResult){List<contacts> cts = new ArrayList<contacts>();SQLiteDatabase sqdb = db.getReadableDatabase();Cursor cursor =sqdb.rawQuery("select * from contacts order by contactID asc limit ?,?", new String[]{offset.toString(),maxResult.toString()});while(cursor.moveToNext()){int contactsID = cursor.getInt(cursor.getColumnIndex("contactID"));String name = cursor.getString(cursor.getColumnIndex("name"));String phonenum = cursor.getString(cursor.getColumnIndex("phonenum"));int amount = cursor.getInt(cursor.getColumnIndex("amount"));cts.add(new contacts(contactsID, name, phonenum, amount));}cursor.close();return cts;}/** * 分页获取记录 * @param offset 跳过前面多少条记录 * @param maxResult 每页获取多少条记录 * @return */public Cursor getcursorScrollData(Integer offset, Integer maxResult){SQLiteDatabase sqdb = db.getReadableDatabase();Cursor cursor =sqdb.rawQuery("select * from contacts order by contactID asc limit ?,?", new String[]{offset.toString(),maxResult.toString()});return cursor;}}

以上是采用Sql语句来操作，当然也可以不采用SQl语句，Android有相关接口直接操作记录。dbOperation2.java就是如下：

/*采用sql语句来完成操作，常规做法是这样的，但也可以采用googleAPI来完成*/package com.example.databaseService;  import java.util.ArrayList;import java.util.List;import android.content.ContentValues;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;public class dataOperation2 {Context context;private Dbservice db;final String mytag="mydb";   public dataOperation2(Context context) {   this.db = new Dbservice(context);}/*增加一个记录*/public void adddata2(contacts ct)   {    ContentValues values=new ContentValues();  values.put("name", ct.getName());  values.put("phonenum", ct.getphonenum());  values.put("amount", ct.getAmount());   SQLiteDatabase sqdb = db.getWritableDatabase();sqdb.insert("contacts", null, values);      }/*删除一个记录*/public void deletedata2(Integer ID){          SQLiteDatabase sqdb = db.getWritableDatabase();   sqdb.delete("contacts", "contactID=?", new String[]{ID.toString()} );   }/*修改一个记录*/public void changedata2(contacts ct,Integer ID){ SQLiteDatabase sqdb = db.getWritableDatabase(); ContentValues values=new ContentValues();  values.put("name", ct.getName());  values.put("phonenum", ct.getphonenum());  values.put("amount", ct.getAmount()); sqdb.update("contacts", values, "contactID=?",new String[]{ID.toString()} );}/*查询一个记录*/public contacts querydata(Integer ID){ SQLiteDatabase sqdb = db.getReadableDatabase(); Cursor cursor =sqdb.rawQuery("select * from contacts where contactID=?", new String[]{ID.toString()}); if(cursor.moveToFirst()) { String name=cursor.getString(cursor.getColumnIndex("name"));String phonenum= cursor.getString(cursor.getColumnIndex("phonenum")); Integer amount=cursor.getInt(cursor.getColumnIndex("amount")); return new contacts(ID,name,phonenum,amount); } cursor.close(); return null;  }/*统计记录的个数*/public  long countdata(){ SQLiteDatabase sqdb = db.getReadableDatabase(); Cursor cursor =sqdb.rawQuery("select count(*) from contacts", null); cursor.moveToFirst(); return cursor.getLong(0);}/** * 分页获取记录 * @param offset 跳过前面多少条记录 * @param maxResult 每页获取多少条记录 * @return */public List<contacts> getScrollData(Integer offset, Integer maxResult){List<contacts> cts = new ArrayList<contacts>();SQLiteDatabase sqdb = db.getReadableDatabase();Cursor cursor =sqdb.rawQuery("select * from contacts order by contactID asc limit ?,?", new String[]{offset.toString(),maxResult.toString()});while(cursor.moveToNext()){int contactsID = cursor.getInt(cursor.getColumnIndex("contactID"));String name = cursor.getString(cursor.getColumnIndex("name"));String phonenum = cursor.getString(cursor.getColumnIndex("phonenum"));int amount = cursor.getInt(cursor.getColumnIndex("amount"));cts.add(new contacts(contactsID, name, phonenum, amount));}cursor.close();return cts;}/** * 分页获取记录 * @param offset 跳过前面多少条记录 * @param maxResult 每页获取多少条记录 * @return */public Cursor getcursorScrollData(Integer offset, Integer maxResult){SQLiteDatabase sqdb = db.getReadableDatabase();Cursor cursor =sqdb.rawQuery("select * from contacts order by contactID asc limit ?,?", new String[]{offset.toString(),maxResult.toString()});return cursor;}}

写一个测试用来进行测试：

package com.example.test;import java.util.List;import com.example.databaseService.Dbservice;import com.example.databaseService.contacts;import com.example.databaseService.dataOperation;import com.example.databaseService.dataOperation2;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.test.AndroidTestCase;import android.util.Log;public class dbtest extends AndroidTestCase { final String mytag="mydbtest";public void  creatdb(){ Dbservice db1= new Dbservice(this.getContext()); db1.getWritableDatabase();}public void adddbdata(){dataOperation Op=new dataOperation(this.getContext());for(int i = 0 ; i < 20 ; i++){contacts cts = new contacts("zhangxx"+ i, "136765765"+ i, 100);Op.adddata(cts);}}public void adddbdata2(){dataOperation2 Op=new dataOperation2(this.getContext());contacts cts = new contacts("zhangxx5", "136765765", 200);Op.adddata2(cts);}    public void deletadbdata2(){ dataOperation2 Op=new dataOperation2(this.getContext());Op.deletedata2(6);}public void deletadbdata(){ dataOperation Op=new dataOperation(this.getContext());Op.deletedata(20);Op.deletedata(10);}public void updatedata(){ dataOperation Op=new dataOperation(this.getContext());contacts cts = new contacts("wangxiaoming", "13787701111", 150);    Op.changedata(cts, 3);}public void updatedata2(){ dataOperation2 Op=new dataOperation2(this.getContext());contacts cts = new contacts("wxy", "13787701111", 250);    Op.changedata2(cts, 3);}public void querydata(){ dataOperation Op=new dataOperation(this.getContext());contacts cts=Op.querydata(4);if (cts==null){Log.e(mytag,"sorry,The recoder is not exist");}else{Log.e(mytag,cts.toString());}}public void getconut(){dataOperation Op=new dataOperation(this.getContext());Log.e(mytag,String.valueOf(Op.countdata()));}public void ScrollData() {dataOperation Op=new dataOperation(this.getContext());List<contacts> cts=Op.getScrollData(0, 10);for(contacts ct:cts){Log.e(mytag,ct.toString());}}public void ScrollData2() {Cursor cursor;dataOperation Op=new dataOperation(this.getContext());    cursor=Op.getcursorScrollData(0, 5);    while(cursor.moveToNext())    {    int contactsID = cursor.getInt(cursor.getColumnIndex("contactID"));    String name = cursor.getString(cursor.getColumnIndex("name"));    String phonenum = cursor.getString(cursor.getColumnIndex("phonenum"));    int amount = cursor.getInt(cursor.getColumnIndex("amount"));       Log.e(mytag, new contacts(contactsID, name, phonenum, amount).toString());    }    } /*事务交易，因为事物交易是原子性的，所以需要不可用简单的数据更新来完成*/public void Paytransaction(){Dbservice db =new Dbservice(this.getContext());SQLiteDatabase sqdb = db.getWritableDatabase();sqdb.beginTransaction();{try{        sqdb.execSQL("update contacts set amount=amount-10 where contactID=3");        sqdb.execSQL("update contacts set amount=amount+10 where contactID=2");        sqdb.setTransactionSuccessful();}catch(Exception e){Log.e(mytag, "Sorry,transaction is failed\n");}finally{        sqdb.endTransaction();}}}}

在这里增加了一个事务操作，事物操作必须是原子性操作，否则会造出交易失败。

实验验证方法：通过单元测试，操作数据库，将应用程序下的数据库导出到桌面。然后用sqlite explore查看即可。

如：