1. PostgreSql连接配置,
2. JDBCTemplate实现增删改
3. JDBCTemplate简单数据类型查询
4. JDBCTemplate复杂数据类型查询

1. PostgreSql连接配置

serverName = 127.0.0.1
databaseName = userinfo
user = postgres
password = 123
initialConnections = 2
maxConnections =10

postgresql.xml文件内容

<!-- 由PropertyPlaceholderConfigurer解析properties文件 -->
<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
	<!-- 指定properties文件所在路径 -->
	<property name="locations" value="classpath:postgresql.properties">
	</property>
</bean>

<!-- postgresql连接配置 -->
<bean id="dataSource" class="org.postgresql.ds.PGPoolingDataSource">
	<!-- 以${指定properties文件中key} -->
	<property name="dataSourceName" value="postgresqlPool"></property>
	<property name="serverName" value="${serverName}"></property>
	<property name="databaseName" value="${databaseName}"></property>
	<property name="user" value="${user}"></property>
	<property name="password" value="${password}"></property>
	<property name="initialConnections" value="${initialConnections}"></property>
	<property name="maxConnections" value="${maxConnections}"></property>
</bean>
<bean id="checkInfoDAO" class="qiufeng.spring.j_jdbctemplate_crud.CheckInfoDAO">
	<!-- 在注入dataSource的,会创建jdbc模板 -->
	<property name="dataSource" ref="dataSource"></property>
</bean>

2. JDBCTemplate实现增删改

package qiufeng.spring.j_jdbctemplate_crud;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

/**
 * 由JDBCTemplate实现增删改查  
 * 将DAO继承JdbcDaoSupport就是可以使用JDBCTemplate
 * 将checkInfoDAO注入的时候,会调用setDataSource,如果jdbcTemplate为空,则创建一个
 */
public class CheckInfoDAO extends JdbcDaoSupport {

	/**
	 * 添加
	 * @param checkInfo
	 * @return
	 */
	public int save(CheckInfo checkInfo){
		String sql = "insert into checkinfo(checkname) values(?);";		
		int result = this.getJdbcTemplate().update(sql, checkInfo.getCheckname());
		return result;
	}
	
	/**
	 * 根据id修改
	 * @param checkInfo
	 * @return
	 */
	public int update(CheckInfo checkInfo){
		String sql = "update checkinfo set checkname=? where nid=?;";
		int result = this.getJdbcTemplate().update(sql, checkInfo.getCheckname(),checkInfo.getNid());
		return result;
	}
	
	/**
	 * 根据id删除
	 * @param checkInfo
	 * @return
	 */
	public int delete(CheckInfo checkInfo){
		String sql = "delete from checkinfo where nid=? ;";
		int result = this.getJdbcTemplate().update(sql, checkInfo.getNid());
		return result;
	}
}

对应的测试类

package qiufeng.spring.j_jdbctemplate_crud;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:postgresql.xml")
public class CheckInfoDAOTest {

	@Autowired
	private CheckInfoDAO checkInfoDAO;

	@Test
	public void saveCheckInfoTest() {
		CheckInfo checkInfo = new CheckInfo();
		checkInfo.setCheckname("名称");
		int result = checkInfoDAO.save(checkInfo);
		System.out.println("添加结果:" + (result > 0 ? "成功" : "失败"));
	}

	@Test
	public void updatCheckInfoTest() {
		CheckInfo checkInfo = new CheckInfo();
		checkInfo.setNid(3);
		checkInfo.setCheckname("修改测试名称");
		int result = checkInfoDAO.update(checkInfo);

		System.out.println("修改结果:" + (result > 0 ? "成功" : "失败"));
	}

	@Test
	public void deleteCheckInfoTest() {
		CheckInfo checkInfo = new CheckInfo();
		checkInfo.setNid(3);
		int result = checkInfoDAO.delete(checkInfo);
		System.out.println("删除结果:" + (result > 0 ? "成功" : "失败"));
	}
}

3. JDBCTemplate简单数据类型查询

/**
 * 获取表中的行数
 * @return 行数
 */
public int getTotalCount(){
	String sql ="select count(nid) from checkinfo;";
	int result = this.getJdbcTemplate().queryForInt(sql);
	return result;
}

/**
 * 根据id获取checkname
 */
public String getCheckNameById(int id){
	String sql = "select checkname from checkinfo where nid=?;";
	String checkName = this.getJdbcTemplate().queryForObject(sql, String.class, id);
	return checkName;
}

测试方法

@Test
public void getTotalCountTest() {
	int totalCount = checkInfoDAO.getTotalCount();
	System.out.println("表中的数量为:" + totalCount);
}

@Test
public void getCheckNameTest() {
	String checkName = checkInfoDAO.getCheckNameById(3);
	System.out.println("nid为3的checkName:" + checkName);
}

/**
 * 根据id查询单个对象
 */
public CheckInfo getCheckInfoById(int id) {
	String sql = "select nid,checkname from checkinfo where nid=?;";
	return this.getJdbcTemplate().queryForObject(sql,new CheckInfoRowMapper(), id);
}

public List<CheckInfo> getAllCheckInfo() {
	String sql = "select nid,checkname from checkinfo;";
	return this.getJdbcTemplate().query(sql, new CheckInfoRowMapper());
}

/**
 * RowMapper将结果集填充到对象中 
 */
private class CheckInfoRowMapper implements RowMapper<CheckInfo> {
	@Override
	public CheckInfo mapRow(java.sql.ResultSet rs, int rowNum) throws SQLException {
		CheckInfo checkInfo = new CheckInfo();
		checkInfo.setNid(rs.getInt("nid"));
		checkInfo.setCheckname(rs.getString("checkname"));
		return checkInfo;
	}
}

测试方法

@Test
public void getCheckInfoByIdTest() {
	CheckInfo checkInfo = checkInfoDAO.getCheckInfoById(2);
	System.out.println("nid为2的checkName:" + checkInfo.getCheckname());
}

@Test
public void getAllCheckInfoTest() {
	List<CheckInfo> list = checkInfoDAO.getAllCheckInfo();
	for (int i = 0; i < list.size(); i++) {
		System.out.println(list.get(i));
	}
}