BlackBerry操作sqlite的API封裝

BlackBerry操作sqlite的API封裝

 

BlackBerry提供了對SQLite的API，但是直接拿來使用還是比較麻煩的，這里自己寫了一個小的API封裝。

 

1. 核心工具類DBUtil，提供對數據庫表的操作。

package db;
import java.util.Vector;
import net.rim.device.api.database.Cursor;
import net.rim.device.api.database.Database;
import net.rim.device.api.database.DatabaseException;
import net.rim.device.api.database.DatabaseFactory;
import net.rim.device.api.database.DatabaseIOException;
import net.rim.device.api.database.Row;
import net.rim.device.api.database.Statement;
import net.rim.device.api.io.URI;
public class DBUtil {

    public static DBUtil util = new DBUtil();

    private DBUtil() {

    }

    public static DBUtil getInstance() {
        return util;
    }

    public Vector query(String sql, RowMapper rowMapper) throws DBException {
        return this.query(sql, new Object[0], rowMapper);
    }

    public Vector query(String sql, Object[] params, RowMapper rowMapper) throws DBException {
        Vector list = new Vector();
        Database db = this.getDatabase();
        Statement stmt = null;
        Cursor cursor = null;

        try {
            stmt = db.createStatement(sql);
            stmt.prepare();
            for (int i = 0; i < params.length; i++) {
                stmt.bind(i + 1, params[i].toString());
            }
            cursor = stmt.getCursor();
            while (cursor.next()) {
                Row row = cursor.getRow();
                list.addElement(rowMapper.mapRow(row));
            }
            stmt.execute();
        } catch (Exception ex) {
            throw new DBException(ex.getMessage());
        } finally {
            this.close(cursor);
            this.close(stmt);
            this.close(db);
        }
        return list;
    }

    public Vector query(String sql, ParameterBinder binder, RowMapper rowMapper) throws DBException {
        Vector list = new Vector();
        Database db = this.getDatabase();
        Statement stmt = null;
        Cursor cursor = null;

        try {
            stmt = db.createStatement(sql);
            stmt.prepare();
            binder.bind(stmt);
            cursor = stmt.getCursor();
            while (cursor.next()) {
                Row row = cursor.getRow();
                list.addElement(rowMapper.mapRow(row));
            }
            stmt.execute();
        } catch (Exception ex) {
            throw new DBException(ex.getMessage());
        } finally {
            this.close(cursor);
            this.close(stmt);
            this.close(db);
        }
        return list;
    }

    public void update(String sql) throws DBException {
        this.update(sql, new Object[0]);
    }

    public void update(String sql, Object[] params) throws DBException {
        Database db = this.getDatabase();
        Statement stmt = null;
        try {
            db.beginTransaction();
            stmt = db.createStatement(sql);
            stmt.prepare();
            for (int i = 0; i < params.length; i++) {
                stmt.bind(i + 1, params[i].toString());
            }
            stmt.execute();
            db.commitTransaction();
        } catch (Exception ex) {
            throw new DBException(ex.getMessage());
        } finally {
            this.close(stmt);
            this.close(db);
        }
    }

    public void update(String sql, ParameterBinder binder) throws DBException {
        Database db = this.getDatabase();
        Statement stmt = null;
        try {
            db.beginTransaction();
            stmt = db.createStatement(sql);
            stmt.prepare();
            binder.bind(stmt);
            stmt.execute();
            db.commitTransaction();
        } catch (Exception ex) {
            throw new DBException(ex.getMessage());
        } finally {
            this.close(stmt);
            this.close(db);
        }
    }

    private Database getDatabase() throws DBException {
        try {
            URI myURI = URI.create("file:///SDCard/Databases/test.db");
            Database db = DatabaseFactory.openOrCreate(myURI);
            return db;
        } catch (Exception ex) {
            throw new DBException(ex.getMessage());
        }
    }

    private void close(Database db) {
        try {
            if (db != null) {
                db.close();
            }
        } catch (DatabaseIOException ex) {
            ex.printStackTrace();
        }
    }
    private void close(Statement stmt) {
        try {
            if (stmt != null) {
                stmt.close();
            }
        } catch (DatabaseException ex) {
            ex.printStackTrace();
        }
    }
    private void close(Cursor cursor) {
        try {
            if (cursor != null) {
                cursor.close();
            }
        } catch (DatabaseException ex) {
            ex.printStackTrace();
        }
    }
}

2. 數據庫操作異常類DBException，這里由于BB的一下限制，所以個人感覺DBException繼承RuntimeException類更好一點，而不是Exception類。

package db;
public class DBException extends RuntimeException {

    private static final long serialVersionUID = 1L;
    public DBException() {
        super();
    }
    public DBException(String message) {
        super(message);
    }
}

3. ParameterBinder接口，用來做參數化執行sql語句是傳遞參數使用。

package db;
import net.rim.device.api.database.Statement;
public interface ParameterBinder {

    void bind(Statement stmt) throws Exception;

}

4. RowMapper接口，用來提供對于每一行記錄的轉換。

package db;
import net.rim.device.api.database.Row;
public interface RowMapper {

    Object mapRow(Row row) throws Exception;
}

5. Test類

package db;
import net.rim.device.api.database.Row;
import net.rim.device.api.database.Statement;
public class Test {
    public static void test() throws Exception {
        DBUtil.getInstance().update("CREATE TABLE IF NOT EXISTS test ('id' Long,'col1' Text, 'col2' Text, 'col3' Text)");

        DBUtil.getInstance().update("insert into test(id, col1, col2, col3) values(?, ?, ?, ?)", new Object[]{Long.toString(System.currentTimeMillis()), "a", "b", "c"});
        DBUtil.getInstance().update("insert into test(id, col1, col2, col3) values(?, ?, ?, ?)", new ParameterBinder() {
            public void bind(Statement stmt) throws Exception {
                stmt.bind(1, 1);
                stmt.bind(2, "111");
                stmt.bind(3, "222");
                stmt.bind(4, "333");
            }
        });


        DBUtil.getInstance().query("select * from test", new RowMapper() {
            public Object mapRow(Row row) throws Exception {
                System.out.println(row.getObject(0));
                return row.getObject(0);
            }
        });
        DBUtil.getInstance().query("select * from test where id=?", 
                new ParameterBinder() {
                    public void bind(Statement stmt) throws Exception {
                        stmt.bind(1, 1);
                    }
                },
                new RowMapper() {
                    public Object mapRow(Row row) throws Exception {
                        System.out.println(row.getObject(0));
                        return row.getObject(0);
                    }
                });
    }
}