Android之SQLite資料庫建立和更新,以及增刪改差操作 事務

SQLite資料庫
1.SQLite資料庫的特點
       手機自帶的資料庫, 不區分資料型別(除了主鍵), 語法和MySQL相同, 每個庫是一個檔案
2.建立庫
     定義類繼承SQLiteOpenHelper, 定義建構函式, 顯式呼叫父類建構函式, 傳入4個引數
     重寫onCreate()和onUpgrade()方法
     呼叫getWritableDatabase()(加鎖)或者getReadableDatabase()(不加鎖)方法都可以建立資料庫
     資料庫檔案不存在時, 會建立資料庫檔案, 並且執行onCreate()方法
     資料庫檔案存在, 並且版本沒有改變時, 不執行任何方法
     資料庫檔案存在, 版本提升, 執行onUpgrade()方法
3.增刪改查
     增刪改都可以使用SQLiteDatabase.execSQL()方法執行SQL語句完成

     查詢方法需要使用SQLiteDatabase.rawQuery()方法進行查詢, 得到一個Cursor, 再呼叫moveToNext()和             getString()getInt()等方法獲取資料

資料的增刪改差以及分頁操作(通過實現SQLiteDatabase的子類獲取資料庫連線後進行增刪改差)

SQL實現

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class PersonDao {
	private DBOpenHelper helper;

	public PersonDao(Context context) {
		helper = new DBOpenHelper(context);
	}

	public void savePerson(Person person) {
		SQLiteDatabase db = helper.getWritableDatabase();// 獲取資料庫連結,引數是應用環境Context
		Object[] obj = new Object[] { person.getName(), person.getBalance() };
		db.execSQL("INSERT INTO person(name,balance) VALUES(?,?)", obj);// 執行插入的sql語句,第一個引數是sql語句,第二個引數是佔位符的具體引數
		//db.close();  
	}

	public void deletePerson(int id) {
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL("DELETE FROM person WHERE id=?", new Object[] { id });
		//db.close();  如果運算元據庫都不關閉連線,那麼這次拿到的連線是上一次沒關閉的連線(效率稍高,存在於單個使用者運算元據庫的情況下)
	}

	public void updatePerson(Person person) {
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL("UPDATE person SET name=?,balance=? WHERE id=?", new Object[] { person.getName(),person.getBalance(),person.getId() });
		//db.close();
	}

	public Person queryPerson(int id) {
		SQLiteDatabase db = helper.getReadableDatabase();//獲取一個可讀的資料庫連結
		Cursor c = db.rawQuery("SELECT name,balance FROM person WHERE id=?", new String[]{String.valueOf(id)});//執行sql查詢操作,獲取遊標
		Person person = null;
		if(c.moveToNext()){//判斷遊標是否有下一條記錄,如果有的話,將遊標向後移動一位
			//c.getString(0);  獲取0號索引上的資料
			String name = c.getString(c.getColumnIndex("name"));//根據列名得出索引,再用c.getString(索引)獲取值
			int balance = c.getInt(c.getColumnIndex("balance"));
			person = new Person(id, name, balance);
		}
		c.close();
		db.close();
		return person;
	}
	
	public List<Person> queryAll(){
		SQLiteDatabase db = helper.getReadableDatabase();
		Cursor c = db.rawQuery("SELECT * FROM person", null);
		List<Person> persons = new ArrayList<Person>();
		Person person = null;
		while(c.moveToNext()){
			String name = c.getString(c.getColumnIndex("name"));
			int balance = c.getInt(c.getColumnIndex("balance"));
			int id = c.getInt(c.getColumnIndex("id"));
			person = new Person(id, name, balance);
			persons.add(person);
		}
		return persons;
	}
	
	public int queryCount(){
		SQLiteDatabase db = helper.getReadableDatabase();
		Cursor c = db.rawQuery("SELECT COUNT(*) FROM person", null);
		c.moveToNext();
		return c.getInt(0);
	}
	
	/**
	 * 分頁查詢
	 * @param pageNum 當前頁碼
	 * @param pageSize 每頁多少條
	 * @return
	 */
	public List<Person> queryPage(int pageNum,int pageSize){
		String offset = (pageNum-1)*pageSize+"";//從第幾條開始
		SQLiteDatabase db = helper.getReadableDatabase();
		Cursor c = db.rawQuery("SELECT * FROM person LIMIT ?,?", new String[]{offset,pageSize+""});
		List<Person> persons = new ArrayList<Person>();
		Person person = null;
		while(c.moveToNext()){
			String name = c.getString(c.getColumnIndex("name"));
			int balance = c.getInt(c.getColumnIndex("balance"));
			int id = c.getInt(c.getColumnIndex("id"));
			person = new Person(id, name, balance);
			persons.add(person);
		}
		return persons;
	}
	//事務
	public void remit(int from,int to,int amount){
		SQLiteDatabase db = helper.getWritableDatabase();
		try {
			db.beginTransaction();//開始事務(如果不寫endTransaction,也會結束事務,不過要等到超時之後才會結束)
			db.execSQL("UPDATE person SET balance=balance-? WHERE id=?",new Object[]{amount,from});
			db.execSQL("UPDATE person SET balance=balance+? WHERE id=?",new Object[]{amount,to});
			db.setTransactionSuccessful();//設定成功點,在事務結束時,成功點之前操作會被提交
			/**
			 * 成功點可以有多個
			 * db.execSQL("");//成功提交
			 * db.execSQL("");//成功提交
			 * db.setTransactionSuccessful();
			 * db.execSQL("");//這句話將被回滾
			 * System.out.println(1/0);
			 * db.execSQL("");//這句話沒執行
			 * db.setTransactionSuccessful();
			 */
		} finally{
			db.endTransaction();//結束事務,將成功點之前的操作提交
			db.close();
		}
	}
}

