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 事务就会提交，否则回滚默认情形事务的标志位false
				db.endTransaction();
				db.close();
				
			}
		}