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

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.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://schemas.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://schemas.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,r.layout.item,cursor,new string[]{"name","phone","amount"}, new int[]{r.id.name,r.id.phone,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=?",new object[]{person.getname(),person.getphone(),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,phone,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,name,phone,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,name,phone,amount from person order by personid asc limit ?,?", new string[]{string.valueof(offset),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,phone) values('"+person.getname()+"','"+person.getphone()+"')");
 //利用占位符可以避免注入，但是注意数组参与与占位符对应的字段要一一对应
 //sqldb.execsql("insert into person(name,phone) values(?,?)",new object[]{person.getname(),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=?",new object[]{person.getname(),person.getphone(),person.getid()});
 //第一个参数为表名，第二个是一个更新值集合，采用键值对的形式，每个更新的字段对应更新值
 //第三个参数是where后面条件字段用占位符标识，第四个参数是对应where占位符的值
 contentvalues values = new contentvalues();
 values.put("name", person.getname());
 values.put("phone", person.getphone());
 values.put("amount", person.getamount());
 sqldb.update("person", values , "personid=?", 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", null, "personid=?", new string[]{id.tostring()}, null, null, 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,name,phone,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)});
 //第一个参数是表名；第二个参数是查询显示的字段，null时默认查询显示所有字段；
 //第三个参数是where查询条件占位符；第四个是占位符对应的值；
 //第五个参数是group by条件；第六个是having条件；第七个是order by条件
 //第八个参数是limit ?,? 条件
 cursor cursor = sqldb.query("person", null, null, null, null, null, "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,name,phone,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, null, null, null, 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,"xiaopang","18887654321",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, 5);
 for(person person:persons){
  log.i("personservice",person.tostring());
 }
 }
 public void testgetcount() throws exception{
 otherpersonservice ps = new otherpersonservice(getcontext());
 long count = ps.getcount();
 log.i("personservice",count.tostring());
 }
}