Android SQLite API的使用（非原創），androidsqlite

Android SQLite API的使用（非原創），androidsqlite

1.使用SQLite的API來進行數據庫的添加、刪除、修改、查詢

package com.example.sqlitedatabase.test;

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

import com.example.sqlitedatabase.MyOpenHelper;

public class JunitTest2 extends AndroidTestCase{

	private MyOpenHelper helper;
	private SQLiteDatabase db;

	@Override
	protected void setUp() throws Exception {
		// TODO Auto-generated method stub
		super.setUp();
		helper = new MyOpenHelper(getContext(), "emp.db", null, 1);
		db = helper.getWritableDatabase();
	}

	@Override
	protected void tearDown() throws Exception {
		// TODO Auto-generated method stub
		super.tearDown();
		db.close();
	}

	public void test() {

	}

	public void insertAction() {
		db.execSQL("insert into Emp(name,salary) values('張無忌','12000')");
		db.execSQL("insert into Emp(name,salary) values('趙敏','11000')");
		db.execSQL("insert into Emp(name,salary) values('謝遜','16000')");
	}

	public void deleteAction() {
		db.execSQL("delete from Emp where name = '趙敏'");
	}

	public void updateAction() {
		db.execSQL("update Emp set salary = '18000' where name = ?",
				new Object[] { "謝遜" });
	}

	public void selectAction() {
		Cursor c = db.rawQuery("select * from Emp", null);
		while (c.moveToNext()) {
			String id = c.getString(c.getColumnIndex("id"));
			String name = c.getString(c.getColumnIndex("name"));
			String salary = c.getString(c.getColumnIndex("salary"));
			System.out.println(id + " , " + name + " , " + salary);
		}
	}

	public void insertAPI() {//添加
		ContentValues values = new ContentValues();//相當於map
		//添加的時候key一定要是Emp表中存在的字段
		values.put("name", "洪七公");
		values.put("salary", "5000");
		//insert(String table, String nullColumnHack, ContentValues values)
		db.insert("Emp", null, values);
	}

	public void deleteAPI() {//刪除
		/*
		 * delete(String table, String whereClause, String[] whereArgs)
		 * whereClause 
		 * 			the optional WHERE clause to apply when deleting. Passing null will delete all rows.
		 * whereArgs 
		 * 			You may include ?s in the where clause, which will be replaced by the values from whereArgs. The values will be bound as Strings.
		 */
		int columns = db.delete("Emp", "name = ?", new String[] { "謝遜" });
		System.out.println("行數：" + columns);
	}

	public void updateAPI() {//修改
		ContentValues values = new ContentValues();
		values.put("salary", 500);
		//update(String table, ContentValues values, String whereClause, String[] whereArgs)
		int columns = db.update("Emp", values, "name = ?",new String[] { "張無忌" });
		System.out.println("行數：" + columns);
	}

	public void selectAPI() {//查詢
		//query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)
		Cursor c = db.query("Emp", new String[] { "name", "salary" }," id > ?", new String[] { "1" }, null, null, null);
		System.out.println("ID大於1的人有：" + c.getCount() + "個人");
		while (c.moveToNext()) {
			String name = c.getString(c.getColumnIndex("name"));//獲取name字段對應的下標
			String salary = c.getString(c.getColumnIndex("salary"));
			System.out.println(name + " , " + salary);
		}
	}

	public void transaction() {
		try {
			db.beginTransaction();// 開始事務
			ContentValues values = new ContentValues();
			values.put("salary", 300);
			db.update("Emp", values, "name = ?", new String[] { "張無忌" });
			values.clear();
			int r = 4 / 0;// 模擬錯誤
			values.put("salary", 5200);
			db.update("Emp", values, "name = ?", new String[] { "洪七公" });
			db.setTransactionSuccessful();
		} catch (Exception e) {
			System.out.println("事務回滾啦");
		} finally {
			db.endTransaction();//結束事務的同時會提交
		}
	}
}