Android操作SQLite数据库(增、删、改、查、分页等)及ListView显示数据的方法详解

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://scheR_974_11845@as.android.com/apk/res/android"
  package="com.example.dboperate"
  android:versionCode="1"
  android:versionName="1.0" >
  <uses-sdk android:minSdkVersion="8" />
  <application
    android:icon="@drawable/ic_launcher"
    android:label="@String/app_name" >
    <!--单元测试 加这句-->
    <uses-library android:name="android.test.runner" />
    <activity
      android:name=".DBsqliteOperateActivity"
      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.dboperate"
 android:label="Test for my app"/>
</manifest>

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://scheR_974_11845@as.android.com/apk/res/android"
  android:layout_width="fill_parent"
  android:layout_height="fill_parent"
  android:orientation="vertical" >
  <LinearLayout android:layout_width="fill_parent"
  android:layout_height="wrap_content"
  android:orientation="horizontal" >
  <TextView
    android:id="@+id/name"
    android:layout_width="100dip"
    android:layout_height="wrap_content"
    android:text="@String/name"
    android:gravity="center"/>
  <TextView
    android:id="@+id/phone"
    android:layout_width="100dip"
    android:layout_height="wrap_content"
    android:text="@String/phone"
    android:gravity="center"/>
  <TextView
    android:id="@+id/amount"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:text="@String/amount"
    android:gravity="center"/>
</LinearLayout>
  <ListView
    android:id="@+id/listView"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent" >
  </ListView>
</LinearLayout>

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://scheR_974_11845@as.android.com/apk/res/android"
  android:layout_width="fill_parent"
  android:layout_height="fill_parent"
  android:orientation="horizontal" >
  <TextView
    android:id="@+id/name"
    android:layout_width="100dip"
    android:layout_height="wrap_content"
    android:text="@String/name"
    android:gravity="center"/>
  <TextView
    android:id="@+id/phone"
    android:layout_width="100dip"
    android:layout_height="wrap_content"
    android:text="@String/phone"
    android:gravity="center"/>
  <TextView
    android:id="@+id/amount"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:text="@String/amount"
    android:gravity="center"/>
</LinearLayout>

<?xml version="1.0" encoding="utf-8"?>
<resources>
  <String name="Hello">Hello World,DBsqliteOperateActivity!</String>
  <String name="app_name">ExampleDBsqliteOperate8</String>
  <String name="name">姓名</String>
  <String name="phone">电话</String>
  <String name="amount">存款</String>
</resources>

package com.example.dboperate;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.example.adapter.PersonAdapter;
import com.example.domain.Person;
import com.example.service.Personservice;
import android.app.Activity;
import android.database.cursor;
import android.os.bundle;
import android.view.View;
import android.view.View.onClickListener;
import android.widget.AdapterView;
import android.widget.AdapterView.onItemClickListener;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.SimplecursorAdapter;
import android.widget.Toast;
public class DBsqliteOperateActivity extends Activity {
 ListView listView;
 Personservice personservice;
 OnItemClickListener listViewListener;
  /** Called when the activity is first created. */
  @Override
  public void onCreate(Bundle savedInstanceStatE) {
    super.onCreate(savedInstanceStatE);
    setContentView(R.layout.main);
    listViewListener = new OnItemClickListener(){
  @Override
  public void onItemClick(AdapterView<?> parent,View view,int position,long id) {
  //得到listView控件
  ListView listView = (ListView)parent;
  //1、如果使用自定义适配器，返回的是Person对象
  //得到该条目数据
//  Person person = (Person)listView.getItemAtPosition(position);
//  //广播出去
//  Toast.makeText(getApplicationContext(),person.toString(),Toast.LENGTH_LONG).show();
  //2、如果使用showList2()方法中的适配器时，则取得的值是不一样的,返回的是cursor
//  cursor cursor = (cursor)listView.getItemAtPosition(position);
//  int personid = cursor.geTint(cursor.getcolumnIndex("_id"));
//  Toast.makeText(getApplicationContext(),personid+"",Toast.LENGTH_LONG).show();
  //3、如果使用showList()方法中的适配器时，则取得的值是不一样的,返回的是map
  @SuppressWarnings("unchecked")
  Map<String,Object> map = (Map)listView.getItemAtPosition(position);
  String name = map.get("name").toString();
  String personid = map.get("personid").toString();
  Toast.makeText(getApplicationContext(),personid +"-"+ name,Toast.LENGTH_LONG).show();
  }
    };
    listView = (ListView) this.findViewById(R.id.listView);
    listView.setOnItemClickListener(listViewListener);
    personservice = new Personservice(this);
    showList();
  }
 private void showList() {
 List<Person> persons = personservice.getScrollData(0,50);
 List<HashMap<String,Object>> data = new ArrayList<HashMap<String,Object>>();
 for(Person person : persons){
  HashMap<String,Object> item = new HashMap<String,Object>();
  item.put("name",person.getName());
  item.put("phone",person.getPhone());
  item.put("amount",person.getamount());
  item.put("personid",person.getId());
  data.add(item);
 }
 SimpleAdapter adapter = new SimpleAdapter(this,data,R.layout.item,new String[]{"name","phone","amount"},new int[]{R.id.name,R.id.phone,R.id.amount});
   listView.setAdapter(adapter);
 }
 public void showList2(){
 cursor cursor = personservice.getcursorScrollData(0,50);
 //该适配器要求返回的结果集cursor必须包含_id字段，所以需要对取得结果集进行处理
 SimplecursorAdapter adapter = new SimplecursorAdapter(this,cursor,R.id.amount} );
 listView.setAdapter(adapter);
 }
 /**
 * 自定义适配器
 */
 public void showList3(){
 List<Person> persons = personservice.getScrollData(0,50);
 /**
  * 第一个参数：上下文context，第二个参数：要显示的数据，第三个参数：绑定的条目界面
  */
 PersonAdapter adapter = new PersonAdapter(this,persons,R.layout.item);
 listView.setAdapter(adapter);
 }
}

