【笔记】SpringBoot集成Mybatis

最新推荐文章于 2024-10-22 19:03:08 发布

coderhuihui

最新推荐文章于 2024-10-22 19:03:08 发布

阅读量435

点赞数 1

分类专栏：笔记文章标签： Springboot mybatis 多数据源

本文链接：https://blog.csdn.net/qq_40580023/article/details/92789356

版权

笔记专栏收录该内容

34 篇文章 1 订阅

订阅专栏

使用mysql数据库

建表语句

create table t_user(
    id bigint(20) primary key AUTO_INCREMENT,
    name varchar(50) not null comment'用户名',
    phone varchar(30) not null comment'手机'
)ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

insert into t_user values("1","小明",'15236852365');

代码结构图

2.SpringBoot集成mybatis(单数据源情况)

2.1 准备工作

2.1.1pom.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.springbootdemo</groupId>
  <artifactId>dbandcache</artifactId>
  <version>1.0-SNAPSHOT</version>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <java.version>1.8</java.version>
  </properties>

  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.1.4.RELEASE</version>
  </parent>


  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <dependency>
      <groupId>io.springfox</groupId>
      <artifactId>springfox-swagger2</artifactId>
      <version>2.2.2</version>
    </dependency>

    <dependency>
      <groupId>io.springfox</groupId>
      <artifactId>springfox-swagger-ui</artifactId>
      <version>2.2.2</version>
    </dependency>

    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.16.8</version>
    </dependency>

    <!--数据库相关依赖包-->
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <!--数据源-->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.0.14</version>
    </dependency>

    <!--mysql-->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <scope>runtime</scope>
    </dependency>
    <!--mybatis-->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.2.8</version>
    </dependency>
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis-spring</artifactId>
      <version>1.2.2</version>
    </dependency>
    <dependency>
      <groupId>org.mybatis.spring.boot</groupId>
      <artifactId>mybatis-spring-boot-starter</artifactId>
      <version>1.1.1</version>
    </dependency>
  </dependencies>

  <build>
    <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
      <plugins>
        <plugin>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin>
      </plugins>
    </pluginManagement>
  </build>
</project>

文件主要需要引入与数据库相关的5个包:Spring-boot-starter-jdbc、druid、mysql-connector-java、mybatis、mybatis-spring。

2.1.2 主类代码

package com.springbootdemo;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import springfox.documentation.swagger2.annotations.EnableSwagger2;

@SpringBootApplication
@EnableSwagger2
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class,args);
    }
}

2.1.3模型类User代码

package com.springbootdemo.model;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
@ApiModel
public class User {
    @ApiModelProperty("用户id")
    private Long id;
    @ApiModelProperty("用户名")
    private String name;
    @ApiModelProperty("用户电话")
    private String phone;
}

2.1.4 mapper 包下UserMapper代码

package com.springbootdemo.mapper;
import com.springbootdemo.model.User;
public interface UserMapper {
    User selectUserById(Long id);
}

2.1.5 UserMapper对应的xml文件,即src/main/resource/mapping/UserMapper.xml文件代码

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.springbootdemo.mapper.UserMapper">
  <resultMap id="BaseResultMap" type="com.springbootdemo.model.User">
    <result column="id" jdbcType="BIGINT" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="phone" jdbcType="VARCHAR" property="phone" />
  </resultMap>

  <select id="selectUserById" resultMap="BaseResultMap" parameterType="java.lang.Long">
    select id,name,phone from t_user where id=#{id}
  </select>
</mapper>

2.1.6在application.properties文件中配置数据信息,及定义数据库连接的4要素

jdbc.driverClassName=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://192.168.238.115:3306/springboot_data?zeroDataTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8
jdbc.username=username
jdbc.password=123456

2.2开始集成MyBatis

2.2.1代码如com.springbootdemo.config.MyBatisConfig类所示:

package com.springbootdemo.config;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;


import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

@Configuration
@MapperScan(basePackages = "com.springbootdemo.mapper")
public class MyBatisConfig {
    @Autowired
    private Environment env;
    @Bean
    public DataSource dataSource() throws Exception {
        Properties prop=new Properties();
        prop.put("driverClassName",env.getProperty("jdbc.driverClassName"));
        prop.put("url",env.getProperty("jdbc.url"));
        prop.put("username",env.getProperty("jdbc.username"));
        prop.put("password",env.getProperty("jdbc.password"));
        return  DruidDataSourceFactory.createDataSource(prop);
    }


