Android SQLite数据库和ListView控件 示例代码

1、AndroidManifest.xml:<?xml version="1.0" encoding="utf-8"?><manifest xmlns:android="http://schemas.android.com/apk/res/android" package="cn.etc.db" android:versionCode="1" android:versionName="1.0"> <application android:icon="@drawable/icon" android:label="@string/app_name"> <uses-library android:name="android.test.runner" /> <provider android:name=".PersonContentProvider" android:authorities="cn.etc.provider.personprovider"/> <activity android:name=".DBActivity" 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> <uses-sdk android:minSdkVersion="8" /> <instrumentation android:name="android.test.InstrumentationTestRunner" android:targetPackage="cn.etc.db" android:label="Tests for My App"/> <uses-permission android:name="android.permission.READ_CONTACTS"/></manifest>

2、main.xml:<?xml version="1.0" encoding="utf-8"?><LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:orientation="vertical" android:layout_width="fill_parent" android:layout_height="fill_parent" ><RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android" android:orientation="vertical" android:layout_width="fill_parent" android:layout_height="wrap_content" ><TextView android:layout_width="40px" android:layout_height="wrap_content" android:textSize="20px" android:id="@+id/personidTitle" android:text="编号" /> <TextView android:layout_width="200px" android:layout_height="wrap_content" android:layout_toRightOf="@id/personidTitle" android:layout_alignTop="@id/personidTitle" android:gravity="center_horizontal" android:textSize="20px" android:id="@+id/nameTitle" android:text="姓名" /> <TextView android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_toRightOf="@id/nameTitle" android:layout_alignTop="@id/nameTitle" android:textSize="20px" android:id="@+id/ageTitle" android:text="年龄" /> </RelativeLayout><ListView android:layout_width="fill_parent" android:layout_height="fill_parent" android:id="@+id/listView" /></LinearLayout>

3、personitem.xml:<?xml version="1.0" encoding="utf-8"?><RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android" android:orientation="vertical" android:layout_width="fill_parent" android:layout_height="wrap_content" ><TextView android:layout_width="40px" android:layout_height="wrap_content" android:textSize="20px" android:id="@+id/personid" /> <TextView android:layout_width="200px" android:layout_height="wrap_content" android:layout_toRightOf="@id/personid" android:layout_alignTop="@id/personid" android:gravity="center_horizontal" android:textSize="20px" android:id="@+id/name" /> <TextView android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_toRightOf="@id/name" android:layout_alignTop="@id/name" android:textSize="20px" android:id="@+id/age" /> </RelativeLayout>

4、package cn.etc.db;import java.util.HashMap;import android.app.Activity;import android.database.Cursor;import android.os.Bundle;import android.view.View;import android.widget.AdapterView;import android.widget.ListView;import android.widget.SimpleCursorAdapter;import android.widget.Toast;import android.widget.AdapterView.OnItemClickListener;import cn.etc.service.PersonService;public class DBActivity extends Activity { private ListView listView; private PersonService personService; /** Called when the activity is first created. */ @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.main); // 获取列表 listView = (ListView) this.findViewById(R.id.listView); // 获取业务逻辑类 personService = new PersonService(this); Cursor cursor = personService.getSScrollData(0,20); SimpleCursorAdapter adapter = new SimpleCursorAdapter(DBActivity.this,R.layout.personitem,cursor, new String[] { "_id", "name", "age" }, new int[]{R.id.personid,R.id.name,R.id.age} ); listView.setAdapter(adapter); listView.setOnItemClickListener(new OnItemClickListener(){ public void onItemClick(AdapterView<?> parent, View view, int position, long id) { ListView listv = (ListView)parent; HashMap<String, String> map = (HashMap<String, String>)listv.getItemAtPosition(position); Toast.makeText(DBActivity.this,"编号："+map.get("_id")+"姓名："+map.get("name")+"年龄："+map.get("age"),4).show(); } }); }}

