开源框架spring详解-spring对JDBC的支持(2)
开源框架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();
}
}