系统API 实现SQLite增删改 sql事务beginTransaction()

<span style="color:#ff0000;">API方式实现sql增删改[API 就是对sql语句检查拼装后在执行]</span>

 db.insert("Person", null, values);

 Cursor cursor = db.query("Person",null, "name=?", new String[]{name}, null, null, null);

 int number = db.delete("Person", "name=?", new String[]{name});

Cursor cursor = db.query("Person", new String[]{"name","id","number"}, null, null, null, null, null);

SQliteDao_API.java

package com.example.android_4_4;

import java.util.ArrayList;
import java.util.List;
import com.example.SQLiteDB.SQliteDomain;
import com.example.SQLiteDB.sqliteDB;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;

public class SQliteDao {  
    private sqliteDB  helper;  
    //构造方法里面初始化helper  
    public SQliteDao(Context context, String name, CursorFactory factory,int version) {  
        helper = new sqliteDB(context, name, factory,version);  
    }  
    /** 
     * helper.getWritableDatabase(); 获取可写的数据库对象 
     * db.execSQL();  values (?,?) 对应数组 Object[] 
     * @param name  名称 
     * @param number  电话号码 
     * @param money   金额
     */  
    public void add(String name,String number,int money){  
        SQLiteDatabase db = helper.getWritableDatabase();  
      //  db.execSQL("insert into Person (name,number) values (?,?,?)",new Object[]{name,number});  
        ContentValues values = new ContentValues();
        values.put("name", name);
        values.put("number", number);
        values.put("account", money);

        long id = db.insert("Person", null, values);
          
        db.close();  
    }  
    /** 
     * helper.getReadableDatabase(); 只读的数据库对象 
     * Cursor cursor 查询返回结果集对象 
     * @param name 
     * @return true 查询存在 ，false 查询不存在 
     */  
    public boolean find (String name){  
        SQLiteDatabase db = helper.getReadableDatabase();  
      //返回Cursor结构结果集  
//        Cursor cursor = db.rawQuery("select * from Person where name=?", new String[]{name});  
        Cursor cursor = db.query("Person",null, "name=?", new String[]{name}, null, null, null);
        
        boolean result = cursor.moveToNext();  
        cursor.close();  
        db.close();  
        return result;  
    }  
    /** 
     * 根据 name 修改 number 
     * @param name    
     * @param newNumber 
     */  
    public int update(String name,String newNumber){  
        SQLiteDatabase db = helper.getReadableDatabase();  
//        db.execSQL("update Person set number=? where name = ?", new Object[]{newNumber,name});  
        ContentValues values = new ContentValues();
        values.put("number", newNumber);
        int number = db.update("Person", values, "name=?", new String[]{newNumber});
        
        db.close();  
        return number;
    }  
    /** 
     * delete DB name 
     * @param name 
     */  
    public int delete(String name){  
        SQLiteDatabase db = helper.getReadableDatabase();  
//        db.execSQL("delete from Person where name = ?", new String[]{name});  
        int number = db.delete("Person", "name=?", new String[]{name});
        
        db.close();  
        return number;
    }  
    /** 
     * 查询返回cursor 数据库指针 
     * 通过cursor.moveToNext()遍历数据库 
     * 每次结果存到 SQliteDomain listDB 
     * 遍历结果存到 List<SQliteDomain> listArrayDB 
     * @return listArrayDB 数据集合 
     */  
    public List<SQliteDomain> showDB(){  
        SQLiteDatabase db = helper.getReadableDatabase();  
        List<SQliteDomain> listArrayDB = new ArrayList<SQliteDomain>();  
//        Cursor cursor = db.rawQuery("select id,name,number from Person", null);  
        Cursor cursor = db.query("Person", new String[]{"name","id","number"}, null, null, null, null, null);
        while (cursor.moveToNext()) {  
            int id = cursor.getInt(cursor.getColumnIndex("id"));  
            String name = cursor.getString(cursor.getColumnIndex("name"));  
            String number = cursor.getString(cursor.getColumnIndex("number"));  
            SQliteDomain listDB = new SQliteDomain(name, id, number);  
            listArrayDB.add(listDB);  
        }  
        cursor.close();  
        db.close();  
        return listArrayDB;  
    }  
    
}  

