开源框架spring详解-----spring对JDBC的支持(二)

 在使用JDBC类操作数据库时，要处理很多相同的繁琐的细节，如：获取数据库的连接、创建Statement、处理数据库异常、关闭数据库资源等。Spring针对这种情况提供了几个类用来简化JDBC  API的使用。
Java代码  
package com.zxf.domain;  
import java.util.Date;  
  
/** 
 * @author z_xiaofei168 
 */  
public class Account {  
    private Long id;  
    private String loginname;  
    private String password;  
    private String email;  
    private String cellphone;  
    private Date registedTime;  
      
    //以下省略所以的set 和get方法  
}  
   
   3.1、使用JdbcTemplate类
  
Java代码  
package com.zxf.dao;  
  
import java.util.List;  
import com.zxf.domain.Account;  
  
/** Account的DAO接口 */  
public interface AccountDao {  
    /** 新增账户 */  
    void create(Account acc);  
    /** 删除指定账户 */  
    void delete(Account acc);  
    /** 更新账户 */  
    void update(Account acc);  
    /** 查询指定ID的账户 */  
    Account findById(Long id);  
    /** 查询所有账户 */  
    List<Account> findAll();  
}  
  
  
  
package com.zxf.dao;  
  
import java.sql.ResultSet;  
import java.sql.SQLException;  
import java.sql.Timestamp;  
import java.util.Date;  
import java.util.List;  
  
import javax.sql.DataSource;  
  
import org.springframework.jdbc.core.JdbcTemplate;  
import org.springframework.jdbc.core.RowMapper;  
  
import com.zxf.domain.Account;  
  
/** 
 * AccountDao实现类 
 * 使用JdbcTemplate来实现 
 */  
public class AccountDaoJDBCImpl implements AccountDao {  
    private JdbcTemplate jdbcTemplate;  
      
    public void setDataSource(DataSource dataSource){  
        jdbcTemplate  = new JdbcTemplate(dataSource);  
    }  
  
    public void create(Account acc) {  
        String sql = "INSERT INTO account(loginname,password,email,"  
                +"cellphone,registed_time) VALUES(?,?,?,?, NOW())";  
          
        Object[] paramValues = {acc.getLoginname(), acc.getPassword(),   
                acc.getEmail(),acc.getCellphone()};  
          
        this.jdbcTemplate.update(sql,paramValues);  
    }  
  
  
    public void delete(Account acc) {  
        String sql = "DELETE FROM account WHERE id=?";  
        Object[] paramValues = {acc.getId()};  
          
        this.jdbcTemplate.update(sql, paramValues);  
    }  
      
    public void update(Account acc) {  
        String sql = "UPDATE account SET loginname=?,password=?,email=?,"  
            +"cellphone=? WHERE id=?";  
      
        Object[] paramValues = {acc.getLoginname(), acc.getPassword(),   
            acc.getEmail(),acc.getCellphone(), acc.getId()};  
      
        this.jdbcTemplate.update(sql,paramValues);  
    }  
      
    @SuppressWarnings("unchecked")  
    public List<Account> findAll() {  
        String sql = "SELECT * FROM account";  
        return this.jdbcTemplate.query(sql, new AccountRowMapper());  
    }  
  
    public Account findById(Long id) {  
        String sql = "SELECT * FROM account WHERE id=?";  
        Object[] paramValues = {id};  
        return (Account)jdbcTemplate.  
                    queryForObject(sql, paramValues, new AccountRowMapper());  
    }  
  
    //把结果集封装成Account对象的包装类  
    private static final class AccountRowMapper implements RowMapper {  
        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {  
            Account acc = new Account();  
            acc.setId(rs.getLong("id"));  
            acc.setLoginname(rs.getString("loginname"));  
            acc.setPassword(rs.getString("password"));  
            acc.setEmail(rs.getString("email"));  
            acc.setCellphone(rs.getString("cellphone"));  
              
            Timestamp temp = rs.getTimestamp("registed_time");  
            if(temp != null){  
                acc.setRegistedTime(new Date(temp.getTime()));  
            }  
            return acc;  
        }  
    }  
}  
  
  3.2、使用NamedParameterJdbcTemplate类
 
