android数据库SQLite操作

定义一个实体类

package com.example.domain;public class Person {   private Integer id;   private String name;   private String phone;public Integer getId() {return id;}public Person() {super();}public Person(Integer id, String name, String phone) {super();this.id = id;this.name = name;this.phone = phone;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getPhone() {return phone;}public void setPhone(String phone) {this.phone = phone;}   }

数据库的创建

package com.example.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 {  public DBopenHelper(Context context, String name, CursorFactory factory,int version) {/** * 指定要生成的数据库的名称，第二个参数为游标工厂可以使用android * 自带的直接写null， version 数据库版本号 要求不能为0 要大于0 */super(context, name, factory, version);}@Override //数据库每一次创建的时候调用public void onCreate(SQLiteDatabase db) {//创建表person     db.execSQL("create table person(personid integer primary key autoincrement,name varchar(20))");}@Override//数据库版本号变更的时候被调用public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {           db.execSQL("alter table person add phone varchar(12) null");//增加电话号码列 且可为空           }}

service

package com.example.service;import java.util.ArrayList;import java.util.List;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import com.example.domain.Person;public class PersonService {   private DBopenHelper dbopenHelper;      public PersonService(DBopenHelper dbopenHelper) {super();this.dbopenHelper = dbopenHelper;  }   //添加记录public void save(Person person){    /**     * 同一个对象调用getWritableDatabase具有缓存功能，比如在     * SQLiteDatabase db2=dbopenHelper.getWritableDatabase();此时     * db和db2是同一个对象     * 如果SQLiteDatabase db2=dbopenHelper1.getWritableDatabase();     * 则db和db2不是同一个对象 因为是不同的对象调用了getWritableDatabase方法     */SQLiteDatabase db=dbopenHelper.getWritableDatabase();db.execSQL("insert into person(name,phone)values(?,?)",new Object[]{person.getName(),person.getPhone()});db.close();}//删除记录public void delete(Integer id){SQLiteDatabase db=dbopenHelper.getWritableDatabase();db.execSQL("delete from person where personid=?", new Object[]{id});}//修改记录public void update(Person person){   SQLiteDatabase db=dbopenHelper.getWritableDatabase();     db.execSQL("update person set name=?,phone=? where personid=?",       new Object[]{person.getName(),person.getPhone(),person.getId()});}/**dbopenHelper.getReadableDatabase()方法首先会调用getWritableDatabase() *方法 如果数据库的磁盘空间已经满了的话 就会调用只读方法，如果不满的话 会 *调用 getWritableDatabase方法。如果仅仅是用于读取数据库建议用getReadableDatabase() *方法 *///查询记录public Person find(Integer id){SQLiteDatabase db=dbopenHelper.getReadableDatabase();//Cursor对象可以对结果集进行随机访问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"));       String phone=cursor.getString(cursor.getColumnIndex("phone"));       return new Person(personid,name,phone);    }    db.close();return null ;}//分页获取记录public List<Person> getScrollData(int offsert,int maxResult){List<Person> persons=new ArrayList<Person>();SQLiteDatabase db=dbopenHelper.getReadableDatabase();Cursor cursor=db.rawQuery("select * from person order by personid asc limit ?,?", new String[]{String.valueOf(offsert),String.valueOf(maxResult)});while(cursor.moveToNext()){ int personid=cursor.getInt(cursor.getColumnIndex("personid"));       String name=cursor.getString(cursor.getColumnIndex("name"));       String phone=cursor.getString(cursor.getColumnIndex("phone"));   persons.add(new Person(personid,name,phone));}cursor.close();return persons;}//获得总数记录public long getCount(){SQLiteDatabase db=dbopenHelper.getReadableDatabase();Cursor cursor=db.rawQuery("select count(*) from person",null );    cursor.moveToFirst();  long result=cursor.getLong(0);  cursor.close();  return result;}}

SQLite事务方法

public void payment(){SQLiteDatabase db=dbopenHelper.getWritableDatabase();db.beginTransaction();//开启事任务try{db.execSQL("update person set amount=amount-10 where person=1");db.execSQL("update person set amount=amount+10 where person=2");db.setTransactionSuccessful();//设置事务的标志位true；}catch(Exception e){e.printStackTrace();}finally{//结束事务,有两种情况：commit，rollback,事务的提交或者回滚由事务//的标志决定的,如果事务的标志位true 事务就会提交，否则回滚默认情形事务的标志位falsedb.endTransaction();db.close();}}