[华为云在线课程][Spring入门][JdbcTemplate和事务简介][三][学习笔记]

举报
John2021 发表于 2022/06/26 06:07:59 2022/06/26
【摘要】 1.JdbcTemplateJdbcTemplate是Spring利用AOP封装的JDBC操作工具。 1.1.准备工作首先要添加jdbc相关的依赖<dependencies> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId>...

1.JdbcTemplate

JdbcTemplate是Spring利用AOP封装的JDBC操作工具。

1.1.准备工作

首先要添加jdbc相关的依赖

<dependencies>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>5.3.20</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>5.3.20</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.29</version>
    </dependency>
</dependencies>

然后创建数据库testjdbctemplate

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `address` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci"

INSERT INTO `user`(username,address)
VALUES ('hello','beijing'),('world','shanghai'),('nihao','guangzhou'),('a','shenzhen');

完成数据库创建后新建一个实体类User

package org.jdbctemplate;

public class User {
    private Integer id;
    private String username;
    private String address;

    public User() {
    }

    public User(Integer id, String username, String address) {
        this.id = id;
        this.username = username;
        this.address = address;
    }

    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 String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

1.2.Java配置

编写一个Java配置类，在配置类中配置JdbcTemplate

package org.jdbctemplate;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;

@Configuration
public class JdbcConfig {
    /*
    * 这个是数据库配置信息的Bean
    * */
    @Bean
    DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUsername("root");
        dataSource.setPassword("1234");
        dataSource.setUrl("jdbc:mysql:///test01");
        return dataSource;
    }
    
    /*
    * 这个是JdbcTemplate的Bean
    * 只需要new一个Bean出来，配置DataSource就可以了
    * */
    @Bean
    JdbcTemplate jdbcTemplate() {
        return new JdbcTemplate(dataSource());
    }
}

编写启动类

package org.jdbctemplate;

import org.junit.Before;
import org.junit.Test;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;


public class Main {
    private JdbcTemplate jdbcTemplate;

    @Before
    public void before() {
        AnnotationConfigApplicationContext applicationContext = new AnnotationConfigApplicationContext(JdbcConfig.class);
        jdbcTemplate = applicationContext.getBean(JdbcTemplate.class);
    }

    @Test
    public void select() {
        User user = jdbcTemplate.queryForObject("select * from test01.user where username=?",
                new BeanPropertyRowMapper<User>(User.class),"hello");
        System.out.println(user);
    }

    @Test
    public void insert() {
        jdbcTemplate.update("insert into test01.user(username, address) values (?,?);",
                "new01", "diqiu");
    }

    @Test
    public void update() {
        jdbcTemplate.update("update test01.user set username=? where id=?",
                "new001", 5);
    }

    @Test
    public void delete() {
        jdbcTemplate.update("delete from test01.user where id=?",
                5);
    }
}

如果是使用了BeanPropertyRowMapper来查询，要求查出来的字段必须和Bean的属性名一一对应。如果不是就需要自定义RowMapper或者给查询的字段取别名。
给查询出的列取别名

@Test
public void selectAlias() {
    User user = jdbcTemplate.queryForObject("select id,username as name,address from test01.user where id=?",
            new BeanPropertyRowMapper<>(User.class), 1);
    System.out.println(user); //User{id=1, username='null', address='beijing'}
}

自定义RowMapper

@Test
public void rowMapper() {
    User user = jdbcTemplate.queryForObject("select * from test01.user where id=?",
            new RowMapper<User>() {
                @Override
                public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                    int id = rs.getInt("id");
                    String username = rs.getString("username");
                    String address = rs.getString("address");
                    User u = new User();
                    u.setId(id);
                    u.setUsername(username);
                    u.setAddress(address);
                    return u;
                }
            }, 1);
    System.out.println(user); //User{id=1, username='hello', address='beijing'}
}

如果要查询多条记录，代码如下

@Test
public void selectMultiple() {
    List<User> userList = jdbcTemplate.query("select * from test01.user", new BeanPropertyRowMapper<>(User.class));
    System.out.println(userList);
    // [User{id=1, username='hello', address='beijing'},
    // User{id=2, username='world', address='shanghai'}, 
    // User{id=3, username='nihao', address='guangzhou'}, 
    // User{id=4, username='a', address='shenzhen'}]
}

1.3.XML配置

也可以通过Spring配置文件来实现配置。通过XML文件实现只是提供JdbcTemplate实例，JdbcConfig被Spring配置文件替代。

