Spring基于xml配置用spring-jdbcTemplate实现简单的增删改查（CRUD）

目录结果

导入maven坐标

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.cenzn</groupId>
    <artifactId>SpringJDBCTemplate</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.2.5.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.2.5.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.32</version>
        </dependency>


        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>5.2.5.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13</version>
            <scope>test</scope>
        </dependency>

    </dependencies>

</project>

创建数据库

create table account(
	id int primary key auto_increment,
	name varchar(40),
	money float
)character set utf8 collate utf8_general_ci;

实体类Account.java

package com.cenzn.domain;

public class Account {

    private Integer id;
    private String name;
    private Float money;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Float getMoney() {
        return money;
    }

    public void setMoney(Float money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", money=" + money +
                '}';
    }
}

业务层接口AccountService.java

package com.cenzn.service;

import com.cenzn.domain.Account;

import java.util.List;

public interface IAccountService {

    void saveAccount(Account account);

    Account findAccountById(Integer id);

    Account findAccountByName(String name);

    List<Account> findAllAccount();

    void updateAccount(Account account);

    void deleteAccount(Integer id);

}

业务层实现类AccountServiceImpl.java

package com.cenzn.service.impl;

import com.cenzn.dao.IAccountDao;
import com.cenzn.domain.Account;
import com.cenzn.service.IAccountService;

import java.util.List;

public class AccountServiceImpl implements IAccountService {

    private IAccountDao accountDao;

    public void setAccountDao(IAccountDao accountDao) {
        this.accountDao = accountDao;
    }

    public void saveAccount(Account account) {
        accountDao.saveAccount(account);
    }

    public Account findAccountById(Integer id) {
        return accountDao.findAccountById(id);
    }

    public Account findAccountByName(String name) {
        return accountDao.findAccountByName(name);
    }

    public List<Account> findAllAccount() {
        return accountDao.findAllAccount();
    }

    public void updateAccount(Account account) {
        accountDao.updateAccount(account);
    }

    public void deleteAccount(Integer id) {
        accountDao.deleteAccount(id);
    }
}

持久层接口AccountDao.java

public interface IAccountDao {

    void saveAccount(Account account);

    Account findAccountById(Integer id);

    Account findAccountByName(String name);

    List<Account> findAllAccount();

    void updateAccount(Account account);

    void deleteAccount(Integer id);
}

持久层实现类AccountServiceImpl.java

package com.cenzn.dao.impl;

import com.cenzn.dao.IAccountDao;
import com.cenzn.domain.Account;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

public class AccountDaoImpl implements IAccountDao {

    private JdbcTemplate jt ;

    public void setJt(JdbcTemplate jt) {
        this.jt = jt;
    }

    public void saveAccount(Account account) {
        jt.update("insert into account(name,money)values (?,?)",account.getName(),account.getMoney());
    }


    public Account findAccountById(Integer id) {
        List<Account> accounts = jt.query("select * from account where id=?", new BeanPropertyRowMapper<Account>(Account.class),id);
        if(accounts.isEmpty()){
            return null;
        }
        if(accounts.size()>1){
            throw new RuntimeException("结果集不唯一，不是只有一个账户对象");
        }
        return accounts.get(0);
    }

    public Account findAccountByName(String name) {
        List<Account> accounts = jt.query("select * from account where name=?", new BeanPropertyRowMapper<Account>(Account.class),name);
        if(accounts.isEmpty()){
            return null;
        }
        if(accounts.size()>1){
            throw new RuntimeException("结果集不唯一，不是只有一个账户对象");
        }
        return accounts.get(0);
    }

    public List<Account> findAllAccount() {
        return jt.query("select * from account",new BeanPropertyRowMapper<Account>(Account.class));
    }

    public void updateAccount(Account account) {
        jt.update("update account set money=? where name=?",account.getMoney(),account.getName());
    }

    public void deleteAccount(Integer id) {
        jt.update("delete from account where id = ?",id);
    }


}

如果有多个dao,以一下代码将重复

private JdbcTemplate jdbcTemplate;

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

所以可以让dao继承JdbcDaoSupport

JdbcDaoSupport 是spring 框架为我们提供的一个类，该类中定义了一个 JdbcTemplate 对象，我们可以直接获取使用，但是要想创建该对象，需要为其提供一个数据源：具体源码如下：

JdbcDaoSupport的源码

//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//

package org.springframework.jdbc.core.support;

import java.sql.Connection;
import javax.sql.DataSource;
import org.springframework.dao.support.DaoSupport;
import org.springframework.jdbc.CannotGetJdbcConnectionException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.SQLExceptionTranslator;
import org.springframework.lang.Nullable;
import org.springframework.util.Assert;

public abstract class JdbcDaoSupport extends DaoSupport {
	//定义对象
    @Nullable
    private JdbcTemplate jdbcTemplate;

