JdbcTemplate 事务

 

      使用JdbcTemplate实现转账操作。

一）添加依赖 POM.xml

<?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.company</groupId>
    <artifactId>JdbcTemplate_EasyUse</artifactId>
    <version>1.0-SNAPSHOT</version>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <target>1.8</target>
                    <source>1.8</source>
                    <encoding>utf-8</encoding>
                </configuration>
            </plugin>
        </plugins>
    </build>
    <dependencies>
        <!--測試包-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <!--sql server 驅動包-->
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <version>7.2.2.jre8</version>
        </dependency>
        <!--Spring 框架容器包-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.2.0.RELEASE</version>
        </dependency>
        <!--Spring 自帶的測試-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>5.2.0.RELEASE</version>
        </dependency>
        <!--JdbcTemplate 包-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.2.0.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aop</artifactId>
            <version>5.2.0.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>5.2.0.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
            <version>1.9.4</version>
        </dependency>
    </dependencies>
</project>

二）配置数据库配置文件 db.properties

jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc.url=jdbc:sqlserver://localhost:1433;DatabaseName=testdb
jdbc.username=sa
jdbc.password=123.abc

三）配置Spring 配置文件 applicationContext.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"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:tx="http://www.springframework.org/schema/tx" 
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans.xsd
       http://www.springframework.org/schema/context
       https://www.springframework.org/schema/context/spring-context.xsd
       http://www.springframework.org/schema/tx
       http://www.springframework.org/schema/tx/spring-tx.xsd 
       http://www.springframework.org/schema/aop 
       https://www.springframework.org/schema/aop/spring-aop.xsd
">

    <!--加載數據庫配置文件，讀取文件中數據-->
    <context:property-placeholder location="classpath:db.properties"></context:property-placeholder>

    <!--開啟自動掃描-->
    <context:component-scan base-package="com.company"/>

    <!--
     Spring的事務管理器PlatformTransactionManager接口實現類
     事務管理依賴連接對象
     注入數據源
    -->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <constructor-arg name="dataSource" ref="dataSource"></constructor-arg>
    </bean>

    <aop:aspectj-autoproxy/>

    <!--
    配置事務的屬性 advice通知 橫切邏輯
    切入是事務管理
    -->
    <tx:advice id="adviceId" transaction-manager="transactionManager">
        <tx:attributes>
            <!--
            方法：給哪個方法添加事務 name方法名字
            *所有方法使用
            事務：只讀（查詢） 非只讀（增刪改）
            name="*" read-only="false" 所有方法的事務都是非只讀
            isolation:事務隔離級別 默認級別，不寫就是默認
            REQUIRED:你無事務，我創建，你有事務，我融合。非只讀
            propagation：傳播行為
            rollback-for:屬性指的是，遇到哪些異常回滾，寫異常類名，全名，多個之間逗號
            no-rollback-for:哪些異常不回滾

            -->
            <tx:method name="*" read-only="false" propagation="REQUIRED" timeout="-1" ></tx:method>
            <!--
            配置只讀事務
            查詢方法：某個開頭 query get select
            -->
            <tx:method name="query*" read-only="true" propagation="SUPPORTS"></tx:method>
        </tx:attributes>
    </tx:advice>

    <!--事務配置-->
    <aop:config>
        <!--
        事務通知增強， advice-ref配置事務屬性的id
        事務通知，基本是固定方位用法，環繞通知
        -->
        <aop:advisor advice-ref="adviceId" pointcut="execution(* com.company.service.AccountServiceImpl.*(..))"></aop:advisor>
    </aop:config>


    <!--配置DriverManagerDataSource Bean，并注入屬性值-->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="${jdbc.driver}"></property>
        <property name="url" value="${jdbc.url}"></property>
        <property name="username" value="${jdbc.username}"></property>
        <property name="password" value="${jdbc.password}"></property>
    </bean>

    <!--配置JdbcTemplate Bean，并注入dataSource-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <constructor-arg name="dataSource" ref="dataSource"></constructor-arg>
    </bean>
</beans>

四）逻辑代码

pojo目录下 Account.java

public class Account {
    private int id;
    private String name;
    private float money;

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

    public int getId() {
        return id;
    }

    public void setId(int 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;
    }
}

dao目录下

AccountDao.java

public interface AccountDao {
    void updateAccount(Account account) throws SQLException;
    Account queryAccountByName(String name) throws SQLException;
}

AccountDaoImpl.java

@Repository("accountDao")
public class AccountDaoImpl implements AccountDao {

    @Autowired
    @Qualifier("jdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @Override
    public void updateAccount(Account account) throws SQLException {
         String sql="update account set name=? , money=? where id=?";
         Object[] params={account.getName(),account.getMoney(),account.getId()};
         jdbcTemplate.update(sql,params);
    }

    @Override
    public Account queryAccountByName(String name) throws SQLException {
        String sql="select * from account where name=?";
        Object[] params={name};
        Account account = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Account.class), params);
        return account;
    }
}

service目录下

AccountService.java

public interface AccountService {
    void updateAccount(Account account);
    void transfer(String fromAccount,String toAccount,float money);
}

AccountServiceImpl.java

@Service("accountService")
public class AccountServiceImpl implements AccountService {

    @Autowired
    @Qualifier("accountDao")
    private AccountDao accountDao;

    @Override
    public void updateAccount(Account account) {
        try {
            accountDao.updateAccount(account);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void transfer(String fromAccount, String toAccount, float money) {

        try {
            Account accountFrom = accountDao.queryAccountByName(fromAccount);
            Account accountTo = accountDao.queryAccountByName(toAccount);
            System.out.println(accountFrom);
            System.out.println(accountTo);
            accountFrom.setMoney(accountFrom.getMoney()-money);
            accountTo.setMoney(accountTo.getMoney()+money);
            accountDao.updateAccount(accountFrom);
            //int i=1/0;
            accountDao.updateAccount(accountTo);
        } catch (SQLException e) {
            e.printStackTrace();  
        }
    }
}

五）测试

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:applicationContext.xml"})
@ComponentScan("com.company")
public class JdbcTemplateTransaction {
    @Autowired
    @Qualifier("accountService")
    private AccountService accountService;

    @Test
    public void transfer(){
        accountService.transfer("march","feb",500f);
    }
}

 

重点：通过AOP思想将事务逻辑横切到业务逻辑中，重点在Spring配置文件中。