09.SQLite_2

CRUD 操作

Ÿ   SQLiteDatabase封装了insert()、delete()、update()、query()四个方法也可以对数据库进行操作

这些方法封装了部分SQL语句，通过参数进行拼接

 

    public void insertPerson(Person person) {
        SQLiteDatabase db = helper.getWritableDatabase();
        
        ContentValues values = new ContentValues();
        values.put("id", person.getId());
        values.put("name", person.getName());
        values.put("balance", person.getBalance());
        db.insert("person", "id", values);
        
        values = new ContentValues();
        db.insert("person", "id", values);    // 插入一条除了 id 其他为 NULL 的数据
        
        db.close();    // 推荐关闭。对于同一个程序，没有并发操作，不关闭也没事
    }

    public void deletePerson(int id) {
        SQLiteDatabase db = helper.getWritableDatabase();
        db.delete("person", "id = ?", new String[] { String.valueOf(id) });
        db.close();
    }

    public void updatePerson(Person person) {
        SQLiteDatabase db = helper.getWritableDatabase();
        
        ContentValues values = new ContentValues();
        values.put("name", person.getName());
        values.put("balance", person.getBalance());
        db.update("person", values, "id = ?", new String[] { String.valueOf(person.getId()) });
        
        db.close();
    }
    
    public Person queryPerson(int id) {
        SQLiteDatabase db = helper.getReadableDatabase();    
        Cursor c = db.query("person", new String[] { "name", "balance"}, "id = ?", new String[] { String.valueOf(id) }, null, null, null);
        Person person = null;
        if (c.moveToNext()) {
            person = new Person();
            person.setId(id);
            person.setName(c.getString(0));
            person.setBalance(c.getInt(1));
            helper.close();
        }
        c.close();
        db.close();
        return person;
    }
    
    public List<Person> queryAll() {
        SQLiteDatabase db = helper.getReadableDatabase();
        List<Person> persons = new ArrayList<Person>();
        Person person = null;
        
        Cursor c = db.query("person", new String[] { "id", "name", "balance"}, null, null, null, null, "id DESC");
        while (c.moveToNext()) {
            person = new Person(c.getInt(c.getColumnIndex("id")), c.getString(c.getColumnIndex("name")), c.getInt(c.getColumnIndex("balance")));
            persons.add(person);
        }
        c.close();
        db.close();
        return persons;
    }
    
    public int queryCount() {
        SQLiteDatabase db = helper.getReadableDatabase();
        Cursor c = db.query("person", new String[] { "COUNT(*)"}, null, null, null, null, null);
        c.moveToNext();
        int count = c.getInt(0);
        c.close();
        db.close();
        return count;
    }
    
    public List<Person> pageQuery(int pageNum, int pageSize) {
        int offset = (pageNum - 1) * pageSize;
        SQLiteDatabase db = helper.getReadableDatabase();
        List<Person> persons = new ArrayList<Person>();
        Person person = null;
        Cursor c = db.query("person", new String[] { "id", "name", "balance"}, null, null, null, null, null, offset + "," + pageSize);
        while (c.moveToNext()) {
            person = new Person(c.getInt(0), c.getString(1), c.getInt(2));
            persons.add(person);
        }
        c.close();
        db.close();
        return persons;
    }

事务管理

Ÿ   在使用SQLite数据库时可以用SQLiteDatabase类中定义的相关方法控制事务

beginTransaction() 开启事务

setTransactionSuccessful() 设置事务成功标记

endTransaction() 结束事务

Ÿ   endTransaction()需要放在finally中执行，否则事务只有到超时的时候才自动结束，会降低数据库并发效率

    public void remit(int from, int to, int amount) {
        SQLiteDatabase db = helper.getWritableDatabase();
        try {
            db.beginTransaction();    // 开启事务
            db.execSQL("UPDATE person SET balance = balance - ? WHERE id = ?", new Object[] { String.valueOf(amount), String.valueOf(from) });
            System.out.println(1/0);
            db.execSQL("UPDATE person SET balance = balance + ? WHERE id = ?", new Object[] { String.valueOf(amount), String.valueOf(to) });
            db.setTransactionSuccessful();    // 设置事务成功标记
        } finally {
            db.endTransaction();        // 结束事务，将最后一个成功标记之前的内容提交
        }
    }