5、package cn.etc.db;import java.util.List;import android.test.AndroidTestCase;import android.util.Log;import cn.etc.domain.Person;import cn.etc.service.PersonService;public class DBActivityTest extends AndroidTestCase{ private static final String TAG = "DBActivityTest"; //增加信息 public void testSave(){ //创建业务逻辑实例 PersonService service = new PersonService(this.getContext()); Person person = new Person(); person.setName("lihua"); person.setAge((short)23); service.save(person); } public void testFind(){ PersonService service = new PersonService(this.getContext()); Person person = service.findPerson(1); Log.i(TAG,person.toString()); } public void testUpdate(){ PersonService service = new PersonService(this.getContext()); Person person = service.findPerson(1); person.setName("liuyingjie"); person.setAge((short)24); service.update(person); Log.i(TAG,person.toString()); } public void testCount(){ PersonService service = new PersonService(this.getContext()); Log.i(TAG,service.getCount()+""); } public void tetGetAll(){ PersonService service = new PersonService(this.getContext()); Log.i(TAG,service.getCount()+""); List<Person> persons = service.getScrollData(0, 11); for(Person person:persons){ Log.i(TAG,person.toString()); } } }

6、package cn.etc.db;import java.util.List;import android.test.AndroidTestCase;import android.util.Log;import cn.etc.domain.Person;import cn.etc.service.OtherPersonService;import cn.etc.service.PersonService;public class OtherDBActivityTest extends AndroidTestCase{ private static final String TAG = "DBActivityTest"; //增加信息 public void testSave(){ //创建业务逻辑实例 OtherPersonService service = new OtherPersonService(this.getContext()); Person person = new Person(); person.setName("anhui"); person.setAge((short)23); service.save(person); } public void testFind(){ PersonService service = new PersonService(this.getContext()); Person person = service.findPerson(1); Log.i(TAG,person.toString()); } public void testUpdate(){ PersonService service = new PersonService(this.getContext()); Person person = service.findPerson(1); person.setName("liuyingjie"); person.setAge((short)24); service.update(person); Log.i(TAG,person.toString()); } public void testCount(){ PersonService service = new PersonService(this.getContext()); Log.i(TAG,service.getCount()+""); } public void tetGetAll(){ PersonService service = new PersonService(this.getContext()); Log.i(TAG,service.getCount()+""); List<Person> persons = service.getScrollData(0, 4); for(Person person:persons){ Log.i(TAG,person.toString()); } }}

7、package cn.etc.db挢旗扦渌;import android.content.ContentProvider;imp泠贾高框ort android.content.ContentUris;import android.content.ContentValues;import android.content.UriMatcher;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.net.Uri;import android.text.TextUtils;import cn.etc.service.DataBaseOpenHelper;public class PersonContentProvider extends ContentProvider { // 数据集的MIME类型字符串则应该以vnd.android.cursor.dir/开头 public static final String PERSONS_TYPE = "vnd.android.cursor.dir/person"; // 单一数据的MIME类型字符串应该以vnd.android.cursor.item/开头 public static final String PERSONS_ITEM_TYPE = "vnd.android.cursor.item/person"; public static final String AUTHORITY = "cn.etc.provider.personprovider";// 主机名 // 自定义匹配码 public static final int PERSONS = 1; public static final int PERSON = 2; public static final Uri PERSON_URI = Uri.parse("content://" + AUTHORITY + "/person"); /* 这里UriMatcher是用来匹配Uri的类，使用match()方法匹配路径时返回匹配码 */ private static final UriMatcher sMatcher; static { sMatcher = new UriMatcher(UriMatcher.NO_MATCH); // 如果match()方法匹配content://cn.itcast.provider.personprovider/person路径，返回匹配码为PERSONS sMatcher.addURI(AUTHORITY, "person", PERSONS); // 如果match()方法匹配content://cn.itcast.provider.personprovider/person/230路径，返回匹配码为PERSON sMatcher.addURI(AUTHORITY, "person/#", PERSON); } private DataBaseOpenHelper databaseHelper; @Override public int delete(Uri uri, String selection, String[] selectionArgs) { SQLiteDatabase db = databaseHelper.getWritableDatabase(); int count = 0; switch (sMatcher.match(uri)) { case PERSONS: count = db.delete("person", selection, selectionArgs); break; case PERSON: long personid = ContentUris.parseId(uri); String where = TextUtils.isEmpty(selection) ? "personid=?" : selection + "and personid=?"; String[] params = new String[] { String.valueOf(personid) }; if (!TextUtils.isEmpty(selection) && selectionArgs != null) { params = new String[selectionArgs.length + 1]; for (int i = 0; i < selectionArgs.length; i++) { params[i] = selectionArgs[i]; } params[selectionArgs.length + 1] = String.valueOf(personid); } count = db.delete("person", where, params); break; default: throw new IllegalArgumentException("Unknow" + uri); } return count; } @Override public String getType(Uri uri) { switch (sMatcher.match(uri)) { case PERSONS: return PERSONS_TYPE; case PERSON: return PERSONS_ITEM_TYPE; default: throw new IllegalArgumentException("Unknown URI " + uri); } } @Override public Uri insert(Uri uri, ContentValues values) { SQLiteDatabase db = databaseHelper.getWritableDatabase(); long id = 0; switch (sMatcher.match(uri)) { case PERSONS: id = db.insert("person", "name", values);// 返回的是记录行号，主键为int，实际上就是主键 return ContentUris.withAppendedId(uri, id); case PERSON: id = db.insert("person", "name", values);// 返回的是记录行号，主键为int，实际上就是主键 String path = uri.toString(); return Uri.parse(path.substring(0, path.lastIndexOf('/')) + id); default: throw new IllegalArgumentException("Unknow" + uri); } } @Override public boolean onCreate() { databaseHelper = new DataBaseOpenHelper(this.getContext()); // TODO Auto-generated method stub return true; } @Override public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) { SQLiteDatabase db = databaseHelper.getReadableDatabase(); switch (sMatcher.match(uri)) { case PERSONS: return db.query("person", projection, selection, selectionArgs, null, null, null); case PERSON: // 获取编号 long personid = ContentUris.parseId(uri); // 拼写where条件 String where = TextUtils.isEmpty(selection) ? "personid=?" : selection + "and personid=?"; String[] params = new String[] { String.valueOf(personid) }; if (!TextUtils.isEmpty(selection) && selectionArgs != null) { params = new String[selectionArgs.length + 1]; for (int i = 0; i < selectionArgs.length; i++) { params[i] = selectionArgs[i]; } params[selectionArgs.length + 1] = String.valueOf(personid); } return db.query("person", projection, where, params, null, null, null); default: throw new IllegalArgumentException("Unknow" + uri); } } @Override public int update(Uri uri, ContentValues values, String selection, String[] selectionArgs) { SQLiteDatabase db = databaseHelper.getWritableDatabase(); int count = 0; switch (sMatcher.match(uri)) { case PERSONS: count = db.update("person", values, selection, selectionArgs); break; case PERSON: long personid = ContentUris.parseId(uri); String where = TextUtils.isEmpty(selection) ? "personid=?" : selection + "and personid=?"; String[] params = new String[] { String.valueOf(personid) }; if (!TextUtils.isEmpty(selection) && selectionArgs != null) { params = new String[selectionArgs.length + 1]; for (int i = 0; i < selectionArgs.length; i++) { params[i] = selectionArgs[i]; } params[selectionArgs.length + 1] = String.valueOf(personid); } count = db.update("person", values, where, params); break; default: throw new IllegalArgumentException("Unknow" + uri); } return count; }}

8、package cn.etc.domain;public class Person { private Integer personid; private String name; private Short age; public Integer getPersonid() { return personid; } public void setPersonid(Integer personid) { this.personid = personid; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Short getAge() { return age; } public void setAge(Short age) { this.age = age; } public String toString(){ return "name:"+name+" age:"+age; }}

9、package cn.etc.service;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;public class DataBaseOpenHelper extends SQLiteOpenHelper { private static final String DBNAME = "etc"; private static final int version = 1; public DataBaseOpenHelper(Context context) { //1.上下文2.数据库名称3.游标工厂4.数据库版本 super(context, DBNAME, null, version); // TODO Auto-generated constructor stub } @Override public void onCreate(SQLiteDatabase db) { db.execSQL("CREATE TABLE person(personid integer primary key autoincrement,name varchar(20),age integer)"); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("DROP TABLE IF EXISTS person"); onCreate(db); }}

10、package cn.etc.service;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;import cn.etc.domain.Person;public class OtherPersonService { /** * 获取数据库连接 */ private DataBaseOpenHelper dbOpenHelper; /** * 获取上下文 */ private Context context; public OtherPersonService(Context context) { this.context = context; dbOpenHelper = new DataBaseOpenHelper(context); } /** * 保存信息 * * @param person */ public void save(Person person) { SQLiteDatabase database = dbOpenHelper.getWritableDatabase(); ContentValues values = new ContentValues(); values.put("name", "李华"); values.put("age", (short)22); database.insert("person", "personid", values); } /** * 修改信息 * * @param person */ public void update(Person person) { SQLiteDatabase database = dbOpenHelper.getWritableDatabase(); // database.update(table, values, whereClause, whereArgs) database.execSQL("update person set name=?,age=? where personid=? ", new Object[] { person.getName(), person.getAge(), person.getPersonid() }); } /** * 根据编号查询信息 * * @param id * @return */ public Person findPerson(Integer id) { // 以读写方式打开连接 SQLiteDatabase database = dbOpenHelper.getWritableDatabase(); //database.query(table, columns, selection, selectionArgs, groupBy, having, orderBy) // 得到游标跟JDBC的结果集差不多 Cursor cursor = database.rawQuery( "select * from person where personid=?", new String[] { String .valueOf(id) }); if (cursor.moveToNext()) { Person person = new Person(); person.setPersonid(cursor.getInt(0)); person.setName(cursor.getString(1)); person.setAge(cursor.getShort(2)); return person; } return null; } /** * 删除信息 * * @param ids */ public void delete(Integer... ids) { if (ids.length > 0) { StringBuilder sb = new StringBuilder(); for (Integer id : ids) { sb.append('?').append(','); } sb.deleteCharAt(sb.length() - 1); SQLiteDatabase database = dbOpenHelper.getWritableDatabase(); database.execSQL("delete from person where personid in (" + sb + ")", (Object[]) ids); } } /** * 返回数据 * * @param startResrult * 起始条数 * @param maxResult * 最大条数 * @return */ public List<Person> getScrollData(int startResrult, int maxResult) { List<Person> persons = new ArrayList<Person>(); SQLiteDatabase database = dbOpenHelper.getWritableDatabase(); Cursor cursor = database.rawQuery("select * from person limit ?,? ", new String[] {String.valueOf(startResrult),String.valueOf(maxResult)}); while(cursor.moveToNext()){ Person person = new Person(); person.setPersonid(cursor.getInt(0)); person.setName(cursor.getString(1)); person.setAge(cursor.getShort(2)); persons.add(person); } return persons; } /** * 获取总的条数 * * @return 总记录数 */ public long getCount() { // 以读写方式打开连接 SQLiteDatabase database = dbOpenHelper.getWritableDatabase(); // 得到游标跟JDBC的结果集差不多 Cursor cursor = database.rawQuery( "select count(*) from person ",null); if (cursor.moveToNext()) { return cursor.getLong(0); } return 0; }}

11、package cn.etc.service;import java.util.ArrayList;import java.util.讣嘬铮篌List;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import cn.etc.domain.Person;/*** 业务类** @author liu**/public class PersonService { /** * 获取数据库连接 */ private DataBaseOpenHelper dbOpenHelper; /** * 获取上下文 */ private Context context; public PersonService(Context context) { this.context = context; dbOpenHelper = new DataBaseOpenHelper(context); } /** * 保存信息 * * @param person */ public void save(Person person) { SQLiteDatabase database = dbOpenHelper.getWritableDatabase(); database.beginTransaction(); try { database.execSQL("insert into person(name,age) values(?,?)", new Object[] { person.getName(), person.getAge() }); database.execSQL("insert into person(name,age) values(?,?)", new Object[] { person.getName(), person.getAge() }); database.setTransactionSuccessful(); } catch (Exception e) { }finally{ database.endTransaction(); } } /** * 修改信息 * * @param person */ public void update(Person person) { SQLiteDatabase database = dbOpenHelper.getWritableDatabase(); database.execSQL("update person set name=?,age=? where personid=? ", new Object[] { person.getName(), person.getAge(), person.getPersonid() }); } /** * 根据编号查询信息 * * @param id * @return */ public Person findPerson(Integer id) { // 以读写方式打开连接 SQLiteDatabase database = dbOpenHelper.getWritableDatabase(); // 得到游标跟JDBC的结果集差不多 Cursor cursor = database.rawQuery( "select * from person where personid=?", new String[] { String .valueOf(id) }); if (cursor.moveToNext()) { Person person = new Person(); person.setPersonid(cursor.getInt(0)); person.setName(cursor.getString(1)); person.setAge(cursor.getShort(2)); return person; } return null; } /** * 删除信息 * * @param ids */ public void delete(Integer... ids) { if (ids.length > 0) { StringBuilder sb = new StringBuilder(); for (Integer id : ids) { sb.append('?').append(','); } sb.deleteCharAt(sb.length() - 1); SQLiteDatabase database = dbOpenHelper.getWritableDatabase(); database.execSQL("delete from person where personid in (" + sb + ")", (Object[]) ids); } } /** * 返回数据 * * @param startResrult * 起始条数 * @param maxResult * 最大条数 * @return */ public List<Person> getScrollData(int startResrult, int maxResult) { List<Person> persons = new ArrayList<Person>(); SQLiteDatabase database = dbOpenHelper.getWritableDatabase(); Cursor cursor = database.rawQuery("select * from person limit ?,? ", new String[] {String.valueOf(startResrult),String.valueOf(maxResult)}); while(cursor.moveToNext()){ Person person = new Person(); person.setPersonid(cursor.getInt(0)); person.setName(cursor.getString(1)); person.setAge(cursor.getShort(2)); persons.add(person); } return persons; } /** * 返回数据 * * @param startResrult * 起始条数 * @param maxResult * 最大条数 * @return */ public Cursor getSScrollData(int startResrult, int maxResult) { List<Person> persons = new ArrayList<Person>(); SQLiteDatabase database = dbOpenHelper.getWritableDatabase(); return database.rawQuery("select personid as _id,name,age from person limit ?,? ", new String[] {String.valueOf(startResrult),String.valueOf(maxResult)}); } /** * 获取总的条数 * * @return 总记录数 */ public long getCount() { // 以读写方式打开连接 SQLiteDatabase database = dbOpenHelper.getWritableDatabase(); // 得到游标跟JDBC的结果集差不多 Cursor cursor = database.rawQuery( "select count(*) from person ",null); if (cursor.moveToNext()) { return cursor.getLong(0); } return 0; }}