內容提供者方式實現

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;

public class PersonDaoNew {
	private DBOpenHelper helper;

	public PersonDaoNew(Context context) {
		helper = new DBOpenHelper(context);
	}

	public void savePerson(Person person) {
		SQLiteDatabase db = helper.getWritableDatabase();// 獲取資料庫連結,引數是應用環境Context
		ContentValues values = new ContentValues();//類似於一個map集合,鍵是String,用來存放列名,值是Object,用來存入插入的資料
		values.put("name", person.getName());
		values.put("balance", person.getBalance());
		db.insert("person", "id", values);//此方法會返回插入資料的id值
		/**
		 * 第二個引數
		 * ContentValues values = new ContentValues();
		 * db.insert("person", null, values);
		 * 或者
		 * db.insert("person", null, null);
		 * 如果想往表中插入null資料時
		 * 因為如果不指定第二個引數,db.insert("person", null, null);
		 * 最後執行的sql語句是  INSERT INTO person(null)  VALUES(NULL);
		 * 所以,如果想要正確執行這條插入null資料的sql語句,那麼第二個引數應該任意指定個表中的列名,就算指定主鍵id也行(此時如果id自增的話照樣自增,不會為null)
		 * db.insert("person", "id", null);
		 */
		db.close();
	}

	public void deletePerson(int id) {
		SQLiteDatabase db = helper.getWritableDatabase();
		db.delete("person", "id=?", new String[]{id+""});
	}

	public void updatePerson(Person person) {
		SQLiteDatabase db = helper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("name", person.getName());
		values.put("balance", person.getBalance());
		db.update("person", values, "id=?", new String[]{person.getId()+""});
	}

	public Person queryPerson(int id) {
		SQLiteDatabase db = helper.getReadableDatabase();//獲取一個可讀的資料庫連結
		Cursor c = db.query("person", new String[]{"name","balance"}, "id=?", new String[]{id+""}, null, null, null);
		Person person = null;
		if(c.moveToNext()){
			String name = c.getString(c.getColumnIndex("name"));
			int balance = c.getInt(c.getColumnIndex("balance"));
			person = new Person(id,name,balance);
		}
		return person;
	}
	
	public List<Person> queryAll(){
		SQLiteDatabase db = helper.getReadableDatabase();
		Cursor c = db.query("person", null, null, null, null, null, "id DESC");
		List<Person> persons = new ArrayList<Person>();
		Person person = null;
		while(c.moveToNext()){
			String name = c.getString(c.getColumnIndex("name"));
			int balance = c.getInt(c.getColumnIndex("balance"));
			int id = c.getInt(c.getColumnIndex("id"));
			person = new Person(id, name, balance);
			persons.add(person);
		}
		return persons;
	}
	
	public int queryCount(){
		SQLiteDatabase db = helper.getReadableDatabase();
		Cursor c = db.query("person", new String[]{"COUNT(*)"}, null, null, null, null, null);
		c.moveToNext();
		return c.getInt(0);
	}
	
	/**
	 * 分頁查詢
	 * @param pageNum 當前頁碼
	 * @param pageSize 每頁多少條
	 * @return
	 */
	public List<Person> queryPage(int pageNum,int pageSize){
		String offset = (pageNum-1)*pageSize+"";//從第幾條開始
		SQLiteDatabase db = helper.getReadableDatabase();
		Cursor c = db.query("person", null, null, null, null, null, null,offset+","+pageSize);
		List<Person> persons = new ArrayList<Person>();
		Person person = null;
		while(c.moveToNext()){
			String name = c.getString(c.getColumnIndex("name"));
			int balance = c.getInt(c.getColumnIndex("balance"));
			int id = c.getInt(c.getColumnIndex("id"));
			person = new Person(id, name, balance);
			persons.add(person);
		}
		return persons;
	}
	
