技术教程 编程教程 springboot(二)整合mybatis，多数据源和事务管理 正文

springboot(二)整合mybatis，多数据源和事务管理

2023-06-15,,

 -- 1.整合mybatis -- 2.整合多数据源 -- 3. 整合事务
代码地址：https://github.com/showkawa/springBoot_2017/tree/master/spb-demo

如果小伙伴是本地测试mysql,建议使用docker容器安装mysql,毕竟docker这么火，而且相比自己本地手动安装mysql,docker简便很多，三分钟掌握docker基本基本指令，这里面最后有讲到mysql安装。 

1.整合mybatis 

1.1 添加集成mybatis的依赖

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.1</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

<!-- 分页插件 -->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.2.10</version>
</dependency>

1.2 数据源的配置

1.2.1 applacation.yml的配置

#主配置开发环境
spring:
  profiles:
    active: dev
    #时间格式化
  ackson:
    date-format: yyyy-MM-dd HH:mm:ss
    #时区设置
  jackson:
    time-zone: Asia/Chongqing

#配置springboot默认的context-path
server:
  port: 8002
  servlet:
    context-path: /kawa
#日志设置
logging:
  config: classpath:log4j2-dev.xml

1.2.2 application-dev.yml的配置

#数据库配置
spring:
  datasource:
    url: jdbc:mysql://o186928x99.imwork.net:12921/brian?useUnicode=true&characterEncoding=utf-8&useSSL=false
    username: root
    password: 123456
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver

#服务名
  application:
    name: brian-query-service

1.3.添加数据源此处使用阿里的德鲁克来管理

1.3.1 添加德鲁克依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.8</version>
</dependency>

1.3.2 注入druid到ioc Bean容器中



package com.kawa.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Configuration
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource druid(){
        return new DruidDataSource();
    }
}


1.4.1 初始化sql脚本




1.4.2 创建实体类


public class User {

    private Long id;

    private String username;

    private String password;

    private String phone;

    private String email;

    private Date created;

    private Date updated;

  //getter&setter

}


1.4.3 Dao层


dao记得加上@Mapper注解，不然会报错，开发时dao太多了，每一个都要加上@Mapper注解太麻烦了。也可以直接在启动类加上@MapperScan指定好扫包包路径，就不用在dao上指定@Mapper了


package com.kawa.dao;

import com.kawa.pojo.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;
import java.util.Map;

/**
 * 
 * @author huangliang
 *
 */
//@Mapper
public interface UserDao {

   @Insert("INSERT INTO user (id,username,password,phone,email,created,updated) VALUES (#{id},#{username},#{password},#{phone},#{email},#{created},#{updated})")
   Integer addUser(User user);

   Boolean updateUser(User user);

   Integer deleteUserById(Long id);

   User queryUserById(Long id);

   List<User> queryUserList(Map<String, Object> params);
}


1.5 Service和controller


1.5.1 service 


package com.kawa.sercice.impl;

import com.github.pagehelper.PageHelper;
import com.kawa.dao.UserDao;
import com.kawa.pojo.User;
import com.kawa.sercice.UserService;
import org.springframework.cache.annotation.CacheEvict;
import org.springframework.cache.annotation.CachePut;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.time.LocalDate;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.UUID;

@Service
public class UserServiceImpl implements UserService {

   @Resource
   UserDao userDao;

   @Override
   public List<User> queryUserList(Map<String, Object> params) {
      PageHelper.startPage(Integer.parseInt(params.get("page").toString()) , Integer.parseInt(params.get("rows").toString()));
      return userDao.queryUserList(params);
   }

   @Override
   public Integer insertUser(User user) {
      user.setPassword("10086");
      user.setCreated(new Date());
      user.setUpdated(user.getCreated());
      return userDao.addUser(user);
   }

   @CachePut(value = "user",key = "#result.id")
   @Override
   public Boolean updateUser(User user) {
      return userDao.updateUser(user);
   }

   @CacheEvict(value = "user",key = "#id")
   @Override
   public Integer deleteUserById(Long id) {
      return userDao.deleteUserById(id);
   }

   @Cacheable(value = {"user"})
   @Override
   public User queryUserById(Long id) {
      return userDao.queryUserById(id);
   }
}


1.5.2 controller


package com.controller;

import com.github.pagehelper.PageInfo;
import com.kawa.job.AsyncService;
import com.kawa.pojo.User;
import com.kawa.pojo.UserQuery;
import com.kawa.pojo.UserQueryList;
import com.kawa.sercice.BrianService;
import com.kawa.sercice.UserService;
import com.kawa.sercice.impl.UserServiceImpl;
import com.kawa2.sercice.UserService02;
import com.kawa2.sercice.impl.UserServiceImpl02;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ExecutionException;