<!--    JdbcTemplateXML-->
<bean class="org.springframework.jdbc.datasource.DriverManagerDataSource" id="dataSource">
    <property name="username" value="root"/>
    <property name="password" value="1234"/>
    <property name="url" value="jdbc:mysql:///test01?serverTimezone=Asia/Shanghai"/>
    <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
</bean>
<bean class="org.springframework.jdbc.core.JdbcTemplate" id="jdbcTemplate">
    <property name="dataSource" ref="dataSource"/>
</bean>

编写启动类

package org.jdbctemplate;

import org.junit.Before;
import org.junit.Test;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

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

public class MainXML {
    private JdbcTemplate jdbcTemplate;
    @Before
    public void before() {
        ClassPathXmlApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationcontext.xml");
        jdbcTemplate = applicationContext.getBean(JdbcTemplate.class);
    }

    @Test
    public void insert() {
        jdbcTemplate.update("insert into test01.user(username, address) values (?,?);",
                "new001", "001address");
    }

    @Test
    public void update() {
        jdbcTemplate.update("update test01.user set username=? where id=?",
                "new002", 6);
    }

    @Test
    public void delete() {
        jdbcTemplate.update("delete from test01.user where id=?", 6);
    }

    @Test
    public void select() {
        User user = jdbcTemplate.queryForObject("select * from test01.user where id=?",
                new BeanPropertyRowMapper<>(User.class), 1);
        System.out.println(user); //User{id=1, username='hello', address='beijing'}
    }

    @Test
    public void selectAll() {
        List<User> userList = jdbcTemplate.query("select * from test01.user",
                new BeanPropertyRowMapper<>(User.class));
        System.out.println(userList);
        // [User{id=1, username='hello', address='beijing'},
        // User{id=2, username='world', address='shanghai'},
        // User{id=3, username='nihao', address='guangzhou'},
        // User{id=4, username='a', address='shenzhen'}]
    }

    @Test
    public void select2() {
        User user = jdbcTemplate.queryForObject("select id,username as name,address from test01.user where id=?",
                new BeanPropertyRowMapper<>(User.class), 1);
        System.out.println(user); //User{id=1, username='null', address='beijing'}
    }

    @Test
    public void select3() {
        User user = jdbcTemplate.queryForObject("select * from test01.user where id=?",
                new RowMapper<User>() {
                    @Override
                    public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                        int id = rs.getInt("id");
                        String username = rs.getString("username");
                        String address = rs.getString("address");
                        User user = new User();
                        user.setId(id);
                        user.setUsername(username);
                        user.setAddress(address);
                        return user;
                    }
                },1);
        System.out.println(user);
    }
}

2.事务

Spring中的事务主要利用AOP来简化事务的配置，可以通过XML配置，也可以通过Java配置。下面通过一个经典的场景-转账，来演示Spring中的事务配置。

create table `account`(
    `id` int(11) not null auto_increment,
    `username` varchar(255) default null,
    `money` int(11) default null,
    primary key (`id`)
)engine=InnoDB auto_increment=3 default charset=utf8mb4 collate=utf8mb4_general_ci;

insert into `account`(id, username, money)
values (1, 'zhangsan', 1000),
       (2, 'lisi', 1000);

package org.jdbctemplate;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;

@Configuration
public class JdbcConfig {
    /*
    * 这个是数据库配置信息的Bean
    * */
    @Bean
    DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUsername("root");
        dataSource.setPassword("1234");
        dataSource.setUrl("jdbc:mysql:///test01");
        return dataSource;
    }

    /*
    * 这个是JdbcTemplate的Bean
    * 只需要new一个Bean出来，配置DataSource就可以了
    * */
    @Bean
    JdbcTemplate jdbcTemplate() {
        return new JdbcTemplate(dataSource());
    }
}

<!--    JdbcTemplateXML-->
<bean class="org.springframework.jdbc.datasource.DriverManagerDataSource" id="dataSource">
    <property name="username" value="root"/>
    <property name="password" value="1234"/>
    <property name="url" value="jdbc:mysql:///test01?serverTimezone=Asia/Shanghai"/>
    <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
</bean>
<bean class="org.springframework.jdbc.core.JdbcTemplate" id="jdbcTemplate">
    <property name="dataSource" ref="dataSource"/>
</bean>

编写转账方法

