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

摘要：本文实例讲述了Android操作SQLite数据库(增、删、改、查、分页等)及ListView显示数据的方法。分享给大家供大家参考，具体如下...

本文实例讲述了Android操作SQLite数据库(增、删、改、查、分页等)及ListView显示数据的方法。分享给大家供大家参考，具体如下：

由于刚接触android开发，故此想把学到的基础知识记录一下，以备查询，故此写的比较啰嗦：

步骤如下：

一、介绍：

此文主要是介绍怎么使用android自带的数据库SQLite，以及把后台的数据用ListView控件显示

二、新建一个android工程——DBSQLiteOperate

工程目录：

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

三、清单列表AndroidManifest.xml的配置为：

<"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>

四、main.xml配置清单：

<"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>

五、item.xml配置清单：

<"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>

六、string.xml配置清单：

<"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>

七、DBSQLiteOperateActivity.java Activity类的源码：

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); } }

八、person.java 实体类源码：

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 + "]"; } }

九、DBOperateHelper.java 业务类源码：

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"); } }

十、PersonService.java 业务类源码：

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 { //结束事务：有两种情况：commitrollback，事务提交或者回滚是由事务的标识决定的 //事务为ture则提交，事务为flase则回滚，默认为false sqldb.endTransaction(); } } }

十一、OtherPersonService.java 业务类源码：

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; } }

十二、PersonServiceTest.java 单元测试类源码：

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(); } }

十三、OtherPersonServiceTest 单元测试类源码：

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()); } }

十四、注意事项以及相关知识点：

1、掌握SQLite数据库如何创建数据库、建立表、维护字段等操作

继承SQLiteOpenHelper类，构造函数调用父类构造函数创建数据库，利用onCreate创建表，利用onUpgrade更新表字段信息

2、掌握SQLite数据库如何增、删、改、查以及分页

取得SQLiteDatabase的实例，然后调用该实例的方法可以完成上述操作

SQLiteDataBase提供两种操作上述功能的方式：一是直接调用execSQL书写sql语句，另一种是通过insert、update、delete、query等方法来传值来拼接sql，前一种适合熟练掌握sql 语句的

3、对需要数据同步的处理请添加事务处理，熟悉事务的处理方式

4、了解各个方法参数的意义以及传值

5、掌握ListView显示后台数据的使用方法

SimpleAdapter、SimpleCursorAdapter以及自定义适配器的使用，以及OnItemClickListener取值时各个适配器返回值的区别以及取值方法

6、多学、多记、多练、多思，加油！

希望本文所述对大家Android程序设计有所帮助。