学步园

 

BlackBerry操作sqlite的API封装

 

BlackBerry提供了对SQLite的API，但是直接拿来使用还是比较麻烦的，这里自己写了一个小的API封装。

 

1. 核心工具类DBUtil，提供对数据库表的操作。

 

package db;<br /> import java.util.Vector;<br /> import net.rim.device.api.database.Cursor;<br /> import net.rim.device.api.database.Database;<br /> import net.rim.device.api.database.DatabaseException;<br /> import net.rim.device.api.database.DatabaseFactory;<br /> import net.rim.device.api.database.DatabaseIOException;<br /> import net.rim.device.api.database.Row;<br /> import net.rim.device.api.database.Statement;<br /> import net.rim.device.api.io.URI;<br /> public class DBUtil {</p> <p> public static DBUtil util = new DBUtil();</p> <p> private DBUtil() {</p> <p> }</p> <p> public static DBUtil getInstance() {<br /> return util;<br /> }</p> <p> public Vector query(String sql, RowMapper rowMapper) throws DBException {<br /> return this.query(sql, new Object[0], rowMapper);<br /> }</p> <p> public Vector query(String sql, Object[] params, RowMapper rowMapper) throws DBException {<br /> Vector list = new Vector();<br /> Database db = this.getDatabase();<br /> Statement stmt = null;<br /> Cursor cursor = null;</p> <p> try {<br /> stmt = db.createStatement(sql);<br /> stmt.prepare();<br /> for (int i = 0; i < params.length; i++) {<br /> stmt.bind(i + 1, params[i].toString());<br /> }<br /> cursor = stmt.getCursor();<br /> while (cursor.next()) {<br /> Row row = cursor.getRow();<br /> list.addElement(rowMapper.mapRow(row));<br /> }<br /> stmt.execute();<br /> } catch (Exception ex) {<br /> throw new DBException(ex.getMessage());<br /> } finally {<br /> this.close(cursor);<br /> this.close(stmt);<br /> this.close(db);<br /> }<br /> return list;<br /> }</p> <p> public Vector query(String sql, ParameterBinder binder, RowMapper rowMapper) throws DBException {<br /> Vector list = new Vector();<br /> Database db = this.getDatabase();<br /> Statement stmt = null;<br /> Cursor cursor = null;</p> <p> try {<br /> stmt = db.createStatement(sql);<br /> stmt.prepare();<br /> binder.bind(stmt);<br /> cursor = stmt.getCursor();<br /> while (cursor.next()) {<br /> Row row = cursor.getRow();<br /> list.addElement(rowMapper.mapRow(row));<br /> }<br /> stmt.execute();<br /> } catch (Exception ex) {<br /> throw new DBException(ex.getMessage());<br /> } finally {<br /> this.close(cursor);<br /> this.close(stmt);<br /> this.close(db);<br /> }<br /> return list;<br /> }</p> <p> public void update(String sql) throws DBException {<br /> this.update(sql, new Object[0]);<br /> }</p> <p> public void update(String sql, Object[] params) throws DBException {<br /> Database db = this.getDatabase();<br /> Statement stmt = null;<br /> try {<br /> db.beginTransaction();<br /> stmt = db.createStatement(sql);<br /> stmt.prepare();<br /> for (int i = 0; i < params.length; i++) {<br /> stmt.bind(i + 1, params[i].toString());<br /> }<br /> stmt.execute();<br /> db.commitTransaction();<br /> } catch (Exception ex) {<br /> throw new DBException(ex.getMessage());<br /> } finally {<br /> this.close(stmt);<br /> this.close(db);<br /> }<br /> }</p> <p> public void update(String sql, ParameterBinder binder) throws DBException {<br /> Database db = this.getDatabase();<br /> Statement stmt = null;<br /> try {<br /> db.beginTransaction();<br /> stmt = db.createStatement(sql);<br /> stmt.prepare();<br /> binder.bind(stmt);<br /> stmt.execute();<br /> db.commitTransaction();<br /> } catch (Exception ex) {<br /> throw new DBException(ex.getMessage());<br /> } finally {<br /> this.close(stmt);<br /> this.close(db);<br /> }<br /> }</p> <p> private Database getDatabase() throws DBException {<br /> try {<br /> URI myURI = URI.create("file:///SDCard/Databases/test.db");<br /> Database db = DatabaseFactory.openOrCreate(myURI);<br /> return db;<br /> } catch (Exception ex) {<br /> throw new DBException(ex.getMessage());<br /> }<br /> }</p> <p> private void close(Database db) {<br /> try {<br /> if (db != null) {<br /> db.close();<br /> }<br /> } catch (DatabaseIOException ex) {<br /> ex.printStackTrace();<br /> }<br /> }<br /> private void close(Statement stmt) {<br /> try {<br /> if (stmt != null) {<br /> stmt.close();<br /> }<br /> } catch (DatabaseException ex) {<br /> ex.printStackTrace();<br /> }<br /> }<br /> private void close(Cursor cursor) {<br /> try {<br /> if (cursor != null) {<br /> cursor.close();<br /> }<br /> } catch (DatabaseException ex) {<br /> ex.printStackTrace();<br /> }<br /> }<br /> } 

 

