JdbcTemplate使用

普鲁登斯

已于 2023-03-22 19:48:38 修改

阅读量132

点赞数

分类专栏： Spring 文章标签： java Powered by 金山文档

于 2023-03-16 14:18:10 首次发布

本文链接：https://blog.csdn.net/qq_46335546/article/details/129585006

版权

Spring 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

这是以mysql作为示范

maven 依赖

       <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.3.21</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.42</version>
            <scope>runtime</scope>
        </dependency>

Java内进行使用

        //1.配置数据源
        DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
        driverManagerDataSource.setDriverClassName("com.mysql.jdbc.Driver");
        driverManagerDataSource.setUrl("jdbc:mysql://localhost:3306/库名");
        driverManagerDataSource.setUsername("");
        driverManagerDataSource.setPassword("");
        //2.使用JDBCTemplate对数据源进行操作
        JdbcTemplate jdbcTemplate = new JdbcTemplate(driverManagerDataSource);

JdbcTemplate的操作

1.常用方法

      JdbcTemplate jdbcTemplate = new JdbcTemplate(driverManagerDataSource);
        jdbcTemplate.execute("");   //执行 数据定义语言DDL(表创建,表删除....)
        jdbcTemplate.update("");    //执行 数据操纵语言DML(增删改)
        jdbcTemplate.query("");     //执行 数据查询语言DQL(查)

jdbcTemplate.execute(sql);	执行数据定义语言DDL(表创建,表删除....)
jdbcTemplate.update(sql);	执行数据操纵语言DML(增删改)
jdbcTemplate.queryForObject(sql,rowMapper);	查一条内容,参数需要设置RowMapper返回映射
jdbcTemplate.query(sql,rowMapper);	查多条内容,参数需要设置RowMapper返回映射

2.RowMapper

结果映射:用于设置查询结果的储存类，使得查出来的表每行内容存入每个RowMapper指定的对象中

//BeanPropertyRowMapper是基于属性储存的RowMapper
BeanPropertyRowMapper<User> userRowMapper = new BeanPropertyRowMapper<>(User.class);
User user = jdbcTemplate.queryForObject("sql语句", userRowMapper);

3.普通事务处理

        //1.配置数据源
        DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
        driverManagerDataSource.setDriverClassName("com.mysql.jdbc.Driver");
        driverManagerDataSource.setUrl("jdbc:mysql://localhost:3306/库名");
        driverManagerDataSource.setUsername("");
        driverManagerDataSource.setPassword("");
        //2.通过数据源过的数据库Connection对象，操作Connection来commit，rollback；
        Connection connection = driverManagerDataSource.getConnection();
        connection.commit();
        connection.rollback();

4.基于注解的声明式事务

声明式事务所需依赖

 <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.3.21</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-expression</artifactId>
            <version>5.3.21</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>5.3.21</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>5.3.21</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aop</artifactId>
            <version>5.3.21</version>
        </dependency>
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjrt</artifactId>
            <version>1.9.1</version>
        </dependency>
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
            <version>1.9.1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.3.21</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>5.3.21</version>
        </dependency>
        <dependency>
            <groupId>commons-logging</groupId>
            <artifactId>commons-logging</artifactId>
            <version>1.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.42</version>
            <scope>runtime</scope>
        </dependency>
    </dependencies>

applicationContext.xml ioc配置文件所需配置

<?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"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
               http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
               http://www.springframework.org/schema/tx
               http://www.springframework.org/schema/tx/spring-tx-4.3.xsd
               http://www.springframework.org/schema/context
               http://www.springframework.org/schema/context/spring-context-4.3.xsd
               http://www.springframework.org/schema/aop
               http://www.springframework.org/schema/aop/spring-aop-4.3.xsd">
    <!-- 1.配置数据源 -->
    <bean id="dataSource"
          class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <!--数据库驱动 -->
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <!--连接数据库的url -->
        <property name="url" value="jdbc:mysql://localhost/spring"/>
        <!--连接数据库的用户名 -->
        <property name="username" value="root"/>
        <!--连接数据库的密码 -->
        <property name="password" value=""/>
    </bean>
    <!-- 2.配置JDBC模板 -->
    <bean id="jdbcTemplate"
          class="org.springframework.jdbc.core.JdbcTemplate">
        <!-- 默认必须使用数据源 -->
        <property name="dataSource" ref="dataSource"/>
    </bean>
    <!--3.定义id为accountDao的Bean -->
    <bean id="accountDaoImpl" class="com.sqllearn.dao.AccountDaoImpl">
        <!-- 将jdbcTemplate注入到AccountDao实例中 -->
        <property name="jdbcTemplate" ref="jdbcTemplate"/>
    </bean>
    <!-- 4.事务管理器，依赖于数据源 -->
    <bean id="transactionManager" class=
            "org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    <!-- 5.注册事务管理器驱动 -->
    <tx:annotation-driven transaction-manager="transactionManager"/>
</beans>

原理：使用了@Transactional注解的方法，会在其异常通知处插入事务回滚方法

    @Transactional(propagation = Propagation.REQUIRED,isolation = Isolation.DEFAULT,readOnly = false)
    public void transfer(String outUser,String inUser,double money){
        //1.收钱方收钱
        jdbcTemplate.update("update account set balance = balance + ? where username = ?",money,inUser);
        //2.发钱方扣钱
        jdbcTemplate.update("update account set balance = balance - ? where username = ?",money,outUser);
    }