Java代码  
package com.zxf.dao;  
  
import java.sql.ResultSet;  
import java.sql.SQLException;  
import java.sql.Timestamp;  
import java.util.Date;  
import java.util.HashMap;  
import java.util.List;  
import java.util.Map;  
  
import javax.sql.DataSource;  
  
import org.springframework.jdbc.core.RowMapper;  
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;  
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;  
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;  
import org.springframework.jdbc.core.namedparam.SqlParameterSource;  
  
import com.zxf.domain.Account;  
  
/** 
 * 使用NamedParameterJdbcTemplate来实现AccountDao接口 
 */  
public class AccountDaoNamedParameterJdbcTemplateImpl implements AccountDao {  
    //带命名参数功能的Jdbc模板类实例  
    private NamedParameterJdbcTemplate jdbcTemplate;  
      
    public void setDataSource(DataSource dataSource){  
        jdbcTemplate  = new NamedParameterJdbcTemplate(dataSource);  
    }  
  
    public void create(Account acc) {  
        String sql = "INSERT INTO account(loginname,password,email,"  
                + "cellphone,registed_time) "  
                + "VALUES(:loginname,:password,:email,:cellphone, NOW())";  
        //使用一个Bean对象的属性值作为命名参数的值  
        SqlParameterSource namedParameters =   
                new BeanPropertySqlParameterSource(acc);  
  
        this.jdbcTemplate.update(sql,namedParameters);  
    }  
  
  
    public void delete(Account acc) {  
        String sql = "DELETE FROM account WHERE id=:id";  
        //使用指定的值来代替命名参数  
        SqlParameterSource namedParameters =   
                new MapSqlParameterSource("id", acc.getId());  
          
        this.jdbcTemplate.update(sql, namedParameters);  
    }  
      
    public void update(Account acc) {  
        String sql = "UPDATE account SET loginname=:loginname,"  
                + "password=:password,email=:email,"  
                + "cellphone=:cellphone WHERE id=:id";  
        //使用Map对象中的键/值对来代替多个命名参数的实际值  
        Map<String, Object> namedParameters = new HashMap<String, Object>();  
        namedParameters.put("loginname", acc.getLoginname());  
        namedParameters.put("password", acc.getPassword());  
        namedParameters.put("email", acc.getEmail());  
        namedParameters.put("cellphone", acc.getCellphone());  
        namedParameters.put("id", acc.getId());  
      
        this.jdbcTemplate.update(sql,namedParameters);  
    }  
      
    @SuppressWarnings("unchecked")  
    public List<Account> findAll() {  
        String sql = "SELECT * FROM account";  
        //通过getJdbcOperations()来访问只有在JdbcTemplate中拥有的功能  
        return this.jdbcTemplate  
                   .getJdbcOperations()  
                   .query(sql, new AccountRowMapper());  
    }  
  
    public Account findById(Long id) {  
        String sql = "SELECT * FROM account WHERE id=?";  
        //使用指定的值来代替命名参数  
        SqlParameterSource namedParameters =   
                new MapSqlParameterSource("id", id);  
        return (Account)jdbcTemplate  
                    .query(sql, namedParameters, new AccountRowMapper());  
    }  
  
    //把结果集封装成Account对象的包装类  
    private static final class AccountRowMapper implements RowMapper {  
        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {  
            Account acc = new Account();  
            acc.setId(rs.getLong("id"));  
            acc.setLoginname(rs.getString("loginname"));  
            acc.setPassword(rs.getString("password"));  
            acc.setEmail(rs.getString("email"));  
            acc.setCellphone(rs.getString("cellphone"));  
            Timestamp temp = rs.getTimestamp("registed_time");  
            if(temp != null){  
                acc.setRegistedTime(new Date(temp.getTime()));  
            }  
            return acc;  
        }  
    }  
}  
  
  3.3、使用SimpleJdbcTemplate类
