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.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.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);}});}}