Spring4的介绍10-spring的JDBC模板的CRUD

1. 添加

jdbcTemplate.update("insert into account values (null,?,?)", "张三",10000d);

2. 修改

jdbcTemplate.update("update account set name = ? ,money = ? where id = ?", "张三",2000d,6);

3. 删除

jdbcTemplate.update("delete from account where id = ?", 6);

4. 查询

4.1 查询单个字段的值

String name = jdbcTemplate.queryForObject("select name from account where id = ?", String.class, 5);

4.2 统计查询

Long count = jdbcTemplate.queryForObject("select count(*) from account", Long.class);

4.3 查询单个对象

Account account = jdbcTemplate.queryForObject("select * from account where id = ?", new MyRowMapper(), 5);


class MyRowMapper implements RowMapper<Account>{

		@Override
		public Account mapRow(ResultSet rs, int rowNum) throws SQLException {
			Account account = new Account();
			account.setId(rs.getInt("id"));
			account.setName(rs.getString("name"));
			account.setMoney(rs.getDouble("money"));
			return account;
		}
		
	}

4.4 查询对象集合

List<Account> list = jdbcTemplate.query("select * from account", new MyRowMapper());

class MyRowMapper implements RowMapper<Account>{

		@Override
		public Account mapRow(ResultSet rs, int rowNum) throws SQLException {
			Account account = new Account();
			account.setId(rs.getInt("id"));
			account.setName(rs.getString("name"));
			account.setMoney(rs.getDouble("money"));
			return account;
		}
		
	}

5. 使用JdbcDaoSupport获取JDBCTemplate

当我们有多个Dao对象的时候，在每一个dao中都需要设置JdbcTemplate，会显得很繁琐，我们可以让Dao继承JdbcDaoSupport，来获取JdbcTemplate

5.1 方式一

在xml配置文件中配置JdbcTemplate模板

<?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:context="http://www.springframework.org/schema/context"
	xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:tx="http://www.springframework.org/schema/tx"
	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
	http://www.springframework.org/schema/tx 
	http://www.springframework.org/schema/tx/spring-tx.xsd">
	
	<context:property-placeholder location="classpath:jdbc.properties"/>
	
	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="driverClass" value="${jdbc.driverClass}"></property>
		<property name="jdbcUrl" value="${jdbc.url}"></property>
		<property name="user" value="${jdbc.user}"></property>
		<property name="password" value="${jdbc.password}"></property>
	</bean>
	
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	
	<!-- 配置service -->
	<bean id="accountService" class="com.itcast.tx.demo1.AccountServiceImpl">
		<property name="accountDao" ref="accountDao"></property>
	</bean>
	
	<!-- 配置dao -->
	<bean id="accountDao" class="com.itcast.tx.demo1.AccountDaoImpl">
		<property name="jdbcTemplate" ref="jdbcTemplate"></property>
	</bean>
	
</beans>

在dao继承JdbcDaoSupport，通过getJdbcTemplate获取JdbcTemplate对象

public class AccountDaoImpl extends JdbcDaoSupport implements  AccountDao {


	public void outMoney(String from, Double money) {
		this.getJdbcTemplate().update("update account set money = money - ? where name = ?",money,from);
	}

	public void inMoney(String to, Double money) {
		this.getJdbcTemplate().update("update account set money = money + ? where name = ?",money,to);
	}
}

5.2 方式二（推荐）

在xml配置文件中，不用配置JdbcTemplate，通过观察源码可以知道其有一个setDataSource(DataSource dataSource)
方法，该方法设置了jdbcTemplate，所以，如果继承该类的话，我们可以不用写JDBCTemplate的定义了。

public final void setDataSource(DataSource dataSource) {
		if (this.jdbcTemplate == null || dataSource != this.jdbcTemplate.getDataSource()) {
			this.jdbcTemplate = createJdbcTemplate(dataSource);
			initTemplateConfig();
		}
	}

所以，当我们的dao继承了JdbcDaoSupport的时候，我们可以直接在dao中注入DataSource即可

配置文件

<?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:context="http://www.springframework.org/schema/context"
	xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:tx="http://www.springframework.org/schema/tx"
	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
	http://www.springframework.org/schema/tx 
	http://www.springframework.org/schema/tx/spring-tx.xsd">
	
	<context:property-placeholder location="classpath:jdbc.properties"/>
	
	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="driverClass" value="${jdbc.driverClass}"></property>
		<property name="jdbcUrl" value="${jdbc.url}"></property>
		<property name="user" value="${jdbc.user}"></property>
		<property name="password" value="${jdbc.password}"></property>
	</bean>

	<!-- 配置service -->
	<bean id="accountService" class="com.itcast.tx.demo1.AccountServiceImpl">
		<property name="accountDao" ref="accountDao"></property>
	</bean>
	
	<!-- 配置dao -->
	<bean id="accountDao" class="com.itcast.tx.demo1.AccountDaoImpl">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	
</beans>

Dao中使用jdbcTemplate模板

public class AccountDaoImpl extends JdbcDaoSupport implements  AccountDao {


	public void outMoney(String from, Double money) {
		this.getJdbcTemplate().update("update account set money = money - ? where name = ?",money,from);
	}

	public void inMoney(String to, Double money) {
		this.getJdbcTemplate().update("update account set money = money + ? where name = ?",money,to);
	}
}