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

来源：互联网发布：c语言谭浩强实验编辑：程序博客网时间：2024/06/05 08:44

<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);}/** * 数据库第一次创建时调用 */@Overridepublic void onCreate(SQLiteDatabase db) {//执行sql 语句          db.execSQL("create table Person (id integer primary key autoincrement,name varchar(20),number varchar(20)) "); }/** * 当数据库版本变化时(修改了数据库表结构)调用 */@Overridepublic 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" />

0 0