JDBCTemplate用法

一、JDBCTemplate的用法 XML配置

public class AccountDaoImpl implements IAccountDao {

    @Autowired
    JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    //保存
    public void saveAccount(Account account) {
        String saveSql = "insert into accounts(id, name, money) value(?,?,?)";
        jdbcTemplate.update(saveSql, account.getId(), account.getName(), account.getMoney());
    }
    //删除
    public void deleteAccount(int id) {
        String deleteSql = "delete from accounts where id = ?";
        jdbcTemplate.update(deleteSql, id);
    }
    //更新
    public void updateAccount(Account account) {
        String updateSql = "update accounts set money = ? where id = ?";
        jdbcTemplate.update(updateSql, account.getMoney(), account.getId());
    }
    //查询一个
    public Account searchAccountByName(String name) {
        String searchAccountByName = "select * from accounts where name = ?";
        RowMapper<Account> rowMapper = new BeanPropertyRowMapper<Account>(Account.class);
        return jdbcTemplate.queryForObject(searchAccountByName, rowMapper, name);
    }
    //查询一行或一列
    public Integer serchCount(Float money) {
        String searchCount = "select count(*) from accounts where money > ?";
        return jdbcTemplate.queryForObject(searchCount, Integer.TYPE, money);
    }

    //查询多个
    public List<Account> searchAccounts() {
        String serchAccountsSql = "select * from accounts where money > ?";
        RowMapper<Account> rowMapper = new BeanPropertyRowMapper<Account>(Account.class);
        return jdbcTemplate.query(serchAccountsSql, rowMapper, 100);
    }
	//查询多个 也可以自己写rowMapper，但是一般建议直接用上面的BeanPropertyRowMapper，这样就省去自定义rowMapper的过程
    public List<Account> searchAccounts2(float money) {
        String serchAccountsSql = "select * from accounts where money > ?";
        AccountRowMapper accountRowMapper = new AccountRowMapper();
        return jdbcTemplate.query(serchAccountsSql, accountRowMapper, money);
    }

    /**
     * 定义account封装策略
     */
    class AccountRowMapper implements RowMapper {
        /**
         * 把结果集封装到Account中，然后由spring把每个Account加到集合中
         * @param rs
         * @param rowNum
         * @return
         * @throws SQLException
         */
        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            Account account = new Account();
            account.setId(rs.getInt("id"));
            account.setName(rs.getString("name"));
            account.setMoney(rs.getFloat("money"));
            return account;
        }
    }

}

<?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"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        https://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        https://www.springframework.org/schema/context/spring-context.xsd">

    <context:property-placeholder location="classpath:application.properties"/>

    <bean id="accountService" class="com.springlearning.service.impl.AccountServiceImpl">
        <property name="accountDao" ref="accountDao"></property>
    </bean>

    <bean id="accountDao"  class="com.springlearning.dao.impl.AccountDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"></property>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="${driverClassName}"></property>
        <property name="url" value="${url}"></property>
        <property name="username" value="${spring.datasource.username}"></property>
        <property name="password" value="${spring.datasource.password}"></property>
     </bean>
</beans>

二、JdbcDaoSupport
当存在多个Dao的时候。在每个dao的类中都要注入JdbcTemplate, 也就是说会造成一下代码冗余

    @Autowired
    JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

这是可以继承JdbcDaoSupport， 用其内部的getJdbcTemplate()方法直接获得JdbcTemplate

public class AccountDaoImpl extends JdbcDaoSupport implements IAccountDao {
    //保存
    public void saveAccount(Account account) {
        String saveSql = "insert into accounts(id, name, money) value(?,?,?)";
        getJdbcTemplate().update(saveSql, account.getId(), account.getName(), account.getMoney());
    }
} 

//JdbcDaoSupport：
	protected JdbcTemplate createJdbcTemplate(DataSource dataSource) {
		return new JdbcTemplate(dataSource);
	}

以上代码，JdbcDaoSupport提供了用dataSource去创建JdbcTeplate的方法，所以在接下来的xml可以不用把jdbcTemplate的bean创建出来，而是直接把dataSource直接注入dao的bean对象中

<?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"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        https://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        https://www.springframework.org/schema/context/spring-context.xsd">

    <context:property-placeholder location="classpath:application.properties"/>

    <bean id="accountService" class="com.springlearning.service.impl.AccountServiceImpl">
        <property name="accountDao" ref="accountDao"></property>
    </bean>

    <bean id="accountDao"  class="com.springlearning.dao.impl.AccountDaoImpl">
        <!--<property name="jdbcTemplate" ref="jdbcTemplate"></property>-->
        <property name="dataSource" ref="dataSource"></property>
    </bean>
	
    <!--<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">-->
        <!--<property name="dataSource" ref="dataSource"></property>-->
    <!--</bean>-->

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="${driverClassName}"></property>
        <property name="url" value="${url}"></property>
        <property name="username" value="${spring.datasource.username}"></property>
        <property name="password" value="${spring.datasource.password}"></property>
     </bean>
</beans>

三、自定义JdbcTemplate和JdbcDaoSupport如何选取
注解使用JdbcTemplate， 因为在类中可以使用@AutoWired
xml配置使用JdbcDaoSupport