Android之SQlite

Android中数据库事务处理

使用SQLiteDatabase的beginTransaction()方法可以开启一个事务，程序执行到endTransaction() 方法时会检查事务的标志是否为成功，如果程序执行到endTransaction()之前调用了setTransactionSuccessful() 方法设置事务的标志为成功则提交事务，如果没有调用setTransactionSuccessful() 方法则回滚事务。使用例子如下：

SQLiteDatabase db = ....;db.beginTransaction();//开始事务try {    db.execSQL("insert into person(name, age) values(?,?)", new Object[]{"传智播客", 4});    db.execSQL("update person set name=? where personid=?", new Object[]{"传智", 1});    db.setTransactionSuccessful();//调用此方法会在执行到endTransaction() 时提交当前事务，如果不调用此方法会回滚事务} finally {    db.endTransaction();//由事务的标志决定是提交事务，还是回滚事务} db.close();

上面两条SQL语句在同一个事务中执行。

1:Person 类

package cn.itcast.domain;public class Person {private Integer id;private String name;private Integer amount;public Integer getAmount() {return amount;}public void setAmount(Integer amount) {this.amount = amount;}public Person(){}public Person(Integer id, String name) {this.id = id;this.name = name;}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;}@Overridepublic String toString() {return "Person [amount=" + amount + ", id=" + id + ", name=" + name+ "]";}}

2：DBOpenHelper类

