spring配置c3p0连接池和jdbc模版

1.导入jar包

之前c3p0用了0.9.5.2版本一直报错，后来换了0.9.2.1，就好了。

2..数据库配置文件

jdbc.user=root
jdbc.password=123
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.jdbcUrl=jdbc:mysql:///spring

3.xml配置文件

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans 
	http://www.springframework.org/schema/beans/spring-beans.xsd
	http://www.springframework.org/schema/context
	http://www.springframework.org/schema/context/spring-context.xsd
	http://www.springframework.org/schema/aop 
	http://www.springframework.org/schema/aop/spring-aop.xsd">
	
	<context:property-placeholder location="classpath:jdbc.properties"/>
	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
	<property name="user" value="${jdbc.user}"></property>
	<property name="password" value="${jdbc.password}"></property>
	<property name="driverClass" value="${jdbc.driverClass}"></property>
	<property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
	</bean>
	
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
	<property name="dataSource" ref="dataSource"></property>
	</bean>
</beans>

4.实体类

public class Person {

	private int id;
	private String name;
	private String department;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getDepartment() {
		return department;
	}
	public void setDepartment(String department) {
		this.department = department;
	}
	@Override
	public String toString() {
		return "Person [department=" + department + ", id=" + id + ", name="
				+ name + "]";
	}
	
}

5.对增删查改进行测试

public class TestJDBC {
	ApplicationContext ctx = null;
	DataSource dataSource = null;
	JdbcTemplate jdbcTemplate = null;

	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	{
		ctx = new ClassPathXmlApplicationContext("spring-jdbc.xml");
		jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");
	}

	/**
	 * 测试数据源是否配置成功
	 * 
	 * @throws SQLException
	 */
	@Test
	public void testConnection() throws SQLException {
		dataSource = ctx.getBean(DataSource.class);
		System.out.println(dataSource.getConnection().toString());
	}

	/**
	 * 测试单条记录插入
	 */
	@Test
	public void testInsert() {
		String sql = "insert into person(id,name,dept)  values(?,?,?)";
		jdbcTemplate.update(sql, 1, "Tom", "财务部");
	}

	/**
	 * 测试批量记录插入
	 */
	@Test
	public void testBatchInsert() {
		String sql = "insert into person values(?,?,?)";
		List<Object[]> batchArgs = new ArrayList<Object[]>();
		batchArgs.add(new Object[] { 2, "chenchen", "业务部" });
		batchArgs.add(new Object[] { 3, "liming", "销售部" });
		batchArgs.add(new Object[] { 4, "Bety", "推广部" });
		jdbcTemplate.batchUpdate(sql, batchArgs);
	}

	/**
	 * 测试查询单条记录
	 */
	@Test
	public void testSelect() {
		String sql = "select id,name,dept as department from person where id=?";
		RowMapper<Person> rowMapper = new BeanPropertyRowMapper<Person>(
				Person.class);
		// Person person = jdbcTemplate.queryForObject(sql, rowMapper);
		// System.out.println(person);

		Person p = jdbcTemplate.queryForObject(sql, rowMapper, 3);
		System.out.println(p);

	}

	/**
	 * 测试查询全部记录
	 */
	@Test
	public void testSelectAll() {
		String sql = "SELECT id,name,dept as department FROM person";
		RowMapper<Person> rowMapper = new BeanPropertyRowMapper<Person>(
				Person.class);

		List<Person> personList = jdbcTemplate.query(sql, rowMapper);
		for (Person person : personList) {
			System.out.println(person);
		}
	}
	
	/**
	 * 修改操作类似
	 */

}