springJDBC和SpringJDBCTemplate解决方案探究

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

先来看一个纯JDBC的例子，体会一下springJDBC和SpringJDBCTemplate两者的区别

一个Customer类

 1 package com.mkyong.customer.model;
 2 
 3 import java.sql.Timestamp;
 4 
 5 public class Customer 
 6 {
 7     int custId;
 8     String name;
 9     int age;
10     
11     
12     public Customer(int custId, String name, int age) {
13         this.custId = custId;
14         this.name = name;
15         this.age = age;
16     }
17     
18     public int getCustId() {
19         return custId;
20     }
21     public void setCustId(int custId) {
22         this.custId = custId;
23     }
24     public String getName() {
25         return name;
26     }
27     public void setName(String name) {
28         this.name = name;
29     }
30     public int getAge() {
31         return age;
32     }
33     public void setAge(int age) {
34         this.age = age;
35     }
36 
37     @Override
38     public String toString() {
39         return "Customer [age=" + age + ", custId=" + custId + ", name=" + name
40                 + "]";
41     }
42     
43     
44 }

Dao类：

1 package com.mkyong.customer.dao;
2 
3 import com.mkyong.customer.model.Customer;
4 
5 public interface CustomerDAO 
6 {
7     public void insert(Customer customer);
8     public Customer findByCustomerId(int custId);
9 }

DaoImpl类：

 1 package com.mkyong.customer.dao.impl;
 2 
 3 import java.sql.Connection;
 4 import java.sql.PreparedStatement;
 5 import java.sql.ResultSet;
 6 import java.sql.SQLException;
 7 
 8 import javax.sql.DataSource;
 9 
10 import com.mkyong.customer.dao.CustomerDAO;
11 import com.mkyong.customer.model.Customer;
12 
13 public class JdbcCustomerDAO implements CustomerDAO
14 {
15     private DataSource dataSource;
16     
17     public void setDataSource(DataSource dataSource) {
18         this.dataSource = dataSource;
19     }
20     
21     public void insert(Customer customer){
22         
23         String sql = "INSERT INTO CUSTOMER " +
24                 "(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";
25         Connection conn = null;   //每次都要重新声明一个conection
26         
27         try {
28             conn = dataSource.getConnection();
29             PreparedStatement ps = conn.prepareStatement(sql);   //每次都要进行conn.prepareStatment操作
30             ps.setInt(1, customer.getCustId());
31             ps.setString(2, customer.getName());
32             ps.setInt(3, customer.getAge());
33             ps.executeUpdate();  //每次都要提交更新
34             ps.close();  //每次都要手动关闭数据库连接
35             
36         } catch (SQLException e) {   //每次都要进行异常处理。导致大量冗余的代码
37             throw new RuntimeException(e);
38             
39         } finally {
40             if (conn != null) {
41                 try {
42                     conn.close();
43                 } catch (SQLException e) {}
44             }
45         }
46     }
47     
48     public Customer findByCustomerId(int custId){
49         
50         String sql = "SELECT * FROM CUSTOMER WHERE CUST_ID = ?";
51         
52         Connection conn = null;
53         
54         try {
55             conn = dataSource.getConnection();
56             PreparedStatement ps = conn.prepareStatement(sql);
57             ps.setInt(1, custId);
58             Customer customer = null;
59             ResultSet rs = ps.executeQuery();
60             if (rs.next()) {
61                 customer = new Customer(
62                         rs.getInt("CUST_ID"),
63                         rs.getString("NAME"), 
64                         rs.getInt("Age")
65                 );
66             }
67             rs.close();
68             ps.close();
69             return customer;
70         } catch (SQLException e) {
71             throw new RuntimeException(e);
72         } finally {
73             if (conn != null) {
74                 try {
75                 conn.close();
76                 } catch (SQLException e) {}
77             }
78         }
79     }
80 }

上述的例子充满了大量的冗余代码。，CRUD操作都要对应的写：

1 conn.open(); 2 conn.prepareStatment(); 3 conn.close() 以及异常处理....等等

这样子显得非常多余，可以有更加优雅的解决方案吗？废话当然有，Look~↓

提供了JdbcTemplate 来封装数据库jdbc操作细节：
包括：数据库连接[打开/关闭] ，异常转义，SQL执行，查询结果的转换

使用模板方式封装 jdbc数据库操作-固定流程的动作，提供丰富callback回调接口功能，方便用户自定义加工细节，更好模块化jdbc操作，简化传统的JDBC操作的复杂和繁琐过程。

