Spring笔记06 基于xml的IoC案例（实现CRUD）
coconutnut
 2020-03-04 18:00:29      

https://www.bilibili.com/video/av47952931
p35~37

数据库部分

mysql中建一张account表

maven工程

项目结构

pom.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
<?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.coconutnut</groupId>
    <artifactId>day02_02_account_xml</artifactId>
    <version>1.0-SNAPSHOT</version>
<!--    打成jar包-->
    <packaging>jar</packaging>

<!--    加入依赖-->
    <dependencies>
<!--        spring-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.0.2.RELEASE</version>
        </dependency>

<!--        dbutils-->
        <dependency>
            <groupId>commons-dbutils</groupId>
            <artifactId>commons-dbutils</artifactId>
            <version>1.4</version>
        </dependency>

<!--        mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.16</version>
        </dependency>

<!--        jdbc连接池-->
        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.5.2</version>
        </dependency>

<!--    junit测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.10</version>
        </dependency>
    </dependencies>

</project>

实体类

Account.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
package com.cc.domain;

import java.io.Serializable;

/**
 * 账户的实体类
 */
public class Account implements Serializable {

    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 +
                '}';
    }
}

业务层

IAccountService.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
package com.cc.service;

import com.cc.domain.Account;

import java.util.List;

/**
 * 账户的业务层接口
 */
public interface IAccountService {

    /**
     * 增
     * @param account
     */
    void createAccount(Account account);

    /**
     * 删
     * @param accountId
     */
    void deleteAccount(Integer accountId);

    /**
     * 改
     * @param account
     */
    void updateAccount(Account account);

    /**
     * 查一个
     * @param accountId
     * @return
     */
    Account retrieveAccountById(Integer accountId);

    /**
     * 查所有
     * @return
     */
    List<Account> retrieveAllAccounts();

}

AccountServiceImpl.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
package com.cc.service.impl;

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

import java.util.List;

/**
 * 账户的业务层实现类
 */
public class AccountServiceImpl implements IAccountService {

    private IAccountDao accountDao;

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

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

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

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

    public Account retrieveAccountById(Integer accountId) {
        return accountDao.retrieveAccountById(accountId);
    }

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

持久层

IAccountDao.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
package com.cc.dao;

import com.cc.domain.Account;

import java.util.List;

/**
 * 账户的持久层接口
 */
public interface IAccountDao {

    void createAccount(Account account);

    void deleteAccount(Integer accountId);

    void updateAccount(Account account);

    Account retrieveAccountById(Integer accountId);

    List<Account> retrieveAllAccounts();

}

AccountDaoImpl.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
package com.cc.dao.impl;

import com.cc.dao.IAccountDao;
import com.cc.domain.Account;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * 账户的持久层实现类
 */
public class AccountDaoImpl implements IAccountDao {

    private QueryRunner runner;

    public void setRunner(QueryRunner runner) {
        this.runner = runner;
    }