    public JdbcDaoSupport() {
    }
	//set 方法注入数据源，判断是否注入了，注入了就创建 JdbcTemplate
    public final void setDataSource(DataSource dataSource) {
        if (this.jdbcTemplate == null || dataSource != this.jdbcTemplate.getDataSource()) {
        //如果提供了数据源就创建 JdbcTemplate
            this.jdbcTemplate = this.createJdbcTemplate(dataSource);
            this.initTemplateConfig();
        }

    }
	//使用数据源创建 JdcbTemplate
    protected JdbcTemplate createJdbcTemplate(DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    @Nullable
    public final DataSource getDataSource() {
        return this.jdbcTemplate != null ? this.jdbcTemplate.getDataSource() : null;
    }
	//当然，我们也可以通过注入 JdbcTemplate 对象
    public final void setJdbcTemplate(@Nullable JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
        this.initTemplateConfig();
    }
	//使用 getJdbcTmeplate 方法获取操作模板对象
    @Nullable
    public final JdbcTemplate getJdbcTemplate() {
        return this.jdbcTemplate;
    }

    protected void initTemplateConfig() {
    }

    protected void checkDaoConfig() {
        if (this.jdbcTemplate == null) {
            throw new IllegalArgumentException("'dataSource' or 'jdbcTemplate' is required");
        }
    }

    protected final SQLExceptionTranslator getExceptionTranslator() {
        JdbcTemplate jdbcTemplate = this.getJdbcTemplate();
        Assert.state(jdbcTemplate != null, "No JdbcTemplate set");
        return jdbcTemplate.getExceptionTranslator();
    }

    protected final Connection getConnection() throws CannotGetJdbcConnectionException {
        DataSource dataSource = this.getDataSource();
        Assert.state(dataSource != null, "No DataSource set");
        return DataSourceUtils.getConnection(dataSource);
    }

    protected final void releaseConnection(Connection con) {
        DataSourceUtils.releaseConnection(con, this.getDataSource());
    }
}

修改后代码为

package com.cenzn.dao.impl;

import com.cenzn.dao.IAccountDao;
import com.cenzn.domain.Account;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import java.util.List;

public class AccountDaoImpl2 extends JdbcDaoSupport implements IAccountDao {

    public void saveAccount(Account account) {
        getJdbcTemplate().update("insert into account(name,money)values (?,?)",account.getName(),account.getMoney());
    }


    public Account findAccountById(Integer id) {
        List<Account> accounts = getJdbcTemplate().query("select * from account where id=?", new BeanPropertyRowMapper<Account>(Account.class),id);
        if(accounts.isEmpty()){
            return null;
        }
        if(accounts.size()>1){
            throw new RuntimeException("结果集不唯一，不是只有一个账户对象");
        }
        return accounts.get(0);
    }

    public Account findAccountByName(String name) {
        List<Account> accounts = getJdbcTemplate().query("select * from account where name=?", new BeanPropertyRowMapper<Account>(Account.class),name);
        if(accounts.isEmpty()){
            return null;
        }
        if(accounts.size()>1){
            throw new RuntimeException("结果集不唯一，不是只有一个账户对象");
        }
        return accounts.get(0);
    }

    public List<Account> findAllAccount() {
        return getJdbcTemplate().query("select * from account",new BeanPropertyRowMapper<Account>(Account.class));
    }

    public void updateAccount(Account account) {
        getJdbcTemplate().update("update account set money=? where name=?",account.getMoney(),account.getName());
    }

    public void deleteAccount(Integer id) {
        getJdbcTemplate().update("delete from account where id = ?",id);
    }


}

第一种在 Dao 类中定义 JdbcTemplate 的方式，适用于所有配置方式（xml 和注解都可以）。
让 第二种让 Dao 继承 JdbcDaoSupport 的方式，只能用于基于 XML 的方式，注解用不了。

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

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

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

    <bean id="dateSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql:///spring"></property>
        <property name="username" value="root"></property>
        <property name="password" value="root"></property>
    </bean>
    
</beans>

测试类JDBCTemplateTest.java

package com.cenzn.test;

import com.cenzn.domain.Account;
import com.cenzn.service.IAccountService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.util.List;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:bean.xml"})
public class JDBCTemplateTest {

    @Autowired
    private IAccountService accountService;

    /**
     * 保存
     */
    @Test
    public void saveTest(){
        Account account = new Account();
        account.setName("小龙");
        account.setMoney(1234f);
        accountService.saveAccount(account);
    }

    /**
     * 更新
     */
    @Test
    public void updateTest(){
        Account account = new Account();
        account.setName("小龙");
        account.setMoney(12340f);
        accountService.updateAccount(account);
    }

    /**
     * 通过ID查找
     */
    @Test
    public void findAccountByIdTest(){
        Account account = accountService.findAccountById(1);
        System.out.println(account);
    }
    /**
     * 通过name查找
     */
    @Test
    public void findAccountByNameTest(){
        Account account = accountService.findAccountByName("小龙");
        System.out.println(account);
    }
    /**
     * 查找所有账户
     */
    @Test
    public void findAccountTest(){
        List<Account> accounts = accountService.findAllAccount();
        for(Account account : accounts){
            System.out.println(account);
        }
    }
    /**
     * 通过ID删除
     */
    @Test
    public void deleteByIdTest(){
        accountService.deleteAccount(2);
    }


}