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