QueryRunner是dbutils工具类的一个核心类
作用:操作sql语句构造器:
new QueryRunner(Datasource ds);
注意:
底层帮我们创建连接,创建语句执行者 ,释放资源.
常用方法:
query(String sql, ResultSetHandler<T> rsh);
update(..):
ResultSetHandler:封装结果集的接口,以下是其9个实现子类:
ArrayHandler, ArrayListHandler, BeanHandler, BeanListHandler, ColumnListHandler, KeyedHandler, MapHandler, MapListHandler, ScalarHandler 。
(了解)ArrayHandler:将查询结果的第一条记录封装成数组,返回
(了解)ArrayListHandler:将查询结果的每一条记录封装成数组,将每一个数组放入list中返回
★★BeanHandler:将查询结果的第一条记录封装成指定的bean对象,返回
★★BeanListHandler:将查询结果的每一条记录封装成指定的bean对象,将每一个bean对象放入list中 返回.
(了解)ColumnListHandler:将查询结果的指定一列放入list中返回
(了解)MapHandler:将查询结果的第一条记录封装成map,字段名作为key,值为value 返回
★MapListHandler:将查询结果的每一条记录封装map集合,将每一个map集合放入list中返回
★ScalarHandler:针对于聚合函数 例如:count(*) 返回的是一个Long值
KeyedHandler:
需要一个JavaBean类Category:
public class Category { private String cid; private String cname; public String getCid() { return cid; } public void setCid(String cid) { this.cid = cid; } public String getCname() { return cname; } public void setCname(String cname) { this.cname = cname; } @Override public String toString() { return "Category [cid=" + cid + ", cname=" + cname + "]"; } }
因为QueryRunner的构造器需要一个DataSource,所以先自己写一个DataSourceUtils工具类来获取DataSource。
import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javax.sql.DataSource; import com.mchange.v2.c3p0.ComboPooledDataSource; public class DataSourceUtils { private static ComboPooledDataSource ds = new ComboPooledDataSource(); /** * 获取数据源 * * @return 连接池 */ public static DataSource getDataSource() { return ds; } /** * 获取连接 * * @return 连接 * @throws SQLException */ public static Connection getConnection() throws SQLException { return ds.getConnection(); } /** * 释放资源 * * @param conn * 连接 * @param st * 语句执行者 * @param rs * 结果集 */ public static void closeResource(Connection conn, Statement st, ResultSet rs) { closeResultSet(rs); closeStatement(st); closeConn(conn); }; /** * 释放结果集 * * @param rs * 结果集 */ public static void closeResultSet(ResultSet rs) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } rs = null; } /** * 释放语句执行者 * * @param st * 语句执行者 */ public static void closeStatement(Statement st) { if (st != null) { try { st.close(); } catch (SQLException e) { e.printStackTrace(); } } st = null; } /** * 释放连接 * * @param conn * 连接 */ public static void closeConn(Connection conn) { if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } conn = null; } }
然后用Junit来测试不同ResultSetHandler的用法。
import java.sql.SQLException; import java.util.Arrays; import java.util.List; import java.util.Map; import com.jdbc.domain.Category; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.ArrayHandler; import org.apache.commons.dbutils.handlers.ArrayListHandler; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.MapHandler; import org.apache.commons.dbutils.handlers.MapListHandler; import org.apache.commons.dbutils.handlers.ScalarHandler; import org.junit.Test; import com.jdbc.domain.Category; import com.jdbc.utils.DataSourceUtils; // 自己创建的工具类 public class ResultHandleDemo { @Test // ArrayHandler, 将查询结果的第一条记录封装成数组,返回 public void arrayHandlerTest() throws SQLException { QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource()); String sql = "select * from category"; Object[] query = qr.query(sql, new ArrayHandler()); System.out.println(Arrays.toString(query)); } @Test // ArrayListHandler, 将查询结果的每一条记录封装成数组,将每一个数组放入list中返回 public void arrayListHandlerTest() throws SQLException { QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource()); String sql = "select * from category"; List<Object[]> query = qr.query(sql, new ArrayListHandler()); for (Object[] row : query) { System.out.println(Arrays.toString(row)); } } @Test // BeanHandler, 将查询结果的第一条记录封装成指定的bean对象,返回 public void beanHandlerTest() throws SQLException { QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource()); String sql = "select * from category"; Category bean = qr.query(sql, new BeanHandler<>(Category.class)); System.out.println(bean); } @Test // BeanListHandler, 将查询结果的每一条记录封装成指定的bean对象,将每一个bean对象放入list中 返回. public void beanListHandlerTest() throws SQLException { QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource()); String sql = "select * from category"; List<Category> beanList = qr.query(sql, new BeanListHandler<>(Category.class)); for (Category bean : beanList) { System.out.println(bean); } } @Test // MapHandler, 将查询结果的第一条记录封装成map,字段名作为key,值为value 返回 public void mapHandlerTest() throws SQLException { QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource()); String sql = "select * from category"; Map<String, Object> map = qr.query(sql, new MapHandler()); System.out.println(map); } @Test // MapListHandler, 将查询结果的每一条记录封装map集合,将每一个map集合放入list中返回 public void mapListHandlerTest() throws SQLException { QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource()); String sql = "select * from category"; List<Map<String, Object>> mapList = qr.query(sql, new MapListHandler()); for (Map<String, Object> map : mapList) { System.out.println(map); } } @Test // ScalarHandler,针对于聚合函数 例如:count(*) 返回的是一个Long值 public void ScalarHandlerTest() throws SQLException { QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource()); String sql = "select count(*) from category"; Object obj = qr.query(sql, new ScalarHandler()); System.out.println(obj); // obj是一个Long值 } }