/**
 * 
 * @author Administrator
 *
 */
@RestController
public class UserController {

   @Autowired
   UserServiceImpl userService;

   @Autowired
   AsyncService asyncService;

   @Autowired
   BrianService brianService;

   @RequestMapping(path = "/getAll", method = RequestMethod.POST)
   public ResponseEntity<UserQueryList> queryUsers(HttpServletRequest request, @RequestBody UserQuery userQuery) {
      if (userQuery.getPage() == null) {
         //第一页从1开始而不是0
         userQuery.setPage(1);
      }
      if (userQuery.getRows() == null) {
         userQuery.setRows(10);
      }
      Map<String, Object> map = new HashMap<>();
      map.put("page", userQuery.getPage());
      map.put("rows", userQuery.getRows());
      map.put("username", userQuery.getUsername());
      map.put("phone", userQuery.getPhone());
      map.put("email", userQuery.getEmail());
      List<User> queryAllUsers = userService.queryUserList(map);
      PageInfo<User> pageInfo = new PageInfo<>(queryAllUsers);
      UserQueryList queryList = new UserQueryList();
      queryList.setUsers(queryAllUsers);
      queryList.setTotlePage(pageInfo.getPages());
      Integer total = new Long(pageInfo.getTotal()).intValue();
      queryList.setTotleRecords(total);
      return new ResponseEntity<>(queryList, HttpStatus.OK);
   }

   @RequestMapping(path = "/addUser", method = RequestMethod.POST)
   public ResponseEntity addUsers(@RequestBody User user) {
      userService.insertUser(user);
      return new ResponseEntity(user, HttpStatus.OK);
   }

   @RequestMapping(path = "/updateUser", method = RequestMethod.POST)
   public ResponseEntity updateUser(@RequestBody User user) {
      userService.updateUser(user);
      return new ResponseEntity(user, HttpStatus.OK);
   }

   @RequestMapping(path = "/deleteUserById/{id}", method = RequestMethod.GET)
   public ResponseEntity deleteUserById(@PathVariable Long id) {
      userService.deleteUserById(id);
      return new ResponseEntity(id, HttpStatus.OK);
   }

   @RequestMapping(path = "/queryUserById/{id}", method = RequestMethod.GET)
   public ResponseEntity queryUserById(@PathVariable Long id) {
      User user = userService.queryUserById(id);
      return new ResponseEntity(user, HttpStatus.OK);
   }

   @GetMapping("/async")
   public String testJob() {
      asyncService.brianAsync();
      return "Good job";
   }

   @GetMapping("/loop/sendMsg")
   public void loopSendMsg() {
      try {
         brianService.sendMessageByThredPool();
      } catch (ExecutionException e) {
         e.printStackTrace();
      } catch (InterruptedException e) {
         e.printStackTrace();
      }
   }

   @PostMapping("/loop/sendMsg/userInfo")
   public ResponseEntity addUserInfo2MQ(@RequestBody User user) throws ExecutionException, InterruptedException {
      brianService.sendMessageByThredPool(user);
      return new ResponseEntity(user, HttpStatus.OK);

   }
}


1.6.演示结果




2.整合多数据源 


整合多数据源的时候，原来的yml的数据源配置需要重修修改为不同的数据源配置,原理根据包名，加载不同的数据源


2.1 yml文件修改多数据源配置


#多数据源配置

spring:

  datasource:

    kawa: #数据源1

     # url: jdbc:mysql://o186928x99.imwsork.net:12921/brian?useUnicode=true&characterEncoding=utf-8&useSSL=false

      jdbc-url: jdbc:mysql://xuduocloud.qicp.vip:14110/brian?useUnicode=true&characterEncoding=utf-8&useSSL=false

      username: root

      password: 123456

      driver-class-name: com.mysql.cj.jdbc.Driver

    kawa02: #数据源2

      jdbc-url: jdbc:mysql://remotemysql.com:3306/khegvUiO4eh?useUnicode=true&characterEncoding=utf-8&useSSL=false

      username: khegvUiO4eh

      password: BGAAee478r3

      driver-class-name: com.mysql.cj.jdbc.Driver


2.2 项目结构根据多数据源重新划分包名




2.3 配置多数据源




package com.config;

import org.apache.ibatis.session.SqlSessionFactory;

import org.mybatis.spring.SqlSessionFactoryBean;

import org.mybatis.spring.SqlSessionTemplate;

import org.mybatis.spring.annotation.MapperScan;

import org.springframework.beans.factory.annotation.Qualifier;

import org.springframework.boot.context.properties.ConfigurationProperties;

import org.springframework.boot.jdbc.DataSourceBuilder;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration

@MapperScan(basePackages = "com.kawa", sqlSessionFactoryRef = "kawaSqlSessionFactory")
//指定了@MapperScan报扫描，dao层接口就不用加@Mapper注解了

public class DataSourceConfig {

    /**

     * 配置数据源

     * @return

     */

    @Bean(name="kawaDataSource")

    @ConfigurationProperties(prefix = "spring.datasource.kawa")

    public DataSource kawaDataSource(){

        return DataSourceBuilder.create().build();

    }

    /**

     * sql会话工厂

     * @param dataSource

     * @return

     * @throws Exception

     */

    @Bean(name = "kawaSqlSessionFactory")

    public SqlSessionFactory testSqlSessionFactory(@Qualifier("kawaDataSource") DataSource dataSource)

            throws Exception {

        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();

        bean.setDataSource(dataSource);

        return bean.getObject();

    }

    /**

     * 事务管理

     * @param dataSource

     * @return

     */

    @Bean(name = "kawaTransactionManager")

    public DataSourceTransactionManager testTransactionManager(@Qualifier("kawaDataSource") DataSource dataSource) {

        return new DataSourceTransactionManager(dataSource);

    }

    @Bean(name = "kawaSqlSessionTemplate")

    public SqlSessionTemplate testSqlSessionTemplate(

            @Qualifier("kawaSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {

        return new SqlSessionTemplate(sqlSessionFactory);

    }

}


两个数据源的配置一样，就是命名和指定prefix不一样，需要注意的时2.X版本的springboot的多数据源的配置和1.5.X版本有个区别就是，不用指定@Primay


3. 整合事务


一般事务的控制在方法或者类上加上注解@Transactional，如果是多数据源的事务就不好使了。我们需要有一个事务中心来管理所有数据源的事务，使用springboot+jta+atomikos 分布式事物管理。Atomikos 是一个为Java平台提供增值服务的并且开源类事务管理器。


3.1 引入jta-atomikos依赖


<dependency>

    <groupId>org.springframework.boot</groupId>

    <artifactId>spring-boot-starter-jta-atomikos</artifactId>

</dependency>


3.2 修改yml数据源配置


#多数据源配置

#spring:

mysql:

  datasource:

    kawa: #数据源1

     # url: jdbc:mysql://o186928x99.imwork.net:12921/brian?useUnicode=true&characterEncoding=utf-8&useSSL=false

      jdbc-url: jdbc:mysql://xuduocloud.qicp.vip:14110/brian?useUnicode=true&characterEncoding=utf-8&useSSL=false

      username: root

      password: 123456

      driver-class-name: com.mysql.cj.jdbc.Driver

      minPoolSize: 3

      maxPoolSize: 25

      maxLifetime: 20000

      borrowConnectionTimeout: 30

      loginTimeout: 30

      maintenanceInterval: 60

      maxIdleTime: 60

    kawa02: #数据源2

      jdbc-url: jdbc:mysql://xuduocloud.qicp.vip:14110/brian02?useUnicode=true&characterEncoding=utf-8&useSSL=false

      username: root

      password: 123456

      driver-class-name: com.mysql.cj.jdbc.Driver

      minPoolSize: 3

      maxPoolSize: 25

      maxLifetime: 20000

      borrowConnectionTimeout: 30

      loginTimeout: 30

      maintenanceInterval: 60

      maxIdleTime: 60


3.3 修改配置文件


3.3.1 新增db配置文件




3.3.2 在原来多数据源的配置基础上新增Atomikos全局事务管理，取消原来的声明式事务


package com.config;

import com.alibaba.druid.pool.DruidDataSource;

import com.atomikos.jdbc.AtomikosDataSourceBean;

import com.mysql.cj.jdbc.MysqlXADataSource;

import org.apache.ibatis.session.SqlSessionFactory;

import org.mybatis.spring.SqlSessionFactoryBean;

import org.mybatis.spring.SqlSessionTemplate;

import org.mybatis.spring.annotation.MapperScan;

import org.springframework.beans.factory.annotation.Qualifier;

import org.springframework.boot.context.properties.ConfigurationProperties;

import org.springframework.boot.jdbc.DataSourceBuilder;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

import java.sql.SQLException;

@Configuration

//@MapperScan(basePackages = "com.kawa", sqlSessionFactoryRef = "kawaSqlSessionFactory")

@MapperScan(basePackages = "com.kawa", sqlSessionTemplateRef = "kawaSqlSessionTemplate")

public class DataSourceConfig {

   /* @Bean

    @ConfigurationProperties(prefix = "spring.datasource")

    public DataSource druid(){

        return new DruidDataSource();

    }*/

    /**

     * 配置数据源

     * @return

     */

