开源框架spring详解-----spring对JDBC的支持(二)(修改了抱歉)

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

       在使用JDBC类操作数据库时，要处理很多相同的繁琐的细节，如：获取数据库的连接、创建Statement、处理数据库异常、关闭数据库资源等。Spring针对这种情况提供了几个类用来简化JDBC  API的使用。

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类

  

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类

 

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类

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();
	}
}