010.SprintBoot+Mybatis多数据源+druidSQL监控

引入Druid依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.21</version>
</dependency>

 Mybatis多数据源+Druid配置

#连接池druid的配置信息+多数据源
spring.datasource.druid.javacore.url=jdbc:postgresql://localhost:5432/javacore
spring.datasource.druid.javacore.username=postgres
spring.datasource.druid.javacore.password=asdf-1234
spring.datasource.druid.javacore.driver-class-name=org.postgresql.Driver

spring.datasource.druid.postgres.url=jdbc:postgresql://localhost:5432/postgres
spring.datasource.druid.postgres.username=postgres
spring.datasource.druid.postgres.password=asdf-1234
spring.datasource.druid.postgres.driver-class-name=org.postgresql.Driver

spring.datasource.druid.initialSize=5
spring.datasource.druid.minIdle=5
spring.datasource.druid.maxActive=20
spring.datasource.druid.maxWait=60000
spring.datasource.druid.timeBetweenEvictionRunsMillis=60000
spring.datasource.druid.minEvictableIdleTimeMillis=300000
#spring.datasource.druid.validationQuery=SELECT 1 FROM DUAL
spring.datasource.druid.testWhileIdle=true
spring.datasource.druid.testOnBorrow=false
spring.datasource.druid.testOnReturn=false
spring.datasource.druid.poolPreparedStatements=true
spring.datasource.druid.maxPoolPreparedStatementPerConnectionSize=20
#SQL检测打开
spring.datasource.druid.filter.stat.log-slow-sql=true
spring.datasource.druid.filter.stat.enabled=true
spring.datasource.druid.filter.merge-sql=true
spring.datasource.druid.filter.stat.slow-sql-millis=2000

#spring.datasource.druid.filters=stat,wall,log4j,config

#druid的监控站点
spring.datasource.druid.stat-view-servlet.enabled=true
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
spring.datasource.druid.stat-view-servlet.reset-enable=true
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=123

数据库1的配置类

package com.javacore.config.db;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.context.properties.ConfigurationProperties;
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;

/**
 * 主数据源配置类
 */
@Configuration
@MapperScan(basePackages = DataSourceJavacoreConfig.PACKAGE, sqlSessionFactoryRef = "javacoreSqlSessionFactory")
public class DataSourceJavacoreConfig {

    /**
     * mapper操作类扫描路径
     */
    static final String PACKAGE =  "com.javacore.mybatis.javacore.mapper";

    /**
     * mybatis-mapper 扫描路径
     */
    private static final String MAPPER_LOCATION = "classpath:mapper/javacore/*.xml";


    /**
     * 创建名为 javacoreDataSource 的数据源
     */
    @Bean(name = "javacoreDataSource")
    @Primary    //该注解的作用是，当有多个相同的Bean的时候，优先选择有该注解的Bean 配置多数据源，必须有一个主数据源
    @ConfigurationProperties(prefix = "spring.datasource.druid.javacore")
    public DataSource javacoreDataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name = "javacoreTransactionManager")
    @Primary
    public DataSourceTransactionManager javacoreTransactionManager() {
        return new DataSourceTransactionManager(javacoreDataSource());
    }

    /**
     * 将名为 javacoreDataSource 的数据源注入到 SqlSessionFactory
     */
    @Bean(name = "javacoreSqlSessionFactory")
    @Primary
    public SqlSessionFactory javacoreSqlSessionFactory(@Qualifier("javacoreDataSource") DataSource javacoreDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(javacoreDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(DataSourceJavacoreConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }

}

数据库2的配置类

package com.javacore.config.db;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.context.properties.ConfigurationProperties;
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;

/**
 * 主数据源配置类
 */
@Configuration
@MapperScan(basePackages = DataSourcePostgresConfig.PACKAGE, sqlSessionFactoryRef = "postgresSqlSessionFactory")
public class DataSourcePostgresConfig {

    /**
     * mapper操作类扫描路径
     */
    static final String PACKAGE =  "com.javacore.mybatis.postgres.mapper";

    /**
     * mybatis-mapper 扫描路径
     */
    private static final String MAPPER_LOCATION = "classpath:mapper/postgres/*.xml";


    /**
     * 创建名为 postgresDataSource 的数据源
     */
    @Bean(name = "postgresDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.druid.postgres")
    public DataSource postgresDataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name = "postgresTransactionManager")
    public DataSourceTransactionManager postgresTransactionManager() {
        return new DataSourceTransactionManager(postgresDataSource());
    }

    /**
     * 将名为 postgresDataSource 的数据源注入到 SqlSessionFactory
     */
    @Bean(name = "postgresSqlSessionFactory")
    public SqlSessionFactory postgresSqlSessionFactory(@Qualifier("postgresDataSource") DataSource postgresDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(postgresDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(DataSourcePostgresConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }

}

Controller层

package com.javacore.api;

import com.javacore.mybatis.javacore.model.PUser;
import com.javacore.mybatis.postgres.model.SUser;
import com.javacore.service.UserService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

@Api(tags = {"001-用户管理"} )
@RestController
@RequestMapping(value = "/Users")
public class UserApi {
    @Autowired
    private  UserService userService;

    @GetMapping("/GetDB1User")
    @ApiOperation(value = "获取数据库1的数据")
    public PUser GetDB1User() {
        return userService.GetDB1User();
    }

    @GetMapping("/GetDB2User")
    @ApiOperation(value = "获取数据库2的数据")
    public SUser GetDB2User() {
        return userService.GetDB2User();
    }
}

Service层

package com.javacore.service;

import com.javacore.mybatis.javacore.mapper.PUserMapper;
import com.javacore.mybatis.javacore.model.PUser;
import com.javacore.mybatis.postgres.mapper.SUserMapper;
import com.javacore.mybatis.postgres.model.SUser;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;


@Service
public class UserService {
    @Autowired
    private PUserMapper userMapperPrimary;
    @Autowired
    private SUserMapper userMapperSecondary;

    //获取数据库1的数据
    public PUser GetDB1User(){
        return userMapperPrimary.findByName("来源数据库1");
    }
    //获取数据库2的数据
    public SUser GetDB2User(){
        return userMapperSecondary.findByName("来源数据库2");
    }
}

Mapper

package com.javacore.mybatis.javacore.mapper;


import com.javacore.mybatis.javacore.model.PUser;
import org.apache.ibatis.annotations.*;

@Mapper
public interface PUserMapper {

    //@Select("SELECT * FROM \"User\" WHERE name = #{name}")
    PUser findByName(@Param("name") String name);

    //@Insert("INSERT INTO \"User\"(name, age) VALUES(#{name}, #{age})")
    int insert(@Param("name") String name, @Param("age") Integer age);

    //@Update("UPDATE \"User\" SET age=#{age} WHERE name=#{name}")
    void update(PUser user);

    //@Delete("DELETE FROM \"User\" WHERE id =#{id}")
    void delete(Long id);
}

MapperXML

<?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.javacore.mybatis.javacore.mapper.PUserMapper">
    <select id="findByName" resultType="com.javacore.mybatis.javacore.model.PUser">
        SELECT * FROM "User" WHERE NAME = #{name}
    </select>

    <insert id="insert">
        INSERT INTO "User"(NAME, AGE) VALUES(#{name}, #{age})
    </insert>

    <update id="update">
        UPDATE "User" SET age=#{age} WHERE name=#{name}
    </update>

    <delete id="delete">
        DELETE FROM "User" WHERE id =#{id}
    </delete>
</mapper>

项目结构

 启动后访问：http://localhost:8080/druid