    @Bean
    public SqlSessionFactory sqlSessionFactory(DataSource ds) throws Exception {
        SqlSessionFactoryBean fb=new SqlSessionFactoryBean();
        fb.setDataSource(ds);//指定数据源
        fb.setTypeAliasesPackage("com.springbootdemo.model");//指定基包
        fb.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapping/*.xml"));//指定XML文件位置
        return fb.getObject();
    }
}

这个类是整个项目中最重要的类,他替代了常规Spring与mybatis集成时使用的xml文件,用于配置持久层所需要的信息，将dataSource、sessionFactory和Mapper的扫描配置放在这里。

整个类流程如下：

SqlSessionFactoryBean中的TypeAliasesPackage用来指定domain类的基包，即指定在xxxMapper.xml中可以用简名代替全类名；

MapperLocations用来指定xxxMapper.xml文件的所在位置,不过如果mybatis完全使用注解,就不需要设置这两个参数。

2.3根据基本分成架构，测试mybatis集成，在此设计三个类，dao，service和controller

2.3.1 dao类，com.springbootdemo.dao.UserDao代码如下：

package com.springbootdemo.dao;

import com.springbootdemo.mapper.UserMapper;
import com.springbootdemo.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
public class UserDao {
    @Autowired
    private UserMapper userMapper;
    public User selectByPrimaryKey(Long id){
        return userMapper.selectUserById(id);
    }
}

2.3.2 service类，com.springbootdemo.service.UserService代码如下：

package com.springbootdemo.service;

import com.springbootdemo.dao.UserDao;
import com.springbootdemo.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class UserService {
    @Autowired
    private UserDao userDao;
    
    public User getUser(Long id){
       return userDao.selectByPrimaryKey(id);
    }

}

2.3.3 controller类，com.springbootdemo.controller.UserController代码如下：

package com.springbootdemo.controller;

import com.springbootdemo.model.User;
import com.springbootdemo.service.UserService;
import io.swagger.annotations.*;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;

@Api("用户相关Api")
@RestController
@RequestMapping("/user")
public class UserController {
    @Resource
    private UserService userService;
    @ApiOperation("根据id获取用户信息")
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = "query",name="id",dataType = "long", required=true,value="用户id",defaultValue = "1")
    })
    @ApiResponses({
            @ApiResponse(code=401,message = "权限校验不通过")
    })
    @RequestMapping(value = "/getUserInfo",method = RequestMethod.GET)
    public User getUserInfo(@RequestParam("id") Long id){
        return userService.getUser(id);
    }
}

至此，完成了整个项目，功能也很简单，就是根据用户id从数据库获取用户信息。

2.4测试效果

3.多数据源集成

大多数情况下，一个服务只需要一个数据源，但有时也会需要访问多个数据源，比如常见的读写分离就是对两个数据源进行操作。

假设现在有两个数据源db1和db2,用户信息表t_user存在db1中，用户的车辆信息表t_car存在db2中，dbandcache这个服务需要根据用户的id查询出用户信息的同时也要查询出该用户车辆信息，一起返回给前端，这个时候就出现了需要访问两个数据源的情况了。

下面来完成这个需求

3.1建库建表

建表语句

CREATE TABLE `t_car`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '车名',
  `color` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '颜色',
  `owner` bigint(20) NOT NULL COMMENT '车主',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

INSERT INTO `t_car` VALUES (1, '宝马', 'red', 1);

3.2 Model，Mapper类

3.2.1 com.springbootdemo.model.Car代码

package com.springbootdemo.model;

import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
public class Car {
    private Long id;
    private String name;
    private String color;
    private Long owner;
}

3.2.2 com.springbootdemo.mapper.CarMapper代码

package com.springbootdemo.mapper;

import com.springbootdemo.model.Car;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface CarMapper {
    List<Car> selectByOwner(@Param("ownerId")Long ownerId);
}

3.2.3 src.main.resources.mapping.CarMapper.xml代码

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.springbootdemo.mapper.CarMapper">
  <resultMap id="BaseResultMap" type="com.springbootdemo.model.Car">
    <result column="id" jdbcType="BIGINT" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="color" jdbcType="VARCHAR" property="color" />
    <result column="owner" jdbcType="BIGINT" property="owner" />
  </resultMap>
  
  <select id="selectByOwner" resultMap="BaseResultMap" parameterType="java.lang.Long">
     select * from t_car where owner=#{ownerId}
  </select>
</mapper>

3.3修改application.properties文件配置信息,添加数据源,新增了springboot_data2数据库的配置

jdbc.driverClassName=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://192.168.238.115:3306/springboot_data?zeroDataTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8
jdbc.username=username
jdbc.password=123456

jdbc2.driverClassName=com.mysql.cj.jdbc.Driver
jdbc2.url=jdbc:mysql://192.168.238.115:3306/springboot_data2?zeroDataTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8
jdbc2.username=username
jdbc2.password=123456

3.4开始配置,结合AbstractRoutingDataSource实现动态数据源

3.4.1 定义一个枚举类com.springbootdemo.config.DatabaseType

package com.springbootdemo.config;

public enum DatabaseType {
    dbdata1,dbdata2
}

该类列出了所有数据源的key,然后需要创建数据源key的持有类com.springbootdemo.config.DatabaseContextHolder

3.4.2创建数据源key的持有类,com.springbootdemo.config.DatabaseContextHolder

package com.springbootdemo.config;

public class DatabaseContextHolder {
    private static final ThreadLocal<DatabaseType> contextHolder =new ThreadLocal<>();
    public static  void setDatabaseType(DatabaseType type){
        contextHolder.set(type);
    }
    public static  DatabaseType getDatabaseType(){
        return contextHolder.get();
    }
}

该类主要用于选择数据源是,将相应的数据源的key设置到contextHolder中,之后对数据源的访问就使用key对应的数据源.

3.4.3 实现动态数据源,创建类DynamicDataSource继承springjdbc的AbstractRoutingDataSource类.

代码如下

package com.springbootdemo.config;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DatabaseContextHolder.getDatabaseType();
    }
}

该类实现了继承AbstractRoutingDataSource的determineCurrentLookupKey()方法,该方法通过调用数据源key的持有类DatabaseContextHolder的getDatabaseType()方法获取数据源key。

3.4.4构造动态数据源，修改MyBatisConfig类，代码如下

package com.springbootdemo.config;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;


import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

@Configuration
@MapperScan(basePackages = "com.springbootdemo.mapper")
public class MyBatisConfig {
    @Autowired
    private Environment env;
    @Bean
    public DataSource db1DataSource() throws Exception {
        Properties prop=new Properties();
        prop.put("driverClassName",env.getProperty("jdbc.driverClassName"));
        prop.put("url",env.getProperty("jdbc.url"));
        prop.put("username",env.getProperty("jdbc.username"));
        prop.put("password",env.getProperty("jdbc.password"));
        return  DruidDataSourceFactory.createDataSource(prop);
    }
    @Bean
    public DataSource db2DataSource() throws Exception {
        Properties prop=new Properties();
        prop.put("driverClassName",env.getProperty("jdbc2.driverClassName"));
        prop.put("url",env.getProperty("jdbc2.url"));
        prop.put("username",env.getProperty("jdbc2.username"));
        prop.put("password",env.getProperty("jdbc2.password"));
        return  DruidDataSourceFactory.createDataSource(prop);
    }
    @Bean
    @Primary
    public DynamicDataSource dataSource(@Qualifier("db1DataSource")DataSource db1DataSource,@Qualifier("db2DataSource")DataSource db2DataSource){
        Map<Object,Object> targetDataSources=new HashMap<>();
        targetDataSources.put(DatabaseType.dbdata1,db1DataSource);
        targetDataSources.put(DatabaseType.dbdata2,db2DataSource);
        DynamicDataSource dataSource = new DynamicDataSource();
        dataSource.setTargetDataSources(targetDataSources);//该方法是AbstractRoutingDataSource的方法
        dataSource.setDefaultTargetDataSource(db1DataSource);//默认的dataSource设置为myTestDbDataSource
        return dataSource;
    }

    @Bean
    public SqlSessionFactory sqlSessionFactory(DataSource ds) throws Exception {
        SqlSessionFactoryBean fb=new SqlSessionFactoryBean();
        fb.setDataSource(ds);//指定数据源
        fb.setTypeAliasesPackage("com.springbootdemo.model");//指定基包
        fb.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapping/*.xml"));//指定XML文件位置
        return fb.getObject();
    }
}

在该类中首先创建了两个数据源db1DataSource和db2DataSource，然后将这两个数据源设置到DynamicDataSource数据源中，在DynamicDataSource设置了目标数据源map,并设置默认数据源为db1DataSource，这样就不需要为访问db1DataSource的dao类选择数据源了。对于db2DataSource的访问dao则需要显示指定。

注意：MyBatisConfig类中的三个数据源都是javax.sql.DataSource的子类，所以在DynamicDataSource类上加入@Primary注解，作用是在用一个接口有多个实现类可以注入的时候，默认选择一个，以防Spring因为有多个选择而不知道注入哪个导致报错。

3.5根据分层，配置dao,service,controller

3.5.1 创建model用于获取用户和车辆信息

package com.springbootdemo.model;

import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

import java.util.List;

@Getter
@Setter
@NoArgsConstructor
public class UserAndCar extends User{
    private List<Car> cars;

}

3.5.2 创建dao类，com.springbootdemo.dao.CarDao

package com.springbootdemo.dao;

import com.springbootdemo.config.DatabaseContextHolder;
import com.springbootdemo.config.DatabaseType;
import com.springbootdemo.mapper.CarMapper;
import com.springbootdemo.model.Car;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class CarDao {
    @Autowired
    private CarMapper carMapper;

    public List<Car> selectByOwnerId(Long id){
        //操作数据库2的数据时,先选择对应的数据源在操作
        DatabaseContextHolder.setDatabaseType(DatabaseType.dbdata2);
        return carMapper.selectByOwner(id);
    }
}

注意：该类在调用Mapper操作数据库之前，要先选择数据源，即加上 DatabaseContextHolder.setDatabaseType(DatabaseType.dbdata2);语句

3.5.3 修改UserService代码，

package com.springbootdemo.service;

import com.springbootdemo.dao.CarDao;
import com.springbootdemo.dao.UserDao;
import com.springbootdemo.model.Car;
import com.springbootdemo.model.User;
import com.springbootdemo.model.UserAndCar;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserService {
    @Autowired
    private UserDao userDao;
    @Autowired
    private CarDao carDao;

    public User getUser(Long id){
       return userDao.selectByPrimaryKey(id);
    }

    public UserAndCar getUserAndCar(Long userId){
        UserAndCar userAndCar = new UserAndCar();
        User user = userDao.selectByPrimaryKey(userId);
        if(user!=null){
            List<Car> cars = carDao.selectByOwnerId(userId);
            userAndCar.setId(user.getId());
            userAndCar.setName(user.getName());
            userAndCar.setPhone(user.getPhone());
            userAndCar.setCars(cars);
        }
        return userAndCar;
    }
}

3.5.4 创建Controller，com.springbootdemo.controller.DbAndCacheController

package com.springbootdemo.controller;

import com.springbootdemo.model.UserAndCar;
import com.springbootdemo.service.UserService;
import io.swagger.annotations.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
@Api("用户信息和车辆信息相关")
@RestController
@RequestMapping("/userAndCar")
public class DbAndCacheController {
    @Autowired
    private UserService userService;

    @ApiOperation("根据用户ID获取用户及车辆信息")
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = "query",name = "id",dataType = "long",required=true,value = "用户id",defaultValue = "1")
    })
    @ApiResponses({
            @ApiResponse(code = 401,message = "权限校验不通过")
    })
    @RequestMapping(value="/getUserAndCars",method = RequestMethod.GET)
    public UserAndCar getUserAndCar(@RequestParam("id") long id){
        return userService.getUserAndCar(id);
    }
}

至此，实现了服务中对多数据源的使用。

3.6测试效果

3.7 使用Spring aop简化数据源选择功能

当服务中dao很多，dao中的方法也很多的时候，就发现每次都要写一遍选择数据源的语句。使用Aop便可简化选择功能

创建切面类com.springbootdemo.aspect.DataSourceAspect，代码如下

package com.springbootdemo.aspect;

import com.springbootdemo.config.DatabaseContextHolder;
import com.springbootdemo.config.DatabaseType;
import com.springbootdemo.dao.CarDao;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.springframework.stereotype.Component;

@Aspect
@Component
public class DataSourceAspect {
    /**
     * 该类拦截dao包下的所有类的所有方法,
     * 将其中CarDao类的数据源设为db2,
     * 这样就免除了在CarDao类中每个方法都要加入选择数据源语句
     * @param point
     */
    @Before("execution(* com.springbootdemo.dao.*.*(..))")
    public void setDataSourceKey(JoinPoint point){
        //如果有其他类也是使用dbdata2获取使用其他数据源,加条件判断
        if(point.getTarget() instanceof CarDao){
            DatabaseContextHolder.setDatabaseType(DatabaseType.dbdata2);
        }
    }
}

通过该类拦截dao包下的所有类和方法，将carDao类数据源设置为db2，就可以将CarDao中的DatabaseContextHolder.setDatabaseType(DatabaseType.dbdata2);删除了。

至此，SpringBoot集成Mybatis来实现多数据源就完成了。