BlackBerry操作sqlite的API封装

     <p>BlackBerry操作sqlite的API封装</p>    <p> </p>    <p>BlackBerry提供了对SQLite的API，但是直接拿来使用还是比较麻烦的，这里自己写了一个小的API封装。</p>    <p> </p>    <p>1. 核心工具类DBUtil，提供对数据库表的操作。</p>    <pre class="brush:java; toolbar: true; auto-links: false;">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();   }  } }</pre>2. 数据库操作异常类DBException，这里由于BB的一下限制，所以个人感觉DBException继承RuntimeException类更好一点，而不是Exception类。    <pre class="brush:java; toolbar: true; auto-links: false;">package db; public class DBException extends RuntimeException {    private static final long serialVersionUID = 1L;  public DBException() {   super();  }  public DBException(String message) {   super(message);  } }</pre>3. ParameterBinder接口，用来做参数化执行sql语句是传递参数使用。    <pre class="brush:java; toolbar: true; auto-links: false;">package db; import net.rim.device.api.database.Statement; public interface ParameterBinder {    void bind(Statement stmt) throws Exception;   }</pre>4. RowMapper接口，用来提供对于每一行记录的转换。    <pre class="brush:java; toolbar: true; auto-links: false;">package db; import net.rim.device.api.database.Row; public interface RowMapper {    Object mapRow(Row row) throws Exception; }</pre>5. Test类    <pre class="brush:java; toolbar: true; auto-links: false;">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);      }     });  } }</pre>    <p></p>