首页 > 代码库 > Apache-Commons-Dbutils 学习笔记
Apache-Commons-Dbutils 学习笔记
1.项目结构
org.apache.commons.dbutils 包
|__DbUtils：jdbc辅助方法集合，线程安全
|__ResultSetHandler<T>:转换ResultSets对象 为其他Object 接口,下面为它的一些具体实现类
|__org.apache.commons.dbutils.handlers包
|__AbstractKeyedHandler<K,V>：把ResultSet里面数据转换为用Map存储，抽象类|__BeanMapHandler：map里面value保存的是一个bean类，根据相应key获取bean数据
|__KeyedHandler;ResultSetHandler h = new KeyedHandler("id"); 根据类中保存的key（id），获取相应value
|__AbstractListHandler<T>：把ResultSet里面数据转换为用List存储，抽象类
|__ArrayListHandler：list为arrayList
|__ColumnListHandler：
|__MapListHandler：
|__ArrayHandler：把ResultSet里面数据转换为用Object[]存储
|__BeanHandler<T>：把ResultSet里面数据转换为用javaBean存储
|__BeanListHandler<T>：把ResultSet里面数据转换为用List存储，list里面对象为javaBean
|__MapHandler：把ResultSet里面数据转换为用Map存储
|__ScalarHandler：
|__AbstractQueryRunner
|__QueryRunner ：基本查询类
|__AsyncQueryRunner
|__RowProcessor
|__BasicRowProcessor
|__BeanProcessor
|__ProxyFactory：代理类
|__QueryLoader
|__ResultSetIterator
org.apache.commons.dbutils.wrappers
|__SqlNullCheckedResultSet
|__StringTrimmedResultSet
2.配置maven
1 <dependency>2 <groupId>commons-dbutils</groupId>3 <artifactId>commons-dbutils</artifactId>4 <version>1.6</version>5 </dependency>
3.创建测试表数据
1 SET FOREIGN_KEY_CHECKS=0; 2 3 -- ---------------------------- 4 -- Table structure for person 5 -- ---------------------------- 6 DROP TABLE IF EXISTS `person`; 7 CREATE TABLE `person` ( 8 `id` bigint(20) NOT NULL AUTO_INCREMENT, 9 `name` varchar(24) DEFAULT NULL,10 `age` int(11) DEFAULT NULL,11 `address` varchar(120) DEFAULT NULL,12 PRIMARY KEY (`id`)13 ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=gbk;14 15 -- ----------------------------16 -- Records of person17 -- ----------------------------18 INSERT INTO `person` VALUES (‘1‘, ‘darkdog‘, ‘99‘, ‘beijing‘);
4.开始测试
首先封装一个工具类用于获取数据库连接
1 class CommonDbutils { 2 3 public static DataSource getDataSource() { 4 MysqlDataSource ds = new MysqlDataSource(); 5 ds.setURL("jdbc:mysql://localhost:3306/test?user=root&password=root"); 6 return ds; 7 } 8 9 public static Connection getConnection() throws SQLException {10 MysqlDataSource ds = (MysqlDataSource) getDataSource();11 Connection con = ds.getConnection();12 return con;13 }14 15 }
查询时，工具包提供了几种继承自ResultSetHandler， 用于处理结果集的Handler
1 /** 2 * 使用ResultSetHandler存储方式查询 3 * 4 * @param id 5 * @return 6 * @throws SQLException 7 */ 8 public Object[] getResultSet(Integer id) throws SQLException { 9 ResultSetHandler<Object[]> rsh = new ResultSetHandler<Object[]>() {10 public Object[] handle(ResultSet rs) throws SQLException {11 if (!rs.next()) {12 return null;13 }14 ResultSetMetaData meta = rs.getMetaData();15 int cols = meta.getColumnCount();16 Object[] result = new Object[cols];17 18 for (int i = 0; i < cols; i++) {19 result[i] = rs.getObject(i + 1);20 }21 return result;22 }23 };24 25 /*26 QueryRunner qr = new QueryRunner();27 return qr.query(CommonDbutils.getConnection(),28 "SELECT * FROM Person WHERE id=?", rsh, id);29 */30 31 QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());32 return qr.query("SELECT * FROM Person WHERE id=?", rsh, id);33 }
1 /** 2 * 使用BeanHandler存储方式查询 3 * 4 * @param id 5 * @return 6 * @throws SQLException 7 * @throws IllegalArgumentException 8 * @throws IllegalAccessException 9 */10 public Object getBean(Integer id) throws SQLException,11 IllegalArgumentException, IllegalAccessException {12 ResultSetHandler<Person> rsh = new BeanHandler<Person>(Person.class);13 QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());14 return qr.query("select id, name, age, address from Person where id=?",15 rsh, id);16 }
1 /** 2 * 使用BeanMapHandler存储方式查询 3 * 4 * @param id 5 * @return 6 * @throws SQLException 7 */ 8 public Map<Object, Person> getBeanMap(Integer id) throws SQLException { 9 ResultSetHandler<Map<Object, Person>> rsh = new BeanMapHandler<Object, Person>(10 Person.class);11 QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());12 return qr.query("select id, name, age, address from Person", rsh);13 }
1 /** 2 * 使用BeanListHandler存储方式查询 3 * 4 * @param params 5 * @return 6 * @throws SQLException 7 */ 8 public List<Person> getBeanList() throws SQLException { 9 ResultSetHandler<List<Person>> rsh = new BeanListHandler<Person>(10 Person.class);11 QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());12 String sql = "SELECT id, name, age, address FROM person";13 14 return qr.query(sql, rsh);15 }
1 /** 2 * 使用ScalarHandler存储方式查询 3 * 4 * @param id 5 * @return 6 * @throws SQLException 7 */ 8 public Object getObject(Integer id) throws SQLException { 9 ResultSetHandler<Object> rsh = new ScalarHandler<Object>();10 QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());11 return qr.query("select name from Person where id=?", rsh, id);12 }
1 /** 2 * 使用ArrayHandler存储方式查询 3 * 4 * @param id 5 * @return 6 * @throws SQLException 7 */ 8 public Object[] getArray(Integer id) throws SQLException { 9 ResultSetHandler<Object[]> rsh = new ArrayHandler();10 QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());11 return qr.query("SELECT * FROM Person WHERE id=?", rsh, id);12 }
1 /** 2 * 使用ArrayListHandler存储方式查询 3 * 4 * @param id 5 * @return 6 * @throws SQLException 7 */ 8 public List<Object[]> getArrayList(Integer id) throws SQLException { 9 ResultSetHandler<List<Object[]>> rsh = new ArrayListHandler();10 QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());11 return qr.query("SELECT * FROM Person WHERE id=?", rsh, id);12 }
1 /** 2 * 使用ColumnListHandler存储方式查询 3 * 4 * @param index 5 * @return 6 * @throws SQLException 7 */ 8 public List<Object> getColumnList(int index) throws SQLException { 9 ResultSetHandler<List<Object>> rsh = new ColumnListHandler<Object>(10 index);11 QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());12 return qr.query("SELECT * FROM Person", rsh);13 }
1 /** 2 * 使用MapHandler存储方式查询 3 * 4 * @param id 5 * @return 6 * @throws SQLException 7 */ 8 public Map<String, Object> getMap(Integer id) throws SQLException { 9 ResultSetHandler<Map<String, Object>> rsh = new MapHandler();10 QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());11 return qr.query("SELECT * FROM Person WHERE id=?", rsh, id);12 }
1 /** 2 * 使用MapListHandler存储方式查询 3 * 4 * @return 5 * @throws SQLException 6 */ 7 public List<Map<String, Object>> getMapList() throws SQLException { 8 ResultSetHandler<List<Map<String, Object>>> rsh = new MapListHandler(); 9 QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());10 return qr.query("SELECT * FROM Person", rsh);11 }
1 /** 2 * 使用KeyedHandler存储方式查询 3 * 4 * @return 5 * @throws SQLException 6 */ 7 public Map<Object, Map<String, Object>> getKeyed() throws SQLException { 8 ResultSetHandler<Map<Object, Map<String, Object>>> rsh = new KeyedHandler<Object>(); 9 QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());10 return qr.query("SELECT * FROM Person", rsh);11 }
以上是所有查询相关, 除了第一个ResultSetHandler其余几个几乎不需要读API文档就可以从返回值类型上看出区别
由于ResultSetHandler是一个接口所以必须实现handle方法来处理每个返回对象。
以下 insert, update, delete 用的都是QueryRunner中的update方法
1 /** 2 * insert 3 * @param p 4 * @return 5 * @throws SQLException 6 */ 7 public int insert(Person p) throws SQLException { 8 QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource()); 9 String sql = "insert into Person (id, name, age, address) values (?, ?, ?, ?)";10 return qr.update(sql, p.getId(), p.getName(), p.getAge(), p.getAddress());11 }12 13 /**14 * update15 * @param p16 * @return17 * @throws SQLException18 */19 public int update(Person p) throws SQLException {20 QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());21 String sql = "update Person set name = ?, age = ?, address = ? where id = ?";22 return qr.update(sql, p.getName(), p.getAge(), p.getAddress(), p.getId());23 }24 25 /**26 * delete27 * @param id28 * @return29 * @throws SQLException30 */31 public int delete(Integer id) throws SQLException {32 QueryRunner qr = new QueryRunner(CommonDbutils.getDataSource());33 String sql = "delete from Person where id = ?";34 return qr.update(sql, id);35 }
最后关掉数据库连接, 使用DBUtils类中提供了一些简单封装的静态方法
DbUtils.closeQuietly(CommonDbutils.getConnection());
Apache-Commons-Dbutils 学习笔记
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。