看下面代码：

  1 package com.mkyong.customer.dao.impl;
  2 
  3 import java.util.ArrayList;
  4 import java.util.HashMap;
  5 import java.util.List;
  6 import java.util.Map;
  7 
  8 import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
  9 import org.springframework.jdbc.core.namedparam.SqlParameterSource;
 10 import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
 11 import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
 12 import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
 13 
 14 import com.mkyong.customer.dao.CustomerDAO;
 15 import com.mkyong.customer.model.Customer;
 16 import com.mkyong.customer.model.CustomerParameterizedRowMapper;
 17 
 18 
 19 public class SimpleJdbcCustomerDAO extends SimpleJdbcDaoSupport implements CustomerDAO
 20 {
 21     //insert example
 22     public void insert(Customer customer){
 23         
 24         String sql = "INSERT INTO CUSTOMER " +
 25             "(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";
 26              
 27         getSimpleJdbcTemplate().update(sql, customer.getCustId(),
 28                     customer.getName(),customer.getAge()  
 29         );
 30             
 31     }
 32     
 33     //insert with named parameter
 34     public void insertNamedParameter(Customer customer){
 35         
 36         String sql = "INSERT INTO CUSTOMER " +
 37             "(CUST_ID, NAME, AGE) VALUES (:custId, :name, :age)";
 38         
 39         Map<String, Object> parameters = new HashMap<String, Object>();
 40         parameters.put("custId", customer.getCustId());
 41         parameters.put("name", customer.getName());
 42         parameters.put("age", customer.getAge());
 43         
 44         getSimpleJdbcTemplate().update(sql, parameters);
 45             
 46     }
 47     
 48     
 49     //insert batch example
 50     public void insertBatch(final List<Customer> customers){
 51         
 52         String sql = "INSERT INTO CUSTOMER " +
 53             "(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";
 54             
 55         List<Object[]> parameters = new ArrayList<Object[]>();
 56         for (Customer cust : customers) {
 57             parameters.add(new Object[] {cust.getCustId(), cust.getName(), cust.getAge()});
 58         }
 59         getSimpleJdbcTemplate().batchUpdate(sql, parameters);
 60         
 61     }
 62 
 63     //insert batch with named parameter
 64     public void insertBatchNamedParameter(final List<Customer> customers){
 65         
 66         String sql = "INSERT INTO CUSTOMER " +
 67             "(CUST_ID, NAME, AGE) VALUES (:custId, :name, :age)";
 68             
 69         List<SqlParameterSource> parameters = new ArrayList<SqlParameterSource>();
 70         for (Customer cust : customers) {
 71             
 72             parameters.add(new BeanPropertySqlParameterSource(cust));
 73            
 74         }
 75         getSimpleJdbcTemplate().batchUpdate(sql,
 76                 parameters.toArray(new SqlParameterSource[0]));
 77     }
 78     
 79     //insert batch with named parameter
 80     public void insertBatchNamedParameter2(final List<Customer> customers){
 81         
 82         SqlParameterSource[] params = SqlParameterSourceUtils.createBatch(customers.toArray());
 83         getSimpleJdbcTemplate().batchUpdate(
 84                 "INSERT INTO CUSTOMER (CUST_ID, NAME, AGE) VALUES (:custId, :name, :age)",
 85                 params);
 86     
 87     }
 88     
 89     //insert batch example with SQL
 90     public void insertBatchSQL(final String sql){
 91         
 92         getJdbcTemplate().batchUpdate(new String[]{sql});
 93         
 94     }
 95     
 96     //query single row with ParameterizedRowMapper
 97     public Customer findByCustomerId(int custId){
 98          
 99         String sql = "SELECT * FROM CUSTOMER WHERE CUST_ID = ?";
100  
101         Customer customer = getSimpleJdbcTemplate().queryForObject(
102                 sql,  new CustomerParameterizedRowMapper(), custId);
103     
104         return customer;
105     }
106     
107     //query single row with ParameterizedBeanPropertyRowMapper (Customer.class)
108     public Customer findByCustomerId2(int custId){
109          
110         String sql = "SELECT * FROM CUSTOMER WHERE CUST_ID = ?";
111  
112         Customer customer = getSimpleJdbcTemplate().queryForObject(
113                 sql,ParameterizedBeanPropertyRowMapper.newInstance(Customer.class), custId);
114     
115         return customer;
116     }
117     
118     //query mutiple rows with ParameterizedBeanPropertyRowMapper (Customer.class)
119     public List<Customer> findAll(){
120         
121         String sql = "SELECT * FROM CUSTOMER";
122          
123         List<Customer> customers = 
124             getSimpleJdbcTemplate().query(sql, ParameterizedBeanPropertyRowMapper.newInstance(Customer.class));
125         
126         return customers;
127     }
128     
129     //query mutiple rows with ParameterizedBeanPropertyRowMapper (Customer.class)
130     public List<Customer> findAll2(){
131         
132         String sql = "SELECT * FROM CUSTOMER";
133         
134         List<Customer> customers = 
135             getSimpleJdbcTemplate().query(sql, ParameterizedBeanPropertyRowMapper.newInstance(Customer.class));
136         
137         return customers;
138     }
139     
140     public String findCustomerNameById(int custId){
141         
142         String sql = "SELECT NAME FROM CUSTOMER WHERE CUST_ID = ?";
143          
144         String name = getSimpleJdbcTemplate().queryForObject(
145                 sql, String.class, custId);
146     
147         return name;
148         
149     }
150     
151     public int findTotalCustomer(){
152         
153         String sql = "SELECT COUNT(*) FROM CUSTOMER";
154          
155         int total = getSimpleJdbcTemplate().queryForInt(sql);
156                 
157         return total;
158     }
159     
160     
161 }