android中使用SQLite進行CRUD操作的執行個體

在android中主要使用SQLite資料庫進行對一些資料的存儲，以下是相應的CRUD的執行個體。

[code]

package com.myclover.db.service;

import android.content.Context;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteDatabase.CursorFactory;

import android.database.sqlite.SQLiteOpenHelper;

public class DBOpenHelper extends SQLiteOpenHelper {

private static final String DBNAME = "myclover.db";

private static final int VERSION = 1;

public DBOpenHelper(Context context) {

super(context, DBNAME, null, VERSION);

}

public DBOpenHelper(Context context, String name, CursorFactory factory,

int version) {

super(context, name, factory, version);

}

@Override

public void onCreate(SQLiteDatabase db) {

db.execSQL("create table t_person (id integer primary key autoincrement , name varchar(30) , sex varchar(10) , age integer , amount real)");

}

@Override

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

db.execSQL("drop table if exists t_person");

onCreate(db);

}

}

[/code]

[code]

package com.myclover.db.service;

import java.util.ArrayList;

import java.util.List;

import com.myclover.db.bean.Person;

import android.content.Context;

import android.database.Cursor;

import android.database.SQLException;

import android.database.sqlite.SQLiteDatabase;

public class DBService {

@SuppressWarnings("unused")

private Context context;

private DBOpenHelper dbHelper;

public DBService(Context context) {

dbHelper = new DBOpenHelper(context);

}

public void save(Person p) {

SQLiteDatabase db = dbHelper.getWritableDatabase();

db.execSQL("insert into t_person (name , sex , age , amount) values (? , ? , ? , ?)",

new Object[] { p.getName(), p.getSex(), p.getAge(),

p.getAmount() });

}

public void update(Person p) {

SQLiteDatabase db = dbHelper.getWritableDatabase();

db.execSQL("update t_person set name = ? , sex = ? , age = ? , amount = ? where id = ? ",

new Object[] { p.getName(), p.getSex(), p.getAge(),

p.getAmount(), p.getId() });

}

public Person findById(Integer id) {

SQLiteDatabase db = dbHelper.getReadableDatabase();

Cursor cs = db.rawQuery("select * from t_person where id = ? ",

new String[] { String.valueOf(id) });

if (cs.moveToNext()) {

Person p = new Person();

p.setId(cs.getInt(cs.getColumnIndex("id")));

p.setName(cs.getString(cs.getColumnIndex("name")));

p.setSex(cs.getString(cs.getColumnIndex("sex")));

p.setAge(cs.getInt(cs.getColumnIndex("age")));

p.setAmount(cs.getFloat(cs.getColumnIndex("amount")));

cs.close();

return p;

}

return null;

}

public Person findByName(String name) {

SQLiteDatabase db = dbHelper.getReadableDatabase();

Cursor cs = db.rawQuery("select * from t_person where name = ? ",

new String[] { name });

if (cs.moveToNext()) {

Person p = new Person();

p.setId(cs.getInt(cs.getColumnIndex("id")));

p.setName(cs.getString(cs.getColumnIndex("name")));

p.setSex(cs.getString(cs.getColumnIndex("sex")));

p.setAge(cs.getInt(cs.getColumnIndex("age")));

p.setAmount(cs.getFloat(cs.getColumnIndex("amount")));

cs.close();

return p;

}

return null;

}

public void delete(Integer... ids) {

if (ids.length > 0) {

StringBuffer sb = new StringBuffer();

for (@SuppressWarnings("unused")

int id : ids) {

sb.append("?").append(",");

}

sb.deleteCharAt(sb.length() - 1);

SQLiteDatabase db = dbHelper.getWritableDatabase();

db.execSQL("delete from t_person where id in (" + sb + ")",(Object[]) ids);

}

}

public List<Person> getPerosns(int startNum, int perNum) {

List<Person> lists = new ArrayList<Person>();

SQLiteDatabase db = dbHelper.getReadableDatabase();

Cursor cs = db

.rawQuery("select * from t_person limit ? , ? ", new String[] {

String.valueOf(startNum), String.valueOf(perNum) });

while (cs.moveToNext()) {

Person p = new Person();

p.setId(cs.getInt(cs.getColumnIndex("id")));

p.setName(cs.getString(cs.getColumnIndex("name")));

p.setSex(cs.getString(cs.getColumnIndex("sex")));

p.setAge(cs.getInt(cs.getColumnIndex("age")));

p.setAmount(cs.getFloat(cs.getColumnIndex("amount")));

lists.add(p);

}

cs.close();

return lists;

}

public long getTotalCounts() {

SQLiteDatabase db = dbHelper.getReadableDatabase();

Cursor cs = db.rawQuery("select count(*) from t_person ", null);

if (cs.moveToNext()) {

return cs.getLong(0);

}

return 0;

}

public void transAmount(int id, float amount, int tid) {

SQLiteDatabase db = dbHelper.getWritableDatabase();

// 開啟事務

db.beginTransaction();

try {

if (findById(id) != null && findById(tid) != null) {

db.execSQL("update t_person set amount = amount - ? where id = ? ", new Object[] { amount, id });

db.execSQL("update t_person set amount = amount + ? where id = ? ", new Object[] { amount, tid });

// 設定事務的成功标志

db.setTransactionSuccessful();

}

} catch (SQLException e) {

} finally {

// 結束事務，如果設定事務是否成功标志為成功時，則此時先送出事務再結束，否則就是復原事務并結束

db.endTransaction();

}

}

public void closeDB(){

dbHelper.close();

}

}

[/code]