Spring的数据库开发及事务管理

发表于 更新于 分类于 阅读次数： Valine：

一、创建AccountDao接口

先创建接口，实现增加、更新、删除、查询以及事务管理中模拟交易的方法：

1
2
3
4
5
6
7
8
9
10
11
12
13
package springjdbc;

import java.util.List;

public interface AccountDao {
    public int addAccount(Account account);
    public int updateAccount(Account account);
    public int deleteAccount(int id);
    public Account findAccountById(int id);
    public List<Account>  findAllAcount();
    public void transfer(String outUser,String inUser,Double money);
}

二、创建Account类

创建Account类，账户类实现setter/getter方法

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
package springjdbc;

public class Account {
    private Integer id;
    private String username;
    private Double balance;

    public Integer getId(){
        return id;
    }
    public void setId(Integer id) {
        this.id=id;
    }
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username=username;
    }
    public  Double getBalance() {
        return balance;
    }
    public void setBalance(Double balance) {
        this.balance=balance;
    }

    public String toString() {
        return "Account [id=" + id +"," + "username=" + username + ", balance=" + balance + "]";
    }
}

三、创建实现类AccountDaoImpl

创建实现类并覆盖接口中的方法

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
package springjdbc;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import  org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.util.List;

public class AccountDaoImpl implements AccountDao {
    private JdbcTemplate jdbcTemplate;
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate=jdbcTemplate;
    }

    @Override
    public int addAccount(Account account) {
        String sql="insert into account(username,balance) value(?,?)";
        Object[] obj=new Object[] {
                account.getUsername(),
                account.getBalance()
        };
        int num=this.jdbcTemplate.update(sql,obj);
        return num;
    }

    @Override
    public int updateAccount(Account account) {
        String sql="update account set username=?,balance=? where id =?";
        Object[] params=new Object[] {
                account.getUsername(),
                account.getBalance(),
                account.getId()
        };
        int num=this.jdbcTemplate.update(sql,params);
        return num;
    }

    @Override
    public int deleteAccount(int id) {
        String sql="delete from account where id=?";
        int num=jdbcTemplate.update(sql,id);
        return num;
    }

    @Override
    public Account findAccountById(int id) {
        String sql="select * from account where id=?";
        RowMapper<Account> rowMapper=new BeanPropertyRowMapper<Account>(Account.class);
        return this.jdbcTemplate.queryForObject(sql,rowMapper,id);
    }

    @Override
    public List<Account> findAllAcount() {
        String sql="select * from account";
        RowMapper<Account> rowMapper=new BeanPropertyRowMapper<Account>(Account.class);
        return this.jdbcTemplate.query(sql,rowMapper);
    }

    @Override
    public void transfer(String outUser,String inUser,Double money) {
        this.jdbcTemplate.update("update account set balance = balance +? where username= ?",money,inUser);
        int i=1/0;
        this.jdbcTemplate.update("update account set balance = balance-? where username= ?",money,outUser);
    }
}

四、配置文件实现装载数据库以及模拟事务处理

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

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

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

    <bean id="accountDao" class="springjdbc.AccountDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate" />
    </bean>

    <bean id="transacitionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="datasource" />
    </bean>

    <tx:advice id="txAdvice" transaction-manager="transacitionManager">
        <tx:attributes>
            <tx:method name="*" propagation="REQUIRED" isolation="DEFAULT" read-only="false" />
        </tx:attributes>
    </tx:advice>

    <aop:config>
        <aop:pointcut expression="execution(* springjdbc.*.*(..))" id="txPointCut" />
        <aop:advisor advice-ref="txAdvice" pointcut-ref="txPointCut" />
    </aop:config>
</beans>

五、创建测试类测试Jdbc几种操作

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
76
77
package springjdbc;

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

public class JdbcTemplateTest {
    public static void main(String[] args) {
        ApplicationContext applicationContext=new ClassPathXmlApplicationContext("/springjdbc/applicationContext.xml");
        JdbcTemplate jdbcTemplate=(JdbcTemplate) applicationContext.getBean("jdbcTemplate");
        jdbcTemplate.execute("create table account(" +
                "id int primary key auto_increment," +
                "username varchar(50)," +
                "balance double)");
        System.out.println("账户表格account创建成功");
    }

    @Test
    public void addAccountTest() {
        ApplicationContext applicationContext=new ClassPathXmlApplicationContext("/springjdbc/applicationContext.xml");
        AccountDao accountDao=(AccountDao) applicationContext.getBean("accountDao");
        Account account=new Account();
        account.setUsername("XWY");
        account.setBalance(999.99);
        int num=accountDao.addAccount(account);
        if(num>0) {
            System.out.println("成功插入了"+num+"条数据！");
        } else {
            System.out.println("插入失败！！");
        }
    }
    @Test
    public void updateAccountTest() {
        ApplicationContext applicationContext=new ClassPathXmlApplicationContext("/springjdbc/applicationContext.xml");
        AccountDao accountDao=(AccountDao) applicationContext.getBean("accountDao");
        Account account=new Account();
        account.setId(1);
        account.setUsername("当家XZP");
        account.setBalance(99999.9999);
        int num=accountDao.updateAccount(account);
        if(num>0) {
            System.out.println("成功修改了"+num+"条数据！");
        } else {
            System.out.println("修改失败！！");
        }
    }
    @Test
    public void deleteAccount() {
        ApplicationContext applicationContext=new ClassPathXmlApplicationContext("/springjdbc/applicationContext.xml");
        AccountDao accountDao=(AccountDao) applicationContext.getBean("accountDao");
        int num=accountDao.deleteAccount(1);
        if(num>0) {
            System.out.println("成功删除了"+num+"条数据！");
        } else {
            System.out.println("删除失败！！");
        }
    }
    @Test
    public void findAccountByIdTest() {
        ApplicationContext applicationContext=new ClassPathXmlApplicationContext("/springjdbc/applicationContext.xml");
        AccountDao accountDao=(AccountDao) applicationContext.getBean("accountDao");
        Account account=accountDao.findAccountById(2);
        System.out.println(account);
    }
    @Test
    public void finddAllAccountTest() {
        ApplicationContext applicationContext=new ClassPathXmlApplicationContext("/springjdbc/applicationContext.xml");
        AccountDao accountDao=(AccountDao) applicationContext.getBean("accountDao");
        List<Account> account=accountDao.findAllAcount();
        for (Account act : account) {
            System.out.println(act);
        }
    }
}

六、创建测试类测试Spring的事务管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
package springjdbc;

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class TransactionTest {
    @Test
    public void xmlTest() {
        ApplicationContext applicationContext=new ClassPathXmlApplicationContext("/springjdbc/applicationContext.xml");
        AccountDao accountDao=(AccountDao) applicationContext.getBean("accountDao");
        accountDao.transfer("XZP","XWY",520.999);
        System.out.println("转账成功！");
    }
}