Android資料的四種儲存方式之SQLite資料庫

標籤：android   blog   java   get   使用   檔案   

Test.java:

/** * 本例解決的問題： * 核心問題：通過SQLiteOpenHelper類建立資料庫物件 * 通過資料庫物件對資料庫的資料的操作 * 1.sql語句方式操作SQLite資料庫 * 2.Google提供的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 */@Overrideprotected void setUp() throws Exception {// TODO Auto-generated method stubsuper.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);}}// ==============Google提供的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 affecteddb.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：版本號碼,必須大於等於1public MyOpenHelper(Context context, String name, CursorFactory factory, int version) {super(context, name, factory, version);// TODO Auto-generated constructor stub}//資料庫建立時調用@Overridepublic 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調用了");}//資料庫升級時調用@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {// TODO Auto-generated method stubSystem.out.println("onupgrade調用了");db.execSQL("alter table person add money char(20)");}}