JDBC学习笔记(15)：利用结果集元数据将查询结果封装为map

浏览数：26 / 时间：2015年06月12日

在实际开发过程中，可能需要根据各种不同的条件进行查询，使用这种方式不需要条件写出一种查询方式。

 1 package com.xxyh.jdbc;
 2 import java.sql.Connection;
 3 import java.sql.PreparedStatement;
 4 import java.sql.ResultSet;
 5 import java.sql.ResultSetMetaData;
 6 import java.sql.SQLException;
 7 import java.util.Map;
 8 public class ResultSetMetaDataTest {
 9     
10     public static void main(String[] args) throws SQLException {
11         read("select id, name as 姓名 from user");
12     }
13     
14     static Map<String, Object> read(String sql) throws SQLException {
15         Connection conn = null;
16         PreparedStatement ps = null;
17         ResultSet rs = null;
18         
19         try {
20             conn = JdbcUtils.getConnection();
21             ps = conn.prepareStatement(sql);
22             rs = ps.executeQuery();
23             ResultSetMetaData rsmd = rs.getMetaData();
24             int count = rsmd.getColumnCount();// 获取返回结果列数
25             for (int i = 1; i <= count; i++) {
26                 System.out.print(rsmd.getColumnClassName(i) + "\t");
27                 System.out.print(rsmd.getColumnName(i) + "\t");
28                 System.out.println(rsmd.getColumnLabel(i));
29             }
30             return null;
31         } finally {
32             JdbcUtils.close(rs, ps, conn);
33         }
34     }
35 }

【运行结果】：

java.lang.Integer id id

java.lang.String name 姓名

从以上结果可以看出getColumnName方法和getColumnLabel的区别：getColumnName方法获取的是该列在数据库中真正的名字，而getColumnLabel是列的别名。

 1 package com.xxyh.jdbc;
 2 import java.sql.Connection;
 3 import java.sql.PreparedStatement;
 4 import java.sql.ResultSet;
 5 import java.sql.ResultSetMetaData;
 6 import java.sql.SQLException;
 7 import java.util.HashMap;
 8 import java.util.Map;
 9 public class ResultSetMetaDataTest {
10     
11     public static void main(String[] args) throws SQLException {
12         Map<String, Object> data = read("select id, name from user where id=1");//1
13         System.out.println(data);
14     }
15     
16     static Map<String, Object> read(String sql) throws SQLException {
17         Connection conn = null;
18         PreparedStatement ps = null;
19         ResultSet rs = null;
20         
21         try {
22             conn = JdbcUtils.getConnection();
23             ps = conn.prepareStatement(sql);
24             rs = ps.executeQuery();
25             ResultSetMetaData rsmd = rs.getMetaData();
26             int count = rsmd.getColumnCount();// 获取返回结果列数
27             String[] colNames = new String[count];
28             for (int i = 1; i <= count; i++) {
29                 colNames[i-1] = rsmd.getColumnName(i);//2
30             }
31             Map<String, Object> data = null;
32             if (rs.next()) {
33                 data = new HashMap<String, Object>();
34                 for (int i = 0; i < colNames.length; i++) {
35                     data.put(colNames[i], rs.getObject(colNames[i]));
36                 }
37             }
38             return data;
39         } finally {
40             JdbcUtils.close(rs, ps, conn);
41         }
42     }
43 }

【运行结果】：

{id=1, name=zhangs}

注：当 1处使用别名时，2处应该根据别名获取列明，改为getColumnLabel方法。

结果集存在多列，将其封装在List中：

 1 package com.xxyh.jdbc;
 2 import java.sql.Connection;
 3 import java.sql.PreparedStatement;
 4 import java.sql.ResultSet;
 5 import java.sql.ResultSetMetaData;
 6 import java.sql.SQLException;
 7 import java.util.ArrayList;
 8 import java.util.HashMap;
 9 import java.util.List;
10 import java.util.Map;
11 public class ResultSetMetaDataTest {
12     
13     public static void main(String[] args) throws SQLException {
14         List<Map<String, Object>> datas = read("select id, name from user where id<5");
15         System.out.println(datas);
16     }
17     
18     static List<Map<String, Object>> read(String sql) throws SQLException {
19         Connection conn = null;
20         PreparedStatement ps = null;
21         ResultSet rs = null;
22         
23         try {
24             conn = JdbcUtils.getConnection();
25             ps = conn.prepareStatement(sql);
26             rs = ps.executeQuery();
27             ResultSetMetaData rsmd = rs.getMetaData();
28             int count = rsmd.getColumnCount();// 获取返回结果列数
29             String[] colNames = new String[count];
30             for (int i = 1; i <= count; i++) {
31                 colNames[i-1] = rsmd.getColumnName(i);
32             }
33             List<Map<String, Object>> datas = new ArrayList<Map<String, Object>>();
34             while (rs.next()) {
35                 Map<String, Object> data = new HashMap<String, Object>();
36                 for (int i = 0; i < colNames.length; i++) {
37                     data.put(colNames[i], rs.getObject(colNames[i]));
38                 }
39                 datas.add(data);
40             }
41             return datas;
42         } finally {
43             JdbcUtils.close(rs, ps, conn);
44         }
45     }
46 }