Android SQLite資料庫和ListView控制元件 示例程式碼
方法/步驟
AndroidManifest.xml:
android:versionCode="1"
android:versionName="1.0">
android:label="@string/app_name">
android:targetPackage="cn.etc.db" android:label="Tests for My App"/>
main.xml:
android:layout_width="fill_parent"
android:layout_height="fill_parent"
>
xmlns:android="; android:orientation="vertical"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
>
android:layout_width="40px"
android:layout_height="wrap_content"
android:textSize="20px"
android:id="@+id/personidTitle"
android:text="編號"
/>
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="姓名"
/>
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="年齡"
/>
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:id="@+id/listView"
/>
personitem.xml:
android:layout_width="fill_parent"
android:layout_height="wrap_content"
>
android:layout_width="40px"
android:layout_height="wrap_content"
android:textSize="20px"
android:id="@+id/personid"
/>
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"
/>
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"
/>
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
Toast.makeText(DBActivity.this,"編號："+map.get("_id")+"姓名："+map.get("name")+"年齡："+map.get("age"),4).show();
}
});
}
}
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
for(Person person:persons){
Log.i(TAG,person.toString());
}
}
}
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
for(Person person:persons){
Log.i(TAG,person.toString());
}
}
}
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;
}
}
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;
}
}
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);
}
}
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
List
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;
}
}
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
List
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
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;
}
}