Spring基于XML的CURD操作

1、数据库准备数据

-- 创建数据库
CREATE DATABASE mydatabase;

-- 使用数据库
USE mydatabase;

-- 创建账户表
CREATE TABLE account(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(40),
    money FLOAT
)CHARACTER SET utf8 COLLATE utf8_general_ci;

-- 插入数据
INSERT INTO account(name,money) VALUES('aaa',1000);
INSERT INTO account(name,money) VALUES('bbb',1000);
INSERT INTO account(name,money) VALUES('ccc',1000);

2、创建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>org.example</groupId>
    <artifactId>spring_02_crud_xml</artifactId>
    <version>1.0-SNAPSHOT</version>

    <packaging>jar</packaging>

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

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

        <dependency>
            <groupId>commons-dbutils</groupId>
            <artifactId>commons-dbutils</artifactId>
            <version>1.6</version>
        </dependency>

        <dependency>
            <groupId>c3p0</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.1.2</version>
        </dependency>

        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.12</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.10</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

</project>

3、创建数据表对应的实体类与接口

3.1 创建pojo

public class Account implements Serializable {
    private Integer id;
    private String name;
    private Float money;
    // setter/getter/构造略
}

3.2 创建接口与实现类

3.2.1 创建dao接口

3.2.1.1 AccountDao接口

public interface AccountDao {
    /**
     * 查询所有
     * @return
     */
    List<Account> findAll();

    /**
     * 根据ID查询账户
     * @return
     */
    Account findAccountById(Integer id);

    /**
     * 保存账户
     * @param account
     */
    void saveAccount(Account account);

    /**
     * 更新账户
     * @param account
     */
    void updateAccount(Account account);

    /**
     * 根据ID删除指定账户
     * @param id
     */
    void deleteAccount(Integer id);
}

3.2.2.2 AccountDao接口的实现类

public class AccountDaoImpl implements AccountDao {
    private QueryRunner queryRunner;

    public AccountDaoImpl(QueryRunner queryRunner) {
        this.queryRunner = queryRunner;
    }

    public List<Account> findAll() {
        try {
            return queryRunner.query("SELECT * FROM account", new BeanListHandler<Account>(Account.class));
        }catch (Exception e) {
            throw new RuntimeException(e);
        }

    }

    public Account findAccountById(Integer id) {
        try {
            return queryRunner.query("SELECT * FROM account WHERE id = ?", new BeanHandler<Account>(Account.class), id);
        }catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public void saveAccount(Account account) {
        try {
            queryRunner.update("INSERT INTO account(name, money) values(?,?)", account.getName(), account.getMoney());
        }catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public void updateAccount(Account account) {
        try {
            queryRunner.update("UPDATE account SET name = ?, money = ? WHERE id = ?", account.getName(), account.getMoney(), account.getId());
        }catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public void deleteAccount(Integer id) {
        try {
            queryRunner.update("DELETE FROM account WHERE id = ?", id);
        }catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}

3.3.2 创建service接口

3.3.2.1 AccountService接口

public interface AccountService {
    /**
     * 查询所有
     * @return
     */
    List<Account> findAll();

    /**
     * 根据ID查询账户
     * @param id
     * @return
     */
    Account findAccountById(Integer id);

    /**
     * 保存账户
     * @param account
     */
    void saveAccount(Account account);

    /**
     * 更新账户
     * @param account
     */
    void updateAccount(Account account);

    /**
     * 根据ID删除指定账户
     * @param id
     */
    void deleteAccount(Integer id);
}

3.2.2.2 AccountService接口的实现类

public class AccountServiceImpl implements AccountService {
    private AccountDao accountDao;

    public AccountServiceImpl() {
    }

    public AccountDao getAccountDao() {
        return accountDao;
    }

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


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

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

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

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

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

4、创建Spring的配置文件

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

    <!-- 注册AccountService对象 -->
    <bean id="accountService" class="com.itcast.service.impl.AccountServiceImpl">
        <!-- 注入AccountDao对象 -->
        <property name="accountDao" ref="accountDao"></property>
    </bean>

    <!-- 注册AccountDao对象 -->
    <bean id="accountDao" class="com.itcast.dao.impl.AccountDaoImpl">
        <!-- 注入QueryRunner对象 -->
        <!-- 由于在本案例中QueryRunner对象为必须存在的对象，所以使用构造注入防止存在为空的情况（构造注入为强制注入，不允许不注入） -->
        <constructor-arg name="queryRunner" ref="queryRunner"></constructor-arg>
    </bean>

    <!-- 注册QueryRunner对象 -->
    <!--
        Bean模式是单例模式，queryRunner可能会存在会被多个dao对象同时使用的情况（即多个线程同时操作同一个对象），
        可能会存在问题，所以此处将该对象设置成多例的
    -->
    <bean id="queryRunner" class="org.apache.commons.dbutils.QueryRunner" scope="prototype">
        <!-- 注入数据源对象，此处使用c3p0的数据源 -->
        <constructor-arg name="ds" ref="datasource"></constructor-arg>
    </bean>

    <!-- 注册c3p0数据源对象 -->
    <bean id="datasource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <!-- 配置数据源必备信息：driver、url、username、password -->
        <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/mydatabase"></property>
        <property name="user" value="root"></property>
        <property name="password" value="root"></property>
    </bean>

</beans>

5、创建测试类

public class AccountServiceTest {
    @Test
    public void testFindAll() {
        ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
        AccountService accountService = ac.getBean("accountService", AccountService.class);
        List<Account> accounts = accountService.findAll();
        for (Account account : accounts) {
            System.out.println(account);
        }
    }

    @Test
    public void testFindAccountById() {
        ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
        AccountService accountService = ac.getBean("accountService", AccountService.class);
        Account account = accountService.findAccountById(1);
        System.out.println(account);
    }

    @Test
    public void testSaveAccount() {
        ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
        AccountService accountService = ac.getBean("accountService", AccountService.class);
        Account account = new Account();
        account.setName("ddd");
        account.setMoney(1000f);
        accountService.saveAccount(account);
    }

    @Test
    public void testUpdateAccount() {
        ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
        AccountService accountService = ac.getBean("accountService", AccountService.class);
        Account account = accountService.findAccountById(7);
        account.setMoney(2000f);
        accountService.updateAccount(account);
    }

    @Test
    public void testDeleteAccount() {
        ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
        AccountService accountService = ac.getBean("accountService", AccountService.class);
        accountService.deleteAccount(7);
    }
}

6、测试结果

testFindAll()的测试结果如下：

entity{id=1, name='aaa', money=1000.0}
entity{id=2, name='bbb', money=1000.0}
entity{id=3, name='ccc', money=1000.0}

testFindAccountById()的测试结果如下：

entity{id=1, name='aaa', money=1000.0}

testSaveAccount()的测试结果如下：

testUpdateAccount()的测试结果如下：

testDeleteAccount()的测试结果如下：