Android數據的四種存儲方式之SQLite數據庫

Test.java:

/**
 * 本例解決的問題：
 * 核心問題：通過SQLiteOpenHelper類創建數據庫對象
 * 通過數據庫對象對數據庫的數據的操作
 * 1.sql語句方式操作SQLite數據庫
 * 2.谷歌提供的api對SQLite數據庫的操作
 * 3.SQLite對事務的操作
 */
import com.ghsy.createsqlitedb.db.MyOpenHelper;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.test.AndroidTestCase;

public class Test extends AndroidTestCase {

	MyOpenHelper oh;
	SQLiteDatabase db;

	public void test() {
		// 創建一個MyOpenHelper對象
		// 修改此處的版本號，會執行upgrade方法--upgrade方法中添加了一列
		MyOpenHelper oh = new MyOpenHelper(getContext(), "people.db", null, 3);
		// 如果數據庫不存在，先創建數據庫，再打開數據庫，如果已經存在，直接打開
		SQLiteDatabase db = oh.getWritableDatabase();
		db.close();
	}

	// 測試框架初始化完成
	/**
	 * This method is called before a test is executed
	 */
	@Override
	protected void setUp() throws Exception {
		// TODO Auto-generated method stub
		super.setUp();
		oh = new MyOpenHelper(getContext(), "people.db", null, 3);
		db = oh.getWritableDatabase();
	}

	// ===========sql語句方式操作SQLite數據庫================================
	public void insert() {

		db.execSQL("insert into person(name, phone, money) values(?, ?, ?)",
				new Object[] { "大明", "18666", 6000 });
		db.execSQL("insert into person(name, phone, money) values(?, ?, ?)",
				new Object[] { "小紅", "18666", 6000 });
		db.execSQL("insert into person(name, phone, money) values(?, ?, ?)",
				new Object[] { "大紅", "18666", 6000 });
	}

	public void delete() {
		db.execSQL("delete from person where name = ?", new Object[] { "大明" });
	}

	public void update() {
		db.execSQL("update person set money = 10000 where name = ?",
				new Object[] { "小明" });
	}

	public void query() {
		// 游標，存放查詢返回的數據，獲取方法跟resultSet高度雷同
		Cursor c = db.rawQuery("select * from person", new String[] {});
		while (c.moveToNext()) {
			String name = c.getString(c.getColumnIndex("name"));
			String phone = c.getString(c.getColumnIndex("phone"));
			System.out.println(name + ";" + phone);
		}
	}

	// ==============谷歌提供的api對SQLite數據庫的操作======================
	/**
	 * api-insert data to table
	 */
	public void insertApi() {

		ContentValues cv = new ContentValues();
		cv.put("name", "微明");
		cv.put("phone", 15666);
		cv.put("money", 630);
		long id = db.insert("person", null, cv);
		System.out.println(id);
	}

	/**
	 * api-delete data from table
	 * 
	 * @return the number of rows affected
	 */
	public int deleteApi() {
		int affectedNum = db.delete("person", "name=?", new String[] { "小紅" });
		return affectedNum;
	}

	/**
	 * api-update
	 */
	public void updateApi() {
		ContentValues contentValues = new ContentValues();
		contentValues.put("name", "小紅");
		contentValues.put("money", "10");
		// return the number of rows affected
		db.update("person", contentValues, "name=?", new String[] { "大紅" });
	}

	public void queryApi() {
		Cursor cursor = db.query("person", new String[] { "phone", "money" },
				null, null, null, null, null);
		while (cursor.moveToNext()) {
			String phone = cursor.getString(cursor.getColumnIndex("phone"));
			String money = cursor.getString(cursor.getColumnIndex("money"));
			System.out.println(phone + "##" + money);
		}
	}

	// ===============SQLite對事務的操作=====================
	/**
	 * 銀行轉賬操作
	 */
	public void transation(){
		  db.beginTransaction();
		   try {
			   //name為微明的用戶向小紅轉賬
			   ContentValues contentValues=new ContentValues();
			   contentValues.put("money", 1000);
			   db.update("person", contentValues, "name=?", new String[]{"微明"});
			   ContentValues contentValues2=new ContentValues();
			   contentValues2.put("money", 1100);
			   db.update("person", contentValues2, "name=?", new String[]{"小紅"});
			   //所有語句執行完畢，若沒有異常，則會執行這句設置事務成功的標記
			   db.setTransactionSuccessful();
		   } finally {
			   //會檢查事務的標識，若沒有調用setTransactionSuccessful()方法設置標志，則回滾事務。否則提交事務。
		       db.endTransaction();
		   }
	}
}

MyOpenHelper.java

**SQLiteOpenHelper:
 * A helper class to manage database creation and version management. 
 * 所以，SQLiteOpenHelper是對庫本身的操作。若要對庫中數據操作，需要使用庫對象的方法。
 */
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class MyOpenHelper extends SQLiteOpenHelper {

	//name:數據庫文件的名字
	//factory：游標工廠
	//version：版本,必須大於等於1
	public MyOpenHelper(Context context, String name, CursorFactory factory, int version) {
		super(context, name, factory, version);
		// TODO Auto-generated constructor stub
	}

	//數據庫創建時調用
	@Override
	public void onCreate(SQLiteDatabase db) {
		//創建一個person表
		db.execSQL("create table person(_id integer primary key autoincrement, name char(10), phone char(20))");
		System.out.println("oncreate調用了");
	}

	//數據庫升級時調用
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		System.out.println("onupgrade調用了");
		db.execSQL("alter table person add money char(20)");
	}
}