    /*@Bean(name="kawaDataSource")

    @ConfigurationProperties(prefix = "spring.datasource.kawa")

    public DataSource kawaDataSource(){

        return DataSourceBuilder.create().build();

    }*/

    @Bean(name="kawaDataSource")

    public DataSource kawaDataSource(DbConfig dbConfig) throws SQLException {

        MysqlXADataSource mysqlXaDataSource = new MysqlXADataSource();

        mysqlXaDataSource.setUrl(dbConfig.getJdbcUrl());

        mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);

        mysqlXaDataSource.setPassword(dbConfig.getPassword());

        mysqlXaDataSource.setUser(dbConfig.getUsername());

        mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);

        //将本地事务注册到Atomikos全局事务中

        AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();

        xaDataSource.setXaDataSource(mysqlXaDataSource);

        xaDataSource.setUniqueResourceName("kawaDataSource");

        xaDataSource.setMinPoolSize(dbConfig.getMinPoolSize());

        xaDataSource.setMaxPoolSize(dbConfig.getMaxPoolSize());

        xaDataSource.setMaxLifetime(dbConfig.getMaxLifetime());

        xaDataSource.setBorrowConnectionTimeout(dbConfig.getBorrowConnectionTimeout());

        xaDataSource.setLoginTimeout(dbConfig.getLoginTimeout());

        xaDataSource.setMaintenanceInterval(dbConfig.getMaintenanceInterval());

        xaDataSource.setMaxIdleTime(dbConfig.getMaxIdleTime());

        xaDataSource.setTestQuery(dbConfig.getTestQuery());

        return xaDataSource;

    }

    /**

     * sql会话工厂

     * @param dataSource

     * @return

     * @throws Exception

     */

    @Bean(name = "kawaSqlSessionFactory")

    public SqlSessionFactory testSqlSessionFactory(@Qualifier("kawaDataSource") DataSource dataSource)

            throws Exception {

        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();

        bean.setDataSource(dataSource);

        return bean.getObject();

    }

    /**

     * 事务管理

     * @param dataSource

     * @return

     */

    //事务由Atomikos管理，所以不用声明事务管理

   /* @Bean(name = "kawaTransactionManager")

    public DataSourceTransactionManager testTransactionManager(@Qualifier("kawaDataSource") DataSource dataSource) {

        return new DataSourceTransactionManager(dataSource);

    }*/

    @Bean(name = "kawaSqlSessionTemplate")

    public SqlSessionTemplate testSqlSessionTemplate(

            @Qualifier("kawaSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {

        return new SqlSessionTemplate(sqlSessionFactory);

    }

}


3.4 测试方法


package com.kawa2.sercice.impl;

import com.github.pagehelper.PageHelper;

import com.kawa.dao.UserDao;

import com.kawa.pojo.User;

import com.kawa2.dao.UserDao02;

import com.kawa2.sercice.UserService02;

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

import org.springframework.cache.annotation.CacheEvict;

import org.springframework.cache.annotation.CachePut;

import org.springframework.cache.annotation.Cacheable;

import org.springframework.stereotype.Service;

import javax.annotation.Resource;

import javax.transaction.Transactional;

import java.util.Date;

import java.util.List;

import java.util.Map;

@Service

public class UserServiceImpl02 implements UserService02 {

    @Resource

    UserDao02 userDao02;

    @Resource

    UserDao userDao;

    @Override

    public List<User> queryUserList(Map<String, Object> params) {

        return userDao02.queryUserList(params);

    }

    @Override

    public Integer insertUser(User user) {

        user.setPassword("10086");

        user.setCreated(new Date());

        user.setUpdated(user.getCreated());

        return userDao02.addUser(user);

    }

    /**

     * 多数据源的事务测试

     * @param user

     * @return

     */

    @Transactional()

    public Integer insert2Multi(User user) {

        user.setPassword("10086");

        user.setCreated(new Date());

        user.setUpdated(user.getCreated());

        Integer res = userDao.addUser(user);

        Integer res2 = userDao02.addUser(user);

        int err = 1 / 0;

        return res+ res2;

    }

    /*@CachePut(value = "user",key = "#result.id")

    @Override

    public Boolean updateUser(User user) {

        return userDao02.updateUser(user);

    }

    @CacheEvict(value = "user",key = "#id")

    @Override

    public Integer deleteUserById(Long id) {

        return userDao02.deleteUserById(id);

    }

    @Cacheable(value = {"user"})

    @Override

    public User queryUserById(Long id) {

        return userDao02.queryUserById(id);

    }*/

}


springboot(二)整合mybatis，多数据源和事务管理的相关教程结束。








《springboot(二)整合mybatis，多数据源和事务管理.doc》


下载本文的Word格式文档，以方便收藏与打印。
























Copyright 2023. 昆居客 - IT编程,互联网信息技术文档大全!