	public void remit(int from,int to,int amount){
		SQLiteDatabase db = helper.getWritableDatabase();
		try {
			db.beginTransaction();//開始事務(如果不寫endTransaction,也會結束事務,不過要等到超時之後才會結束)
			
			/*
			 * values1.put("balance", "balance-"+amount);
			 * 這樣設定值是錯誤的,因為第二個引數程式會認為它是個字串,將字串設定到int型別上是0  
			 * 正確做法是先將balance查詢出來後,進行運算操作後再設定到第二個引數上去
			 * 所以這種情況下,不推薦用這種方式進行資料庫操作,而推薦sql
			 */
			ContentValues values1 = new ContentValues();
			values1.put("balance", "balance-"+amount);
			db.update("person", values1, "id=?", new String[]{from+""});
			
			ContentValues values2 = new ContentValues();
			values2.put("balance", "balance+"+amount);
			db.update("person", values2, "id=?", new String[]{to+""});
			
			db.setTransactionSuccessful();//設定成功點,在事務結束時,成功點之前操作會被提交
		} finally{
			db.endTransaction();//結束事務,將成功點之前的操作提交
			db.close();
		}
	}
}

資料庫的建立和更新在這個類中操作(實現SQLiteOpenHelper介面中的  onCreate和onUpgrade方法來進行資料庫的新建和修改操作)

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 *	SQLiteOpenHelper這個類用來管理資料庫的建立和控制資料庫版本
 */
public class DBOpenHelper extends SQLiteOpenHelper {

	public DBOpenHelper(Context context){//建立物件的時候,需要傳入上下文環境     資料庫檔案是放在當前應用包下的sqlite資料夾下(這個位置資訊也是封裝在Context中的)
		/*
		 * 父類沒有無參建構函式,子類必須顯示呼叫有參的建構函式
		 * 引數一:上下文環境,用來確定資料庫檔案儲存的目錄
		 * 引數二:資料庫檔案的名字
		 * 引數三:生成遊標的工廠,如果為null,就表示使用預設的
		 * 引數四:資料庫的版本,從1開始
		 */
		super(context,"mydb.db",null,2);
	}
	
	//建立
	@Override
	public void onCreate(SQLiteDatabase db) {
		String sql = "CREATE TABLE person(id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(20))";
		db.execSQL(sql);//執行sql語句
	}

	//修改資料庫  注意修改的時候版本號必須大於上一次運算元據庫的版本號
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		String sql = "ALTER TABLE person ADD balance INTEGER";
		db.execSQL(sql);//執行sql語句
	}

}

測試類

import java.util.List;
import java.util.Random;

import android.test.AndroidTestCase;

public class DBTest extends AndroidTestCase {
	public void testCreateDatabase() {
		DBOpenHelper helper = new DBOpenHelper(getContext());
		helper.getWritableDatabase();
		/*
		 * 獲取可寫的資料庫連結
		 * 		資料庫檔案不存在時,會建立資料庫檔案,並且執行onCreate方法
		 * 		資料庫檔案存在時,並且版本沒有改變時,不執行任何方法
		 * 		資料庫檔案存在時,並且版本提升,執行onUpgrade方法
		 */
	}
	
	public void testSave(){
		PersonDao dao = new PersonDao(getContext());
		for(int i=0;i<100;i++){
			dao.savePerson(new Person("李四",new Random().nextInt(10000)));
		}
	}
	
	public void testDelete(){
		PersonDao dao = new PersonDao(getContext());
		dao.deletePerson(1);
	}
	
	public void testUpdate(){
		PersonDao dao = new PersonDao(getContext());
		dao.updatePerson(new Person(2,"皮卡丘",100));
	}
	
	public void testQuery(){
		PersonDao dao = new PersonDao(getContext());
		Person person = dao.queryPerson(2);
		System.out.println(person);
	}
	
	public void queryAll(){
		PersonDao dao = new PersonDao(getContext());
		List<Person> persons = dao.queryAll();
		for (Person person : persons) {
			System.out.println(person);
		}
	}
	
	public void queryCount(){
		PersonDao dao = new PersonDao(getContext());
		System.out.println(dao.queryCount());
	}
	
	public void testQueryPage(){
		PersonDao dao = new PersonDao(getContext());
		List<Person> persons = dao.queryPage(2, 20);
		for (Person person : persons) {
			System.out.println(person);
		}
	}
	
	public void testTransaction(){
		PersonDao dao = new PersonDao(getContext());
		dao.remit(3, 2, 100);
	}
}

domain

public class Person {
	private Integer id;
	private String name;
	private Integer balance;

	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;
	}

	public Integer getBalance() {
		return balance;
	}

	public void setBalance(Integer balance) {
		this.balance = balance;
	}

	public Person(Integer id, String name, Integer balance) {
		this.id = id;
		this.name = name;
		this.balance = balance;
	}

	public Person(String name, Integer balance) {
		this.name = name;
		this.balance = balance;
	}

	public Person() {

	}

	@Override
	public String toString() {
		return "Person [id=" + id + ", name=" + name + ", balance=" + balance
				+ "]";
	}

}