sqliteDB.java

package com.example.SQLiteDB;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class sqliteDB extends SQLiteOpenHelper {
	

	private String TAG="sqliteDB";
	/**
	 * 数据库构造方法， 
	 * @param context  上下文
	 * @param name		数据库 name
	 * @param factory	游标对象。null为默认
	 * @param version	数据库版本
	 */
	public sqliteDB(Context context, String name, CursorFactory factory,int version) {
		super(context, name, factory, version);
	}
	/**
	 * 数据库第一次创建时调用
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		//执行sql 语句  
        db.execSQL("create table Person (id integer primary key autoincrement,name varchar(20),number varchar(20)) "); 
	}
	/**
	 * 当数据库版本变化时(修改了数据库表结构)调用
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.i(TAG, "数据库版本变化了");
		//添加一个 account 表结构
		db.execSQL("alter table Person add account varchar(20)");
	}

}

SQliteDomain.java

package com.example.SQLiteDB;

public class SQliteDomain {
		private String name;  
	    private int id;  
	    private String number;  
	      
	    public String getName() {  
	        return name;  
	    }  
	    public void setName(String name) {  
	        this.name = name;  
	    }  
	    public int getId() {  
	        return id;  
	    }  
	    public void setId(int id) {  
	        this.id = id;  
	    }  
	    public String getNumber() {  
	        return number;  
	    }  
	    public void setNumber(String number) {  
	        this.number = number;  
	    }  
	      
	    public SQliteDomain(String name, int id, String number) {  
	        this.name = name;  
	        this.id = id;  
	        this.number = number;  
	    }  
	    public SQliteDomain() {  
	    	
	    }  
}

DBTest.java

package com.example.SQLiteDB.Test;

import com.example.SQLiteDB.sqliteDB;
import com.example.android_4_4.SQliteDao;

import android.database.sqlite.SQLiteDatabase;
import android.test.AndroidTestCase;

public class DBTest extends AndroidTestCase {  
//	public void testCreateDB() throws Exception{  
//        //1.创建SQliteDB 对象  
//        sqliteDB helper = new sqliteDB(getContext(),"Persons.db", null, 5);  
//        //2.写入 DB  
//        SQLiteDatabase db = helper.getWritableDatabase();  
//    //  db.execSQL(String sql);  
//    //  db.rawQuery(sql, selectionArgs);//查询操作   
//    }  
	public void AddDB() throws Exception{  
		SQliteDao dao = new SQliteDao(getContext(),"Persons.db", null, 5);  
		dao.add("a", "1234567890",100);
        dao.add("aa", "123456789",200);  
        dao.add("aaa", "12345678",300);  
        dao.add("aaaa", "1234567",0);  
    }  
	/**
     * sql 事务 db.beginTransaction();  
     * -50 +50 保证这个代码块同时执行
     * @param 
     */
    public void testTransaction(){
    	 sqliteDB helper = new sqliteDB(getContext(),"Persons.db", null, 5);  
    	 SQLiteDatabase db = helper.getWritableDatabase();
    	 //开启数据库事务
    	 db.beginTransaction();
    	 try {
    		 db.execSQL("update Person set account=account-50 where name = ?", new Object[]{"aaa"});
        	 db.execSQL("update Person set account=account+50 where name = ?", new Object[]{"aaaa"});
    	     //标记数据库事务执行成功
        	 db.setTransactionSuccessful();
    	   } finally {
    	     db.endTransaction();
    	     db.close();
    	   } 
    }
	
	
}

AndroidManifest.xml

<!-- 测试环境  / manifest-->  
    <instrumentation
        android:name="android.test.InstrumentationTestRunner"
        android:label="testa for my app"
        android:targetPackage="com.example.android_4_4" />

  <!-- 测试环境 application-->
        <uses-library android:name="android.test.runner" />