package cn.itcast.service;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;public class DBOpenHelper extends SQLiteOpenHelper {private static final String DATABASENAME = "itcast.db"; //数据库名称private static final int DATABASEVERSION = 2;//数据库版本public DBOpenHelper(Context context) {super(context, DATABASENAME, null, DATABASEVERSION);}@Overridepublic void onCreate(SQLiteDatabase db) {db.execSQL("CREATE TABLE person (personid integer primary key autoincrement, name varchar(20), amount integer)");//执行有更改的sql语句}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {db.execSQL("DROP TABLE IF EXISTS person");onCreate(db);}}

3（1）：通过SQlite访问方法一：PersonService类

package cn.itcast.service;import java.util.ArrayList;import java.util.List;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import cn.itcast.domain.Person;public class PersonService {private DBOpenHelper dbOpenHelper;public PersonService(Context context) {this.dbOpenHelper = new DBOpenHelper(context);}public void payment(){SQLiteDatabase db = dbOpenHelper.getWritableDatabase();db.beginTransaction();//事启事务try{db.execSQL("update person set amount=amount-10 where personid=?", new Object[]{1});db.execSQL("update person set amount=amount+10 where personid=?", new Object[]{2});db.setTransactionSuccessful();//设置事务标志为成功，当结束事务时就会提交事务}finally{db.endTransaction();}}public void save(Person person){//如果要对数据进行更改，就调用此方法得到用于操作数据库的实例,该方法以读和写方式打开数据库SQLiteDatabase db = dbOpenHelper.getWritableDatabase();db.execSQL("insert into person (name,amount) values(?,?)",new Object[]{person.getName(),person.getAmount()});}public void update(Person person){SQLiteDatabase db = dbOpenHelper.getWritableDatabase();db.execSQL("update person set name=? where personid=?", new Object[]{person.getName(),person.getId()});}public void delete(Integer id){SQLiteDatabase db = dbOpenHelper.getWritableDatabase();db.execSQL("delete from person where personid=?", new Object[]{id.toString()});}public Person find(Integer id){//如果只对数据进行读取，建议使用此方法SQLiteDatabase db = dbOpenHelper.getReadableDatabase();Cursor cursor = db.rawQuery("select * from person where personid=?", new String[]{id.toString()});if(cursor.moveToFirst()){int personid = cursor.getInt(cursor.getColumnIndex("personid"));String name = cursor.getString(cursor.getColumnIndex("name"));int amount = cursor.getInt(cursor.getColumnIndex("amount"));Person person = new Person(personid, name);person.setAmount(amount);return person;}return null;}public List<Person> getScrollData(Integer offset, Integer maxResult){List<Person> persons = new ArrayList<Person>();SQLiteDatabase db = dbOpenHelper.getReadableDatabase();Cursor cursor = db.rawQuery("select * from person limit ?,?",new String[]{offset.toString(), maxResult.toString()});while(cursor.moveToNext()){int personid = cursor.getInt(cursor.getColumnIndex("personid"));String name = cursor.getString(cursor.getColumnIndex("name"));int amount = cursor.getInt(cursor.getColumnIndex("amount"));Person person = new Person(personid, name);person.setAmount(amount);persons.add(person);}cursor.close();return persons;}public Cursor getCursorScrollData(Integer offset, Integer maxResult){SQLiteDatabase db = dbOpenHelper.getReadableDatabase();return db.rawQuery("select personid as _id, name, amount from person limit ?,?",new String[]{offset.toString(), maxResult.toString()});}public long getCount() {SQLiteDatabase db = dbOpenHelper.getReadableDatabase();Cursor cursor = db.rawQuery("select count(*) from person", null);cursor.moveToFirst();return cursor.getLong(0);}}

3（2）数据增删改查之测试：PersonServiceTest

package cn.itcast.db;import java.util.List;import cn.itcast.domain.Person;import cn.itcast.service.DBOpenHelper;import cn.itcast.service.PersonService;import android.test.AndroidTestCase;import android.util.Log;public class PersonServiceTest extends AndroidTestCase {private static final String TAG = "PersonServiceTest";public void testCreateDB() throws Throwable{DBOpenHelper dbOpenHelper = new DBOpenHelper(this.getContext());dbOpenHelper.getWritableDatabase();//第一次调用该方法就会创建数据库}public void testSave() throws Throwable{PersonService personService = new PersonService(this.getContext());Person person = new Person();person.setName("xiaoxiao");person.setAmount(100);personService.save(person);person = new Person();person.setAmount(50);person.setName("zhangliming");personService.save(person);person = new Person();person.setAmount(45);person.setName("libaobao");personService.save(person);person = new Person();person.setAmount(190);person.setName("taobao");personService.save(person);}public void testUpate() throws Throwable{PersonService personService = new PersonService(this.getContext());Person person = personService.find(1);person.setName("lili");personService.update(person);}public void testDelete() throws Throwable{PersonService personService = new PersonService(this.getContext());personService.delete(1);}public void testFind() throws Throwable{PersonService personService = new PersonService(this.getContext());Person person = personService.find(1);Log.i(TAG, person.toString());}public void testGetScrollData() throws Throwable{PersonService personService = new PersonService(this.getContext());List<Person> persons = personService.getScrollData(0, 30);for(Person person : persons){Log.i(TAG, person.toString());}}public void testGetCount() throws Throwable{PersonService personService = new PersonService(this.getContext());Log.i(TAG, personService.getCount()+"");}public void testPayment() throws Throwable{PersonService personService = new PersonService(this.getContext());personService.payment();}}

4(1):通过SQlite访问方法二：PersonService类

package cn.itcast.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.itcast.domain.Person;public class OtherPersonService {private DBOpenHelper dbOpenHelper;public OtherPersonService(Context context) {this.dbOpenHelper = new DBOpenHelper(context);}public void save(Person person){SQLiteDatabase db = dbOpenHelper.getWritableDatabase();ContentValues values = new ContentValues();values.put("name", person.getName());db.insert("person", null, values);}public void update(Person person){// update person set name =? where personid =?SQLiteDatabase db = dbOpenHelper.getWritableDatabase();ContentValues values = new ContentValues();values.put("name", person.getName());db.update("person", values, "personid=?", new String[]{person.getId().toString()});}public void delete(Integer id){SQLiteDatabase db = dbOpenHelper.getWritableDatabase();db.delete("person", "personid=?", new String[]{id.toString()});}public Person find(Integer id){//如果只对数据进行读取，建议使用此方法SQLiteDatabase db = dbOpenHelper.getReadableDatabase();Cursor cursor = db.query("person", new String[]{"personid", "name"},"personid=?", new String[]{id.toString()}, null, null, null);//select personid,name from person where personid=? order by ... limit 3,5if(cursor.moveToFirst()){int personid = cursor.getInt(cursor.getColumnIndex("personid"));String name = cursor.getString(cursor.getColumnIndex("name"));return new Person(personid, name);}return null;}public List<Person> getScrollData(Integer offset, Integer maxResult){List<Person> persons = new ArrayList<Person>();SQLiteDatabase db = dbOpenHelper.getReadableDatabase();Cursor cursor = db.query("person", null, null, null, null, null, null, offset+","+ maxResult);while(cursor.moveToNext()){int personid = cursor.getInt(cursor.getColumnIndex("personid"));String name = cursor.getString(cursor.getColumnIndex("name"));Person person = new Person(personid, name);persons.add(person);}cursor.close();return persons;}public long getCount() {// select count(*) from personSQLiteDatabase db = dbOpenHelper.getReadableDatabase();Cursor cursor = db.query("person", new String[]{"count(*)"}, null, null, null, null, null);cursor.moveToFirst();return cursor.getLong(0);}}

4（2）数据增删改查之测试：OtherPersonServiceTest

package cn.itcast.db;import java.util.List;import cn.itcast.domain.Person;import cn.itcast.service.DBOpenHelper;import cn.itcast.service.OtherPersonService;import cn.itcast.service.PersonService;import android.test.AndroidTestCase;import android.util.Log;public class OtherPersonServiceTest extends AndroidTestCase {private static final String TAG = "PersonServiceTest";public void testSave() throws Throwable{OtherPersonService personService = new OtherPersonService(this.getContext());Person person = new Person();person.setName("xiaoxiao");personService.save(person);person = new Person();person.setName("zhangliming");personService.save(person);person = new Person();person.setName("libaobao");personService.save(person);person = new Person();person.setName("taobao");personService.save(person);}public void testUpate() throws Throwable{OtherPersonService personService = new OtherPersonService(this.getContext());Person person = personService.find(1);person.setName("lili");personService.update(person);}public void testDelete() throws Throwable{OtherPersonService personService = new OtherPersonService(this.getContext());personService.delete(1);}public void testFind() throws Throwable{OtherPersonService personService = new OtherPersonService(this.getContext());Person person = personService.find(1);Log.i(TAG, person.toString());}public void testGetScrollData() throws Throwable{OtherPersonService personService = new OtherPersonService(this.getContext());List<Person> persons = personService.getScrollData(0, 10);for(Person person : persons){Log.i(TAG, person.toString());}}public void testGetCount() throws Throwable{OtherPersonService personService = new OtherPersonService(this.getContext());Log.i(TAG, personService.getCount()+"");}}

5：事务处理与数据显示

package cn.itcast.db;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import cn.itcast.domain.Person;import cn.itcast.service.PersonService;import android.app.Activity;import android.content.ContentResolver;import android.content.ContentValues;import android.database.Cursor;import android.net.Uri;import android.os.Bundle;import android.view.View;import android.widget.AdapterView;import android.widget.Button;import android.widget.ListView;import android.widget.SimpleAdapter;import android.widget.SimpleCursorAdapter;import android.widget.Toast;import android.widget.AdapterView.OnItemClickListener;public class MainActivity extends Activity {    private PersonService personService;    @Override    public void onCreate(Bundle savedInstanceState) {        super.onCreate(savedInstanceState);        setContentView(R.layout.main);                this.personService = new PersonService(this);               ListView listView = (ListView) this.findViewById(R.id.listView);        Cursor cursor = personService.getCursorScrollData(0, 5);        SimpleCursorAdapter adapter = new SimpleCursorAdapter(this, R.layout.item, cursor,        new String[]{"_id", "name", "amount"}, new int[]{R.id.id, R.id.name, R.id.amount});        listView.setAdapter(adapter);        listView.setOnItemClickListener(new OnItemClickListener() {@Overridepublic void onItemClick(AdapterView<?> parent, View view, int position, long id) {ListView lView = (ListView)parent;Cursor data = (Cursor)lView.getItemAtPosition(position);int personid = data.getInt(data.getColumnIndex("_id"));Toast.makeText(MainActivity.this, personid+"", 1).show();}});        /*        List<Person> persons = personService.getScrollData(0, 5);        List<HashMap<String, Object>> data = new ArrayList<HashMap<String, Object>>();        for(Person person : persons){        HashMap<String, Object> item = new HashMap<String, Object>();        item.put("id", person.getId());        item.put("name", person.getName());        item.put("amount", person.getAmount());        data.add(item);        }        SimpleAdapter adapter = new SimpleAdapter(this, data, R.layout.item,        new String[]{"id", "name", "amount"}, new int[]{R.id.id, R.id.name, R.id.amount});        listView.setAdapter(adapter);                listView.setOnItemClickListener(new OnItemClickListener() {@Overridepublic void onItemClick(AdapterView<?> parent, View view, int position, long id) {ListView lView = (ListView)parent;HashMap<String, Object> item = (HashMap<String, Object>)lView.getItemAtPosition(position);Toast.makeText(MainActivity.this, item.get("id").toString(), 1).show();}});*/                Button button = (Button) this.findViewById(R.id.insertbutton);        button.setOnClickListener(new View.OnClickListener() {@Overridepublic void onClick(View v) {ContentResolver contentResolver = getContentResolver();Uri insertUri = Uri.parse("content://cn.itcast.providers.personprovider/person");ContentValues values = new ContentValues();values.put("name", "itcastliming");values.put("amount", 100);Uri uri = contentResolver.insert(insertUri, values);Toast.makeText(MainActivity.this, "添加完成", 1).show();}});    }}

更多相关文章

随机推荐