7、JDBC DAO
l DB访问通常使用DAO设计模式,这里说明Spring支持的三种常用的方式:JDBC、iBatis和Herbinate,这里先说明JDBC DAO(Spring提供)
l 需要追加的jar文件:spring-jdbc.jar
(1) DAO接口
public interface UserDao {
public List selectUser(String userid);
}
(2)封装SQL操作
l Spring提供了多种实现JDBC DAO的方法,这里使用操作对象来封装SQL操作:
public class SelectUserQuery extends MappingSqlQuery {
private static final String QUERY_SQL = "SELECT A.USERID AS USERID, A.PASSWORD AS PASSWORD FROM USERS A WHERE A.USERID = ?";
public SelectUserQuery(DataSource dataSource) {
super(dataSource, QUERY_SQL);
declareParameter(new SqlParameter("USERID", Types.VARCHAR));
compile();
}
@Override
protected Object mapRow(ResultSet rs, int rowNumber) throws SQLException {
UserDto userDto = new UserDto();
userDto.setUserid(rs.getString("USERID"));
userDto.setPassword(rs.getString("PASSWORD"));
return userDto;
}
public List executeSQL(String userid) {
Object[] params = new Object[] { userid };
return super.execute(params);
}
}
l Select查询需要继承了MappingSqlQuery
l 构造函数中主要是预编译SQL语句,这里要做的事是调用declareParameter()方法指定每个“?”的DB类型
l mapRow()方法是需要重载的回调函数,这里要做的事是将ResultSet的数据传递给DTO对象(后面说明)
l executeSQL()方法调用MappingSqlQuery.execute()方法执行SQL语句,其间会调用mapRow()方法,将结果集数据传递给DTO对象集合(是个List),最后返回DTO对象List
l 这里executeSQL()方法的参数很简单,实际项目中通常是DTO对象,传递多个参数
l 构造函数需要传递DataSource参数,这里使用Bean管理(samples-dao-jdbc.xml):
<bean name="selectUserQuery"
class="org.xqtu.samples.dao.jdbc.SelectUserQuery">
<constructor-arg>
<ref bean="dataSource" />
</constructor-arg>
</bean>
(3)DTO对象
l DTO对象是个POJO对象,用于业务层和DB层之间的数据传输
public class UserDto {
private String userid;
private String password;
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getUserid() {
return userid;
}
public void setUserid(String userid) {
this.userid = userid;
}
}
(4) 实现DAO接口
public class UserDaoImpl implements UserDao {
public List selectUser(String userid) {
return selectUserQuery.executeSQL(userid);
}
private SelectUserQuery selectUserQuery;
public void setSelectUserQuery(SelectUserQuery selectUserQuery) {
this.selectUserQuery = selectUserQuery;
}
}
l 由于Select查询由SelectUserQuery对象封装,直接调用executeSQL()方法即可
l 这里使用Spring的Bean机制,注入SelectUserQuery对象(samples-dao-jdbc.xml):
<bean name="userDao" class="org.xqtu.samples.dao.impl.UserDaoImpl">
<property name="selectUserQuery" ref="selectUserQuery"></property>
</bean>
(5) 修改业务层:调用DAO
public class UserServiceImpl implements UserService {
public boolean isUserValid(String userid, String password) {
List userList = userDao.selectUser(userid);
if (userList != null && userList.size() > 0) {
UserDto userDto = (UserDto) userList.get(0);
return userDto.getPassword().equals(password);
}
return false;
}
private UserDao userDao;
public void setUserDao(UserDao userDao) {
this.userDao = userDao;
}
}
l 这里UserDao注入到UserServiceImpl(samples-services.xml):
<bean id="userService"
class="org.xqtu.samples.service.impl.UserServiceImpl">
<property name="userDao" ref="userDao"></property>
</bean>
l 这里可以看出业务逻辑的变更,对Web层是不受影响的