QueryRunner查询时封装查询结果的8种方法

前提:使用queryrunner需要导commons-dbutils包，它是DBUtil提供的一个工具类

<dependency>
			<groupId>commons-dbutils</groupId>
			<artifactId>commons-dbutils</artifactId>
			<version>1.6</version>
		</dependency>

还需要使用C3P0连接池，这个也需要导包

<!-- c3p0数据库连接池 -->
		<dependency>
			<groupId>com.mchange</groupId>
			<artifactId>c3p0</artifactId>
			<version>0.9.5.2</version>
		</dependency>

还需要数据库连接支持，导包

<!-- 数据库连接 -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.26</version>
		</dependency>

（前面几个封装处理比较常用）

1.将查询结果封装到实体类bean中，new BeanHandler(Product.class)，注意：实体类需要无参构造方法

/**
		 * 将查询结果封装到bean(实体类),只封装查询结果集中的第一行数据
		 * @param args
		 */
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select * from product where pid=?";
		Product product = null;
		try {
			product = qr.query(sql, new BeanHandler<Product>(Product.class),"1");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if(product!=null){
			System.out.println(product);
		}else{
			System.out.println("查不到数据");
		}

运行结果：

2.将查询结果封装到bean数组中，new BeanListHandler(Product.class)，注意：实体类需要无参构造方法

/**
		 * 封装查询结果到beanlist中，封装查询结果集的所有数据
		 */
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select * from product limit 0,6";
		List<Product> products = null;
		try {
			products = qr.query(sql, new BeanListHandler<Product>(Product.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if(products!=null){
			for(Product product:products){
				System.out.println(product);
			}
		}else{
			System.out.println("查不到数据");
		}

运行结果：

3.封装到Object数组中，new ArrayHandler()

/**
		 * 封装查询结果集到Object数组中，只封装结果集的第一行数据
		 */
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select * from product where pid=?";
		Object[] result = null;
		try {
			result = qr.query(sql, new ArrayHandler(), "10");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if(result!=null){
			for(Object object:result){
				System.out.println(object);
			}
		}else{
			System.out.println("查不到数据");
		}

运行结果:

4.封装查询结果集到List<Object[]>，new ArrayListHandler()

/**
		 * 封装查询结果集到Object数组中，只封装结果集的第一行数据
		 */
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select * from product limit 0,3";
		List<Object[]> result = null;
		try {
			result = qr.query(sql, new ArrayListHandler());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if(result!=null){
			for (Object[] objs : result) {
				for(Object object:objs){
					System.out.println(object);
				}
			}
		}else{
			System.out.println("查不到数据");
		}

运行结果：

5.封装结果集数据到单个对象（String/Long/Integer/Double等等），new ScalarHandler()

/**
		 * 封装单个结果到某个对象new ScalarHandler<Long>()
		 */
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select count(*) from product";
		try {
			Long count = qr.query(sql, new ScalarHandler<Long>());
			System.out.println(count);
		} catch (SQLException e) {
			e.printStackTrace();
		}

运行结果：

6.封装某一列的数据，new ColumnListHandler(“pname”),字符串表示列名,String表示列的类型

/*
		 * 将查询结果的某一列数据封装到List<>,new ColumnListHandler<String>("pname"),字符串表示列名,String表示列的类型
		 */
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select * from product";
		List<String> pnames = null;
		try {
			pnames = qr.query(sql, new ColumnListHandler<String>("pname"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if(pnames!=null){
			for (String pname : pnames) {
				System.out.println(pname);
			}
		}else{
			System.out.println("没有数据");
		}

运行结果：

7.封装查询结果集的第一行数据到map集合，new MapHandler()

/**
		 * 封装查询结果集的第一行数据到map集合，new MapHandler()
		 */
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select * from product where pid=?";
		Map<String, Object> result = null;
		try {
			result = qr.query(sql, new MapHandler(), "10");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		for(String key:result.keySet()){
			System.out.println(key+":"+result.get(key));
		}

运行结果：

8.封装查询结果集到List<Map<String,Object>>(将map集合再封装到list),new MapListHandler()

/**
		 * 封装查询结果集到List<Map<String,Object>>，new MapListHandler()
		 */
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select * from product limit 0,3";
		List<Map<String, Object>> mapsList = null;
		try {
			mapsList = qr.query(sql, new MapListHandler());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if(mapsList!=null){
			for(Map<String,Object> map:mapsList){
				for(String key:map.keySet()){
					System.out.println(key+":"+map.get(key));
				}
			}
		}else{
			System.out.println("无数据");
		}

运行结果：