springJDBC和SpringJDBCTemplate解决方案探究

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

一个Customer类

package com.mkyong.customer.model;

import java.sql.Timestamp;

public class Customer 
{
    int custId;
    String name;
    int age;
    
    
    public Customer(int custId, String name, int age) {
        this.custId = custId;
        this.name = name;
        this.age = age;
    }
    
    public int getCustId() {
        return custId;
    }
    public void setCustId(int custId) {
        this.custId = custId;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "Customer [age=" + age + ", custId=" + custId + ", name=" + name
                + "]";
    }
    
    
}

Dao类：

package com.mkyong.customer.dao;

import com.mkyong.customer.model.Customer;

public interface CustomerDAO 
{
    public void insert(Customer customer);
    public Customer findByCustomerId(int custId);
}

DaoImpl类：

package com.mkyong.customer.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.DataSource;

import com.mkyong.customer.dao.CustomerDAO;
import com.mkyong.customer.model.Customer;

public class JdbcCustomerDAO implements CustomerDAO
{
    private DataSource dataSource;
    
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }
    
    public void insert(Customer customer){
        
        String sql = "INSERT INTO CUSTOMER " +
                "(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";
        Connection conn = null;   //每次都要重新声明一个conection
        
        try {
            conn = dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);   //每次都要进行conn.prepareStatment操作
            ps.setInt(1, customer.getCustId());
            ps.setString(2, customer.getName());
            ps.setInt(3, customer.getAge());
            ps.executeUpdate();  //每次都要提交更新
            ps.close();  //每次都要手动关闭数据库连接
            
        } catch (SQLException e) {   //每次都要进行异常处理。导致大量冗余的代码
            throw new RuntimeException(e);
            
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {}
            }
        }
    }
    
    public Customer findByCustomerId(int custId){
        
        String sql = "SELECT * FROM CUSTOMER WHERE CUST_ID = ?";
        
        Connection conn = null;
        
        try {
            conn = dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, custId);
            Customer customer = null;
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                customer = new Customer(
                        rs.getInt("CUST_ID"),
                        rs.getString("NAME"), 
                        rs.getInt("Age")
                );
            }
            rs.close();
            ps.close();
            return customer;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if (conn != null) {
                try {
                conn.close();
                } catch (SQLException e) {}
            }
        }
    }
}

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

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

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

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

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

看下面代码：

package com.mkyong.customer.dao.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;

import com.mkyong.customer.dao.CustomerDAO;
import com.mkyong.customer.model.Customer;
import com.mkyong.customer.model.CustomerParameterizedRowMapper;


public class SimpleJdbcCustomerDAO extends SimpleJdbcDaoSupport implements CustomerDAO
{
    //insert example
    public void insert(Customer customer){
        
        String sql = "INSERT INTO CUSTOMER " +
            "(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";
             
        getSimpleJdbcTemplate().update(sql, customer.getCustId(),
                    customer.getName(),customer.getAge()  
        );
            
    }
    
    //insert with named parameter
    public void insertNamedParameter(Customer customer){
        
        String sql = "INSERT INTO CUSTOMER " +
            "(CUST_ID, NAME, AGE) VALUES (:custId, :name, :age)";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("custId", customer.getCustId());
        parameters.put("name", customer.getName());
        parameters.put("age", customer.getAge());
        
        getSimpleJdbcTemplate().update(sql, parameters);
            
    }
    
    
    //insert batch example
    public void insertBatch(final List<Customer> customers){
        
        String sql = "INSERT INTO CUSTOMER " +
            "(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";
            
        List<Object[]> parameters = new ArrayList<Object[]>();
        for (Customer cust : customers) {
            parameters.add(new Object[] {cust.getCustId(), cust.getName(), cust.getAge()});
        }
        getSimpleJdbcTemplate().batchUpdate(sql, parameters);
        
    }

    //insert batch with named parameter
    public void insertBatchNamedParameter(final List<Customer> customers){
        
        String sql = "INSERT INTO CUSTOMER " +
            "(CUST_ID, NAME, AGE) VALUES (:custId, :name, :age)";
            
        List<SqlParameterSource> parameters = new ArrayList<SqlParameterSource>();
        for (Customer cust : customers) {
            
            parameters.add(new BeanPropertySqlParameterSource(cust));
           
        }
        getSimpleJdbcTemplate().batchUpdate(sql,
                parameters.toArray(new SqlParameterSource[0]));
    }
    
    //insert batch with named parameter
    public void insertBatchNamedParameter2(final List<Customer> customers){
        
        SqlParameterSource[] params = SqlParameterSourceUtils.createBatch(customers.toArray());
        getSimpleJdbcTemplate().batchUpdate(
                "INSERT INTO CUSTOMER (CUST_ID, NAME, AGE) VALUES (:custId, :name, :age)",
                params);
    
    }
    
    //insert batch example with SQL
    public void insertBatchSQL(final String sql){
        
        getJdbcTemplate().batchUpdate(new String[]{sql});
        
    }
    
    //query single row with ParameterizedRowMapper
    public Customer findByCustomerId(int custId){
         
        String sql = "SELECT * FROM CUSTOMER WHERE CUST_ID = ?";
 
        Customer customer = getSimpleJdbcTemplate().queryForObject(
                sql,  new CustomerParameterizedRowMapper(), custId);
    
        return customer;
    }
    
    //query single row with ParameterizedBeanPropertyRowMapper (Customer.class)
    public Customer findByCustomerId2(int custId){
         
        String sql = "SELECT * FROM CUSTOMER WHERE CUST_ID = ?";
 
        Customer customer = getSimpleJdbcTemplate().queryForObject(
                sql,ParameterizedBeanPropertyRowMapper.newInstance(Customer.class), custId);
    
        return customer;
    }
    
    //query mutiple rows with ParameterizedBeanPropertyRowMapper (Customer.class)
    public List<Customer> findAll(){
        
        String sql = "SELECT * FROM CUSTOMER";
         
        List<Customer> customers = 
            getSimpleJdbcTemplate().query(sql, ParameterizedBeanPropertyRowMapper.newInstance(Customer.class));
        
        return customers;
    }
    
    //query mutiple rows with ParameterizedBeanPropertyRowMapper (Customer.class)
    public List<Customer> findAll2(){
        
        String sql = "SELECT * FROM CUSTOMER";
        
        List<Customer> customers = 
            getSimpleJdbcTemplate().query(sql, ParameterizedBeanPropertyRowMapper.newInstance(Customer.class));
        
        return customers;
    }
    
    public String findCustomerNameById(int custId){
        
        String sql = "SELECT NAME FROM CUSTOMER WHERE CUST_ID = ?";
         
        String name = getSimpleJdbcTemplate().queryForObject(
                sql, String.class, custId);
    
        return name;
        
    }
    
    public int findTotalCustomer(){
        
        String sql = "SELECT COUNT(*) FROM CUSTOMER";
         
        int total = getSimpleJdbcTemplate().queryForInt(sql);
                
        return total;
    }
    
    
}

细心你，会发现JdbcTemplate的实例中有一系列的方法如：queryForXXX,update,delete大大简化了JDBC操作。

当然,还可以再进一步的优化一下,就是通过依赖注入，直接把jdbcTemplate注入到dao类的jdbcT字段。

快动手试试吧！  提示：Spring还提供了和Hibernate整合的SpringHibernateTemplate解决方案哦。