    public void createAccount(Account account) {
        try {
            runner.update("insert into account(name,money) values(?,?)", account.getName(), account.getMoney());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public void deleteAccount(Integer accountId) {
        try {
            runner.update("delete from account where id = ?", accountId);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public void updateAccount(Account account) {
        try {
            runner.update("update account set name = ?, money = ? where id = ?", account.getName(), account.getMoney(),account.getId());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public Account retrieveAccountById(Integer accountId) {
        try {
            return runner.query("select * from account where id = ?", new BeanHandler<Account>(Account.class), accountId);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public List<Account> retrieveAllAccounts() {
        try {
            return runner.query("select * from account", new BeanListHandler<Account>(Account.class));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

}

beans.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
<?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">

<!--    配置Service-->
    <bean id="accountService" class="com.cc.service.impl.AccountServiceImpl">
<!--        注入Dao-->
        <property name="accountDao" ref="accountDao"></property>
    </bean>
    
<!--    配置Dao-->
    <bean id="accountDao" class="com.cc.dao.impl.AccountDaoImpl">
<!--        注入QueryRunner-->
        <property name="runner" ref="runner"></property>
    </bean>
    
<!--    配置QueryRunner（多例）-->
    <bean id="runner" class="org.apache.commons.dbutils.QueryRunner" scope="prototype">
<!--        注入数据源（没有set方法，只能构造函数注入）-->
        <constructor-arg name="ds" ref="dataSource"></constructor-arg>
    </bean>

<!--    配置数据源-->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<!--        注入连接数据库的信息-->
        <property name="driverClass" value="com.mysql.cj.jdbc.Driver"></property>
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/groot?characterEncoding=utf8"></property>
        <property name="user" value="root"></property>
        <property name="password" value="iamgroot"></property>
    </bean>

</beans>

测试方法

AccountServiceTest.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
package com.cc.test;

import com.cc.domain.Account;
import com.cc.service.IAccountService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.util.List;

/**
 * 使用Junit测试配置
 */
public class AccountServiceTest {

    @Test
    public void testCreate(){
        // 1.获取容器
        ApplicationContext ac = new ClassPathXmlApplicationContext("beans.xml");
        // 2.得到业务层对象
        IAccountService as = ac.getBean("accountService",IAccountService.class);
        // 3.执行方法
        Account account = new Account();
        account.setName("ddd");
        account.setMoney(10f);
        as.createAccount(account);
    }

    @Test
    public void testDelete(){
        // 1.获取容器
        ApplicationContext ac = new ClassPathXmlApplicationContext("beans.xml");
        // 2.得到业务层对象
        IAccountService as = ac.getBean("accountService",IAccountService.class);
        // 3.执行方法
        as.deleteAccount(2);
    }

    @Test
    public void testUpdate(){
        // 1.获取容器
        ApplicationContext ac = new ClassPathXmlApplicationContext("beans.xml");
        // 2.得到业务层对象
        IAccountService as = ac.getBean("accountService",IAccountService.class);
        // 3.执行方法
        Account account = as.retrieveAccountById(1);
        account.setMoney(2000f);
        as.updateAccount(account);
    }

    @Test
    public void testRetrieveOne(){
        // 1.获取容器
        ApplicationContext ac = new ClassPathXmlApplicationContext("beans.xml");
        // 2.得到业务层对象
        IAccountService as = ac.getBean("accountService",IAccountService.class);
        // 3.执行方法
        Account account = as.retrieveAccountById(1);

        System.out.println(account);
    }

    @Test
    public void testRetrieveAll(){
        // 1.获取容器
        ApplicationContext ac = new ClassPathXmlApplicationContext("beans.xml");
        // 2.得到业务层对象
        IAccountService as = ac.getBean("accountService",IAccountService.class);
        // 3.执行方法
        List<Account> accounts = as.retrieveAllAccounts();

        for(Account account : accounts){
            System.out.println(account);
        }
    }

测试

testRetrieveAll()

testRetrieveOne()

testCreate()

testDelete()

testUpdate()

遇到的bug

执行testRetrieveAll()时

==BUG01==

警告: Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'accountService' defined in class path resource [beans.xml]: Error setting property values; nested exception is org.springframework.beans.NotWritablePropertyException: Invalid property 'accountDao ' of bean class [com.cc.service.impl.AccountServiceImpl]: Bean property 'accountDao ' is not writable or has an invalid setter method. Did you mean 'accountDao'?

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'accountService' defined in class path resource [beans.xml]: Error setting property values; nested exception is org.springframework.beans.NotWritablePropertyException: Invalid property 'accountDao ' of bean class [com.cc.service.impl.AccountServiceImpl]: Bean property 'accountDao ' is not writable or has an invalid setter method. Did you mean 'accountDao'?

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1650)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1357)
...

其中

Bean property 'accountDao ' is not writable or has an invalid setter method. Did you mean 'accountDao'?

发现多打了个空格

1
2
3
4
beans.xml中
<property name="accountDao " ref="accountDao"></property>
改为
<property name="accountDao" ref="accountDao"></property>

改过来之后

==BUG02==

警告: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@63fd9b65 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception: 
java.sql.SQLException: Unknown initial character set index '255' received from server. Initial client character set can be forced via the 'characterEncoding' property.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
...

似乎是编码问题

1
2
3
4
beans.xml中
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/groot"></property>
改为
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/groot?characterEncoding=utf8"></property>

改了之后

==BUG03==

警告: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@4879bf70 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception: 
java.sql.SQLException: Unknown system variable 'tx_isolation'
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)

查一下解决方啊

https://blog.csdn.net/always_younger/article/details/80421783

说是mysql-connector-java版本太低的原因

1
2
3
4
5
6
7
8
9
10
11
12
pom.xml中
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>5.1.6</version>
</dependency>
改为
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>8.0.16</version>
</dependency>

还有一点小问题

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.

把过时的类换掉

1
2
3
4
beans.xml中
<property name="driverClass" value="com.mysql.jdbc.Driver"></property>
改为
<property name="driverClass" value="com.mysql.cj.jdbc.Driver"></property>

就好了！

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

Process finished with exit code 0