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 person 17 -- ---------------------------- 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 * update 15 * @param p 16 * @return 17 * @throws SQLException 18 */ 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 * delete 27 * @param id 28 * @return 29 * @throws SQLException 30 */ 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());
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。