BlackBerry操作sqlite的API封裝

 

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 />