package com.example.domain;
public class Person {
 private Integer id;
 private String name;
 private String phone;
 private Integer amount;
 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;
 }
 public Person(String name,String phonE) {
 this.name = name;
 this.phone = phone;
 }
 public Person(Integer id,String name,String phone,Integer amount) {
 super();
 this.id = id;
 this.name = name;
 this.phone = phone;
 this.amount = amount;
 }
 public Person() {
 super();
 }
 public Integer getamount() {
 return amount;
 }
 public void setamount(Integer amount) {
 this.amount = amount;
 }
 @Override
 public String toString() {
 return "Person [id=" + id + ",name=" + name + ",phone=" + phone
  + ",amount=" + amount + "]";
 }
}

package com.example.service;
import android.content.Context;
import android.database.sqlite.sqliteDatabase;
import android.database.sqlite.sqliteDatabase.cursorFactory;
import android.database.sqlite.sqliteOpenHelper;
public class DBOperateHelper extends sqliteOpenHelper {
 public DBOperateHelper(Context context) {//默认创建的数据库文件保存在<包名>/database/
 //第一个参数是上下文，第二个参数是数据库名称，第三个是游标工厂为null时使用 数据库默认的游标工厂,第四个是数据库版本号但是不能为0，一般大于0
 super(context,"smallpig",null,4);
 }
 /**
 * 数据库每一次被创建时被调用
 */
 @Override
 public void onCreate(sqliteDatabase sqldb) {
 sqldb.execsql("create table person(personid INTEGER PRIMary key autoincrement,name varchar(20),phone varchar(12) null)");
 }
  /**
   * 每一次数据库版本号发生变动时触发此方法
   * 比如如果想往数据库中再插入一些表、字段或者其他信息时通过修改数据库版本号来触发此方法
   */
 @Override
 public void onUpgrade(sqliteDatabase db,int oldVersion,int newVersion) {
 //db.execsql("alter table person add phone varchar(12) null");\
 db.execsql("alter table person add amount Integer null");
 }
}

