先来看一个纯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 }
细心你，会发现JdbcTemplate的实例中有一系列的方法如：queryForXXX,update,delete大大简化了JDBC操作。
当然,还可以再进一步的优化一下,就是通过依赖注入，直接把jdbcTemplate注入到dao类的jdbcT字段。
快动手试试吧！ 提示：Spring还提供了和Hibernate整合的SpringHibernateTemplate解决方案哦。