Java代码  
package com.zxf.dao;  
  
import java.sql.ResultSet;  
import java.sql.SQLException;  
import java.sql.Timestamp;  
import java.util.Date;  
import java.util.List;  
  
import javax.sql.DataSource;  
  
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;  
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;  
  
import com.zxf.domain.Account;  
  
/** 
 * 使用SimplateJdbcTemplate来实现AccountDao接口 
 */  
public class AccountDaoSimpleJdbcTemplateImpl implements AccountDao {  
    private SimpleJdbcTemplate jdbcTemplate;  
      
    public void setDataSource(DataSource dataSource){  
        jdbcTemplate  = new SimpleJdbcTemplate(dataSource);  
    }  
  
    public void create(Account acc) {  
        String sql = "INSERT INTO account(loginname,password,email,"  
                +"cellphone,registed_time) VALUES(?,?,?,?, NOW())";  
          
        this.jdbcTemplate.update(sql, acc.getLoginname(),  
                                      acc.getPassword(),   
                                      acc.getEmail(),  
                                      acc.getCellphone());  
    }  
  
    public void delete(Account acc) {  
        String sql = "DELETE FROM account WHERE id=?";  
          
        this.jdbcTemplate.update(sql, acc.getId());  
    }  
      
    public void update(Account acc) {  
        String sql = "UPDATE account SET loginname=?,password=?,email=?,"  
            +"cellphone=? WHERE id=?";  
      
        this.jdbcTemplate.update(sql, acc.getLoginname(),   
                                      acc.getPassword(),   
                                      acc.getEmail(),  
                                      acc.getCellphone(),   
                                      acc.getId());  
    }  
      
    public List<Account> findAll() {  
        String sql = "SELECT * FROM account";  
        return this.jdbcTemplate.query(sql, new AccountRowMapper());  
    }  
  
    public Account findById(Long id) {  
        String sql = "SELECT * FROM account WHERE id=?";  
        return jdbcTemplate.queryForObject(sql, new AccountRowMapper(), id);  
    }  
  
    //把结果集封装成Account对象的包装类  
    private static final class AccountRowMapper  
            implements ParameterizedRowMapper<Account> {  
        public Account mapRow(ResultSet rs, int rowNum)   
                throws SQLException {  
            Account acc = new Account();  
            acc.setId(rs.getLong("id"));  
            acc.setLoginname(rs.getString("loginname"));  
            acc.setPassword(rs.getString("password"));  
            acc.setEmail(rs.getString("email"));  
            acc.setCellphone(rs.getString("cellphone"));  
            Timestamp temp = rs.getTimestamp("registed_time");  
            if(temp != null){  
                acc.setRegistedTime(new Date(temp.getTime()));  
            }  
            return acc;  
        }  
    }  
}  
  
  
package com.zxf.service;  
  
import java.util.List;  
  
import com.zxf.dao.AccountDao;  
import com.zxf.domain.Account;  
  
/** 
 * Account业务逻辑类 
 * @author z_xiaofei168 
 */  
public class AccountService {  
    private AccountDao accountDao;  
      
    //setter注入  
    public void setAccountDao(AccountDao accountDao){  
        this.accountDao = accountDao;  
    }  
  
    public void create(Account acc) {  
        accountDao.create(acc);  
    }  
  
    public void delete(Account acc) {  
        accountDao.delete(acc);  
    }  
  
    public void update(Account acc) {  
        accountDao.update(acc);  
    }  
  
    public Account findById(Long id) {  
        return accountDao.findById(id);  
    }  
  
    public List<Account> findAll() {  
        return accountDao.findAll();  
    }  
}