package com.example.service;
import java.util.ArrayList;
import java.util.List;
import android.content.Context;
import android.database.cursor;
import android.database.sqlite.sqliteDatabase;
import com.example.domain.Person;
public class Personservice {
 private DBOperateHelper dbOperateHelper;
 public Personservice(Context context) {
 this.dbOperateHelper = new DBOperateHelper(context);
 }
  /**
   * 保存记录
   * @param person
   */
 public void save(Person person){
 //得到数据库实例，里面封装了数据库操作方法
 sqliteDatabase sqldb = dbOperateHelper.getWritableDatabase();
   //sqldb.execsql("insert into person(name,phonE) values('"+person.getName()+"','"+person.getPhone()+"')");
 //利用占位符可以避免注入，但是注意数组参与与占位符对应的字段要一一对应
 sqldb.execsql("insert into person(name,phone,amount) values(?,?,?)",new Object[]{person.getName(),person.getPhone(),person.getamount()});
   //关闭数据库
 sqldb.close();
 }
 /**
 * 删除记录
 * @param id
 */
 public void delete(Integer id){
 sqliteDatabase sqldb = dbOperateHelper.getWritableDatabase();
 sqldb.execsql("delete from person where personid=?",new Object[]{iD});
 sqldb.close();
 }
 /**
 * 更新记录
 * @param person
 */
 public void update(Person person){
 sqliteDatabase sqldb = dbOperateHelper.getWritableDatabase();
 sqldb.execsql("update person set name=?,phone=?,amount=? where personid=?",person.getamount(),person.getId()});
 sqldb.close();
 }
 /**
 * 通过ID查询记录
 * @param id
 * @return
 */
 public Person find(Integer id){
 /**
  * getWritableDatabase 与 getReadableDatabase 的区别：
  * getReadableDatabase会先返回getWritableDatabase(可写)，如果调用getWritableDatabase失败
  * 则才会调用getReadableDatabase后续方法，使数据库只读
  * 当写入的数据超过数据库大小则调用getWritableDatabase会失败
  * 所以只读时则可以使用此方法，其它情况(只要不是超过数据库大小)也可以使用此方法
  */
 sqliteDatabase sqldb = dbOperateHelper.getReadableDatabase();
 cursor cursor = sqldb.rawQuery("SELEct * from person where personid=?",new String[]{String.valueOf(id)});
 int personid;
 String name;
 String phone;
 int amount;
 Person person = null;
 if(cursor.moveToFirst()){
  personid = cursor.geTint(cursor.getcolumnIndex("personid"));
  name = cursor.getString(cursor.getcolumnIndex("name"));
  phone = cursor.getString(cursor.getcolumnIndex("phone"));
  amount = cursor.geTint(cursor.getcolumnIndex("amount"));
  person = new Person(personid,name,amount);
 }
 cursor.close();
 return person;
 }
 /**
 * 返回指定长度记录，limit 3,5,适用于分页
 * @param offset 起始
 * @param maxResult 长度
 * @return
 */
 public List<Person> getScrollData(int offset,int maxResult){
 sqliteDatabase sqldb = dbOperateHelper.getReadableDatabase();
 cursor cursor = sqldb.rawQuery("SELEct * from person order by personid asc limit ?,?",new String[]{String.valueOf(offset),String.valueOf(maxResult)});
 int personid;
 String name;
 String phone;
 int amount;
 Person person = null;
 List<Person> persons = new ArrayList<Person>();
 while(cursor.moveToNext()){
  personid = cursor.geTint(cursor.getcolumnIndex("personid"));
  name = cursor.getString(cursor.getcolumnIndex("name"));
  phone = cursor.getString(cursor.getcolumnIndex("phone"));
  amount = cursor.geTint(cursor.getcolumnIndex("amount"));
  person = new Person(personid,amount);
  persons.add(person);
 }
 cursor.close();
 return persons;
 }
 /**
 * 返回cursor
 * @param offset 起始
 * @param maxResult 长度
 * @return
 */
 public cursor getcursorScrollData(int offset,int maxResult){
 sqliteDatabase sqldb = dbOperateHelper.getReadableDatabase();
 cursor cursor = sqldb.rawQuery("SELEct personid as _id,amount from person order by personid asc limit ?,String.valueOf(maxResult)});
 return cursor;
 }
 /**
 * 返回总记录数
 * @return
 */
 public long getCount(){
 sqliteDatabase sqldb = dbOperateHelper.getReadableDatabase();
 cursor cursor = sqldb.rawQuery("SELEct count(*) from person",null);
 //该查询语句值返回一条语句
 cursor.moveToFirst();
 long result = cursor.getLong(0);
 cursor.close();
 return result;
 }
 public void payment(){
 sqliteDatabase sqldb = dbOperateHelper.getWritableDatabase();
 sqldb.begintransaction();//开启事务
 try{
  sqldb.execsql("update person set amount = amount -10 where personid=1");
  sqldb.execsql("update person set amount = amount + 10 where personid=2");
  sqldb.settransactionsuccessful();//设置事务标志位true
 } finally {
  //结束事务：有两种情况：commit\rollBACk，事务提交或者回滚是由事务的标识决定的
  //事务为ture则提交，事务为flase则回滚，默认为false
  sqldb.endtransaction();
 }
 }
}

