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