package org.transaction;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class UserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void addMoney(String username, Integer money) {
        jdbcTemplate.update("update test01.account set money=money+? where username=?",
                money, username);
    }

    public void minmoney(String username, Integer money) {
        jdbcTemplate.update("update test01.account set money=money-? where username=?",
                money, username);
    }
}

package org.transaction;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class UserService {
    @Autowired
    UserDao userDao;

    public void updateMoney() {
        userDao.addMoney("zhangsan", 200);
        int i = 1 / 0;
        userDao.minmoney("list", 200);
    }
}

最后开启自动化扫描

<!--    JdbcTemplateXML-->
<bean class="org.springframework.jdbc.datasource.DriverManagerDataSource" id="dataSource">
    <property name="username" value="root"/>
    <property name="password" value="1234"/>
    <property name="url" value="jdbc:mysql:///test01?serverTimezone=Asia/Shanghai"/>
    <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
</bean>
<bean class="org.springframework.jdbc.core.JdbcTemplate" id="jdbcTemplate">
    <property name="dataSource" ref="dataSource"/>
</bean>
<!--    开启自动扫描-->
<context:component-scan base-package="org.transaction"/>

package org.transaction;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;

@Configuration
@ComponentScan(basePackages = "org.transaction")
public class JdbcConfig {
    /*
    * 这个是数据库配置信息的Bean
    * */
    @Bean
    DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUsername("root");
        dataSource.setPassword("1234");
        dataSource.setUrl("jdbc:mysql:///test01");
        return dataSource;
    }

    /*
    * 这个是JdbcTemplate的Bean
    * 只需要new一个Bean出来，配置DataSource就可以了
    * */
    @Bean
    JdbcTemplate jdbcTemplate() {
        return new JdbcTemplate(dataSource());
    }
}

2.1.XML配置

XML配置事务有三个步骤：1，配置TransactionManager。2，配置事务要处理的方法。3，配置AOP。
配置TransactionManager

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

配置事务要处理的方法

<tx:advice id="txAdvice" transaction-manager="transactionManager">
    <tx:attributes>
        <tx:method name="update*"/>
        <tx:method name="insert*"/>
        <tx:method name="add*"/>
        <tx:method name="delete*"/>
    </tx:attributes>
</tx:advice>
<!-- 
    一旦配置了方法名称规则之后，service中的方法一定要按照名称规则命名，不然事务配置不会生效。
 -->

配置AOP

<aop:config>
    <aop:pointcut id="pc1" expression="execution(* org.transaction.service.*.*(..))"/>
    <aop:advisor advice-ref="txAdvice" pointcut-ref="pc1"/>
</aop:config>

package org.transaction;

import org.junit.Before;
import org.junit.Test;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.transaction.service.UserService;

public class Main {
    private JdbcTemplate jdbcTemplate;
    private UserService userService;

    @Before
    public void before() {
        ClassPathXmlApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationcontext.xml");
        jdbcTemplate = applicationContext.getBean(JdbcTemplate.class);
        userService = applicationContext.getBean(UserService.class);
    }

    @Test
    public void test1() {
        userService.updateMoney();
    }
}

2.2.Java配置

Spring配置文件中的配置

<!--    Java配置事务-->
<!--    配置TransactionManager-->
<bean class="org.springframework.jdbc.datasource.DataSourceTransactionManager" id="transactionManager"/>
<tx:annotation-driven transaction-manager="transactionManager"/>

这行配置可以代替<tx:advice>和<aop:config>
然后在UserService上添加事务注解@Transactional注解，表示在某个方法开启事务，如果把注解放到类上就是为所有方法开启事务

package org.transaction.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.transaction.dao.UserDao;

@Service
public class UserService {
    @Autowired
    UserDao userDao;

    @Transactional
    public void updateMoney() {
        userDao.addMoney("zhangsan", 200);
        int i = 1 / 0;
        userDao.minmoney("list", 200);
    }
}

package org.transaction;

import org.junit.Before;
import org.junit.Test;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.transaction.service.UserService;

public class Main {
    private JdbcTemplate jdbcTemplate;
    private UserService userService;

    @Before
    public void before() {
        //ClassPathXmlApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationcontext.xml");
        AnnotationConfigApplicationContext applicationContext = new AnnotationConfigApplicationContext(JdbcConfig.class);
        jdbcTemplate = applicationContext.getBean(JdbcTemplate.class);
        userService = applicationContext.getBean(UserService.class);
    }

    @Test
    public void test1() {
        userService.updateMoney();
    }
}
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
Spring
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入