mybatis多数据源+druid界面展示+swagger

1、实现多数据源，并且展示druid界面调用sql,以及swagger接口文档展示，最后实现效果如下：

druid地址：Druid SQL Stat

swagger本地地址：Swagger UI

swagger界面：

druid界面展示：

2、实现方式

（1）pom文件引入

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.0</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example.demo</groupId>  <!--父子模板保持一致-->
    <artifactId>springbootProject</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>springbootProject</name>
    <packaging>pom</packaging>  <!--为了子模块 能引用主pom-->

    <modules>
        <module>common</module> <!--add了子模块-->
        <module>mybatis-learning</module> <!--add了子模块-->
    </modules>

    <properties>
        <java.version>11</java.version>
        <spring-cloud.version>2020.0.2</spring-cloud.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
            <version>2.5.0</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--用于RestController注解使用 必须引入该jar-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>2.4.5</version>
        </dependency>

        <!--swagger 用于界面显示 -->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.9.2</version>
        </dependency>

        <!--是注解 @EnableSwagger2-->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.9.2</version>
        </dependency>

        <!--swagger 用于引入 APIOperation    Api注解  用于展示接口文档 -->
        <dependency>
            <groupId>io.swagger</groupId>
            <artifactId>swagger-annotations</artifactId>
            <version>1.5.12</version>
        </dependency>

        <!--用于aop切面编程 使用AOP织入，需要导入一个依赖包-->
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
        </dependency>

        <!--用于JSON.toJSonString 方法使用-->
       <dependency>
           <groupId>com.saygoer</groupId>
           <artifactId>fastjson</artifactId>
           <version>1.1-unwrap</version>
       </dependency>

        <!--redis引入-->
        <dependency>
            <groupId>redis.clients</groupId>
            <artifactId>jedis</artifactId>
        </dependency>


        <!--mybatis支持包 -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
           <version>2.2.0</version>
        </dependency>

        <!--jexl引入 支持算法 -->
        <dependency>
            <groupId>commons-jexl</groupId>
            <artifactId>commons-jexl</artifactId>
            <version>1.1</version>
        </dependency>

        <!--用于服务调用的时候熔断机制-->
        <dependency>
            <groupId>com.netflix.hystrix</groupId>
            <artifactId>hystrix-core</artifactId>
            <version>1.5.6</version>
        </dependency>


        <!--Ribbon提供了服务间调用的客户端负载均衡功能，  等同fegin-->
        <dependency>
            <groupId>com.netflix.ribbon</groupId>
            <artifactId>ribbon</artifactId>
            <version>2.2.2</version>
        </dependency>


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

        <!-- Oracle驱动包  不添加，就没有驱动oracle.jdbc.OracleDriver-->
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc8</artifactId>
        </dependency>

        <!-- druid 数据库连接池   -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.6</version>
        </dependency>

    </dependencies>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-dependencies</artifactId>
                <version>${spring-cloud.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>
</project>

（2）配置文件多数据源配置

创建数据库用户：

1、sqlplus / as sysdba

2、建立账号密码 create user lpp identified by dev;

lpp ：新用户名的用户名

dev: 新用户的密码

3.给用户授权：

grant create session,create table,unlimited tablespace to username;

GRANT CREATE SESSION , CREATE TABLE , CREATE SEQUENCE , CREATE VIEW , CREATE PROCEDURE TO lpp;

server.port=8082

#应用名称
spring.application.name=spring-boot-lpp
##使用eureka

spring.datasource.filters: stat,wall,slf4j
##oracle.jdbc.driver.OracleDriver已经过期，所以去掉
master.datasource.driverClassName=oracle.jdbc.OracleDriver
master.datasource.url=jdbc:oracle:thin:@127.0.0.1:1521:DEV
master.datasource.username=lpp
master.datasource.password=dev


cluster.datasource.driverClassName=oracle.jdbc.OracleDriver
cluster.datasource.url=jdbc:oracle:thin:@127.0.0.1:1521:DEV
cluster.datasource.username=HEC
cluster.datasource.password=dev

（3）配置类

package com.example.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.sql.SQLException;

/*
刘佩佩
2021/6/17
*/
@Configuration
// 扫描 Mapper 接口并容器管理
@MapperScan(basePackages = ClusterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "clusterSqlSessionFactory")
public class ClusterDataSourceConfig{
    static final String MAPPER_LOCATION = "classpath:mapper/cluster/*.xml";
    static final String PACKAGE = "com.example.Dao.second"; //mapper文件夹

    @Value("${cluster.datasource.url}")
    private String url;

    @Value("${cluster.datasource.username}")
    private String user;

    @Value("${cluster.datasource.password}")
    private String password;

    @Value("${cluster.datasource.driverClassName}")
    private String driverClass;


    @Value("${spring.datasource.filters}")
    private String filters;

    @Bean(name = "clusterDataSource")
    public DataSource clusterDataSource() throws SQLException {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        dataSource.setFilters(filters);
        return dataSource;
    }

    @Bean(name = "clusterTransactionManager")
    public DataSourceTransactionManager clusterTransactionManager() throws SQLException {
        return new DataSourceTransactionManager(clusterDataSource());
    }

    @Bean(name = "clusterSqlSessionFactory")
    public SqlSessionFactory clusterSqlSessionFactory(@Qualifier("clusterDataSource") DataSource clusterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(clusterDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(ClusterDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }
}

classpath:mapper/cluster/*.xml指的是：用于读取文件resource下面mapper下的cluster下的.xml文件
com.example.Dao.second  指的是：mapper接口类

同理主的配置类如下：

package com.example.config;


import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.Collections;
import java.util.HashMap;
import java.util.Map;

/*
注入数据源
 */
@Configuration
// @ConfigurationProperties是springboot的注解，通过这个注解，项目可以获取到对应的配置属性，并返回数据源，
@MapperScan(basePackages = MasterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {

    static final String PACKAGE = "com.example.Dao.master"; //dao的地址
    static final String MAPPER_LOCATION = "classpath:mapper/master/*.xml"; //mybatis。xml地址


    @Value("${master.datasource.url}")
    private String url;

    @Value("${master.datasource.username}")
    private String user;

    @Value("${master.datasource.password}")
    private String password;

    @Value("${master.datasource.driverClassName}")
    private String driverClass;


    @Value("${spring.datasource.filters}")
    private String filters;

    @Bean(name = "masterDataSource") //方法1：
    @Primary
    public DataSource masterDataSource() throws SQLException {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        dataSource.setFilters(filters);
        return dataSource;
    }



  /* 方法1等价于该方法：
   @Bean(name = "masterDataSource")
    @ConfigurationProperties(prefix = "master.datasource")
    @Primary
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().build();
    }
*/

    @Bean(name = "masterTransactionManager")
    @Primary
    public DataSourceTransactionManager masterTransactionManager() throws SQLException {
        return new DataSourceTransactionManager(masterDataSource());
    }


    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(masterDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(MasterDataSourceConfig.MAPPER_LOCATION));  //设置XML地址
        return sessionFactory.getObject();
    }

}

swagger配置类:

.apis(RequestHandlerSelectors.basePackage("com.example")) 指的是：需要扫描的包下面的类
自定义接口文档类的命名

package com.example.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;
import springfox.documentation.swagger2.annotations.EnableSwagger2;

@Configuration
@EnableSwagger2
public class SwaggerConfig {
    @Bean
    public Docket createRestApi() {
        return new Docket(DocumentationType.SWAGGER_2)
                .apiInfo(apiInfo())
                .select()
                .apis(RequestHandlerSelectors.basePackage("com.example")) // 配置需要扫描的包路径
                .paths(PathSelectors.any())
                .build();
    }

    private ApiInfo apiInfo() {
        return new ApiInfoBuilder()
                .title("本地系统服务")
                .description("提供统一的身份认证与身份验证服务")
                .termsOfServiceUrl("http://www.baidu.com")
                .version("0.0.1")
                .build();
    }

}

实现druid展示：

最后输入Druid SQL Stat地址，然后输入自定义的账号密码登录即可，注意在配置中心文件需要添加filler  

spring.datasource.filters: stat,wall,slf4j

package com.example.config;

import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import com.google.common.collect.Maps;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.Arrays;
import java.util.Collections;
import java.util.HashMap;
import java.util.Map;

/*
刘佩佩
2021/6/25
只是用于界面展示druid ，目前master  cluser文件已经读取主副的连接
原文链接：https://blog.csdn.net/lizhiqiang1217/article/details/90573534

*/
@Configuration
public class DruidConfig {

    @Bean
    public ServletRegistrationBean staticViewServlet() {

        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        Map<String, String> initParams = Maps.newHashMapWithExpectedSize(4);
        initParams.put("loginUsername", "admin");
        initParams.put("loginPassword", "1234");
        initParams.put("allow", "");
        initParams.put("deny", "");
        bean.setInitParameters(initParams);
        return bean;
    }

    @Bean
    public FilterRegistrationBean webStaticFilter() {
        FilterRegistrationBean<WebStatFilter> bean = new FilterRegistrationBean<>();
        bean.setFilter(new WebStatFilter());
        Map<String, String> map = new HashMap<>();
        //移除这些监听
        map.put(WebStatFilter.PARAM_NAME_EXCLUSIONS, "*.js,*.css,/druid/*,*.gif,*.jpg,*.png");
        bean.setInitParameters(map);
        //拦截所有请求，全部都要走druid监听
        bean.setUrlPatterns(Collections.singletonList("/*"));
        return bean;
    }

}

AOP配置类

package com.example.Aop;

import com.alibaba.fastjson.JSON;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.*;
import org.springframework.context.annotation.EnableAspectJAutoProxy;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

@Aspect
@Order(1)//指定切面的优先级
@Component
@EnableAspectJAutoProxy //使aspect起作用 自动匹配代理对象
/*
任意公共方法的执行：execution(public * *(..))
任何一个以“set”开始的方法的执行：execution(* set*(..))
AccountService 接口的任意方法的执行：execution(* com.xyz.service.AccountService.*(..))
定义在service包里的任意方法的执行： execution(* com.xyz.service.*.*(..))
定义在service包和所有子包里的任意类的任意方法的执行：execution(* com.xyz.service..*.*(..))
第一个*表示匹配任意的方法返回值execution(*  ， ..(两个点)表示零个或多个参数，第一个..表示service包及其子包,第二个*表示所有类, 第三个*表示所有方法，第二个..表示方法的任意参数个数
 */
public class LogAspect {

   // @Pointcut("execution(* com.example.springbootproject.Controller.TestController.*(..))")//TestController 接口的任意方法的执行
    //@Pointcut("execution(* com.example.springbootproject.Controller.*.*(..))")//定义在Controller包里的任意方法的执行
    @Pointcut("execution(* com.example.Controller.*.*(..))")//定义在Controller包里所有类，所有的方法的任意方法的执行
    public void apilog() {
    }

    @Before("apilog()")
    public void doBefore() {
        System.out.println("Before方法执行");
    }

    @After("apilog()")
    public void doAfter() {
        System.out.println("after方法执行");
    }


    @Around("apilog()") //这里必须有返回对象，否则切面接口 void就会出现无返回
    public Object logArround(ProceedingJoinPoint joinPoint) throws Throwable {
        Object[] args = joinPoint.getArgs();
        System.out.println("环绕前入参:"+JSON.toJSONString(args[0]));
        System.out.println(joinPoint.getSignature());
        Object proceed = joinPoint.proceed();
        System.out.println("出参："+JSON.toJSONString(proceed));
        System.out.println("环绕后");
        return proceed;
    }

}

（4）Mapper类

package com.example.Dao.master;

import com.example.Response.Student;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
@Repository
public interface StudentMapper {
    List<Student> getStudent(@Param("name") String name);

    void insertStudent(Student  student);

    void  updateStudent (Student student);

    void deleteStudent(@Param("id") int id);
}

（5）、xml文件指定对应上面的Dao层 mapper接口类

<?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.example.Dao.master.StudentMapper">

    <select id="getStudent" resultType="com.example.Response.Student" parameterType="string">
       select  * from student   where name = #{name}
    </select>
    
    <insert id="insertStudent" parameterType="com.example.Response.Student">
        insert into student (id,name,age,course) values(student_seq.Nextval,#{name,jdbcType=VARCHAR},#{age,jdbcType=NUMERIC},#{course,jdbcType=VARCHAR})
    </insert>

    <update id="updateStudent"  parameterType="com.example.Response.Student">
        update student a set a.name=#{name},a.age=#{age},a.course=#{course} where a.id=#{id}
    </update>
    
    <delete id="deleteStudent">
      delete  from  student a where  a.id=#{id}
    </delete>
</mapper>

（6）、controller层，这里可以用@Resource注入到该类

package com.example.Controller;

import com.example.Response.Student;
import com.example.Service.DoService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;

@Api(tags = "本地接口文档", value = "/aa", description = "本地接口文档")
@RestController
public class Controller {

    @Resource
    private DoService doService;

    @ApiOperation(value = "新增学生", httpMethod = "POST", notes = "新增学生信息")
    @PostMapping("/addStudent")
    public void insertStudent(@RequestBody @Validated Student student) {
        doService.insertStudent(student);
    }

    @ApiOperation(value = "修改学生", httpMethod = "POST", notes = "修改学生信息")
    @PostMapping("/updateStudent")
    public void updateStudent(@RequestBody @Validated Student student) {
        doService.updateStudent(student);
    }

    @ApiOperation(value = "删除学生信息", httpMethod = "POST")
    @PostMapping("/deleteStudent/{id}")
    public void deleteStudent(@PathVariable(value = "id") int id) {
        doService.deleteStudent(id);
    }

}

测试：

postman调用

通过切面控制打印日志：

git地址：

GitHub - liupeipei1/lpp-multiple-moudle: 主要本地使用springboot+springcloud框架

欢迎一起探讨~