2. 数据库操作异常类DBException，这里由于BB的一下限制，所以个人感觉DBException继承RuntimeException类更好一点，而不是Exception类。

package db;<br /> public class DBException extends RuntimeException {</p> <p> private static final long serialVersionUID = 1L;<br /> public DBException() {<br /> super();<br /> }<br /> public DBException(String message) {<br /> super(message);<br /> }<br /> } 

 

3. ParameterBinder接口，用来做参数化执行sql语句是传递参数使用。

package db;<br /> import net.rim.device.api.database.Statement;<br /> public interface ParameterBinder {</p> <p> void bind(Statement stmt) throws Exception;</p> <p>} 

 

4. RowMapper接口，用来提供对于每一行记录的转换。

package db;<br /> import net.rim.device.api.database.Row;<br /> public interface RowMapper {</p> <p> Object mapRow(Row row) throws Exception;<br /> } 

 

5. Test类

package db;<br /> import net.rim.device.api.database.Row;<br /> import net.rim.device.api.database.Statement;<br /> public class Test {<br /> public static void test() throws Exception {<br /> DBUtil.getInstance().update("CREATE TABLE IF NOT EXISTS test ('id' Long,'col1' Text, 'col2' Text, 'col3' Text)");</p> <p> DBUtil.getInstance().update("insert into test(id, col1, col2, col3) values(?, ?, ?, ?)", new Object[]{Long.toString(System.currentTimeMillis()), "a", "b", "c"});<br /> DBUtil.getInstance().update("insert into test(id, col1, col2, col3) values(?, ?, ?, ?)", new ParameterBinder() {<br /> public void bind(Statement stmt) throws Exception {<br /> stmt.bind(1, 1);<br /> stmt.bind(2, "111");<br /> stmt.bind(3, "222");<br /> stmt.bind(4, "333");<br /> }<br /> });</p> <p> DBUtil.getInstance().query("select * from test", new RowMapper() {<br /> public Object mapRow(Row row) throws Exception {<br /> System.out.println(row.getObject(0));<br /> return row.getObject(0);<br /> }<br /> });<br /> DBUtil.getInstance().query("select * from test where id=?",<br /> new ParameterBinder() {<br /> public void bind(Statement stmt) throws Exception {<br /> stmt.bind(1, 1);<br /> }<br /> },<br /> new RowMapper() {<br /> public Object mapRow(Row row) throws Exception {<br /> System.out.println(row.getObject(0));<br /> return row.getObject(0);<br /> }<br /> });<br /> }<br /> }<br />