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