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