android 中SQLiteDatabase的使用

官方介绍：

Android provides full support for SQLite databases. Any databases you create will be accessible by name to any class in the application, but not outside the application.

The recommended method to create a new SQLite database is to create a subclass of SQLiteOpenHelper and override the onCreate() method, in which you can execute a SQLite command to create tables in the database. For example:

package com.zizhu.db;

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

/**
 * @author zizhu
 * 当调用SQLiteOpenHelper的getRead
 *
 */
public class DBOpenHelper extends SQLiteOpenHelper {

	public DBOpenHelper(Context context) {
		
		/**
		 * context 当前上下文环境
		 * 第二个参数：文件名称
		 * 第三个参数：游标工厂，null表示使用系统默认游标
		 * 第四个参数：版本号，当版本号发生变化的时候触发onUpgrade方法
		 */
		super(context, "zizhu", null, 1);
	}

	//第一次创建数据库的时候被调用
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("create table t_user (id integer primary key autoincrement, name varchar(20))");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int version, int arg2) {
		db.execSQL("alter table t_user add mobile varchar(11) not null");
	}

}

You can then get an instance of your SQLiteOpenHelper implementation using the constructor you've defined. To write to and read from the database, call getWritableDatabase() and getReadableDatabase(), respectively. These both return a SQLiteDatabase object that represents the database and provides methods for SQLite operations.

一.SQLiteDatabase中基本操作：

package com.zizhu.service;

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

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

import com.zizhu.db.DBOpenHelper;
import com.zizhu.model.User;

public class UserService {

	private DBOpenHelper dbOpenHelper = null;

	public UserService(DBOpenHelper dbOpenHelper) {
		super();
		this.dbOpenHelper = dbOpenHelper;
	}

	/**
	 * getWritableDatabase方法具有缓存功能，在调用这个方法时，如果已存在db 则直接返回了，否则创建一个可写数据库对象
	 */
	private SQLiteDatabase getWritableDB() {
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();//
		return db;
	}

	/**
	 * getReadableDatabase方法中首先会调用getWritableDatabase方法，如果调用失败的时候，
	 * 才会已只读的方式创建一个新的数据库实例
	 * 
	 * 如果磁盘空间已满的情况下，getWritableDatabase方法会调用失败
	 * 相反，如果磁盘空间没有满的情况下，getReadableDatabase方法返回的一定是可写的数据库实例
	 */
	private SQLiteDatabase getReadableDB() {
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		return db;
	}

	public void save(User user){
		String sql = "insert into t_user(name) values('" + user.getName() + "')";
		//第一种方式，自己直接拼接sql语句
//		getWritableDB().execSQL(sql);
		//第二种方式，使用占位符，也是自己拼sql语句
//		getWritableDB().execSQL("insert into t_user(name) values(?)", new Object[]{user.getName()});
		//第三种方式，使用SQLiteDatabase提供的api,不过它的底层也是自己拼接的sql语句
		ContentValues values = new ContentValues();
		values.put("name", "zizhux");
		getWritableDB().insert("t_user", null, values);
	}

	public void delete(int id) {
//		getWritableDB().execSQL("delete from t_user where id=" + id);
		getWritableDB().delete("t_user", "id=?", new String[]{id+""});
	}

	public void update(User user) {
		getWritableDB().execSQL(
				"update t_user set name='" + user.getName() + "' where id="
						+ user.getId());
		// getWritableDB().execSQL("update t_user set name=? where id=?", new
		// Object[]{user.getName(), user.getId()});
		ContentValues values = new ContentValues();
		values.put("name", user.getName() + "hello,world");
		getWritableDB().update("t_user", values, "id=?", new String[]{user.getId()+""});
	}

	public User find(int id) {
		User u = new User();
		Cursor cursor = getReadableDB().rawQuery(
				"select * from t_user where id=?", new String[] { id + "" });
		if (cursor.moveToFirst()) {
			u.setName(cursor.getString(cursor.getColumnIndex("name")));
			u.setId(id);
		}
		cursor.close();
		return u;
	}

	public int getCount() {
		int count = 0;
		Cursor cursor = getReadableDB().rawQuery("select count(*) from t_user",
				null);
		if (cursor.moveToFirst()) {
			count = cursor.getInt(0);
		}
		cursor.close();
		return count;
	}

	/**
	 * 分页，跳过前面over条数据,这个地方以前没注意
	 * 
	 * @param over
	 * @param count
	 * @return
	 */
	public List<User> getUsers(int over, int count) {
		List<User> users = new ArrayList<User>();
		Cursor cursor = getReadableDB().rawQuery(
				"select * from t_user order by name asc limit ?,?",
				new String[] { String.valueOf(over), String.valueOf(count) });
		while (cursor.moveToNext()) {
			User u = new User();
			u.setName(cursor.getString(cursor.getColumnIndex("name")));
			u.setId(cursor.getInt(cursor.getColumnIndex("id")));
			users.add(u);
		}
		cursor.close();
		return users;
	}

}

二.在SQLiteDatabase中使用事务：

/**
	 * 模拟转账，用户1转200元到用户2
	 */
	public void payment() {
		SQLiteDatabase db = getWritableDB();
		/**
		 * 官方解释：事务会默认回滚，如果没有明确调用setTransactionSuccessful方法
		 * The changes will be rolled back if any transaction is ended without
		 * being marked as clean (by calling setTransactionSuccessful).
		 * Otherwise they will be committed.
		 */
		db.beginTransaction();
		try{
			int amount = 200;
			User u1 = find(1);
			User u2 = find(2);
			u1.setAmount(u1.getAmount() - amount);
			u2.setAmount(u2.getAmount() + amount);
			update(u1);
			update(u2);
			db.setTransactionSuccessful();//如果中间报错了，则不能执行到这一句，则事务会回滚
		}finally{
			db.endTransaction();
		}
	}