package com.example.service;
import java.util.ArrayList;
import java.util.List;
import com.example.domain.Person;
import android.content.ContentValues;
import android.content.Context;
import android.database.cursor;
import android.database.sqlite.sqliteDatabase;
import android.database.sqlite.sqliteDatabase.cursorFactory;
import android.database.sqlite.sqliteOpenHelper;
public class OtherPersonservice {
 private DBOperateHelper dbOperateHelper;
 public OtherPersonservice(Context context) {
 this.dbOperateHelper = new DBOperateHelper(context);
 }
  /**
   * 保存记录
   * @param person
   */
 public void save(Person person){
 //得到数据库实例，里面封装了数据库操作方法
 sqliteDatabase sqldb = dbOperateHelper.getWritableDatabase();
   //sqldb.execsql("insert into person(name,'"+person.getPhone()+"')");
 //利用占位符可以避免注入，但是注意数组参与与占位符对应的字段要一一对应
 //sqldb.execsql("insert into person(name,phonE) values(?,person.getPhone()});
 ContentValues values = new ContentValues();
 values.put("name",person.getName());
 values.put("phone",person.getPhone());
 values.put("amount",person.getamount());
 //第一个参数是表名，第三个为字段值集合，第二个参数是空值字段，当第三个字段值集合为空时，系统会自动插入一条第二个参数为空的sql语句
 //否则当第三个参数为空时，如果第二个参数也为空，那么插入表就会找不到插入的字段信息，会报错
 sqldb.insert("person","name",values );
 //关闭数据库
 sqldb.close();
 }
 /**
 * 删除记录
 * @param id
 */
 public void delete(Integer id){
 sqliteDatabase sqldb = dbOperateHelper.getWritableDatabase();
 //sqldb.execsql("delete from person where personid=?",new Object[]{iD});
 //第一个参数是表名，第二个是where后面的条件用占位符表示，第三个对应占位符为参数值
 sqldb.delete("person","personid=?",new String[]{Integer.toString(id)});
 sqldb.close();
 }
 /**
 * 更新记录
 * @param person
 */
 public void update(Person person){
 sqliteDatabase sqldb = dbOperateHelper.getWritableDatabase();
 //sqldb.execsql("update person set name=?,phone=? where personid=?",person.getId()});
 //第一个参数为表名，第二个是一个更新值集合，采用键值对的形式，每个更新的字段对应更新值
 //第三个参数是where后面条件字段用占位符标识，第四个参数是对应where占位符的值
 ContentValues values = new ContentValues();
 values.put("name",person.getamount());
 sqldb.update("person",values,new String[]{person.getId().toString()});
 sqldb.close();
 }
 /**
 * 通过ID查询记录
 * @param id
 * @return
 */
 public Person find(Integer id){
 /**
  * getWritableDatabase 与 getReadableDatabase 的区别：
  * getReadableDatabase会先返回getWritableDatabase(可写)，如果调用getWritableDatabase失败
  * 则才会调用getReadableDatabase后续方法，使数据库只读
  * 当写入的数据超过数据库大小则调用getWritableDatabase会失败
  * 所以只读时则可以使用此方法，其它情况(只要不是超过数据库大小)也可以使用此方法
  */
 sqliteDatabase sqldb = dbOperateHelper.getReadableDatabase();
 //cursor cursor = sqldb.rawQuery("SELEct * from person where personid=?",new String[]{String.valueOf(id)});
 //第一个参数是表名；第二个参数是查询显示的字段，null时默认查询显示所有字段；
 //第三个参数是where查询条件占位符；第四个是占位符对应的值；
 //第五个参数是group by条件；第六个是having条件；第七个是order by条件
 cursor cursor = sqldb.query("person",new String[]{id.toString()},null);
 int personid;
 String name;
 String phone;
 int amount;
 Person person = null;
 if(cursor.moveToFirst()){
  personid = cursor.geTint(cursor.getcolumnIndex("personid"));
  name = cursor.getString(cursor.getcolumnIndex("name"));
  phone = cursor.getString(cursor.getcolumnIndex("phone"));
  amount = cursor.geTint(cursor.getcolumnIndex("amount"));
  person = new Person(personid,int maxResult){
 sqliteDatabase sqldb = dbOperateHelper.getReadableDatabase();
 //cursor cursor = sqldb.rawQuery("SELEct * from person order by personid asc limit ?,String.valueOf(maxResult)});
 //第一个参数是表名；第二个参数是查询显示的字段，null时默认查询显示所有字段；
 //第三个参数是where查询条件占位符；第四个是占位符对应的值；
 //第五个参数是group by条件；第六个是having条件；第七个是order by条件
 //第八个参数是limit ?,? 条件
 cursor cursor = sqldb.query("person","personid",offset+","+maxResult);
 int personid;
 String name;
 String phone;
 int amount;
 Person person = null;
 List<Person> persons = new ArrayList<Person>();
 while(cursor.moveToNext()){
  personid = cursor.geTint(cursor.getcolumnIndex("personid"));
  name = cursor.getString(cursor.getcolumnIndex("name"));
  phone = cursor.getString(cursor.getcolumnIndex("phone"));
  amount = cursor.geTint(cursor.getcolumnIndex("amount"));
  person = new Person(personid,amount);
  persons.add(person);
 }
 cursor.close();
 return persons;
 }
 /**
 * 返回总记录数
 * @return
 */
 public long getCount(){
 sqliteDatabase sqldb = dbOperateHelper.getReadableDatabase();
 //cursor cursor = sqldb.rawQuery("SELEct count(*) from person",null);
 //第一个参数是表名；第二个参数是查询显示的字段，null时默认查询显示所有字段；
 //第三个参数是where查询条件占位符；第四个是占位符对应的值；
 //第五个参数是group by条件；第六个是having条件；第七个是order by条件
 cursor cursor = sqldb.query("person",new String[]{"count(*)"},null);
 //该查询语句值返回一条语句
 cursor.moveToFirst();
 long result = cursor.getLong(0);
 cursor.close();
 return result;
 }
}

package com.example.test;
import java.util.List;
import com.example.domain.Person;
import com.example.service.DBOperateHelper;
import com.example.service.Personservice;import android.test.AndroidTESTCase;
import android.util.Log;
public class PersonserviCETest extends AndroidTESTCase {
 public void testcreatedb() throws Exception{
 DBOperateHelper dbHelper = new DBOperateHelper(getContext());
 dbHelper.getWritableDatabase();
 }
 public void testSave() throws Exception{
 Personservice ps = new Personservice(getContext());
 for(int i=1;i<=100;i++){
  Person person = new Person();
  person.setName("我是"+i);
  person.setPhone(String.valueOf(Long.parseLong("18888888800")+i));
  ps.save(person);
  Log.i("Personservice",person.toString());
 }
 }
 public void testdelete() throws Exception{
 Personservice ps = new Personservice(getContext());
 ps.delete(10);
 }
 public void testupdate() throws Exception{
 Personservice ps = new Personservice(getContext());
 ps.update(new Person(1,"xiaopang","18887654321",0));
 }
 public void testFind() throws Exception{
 Personservice ps = new Personservice(getContext());
 Person person = ps.find(1);
 Log.i("Personservice",person.toString());
 }
 public void testGetScrollData() throws Exception{
 Personservice ps = new Personservice(getContext());
 List<Person> persons = ps.getScrollData(3,5);
 for(Person person:persons){
  Log.i("Personservice",person.toString());
 }
 }
 public void testGetCount() throws Exception{
 Personservice ps = new Personservice(getContext());
 Long count = ps.getCount();
 Log.i("Personservice",count.toString());
 }
 public void testupdateamount() throws Exception{
 Personservice ps = new Personservice(getContext());
 Person person1 = ps.find(1);
 Person person2 = ps.find(2);
 person1.setamount(100);
 person2.setamount(100);
 ps.update(person1);
 ps.update(person2);
 }
 public void testPayment() throws Exception{
 Personservice ps = new Personservice(getContext());
 ps.payment();
 }
}

package com.example.test;
import java.util.List;
import com.example.domain.Person;
import com.example.service.DBOperateHelper;
import com.example.service.otherPersonservice;
import android.test.AndroidTESTCase;
import android.util.Log;
public class OtherPersonserviCETest extends AndroidTESTCase {
 public void testcreatedb() throws Exception{
 DBOperateHelper dbHelper = new DBOperateHelper(getContext());
 dbHelper.getWritableDatabase();
 }
 public void testSave() throws Exception{
 OtherPersonservice ps = new OtherPersonservice(getContext());
 for(int i=1;i<=100;i++){
  Person person = new Person();
  person.setName("我是"+i);
  person.setPhone(String.valueOf(Long.parseLong("18888888800")+i));
  ps.save(person);
  Log.i("Personservice",person.toString());
 }
 }
 public void testdelete() throws Exception{
 OtherPersonservice ps = new OtherPersonservice(getContext());
 ps.delete(10);
 }
 public void testupdate() throws Exception{
 OtherPersonservice ps = new OtherPersonservice(getContext());
 ps.update(new Person(1,0));
 }
 public void testFind() throws Exception{
 OtherPersonservice ps = new OtherPersonservice(getContext());
 Person person = ps.find(1);
 Log.i("Personservice",person.toString());
 }
 public void testGetScrollData() throws Exception{
 OtherPersonservice ps = new OtherPersonservice(getContext());
 List<Person> persons = ps.getScrollData(3,person.toString());
 }
 }
 public void testGetCount() throws Exception{
 OtherPersonservice ps = new OtherPersonservice(getContext());
 Long count = ps.getCount();
 Log.i("Personservice",count.toString());
 }
}