JDBC学习笔记——利用反射及JDBC元数据编写通用的查询方法

public Customer getCustomer(String sql, Object... args) { 2 Customer customer = null; 3 Connection connection = null; 4 PreparedStatement preparedStatement = null; 5 ResultSet resultSet = null; 6 try { 7 connection = JDBCTools.getConnection(); 8 preparedStatement = connection.prepareStatement(sql); 9 for (int i = 0; i < args.length; i++) { 10 preparedStatement.setObject(i + 1, args[i]); 11 } 12 resultSet = preparedStatement.executeQuery(); 13 if (resultSet.next()) { 14 // student = new Student(resultSet.getInt(1), 15 // resultSet.getInt(2), 16 // resultSet.getString(3), resultSet.getString(4), 17 // resultSet.getString(5), resultSet.getString(6), 18 // resultSet.getInt(7)); 19 customer = new Customer(); 20 customer.setId(resultSet.getInt(1)); 21 customer.setName(resultSet.getString(2)); 22 customer.setEmail(resultSet.getString(3)); 23 customer.setBirth(resultSet.getDate(4)); 24 } 25 } catch (Exception e) { 26 e.printStackTrace(); 27 } finally { 28 JDBCTools.release(resultSet, preparedStatement, connection); 29 } 30 return customer; 31 32 }

查询examstudent表中的字段以及字段值：

 1 public Student getStudent(String sql, Object... args) {
 2         Student student = null;
 3         Connection connection = null;
 4         PreparedStatement preparedStatement = null;
 5         ResultSet resultSet = null;
 6         try {
 7             connection = JDBCTools.getConnection();
 8             preparedStatement = connection.prepareStatement(sql);
 9             for (int i = 0; i < args.length; i++) {
10                 preparedStatement.setObject(i + 1, args[i]);
11             }
12             resultSet = preparedStatement.executeQuery();
13             if (resultSet.next()) {
14                 student = new Student(resultSet.getInt(1), resultSet.getInt(2),
15                         resultSet.getString(3), resultSet.getString(4),
16                         resultSet.getString(5), resultSet.getString(6),
17                         resultSet.getInt(7));
18             }
19         } catch (Exception e) {
20             e.printStackTrace();
21         } finally {
22             JDBCTools.release(resultSet, preparedStatement, connection);
23         }
24         return student;
25 
26     }

元数据：元数据就是描述数据的数据

可以看到两个操作有共同的步骤，不同的地方代码相似度也很高，于是我们就可以在此基础上编写一个通用的方法实现我们查询数据表的操作。

元

/**
     * ResultSetMetaData: 
     * 1).是描述ResultSet的元数据对象，即从中可以获取到 结果集中有多少列，列名是什么。。。
     * 2).如何使用呢？ 1.得到ResultSetMetaData对象： 调用ResultSet的getMetaData()方法
     * 2.ResultSetMetaData有哪些好用的方法？
     * 
     * >int getColumnCount():SQL语句中包含哪些列 
     * >String getColumn(int column):获取指定列的别名，其中索引从1开始
     * 
     */

具体的代码实现：

 1 public <T> T get(Class<T> classz, String sql, Object... args) {
 2         T entity = null;
 3         Connection connection = null;
 4         PreparedStatement preparedStatement = null;
 5         ResultSet resultSet = null;
 6         try {
 7             connection = JDBCTools.getConnection();
 8             preparedStatement = connection.prepareStatement(sql);
 9             for (int i = 0; i < args.length; i++) {
10                 preparedStatement.setObject(i + 1, args[i]);
11             }
12             resultSet = preparedStatement.executeQuery();
13 
14             ResultSetMetaData rsmd = resultSet.getMetaData();
15             Map<String, Object> values = new HashMap<String, Object>();
16             if (resultSet.next()) {
17                 for (int i = 0; i < rsmd.getColumnCount(); i++) {
18                     String columnLabel = rsmd.getColumnLabel(i + 1);
19                     Object columnValue = resultSet.getObject(columnLabel);
20                     values.put(columnLabel, columnValue);
21                 }
22             }
23             if (values.size() > 0) {
24                 entity = classz.newInstance();
25                 for (Map.Entry<String, Object> entry : values.entrySet()) {
26                     String fieldName = entry.getKey();
27                     Object fieldValue = entry.getValue();
28                     ReflectionUtils
29                             .setFieldValue(entity, fieldName, fieldValue);
30                 }
31             }
32         } catch (Exception e) {
33             e.printStackTrace();
34         } finally {
35             JDBCTools.release(resultSet, preparedStatement, connection);
36         }
37         // 返回包含查询信息的实体类对象
38         return entity;
39     }

get方法的编写实现步骤：

/**
     * 1.先利用SQL进行查询，得到结果集
     * 2.利用反射创建实体类的对象:创建Student对象
     * 3.获取结果集的列的别名：idCard、studentName 
     * 4.再获取结果集的每一列的值，结合3得到一个Map，键是列的别名：值；列的值：{flowid:5,type=6,idCard:xxx...} 列的别名和我们实体类的属性字段名一致
     * 5.再利用反射为2的对应的属性赋值   属性：Map的键   属性值：Map的键值
     */

我们写一个@test方法测试一下：

 1     @Test
 2     public void testGet() {
 3         String sql = "select id,name,email,birth "
 4                 + " from customers where id=?";
 5         System.out.println(sql);
 6         Customer customer = get(Customer.class, sql, 3);
 7         System.out.println(customer);
 8         sql = "select  flow_id flowId,type, idcard idCard, exam_card examCard,"
 9                 + "student_name studentName,location,grade from examstudent where "
10                 + " flow_id=?";
11         System.out.println(sql);
12         Student student = get(Student.class, sql, 3);
13         System.out.println(student);
14     }

运行结果：

select id,name,email,birth  from customers where id=?
Customer [id=3, name=ABCD, email=mike@163.com, birth=1992-06-07]
select  flow_id flowId,type, idcard idCard, exam_card examCard,student_name studentName,location,grade from examstudent where  flow_id=?
Student [flowId=3, type=6, idCard=371522199206078411, examCard=2015534083, studentName=li, location=dalian, grade=87]

 

JDBC学习笔记——利用反射及JDBC元数据编写通用的查询方法

标签：