随笔- 19 文章- 2 评论- 4 阅读- 7681

Android SQLite API的使用（非原创）

1.使用SQLite的API来进行数据库的添加、删除、修改、查询

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

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();//结束事务的同时会提交
        }
    }
}