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

深碍√TFBOYSˉ_ 2021-11-22 11:48 427阅读 0赞

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

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

还需要使用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)，注意：实体类需要无参构造方法  
![封装到bean的运行结果][bean]

/**
    		 * 封装查询结果到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("查不到数据");
    		}

运行结果：  
![封装到beanlist的运行结果][beanlist]  
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("查不到数据");
    		}

运行结果:  
![封装到Object数组中][Object]  
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("查不到数据");
    		}

运行结果：  
![封装到List<Object\[\]>][List_Object]  
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();
    		}

运行结果：  
![封装scalarhandler][scalarhandler]  
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("没有数据");
    		}

运行结果：  
![封装某一列的数据][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoT0xn_size_16_color_FFFFFF_t_70]  
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));
    		}

运行结果：  
![封装数据到map集合][map]  
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("无数据");
    		}

运行结果：  
![封装到List<Map<String,Object>>][List_Map_String_Object]

[bean]: /images/20211122/e2fe74f4adbb4dc5828837d2340b2808.png
[beanlist]: /images/20211122/5879f92ac03248eea1a361a55b78215e.png
[Object]: /images/20211122/911ef9d5640f4f67b225dc624f808760.png
[List_Object]: /images/20211122/517980dcf77f4653b1e9c2782a0d4cea.png
[scalarhandler]: /images/20211122/f8298b2d29d84c51b928781481160151.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoT0xn_size_16_color_FFFFFF_t_70]: /images/20211122/3a5962c328c74920a4d3a92c15624bb7.png
[map]: /images/20211122/69a8d97bd17640b1963375c168fe03e5.png
[List_Map_String_Object]: /images/20211122/8b48025556a34401bfd3197934bf3506.png