Spring之JDBC与事务

最新推荐文章于 2022-04-10 20:27:20 发布

QPee是谁

最新推荐文章于 2022-04-10 20:27:20 发布

阅读量160

点赞数

分类专栏： # Spring 文章标签： spring jdbc

本文链接：https://blog.youkuaiyun.com/qq_36163918/article/details/111916352

版权

Spring 专栏收录该内容

8 篇文章

订阅专栏

JDBC与事务

JDBC：Java程序如何访问数据库的接口规范，数据库厂商负责实现；JdbcTemplate是Spring对JDBC的封装

1、JDBC

public class JDBCTest {
    public static final String URL = "jdbc:mysql://localhost:3306/local";
    public static final String USER = "root";
    public static final String PASSWORD = "123456";

    public static void main(String[] args) throws Exception {
        //1.加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");
        //2.获得数据库连接
        Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
        //3.操作数据库，实现增删改查
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT name, age FROM user");
        //4.获取数据
        while (rs.next()) {
            System.out.println(rs.getString("name") + " 年龄：" + rs.getInt("age"));
        }
    }
}

2、JdbcTemplate

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

        <!--加载资源文件-->
        <context:property-placeholder location="db.properties"/>

        <!--数据源-->
        <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
                <property name="driverClassName" value="${jdbc.driver}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
                <property name="url" value="${jdbc.url}"/>
        </bean>

        <!--配置JdbcTemplate-->
        <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
                <property name="dataSource" ref="dataSource"/>

        </bean>

</beans>

public class SpringJDBCTest {

    ClassPathXmlApplicationContext ac = new ClassPathXmlApplicationContext("jdbc.xml");
    JdbcTemplate template = ac.getBean("jdbcTemplate", JdbcTemplate.class);

    @Test
    public void testQuery() {
        String sql = "select * from user where id = ?";
        // 将列名（字段名或别名）和实体类属性名映射
        RowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);
        User user = template.queryForObject(sql, new Object[]{1}, rowMapper);
        System.out.println(user.toString());

        sql = "select count(*) from user";
        Integer count = template.queryForObject(sql, Integer.class);
        System.out.println("统计：" + count);

        sql = "select * from user;";
        List<User> list = template.query(sql, rowMapper);
        for (User u : list) {
            System.out.println(u.toString());
        }
    }

}

事务：利用AOP实现事务管理

1、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 http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/tool http://www.springframework.org/schema/tool/spring-tool.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">

    <context:component-scan base-package="com.jason.lee.transaction.xml"/>

    <!--加载资源文件-->
    <context:property-placeholder location="db.properties"/>

    <!--数据源-->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${jdbc.driver}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
        <property name="url" value="${jdbc.url}"/>
    </bean>

    <!--配置JdbcTemplate-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <!--配置事务管理器  事务管理需要DataSourceTransactionManager支持-->
    <bean id="dataSourceTransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <!--配置事务通知-->
    <tx:advice id="tx" transaction-manager="dataSourceTransactionManager">
        <tx:attributes>
            <!--在设置好的切入点表达式下再次进行事务设置-->
            <tx:method name="buyBook" propagation="REQUIRED" isolation="DEFAULT" timeout="3"/>
            <tx:method name="checkOut"/>
            <!-- * 通配符-->
            <tx:method name="select*"/>
        </tx:attributes>
    </tx:advice>

    <!--配置切入点表达式-->
    <aop:config>
        <aop:pointcut id="myPointcut" expression="execution(* com.jason.lee.transaction.xml.service.impl.*.*(..))"/>
        <!--通知器等同于切面：通知 + 切点-->
        <aop:advisor advice-ref="tx" pointcut-ref="myPointcut"/>
    </aop:config>

</beans>

2、注解方式

<?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"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/tool http://www.springframework.org/schema/tool/spring-tool.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">

    <context:component-scan base-package="com.jason.lee.transaction.annotation"/>

    <!--加载资源文件-->
    <context:property-placeholder location="db.properties"/>

    <!--数据源-->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${jdbc.driver}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
        <property name="url" value="${jdbc.url}"/>
    </bean>

    <!--配置JdbcTemplate-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <!--配置事务管理器-->
    <bean id="dataSourceTransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <!--开启事务注解-->
    <tx:annotation-driven transaction-manager="dataSourceTransactionManager"/>

</beans>

@Service
//@Transactional  对注解类下的所有方法生效
public class BookServiceImpl implements BookService {

    @Autowired
    private BookDao dao;

    /**
     * @param bid
     * @param uid
     * @Transactional 可以设置的属性：
     * propagation：A方法 和 B方法都有事务, A调用B时会将A中的事务传播给B, B方法对于事务的处理行为就是事务的传播行为
     *      Propagation.REQUIRED：必须使用调用者的事务 【默认】
     *      Propagation.REQUIRES_NEW：将调用者的事务挂起, 使用新的事务
     * isolation：
     *      读未提交, 脏读
     *      读已提交, 不可重复读
     *      可重复读  【MySQL默认隔离级别】
     *      串行化
     * timeout：强制回滚之前可以等待的时间
     * readOnly：指定当前事务中的操作是否只读, 数据库不加锁
     * rollbackFor|rollbackForClassName|noRollbackFor|noRollbackForClassName  回滚
     *
     */
    @Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT, timeout = 3, readOnly = false)  //仅对注解方法生效
    @Override
    public void buyBook(String bid, String uid) {
        Integer price = dao.selectPrice(bid);
        dao.updateSt(bid);
        dao.updateBalance(uid, price);
    }
}