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 <a href="https://www.f2er.com/tag/sql/" target="_blank" class="keywords">sql</a>,RowMapper rowMapper) throws DBException { return this.query(<a href="https://www.f2er.com/tag/sql/" target="_blank" class="keywords">sql</a>,new Object[0],rowMapper); } public Vector query(String <a href="https://www.f2er.com/tag/sql/" target="_blank" class="keywords">sql</a>,Object[] params,RowMapper rowMapper) throws DBException { Vector list = new Vector(); Database db = this.getDatabase(); Statement stmt = null; Cursor cursor = null; try { stmt = db.createStatement(<a href="https://www.f2er.com/tag/sql/" target="_blank" class="keywords">sql</a>); 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 <a href="https://www.f2er.com/tag/sql/" target="_blank" class="keywords">sql</a>,ParameterBinder binder,RowMapper rowMapper) throws DBException { Vector list = new Vector(); Database db = this.getDatabase(); Statement stmt = null; Cursor cursor = null; try { stmt = db.createStatement(<a href="https://www.f2er.com/tag/sql/" target="_blank" class="keywords">sql</a>); 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 <a href="https://www.f2er.com/tag/sql/" target="_blank" class="keywords">sql</a>) throws DBException { this.update(<a href="https://www.f2er.com/tag/sql/" target="_blank" class="keywords">sql</a>,new Object[0]); } public void update(String <a href="https://www.f2er.com/tag/sql/" target="_blank" class="keywords">sql</a>,Object[] params) throws DBException { Database db = this.getDatabase(); Statement stmt = null; try { db.beginTransaction(); stmt = db.createStatement(<a href="https://www.f2er.com/tag/sql/" target="_blank" class="keywords">sql</a>); 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 <a href="https://www.f2er.com/tag/sql/" target="_blank" class="keywords">sql</a>,ParameterBinder binder) throws DBException { Database db = this.getDatabase(); Statement stmt = null; try { db.beginTransaction(); stmt = db.createStatement(<a href="https://www.f2er.com/tag/sql/" target="_blank" class="keywords">sql</a>); 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,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); } }); } }