使用Spring的SimpleJdbcTemplate类实现jdbc为基础的DAO层

最近使用Spring2.5 的SimpleJdbcTemplate类实现了一个项目的DAO层。从开发的实践来看，上手比较容易，即使是初学者也能在一定的指导下很快掌握，开发的效率很高。

在使用的过程中发现Spring2.5与2.0比较起来已经有了一些变化，所以把代码也贴出来，算是抛砖引玉。这些代码和单元测试代码虽然比较简单，但都是可工作的代码。

DAO类 UserInfoDAO.java：

package com.example.jfxf.dao; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; import javax.sql.DataSource; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; import org.springframework.jdbc.core.simple.ParameterizedRowMapper; import org.springframework.jdbc.core.simple.SimpleJdbcInsert; import org.springframework.jdbc.core.simple.SimpleJdbcTemplate; import org.springframework.transaction.annotation.Transactional; import com.example.jfxf.model.JFXFUserInfo; public class UserInfoDAO { private SimpleJdbcTemplate simpleJdbcTemplate; private SimpleJdbcInsert insertUserInfo; public void setDataSource(DataSource dataSource) { this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource); this.insertUserInfo = new SimpleJdbcInsert(dataSource).withTableName( "jfxf_user_info").usingGeneratedKeyColumns("userinfo_id"); } @Transactional(readOnly = true) public List<JFXFUserInfo> getAllUserInfos() { return this.simpleJdbcTemplate .query( "select userinfo_id, user_msisdn, brand_type, balance from jfxf_user_info", new UserInfoMapper()); } @Transactional(readOnly = true) public JFXFUserInfo getUserInfoById(int userid) { return this.simpleJdbcTemplate .queryForObject( "select userinfo_id, user_msisdn, brand_type, balance from jfxf_user_info where userinfo_id=?", new UserInfoMapper(), userid); } @Transactional(readOnly = true) public JFXFUserInfo getUserInfoByPhoneNumber(String phoneNumber) { return this.simpleJdbcTemplate .queryForObject( "select userinfo_id, user_msisdn, brand_type, balance from jfxf_user_info where user_msisdn=?", new UserInfoMapper(), phoneNumber); } private class UserInfoMapper implements ParameterizedRowMapper<JFXFUserInfo> { public JFXFUserInfo mapRow(ResultSet rs, int rowNum) throws SQLException { JFXFUserInfo user = new JFXFUserInfo(); user.setUserinfoID(rs.getInt("userinfo_id")); user.setPhoneNumber(rs.getString("user_msisdn")); user.setBrandType(rs.getInt("brand_type")); user.setBalance(rs.getInt("balance")); return user; } } @Transactional public void saveUserInfo(JFXFUserInfo user) throws DataAccessException { if (user.isNew()) { Number newKey = this.insertUserInfo .executeAndReturnKey(createUserInfoParameterSource(user)); user.setUserinfoID(newKey.intValue()); } else { this.simpleJdbcTemplate .update( "UPDATE jfxf_user_info SET user_msisdn=:user_msisdn, brand_type=:brand_type, balance=:balance " + " WHERE userinfo_id=:userinfo_id", createUserInfoParameterSource(user)); } } private MapSqlParameterSource createUserInfoParameterSource( JFXFUserInfo user) { return new MapSqlParameterSource().addValue("userinfo_id", user.getUserinfoID()).addValue("user_msisdn", user.getPhoneNumber()).addValue("brand_type", user.getBrandType()).addValue("balance", user.getBalance()); } @Transactional public void deleteUserInfo(JFXFUserInfo user) throws DataAccessException { this.simpleJdbcTemplate.update( "delete from jfxf_user_info where userinfo_id=?", user .getUserinfoID()); } }

 

 

对应的Model对象：

 

package com.example.jfxf.model; public class JFXFUserInfo { public static final String[] brandTypeTrans={ "动感地带", "全球通" }; private int userinfoID = -1; private String phoneNumber; private int brandType; // 品牌 0：动感地带；1：全球通 private int balance; // 用户积分余额 public int getUserinfoID() { return userinfoID; } public void setUserinfoID(int userinfoID) { this.userinfoID = userinfoID; } public String getPhoneNumber() { return phoneNumber; } public void setPhoneNumber(String phoneNumber) { this.phoneNumber = phoneNumber; } public int getBrandType() { return brandType; } public void setBrandType(int brandType) { this.brandType = brandType; } public int getBalance() { return balance; } public void setBalance(int balance) { this.balance = balance; } public boolean isNew() { return (this.userinfoID == -1); } public String getBrandType_String(){ return brandTypeTrans[this.brandType]; } }

 

单元测试代码UserInfoDaoTest.java：

package com.example.jfxf.dao; import java.util.Collection; import java.util.List; import org.junit.Test; import static org.junit.Assert.assertEquals; import static org.junit.Assert.assertTrue; import static org.junit.Assert.assertNotNull; import static org.junit.Assert.assertNull; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.AbstractTransactionalJUnit4SpringContextTests; import com.example.jfxf.model.JFXFUserInfo; @ContextConfiguration(locations={"TestContext.xml"}) public class UserInfoDaoTest extends AbstractTransactionalJUnit4SpringContextTests { @Autowired protected UserInfoDAO userInfoDao; @Test public void getAllUserInfo() { List<JFXFUserInfo> users = this.userInfoDao.getAllUserInfos(); assertEquals("JDBC query must show the same number of UserInfos", super.countRowsInTable("jfxf_user_info"), users.size()); } @Test public void getUserInfoById() { JFXFUserInfo userInfo = this.userInfoDao.getUserInfoById(1); assertEquals("139", userInfo.getPhoneNumber()); } @Test public void getUserInfoByPhoneNumber() { JFXFUserInfo userInfo = this.userInfoDao.getUserInfoByPhoneNumber("139"); assertEquals("139", userInfo.getPhoneNumber()); } @Test public void saveUserInfo() { JFXFUserInfo newuser = new JFXFUserInfo(); newuser.setBalance(1000); newuser.setBrandType(1); newuser.setPhoneNumber("136"); assertEquals(-1, newuser.getUserinfoID() ); this.userInfoDao.saveUserInfo(newuser); System.out.println("newuser.getUserinfoID()="+newuser.getUserinfoID()); assertTrue(newuser.getUserinfoID() > -1 ); newuser.setPhoneNumber("139"); this.userInfoDao.saveUserInfo(newuser); assertEquals("139", newuser.getPhoneNumber()); } @Test public void deleteUserInfo() { JFXFUserInfo user = this.userInfoDao.getUserInfoById(1); assertEquals("139", user.getPhoneNumber()); userInfoDao.deleteUserInfo(user); } } 


Spring 的配置文件较简单，这里就不列出来了。
数据库的环境为Oracle，对应这个表在数据库中有一个生成主键的的序列和触发器。也不一一列出了。