欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

Springboot集成通用Mapper与Pagehelper，实现mybatis+Druid的多数据源配置

程序员文章站 2022-07-15 11:06:46
...

2019独角兽企业重金招聘Python工程师标准>>> Springboot集成通用Mapper与Pagehelper，实现mybatis+Druid的多数据源配置

1.本篇作为对前一篇的补充，可参考

    https://my.oschina.net/swiftloop/blog/1031404

2.具体实现

    1）pom.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.sorata.datasource</groupId>
	<artifactId>mybatis-pagehelper-mapper</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>mybatis-pagehelper-mapper</name>
	<description>集成mybatis多数据源与分页插件和通用mapper的使用</description>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>1.5.4.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>

	<properties>
        <!-- 这里是配置主从库的包，当生成不同的数据库下的dao、mapper、entity需要更改此包，或者是直接在generator.xml文件下硬编码-->
		<!--存放生成的dao和entity-->
		<targetJavaProject>${basedir}/src/main/java</targetJavaProject>
		<!--end-->
		<!--  XML生成路径  -->
		<targetResourcesProject>${basedir}/src/main/resources</targetResourcesProject>
		<targetXMLPackage>mapper/master</targetXMLPackage>
		<!--end-->
		<!--存放dao的包-->
		<targetMapperPackage>com.sorata.datasource.dao.master</targetMapperPackage>
		<!--end-->
		<!--存放entity的包-->
		<targetModelPackage>com.sorata.datasource.entity.master</targetModelPackage>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.3.0</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

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

		<dependency>
			<groupId>com.github.pagehelper</groupId>
			<artifactId>pagehelper-spring-boot-starter</artifactId>
			<version>1.1.1</version>
		</dependency>

		<dependency>
			<groupId>tk.mybatis</groupId>
			<artifactId>mapper-spring-boot-starter</artifactId>
			<version>1.1.1</version>
		</dependency>

		<dependency>
			<groupId>org.springframework.data</groupId>
			<artifactId>spring-data-commons</artifactId>
			<version>1.13.1.RELEASE</version>
		</dependency>

	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>

			<plugin>
				<groupId>org.mybatis.generator</groupId>
				<artifactId>mybatis-generator-maven-plugin</artifactId>
				<version>1.3.2</version>
				<configuration>
					<configurationFile>${basedir}/src/main/resources/generator/generatorConfig.xml</configurationFile>
					<overwrite>true</overwrite>
					<verbose>true</verbose>
				</configuration>
				<dependencies>
					<dependency>
						<groupId>mysql</groupId>
						<artifactId>mysql-connector-java</artifactId>
						<version>5.1.30</version>
					</dependency>
					<dependency>
						<groupId>tk.mybatis</groupId>
						<artifactId>mapper</artifactId>
						<version>3.4.0</version>
					</dependency>
				</dependencies>
			</plugin>

		</plugins>
	</build>


</project>

    2）generator.xml  

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

<generatorConfiguration>
    <properties resource="application.yml"/>

    <context id="Mysql" targetRuntime="MyBatis3Simple" defaultModelType="flat">
        <property name="beginningDelimiter" value="`"/>
        <property name="endingDelimiter" value="`"/>

        <plugin type="tk.mybatis.mapper.generator.MapperPlugin">
            <!-- 这里是项目包下的一个类-->
            <property name="mappers" value="com.sorata.datasource.webmvc.MyMapper"/>
        </plugin>

        <jdbcConnection driverClass="com.mysql.jdbc.Driver"
                        connectionURL="jdbc:mysql://localhost:3306/test1"
                        userId="root"
                        password="root">
        </jdbcConnection>

        <javaModelGenerator targetPackage="${targetModelPackage}" targetProject="${targetJavaProject}"/>

        <sqlMapGenerator targetPackage="${targetXMLPackage}"  targetProject="${targetResourcesProject}"/>

        <javaClientGenerator targetPackage="${targetMapperPackage}" targetProject="${targetJavaProject}" type="XMLMAPPER" />

        <table tableName="test" domainObjectName="CustomTest">
            <!--mysql 配置-->
            <generatedKey column="id" sqlStatement="Mysql" identity="true"/>
            <!--oracle 配置-->
            <!--<generatedKey column="id" sqlStatement="select SEQ_{1}.nextval from dual" identity="false" type="pre"/>-->
        </table>
    </context>
</generatorConfiguration>

    3）MyMapper.java

package com.sorata.datasource.webmvc;

import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;

/**
 * Created by: Sorata 17/4/12 下午12:03
 */
public interface MyMapper<T> extends Mapper<T>,MySqlMapper<T> {
}

    4)MasterDataSourceConfig.java

package com.sorata.datasource.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.slf4j.Logger;
import org.slf4j.LoggerFactory;
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;

/**
 * Created by :  Sorata   2017/6/27 0027 下午 3:56.
 */
@Configuration
@MapperScan(basePackages = MasterDataSourceConfig.MASTER_PACKAGE,sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {

    private Logger logger = LoggerFactory.getLogger(MasterDataSourceConfig.class);


    static final String  MASTER_PACKAGE = "com.sorata.datasource.dao.master";
    private static final String MASTER_MAPPER_LOCAL = "classpath:mapper/master/*.xml";


    /** 配置一个主库
     * @return  DruidDataSource
     */
    @Bean(name = "masterDataSource")
    @Primary
    @ConfigurationProperties(prefix = "druid.datasource")
    public DruidDataSource masterDruidDataSource(){
        return new DruidDataSource();
    }


    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(){
        final SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(masterDruidDataSource());

        try {
            sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MASTER_MAPPER_LOCAL));
            return sessionFactoryBean.getObject();
        } catch (Exception e) {
            logger.error("配置主库的SqlSessionFactory失败，error:{}",e.getMessage());
            throw new RuntimeException(e.getMessage());
        }
    }

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

}

    5)CustomDataSourceConfig.java

package com.sorata.datasource.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.slf4j.Logger;
import org.slf4j.LoggerFactory;
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;

/**
 * Created by :  Sorata   2017/6/27 0027 下午 3:56.
 */
@Configuration
@MapperScan(basePackages = CustomDataSourceConfig.CUSTOM_PACKAGE,sqlSessionFactoryRef = "customSqlSessionFactory")
public class CustomDataSourceConfig {

    private Logger logger = LoggerFactory.getLogger(CustomDataSourceConfig.class);


    static final String CUSTOM_PACKAGE = "com.sorata.datasource.dao.custom";
    private static final String CUSTOM_MAPPER_LOCAL = "classpath:mapper/custom/*.xml";

    /** 配置一个从库
     * @return DruidDataSource
     */
    @Bean(name = "customDataSource")
    @ConfigurationProperties(prefix = "custom.datasource")
    public DruidDataSource customDruidDataSource(){
        return new DruidDataSource();
    }

    @Bean(name = "customSqlSessionFactory")
    public SqlSessionFactory customSqlSessionFactory(){
        final SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(customDruidDataSource());

        try {
            sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(CUSTOM_MAPPER_LOCAL));
            return sessionFactoryBean.getObject();
        } catch (Exception e) {
            logger.error("配置从库的SqlSessionFactory失败，error:{}",e.getMessage());
            throw new RuntimeException(e.getMessage());
        }
    }

    @Bean(name = "customTransactionManager")
    public DataSourceTransactionManager customTransactionManager(){
        return new DataSourceTransactionManager(customDruidDataSource());
    }

}

    6)DataSourcesAutoConfiguration.java

package com.sorata.datasource.config;

import org.springframework.boot.autoconfigure.condition.ConditionalOnBean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.transaction.ChainedTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.transaction.annotation.TransactionManagementConfigurer;

import javax.annotation.Resource;


/**
 * Created by :  Sorata   2017/6/27 0027 下午 1:38.
 */
@Configuration
@EnableTransactionManagement
@ConditionalOnBean({MasterDataSourceConfig.class,CustomDataSourceConfig.class})
public class DataSourcesAutoConfiguration  implements TransactionManagementConfigurer{

    @Resource
    private MasterDataSourceConfig masterDataSourceConfig;

    @Resource
    private CustomDataSourceConfig customDataSourceConfig;

//  配置分布式事务管理 
    @Override
    public PlatformTransactionManager annotationDrivenTransactionManager() {

        return  new  ChainedTransactionManager(masterDataSourceConfig.masterTransactionManager(),customDataSourceConfig.customTransactionManager());
    }

}

    说明：此处解决了，存在两个事务管理器的时候，使用注解时需要显示注明从库的事务管理器的问题

    7）Druid  注意在启动类上加上@ServletComponentScan

package com.sorata.datasource.config;

import com.alibaba.druid.support.http.StatViewServlet;

import javax.servlet.annotation.WebInitParam;
import javax.servlet.annotation.WebServlet;

/**
 * Created by: guobo 17/1/19 下午5:20
 *
 * alibaba druid 页面监控配置   访问url  http://localhost:8080/项目名/druid/index.html
 *
 * 在使用springboot的时候,需要在启动类使用 @ServletComponentScan注解
 */

@SuppressWarnings("serial")
@WebServlet(urlPatterns = "/druid/*",initParams = {
        //@WebInitParam(name = "allow",value = "127.0.0.1,192.168.1.142"),//白名单
        @WebInitParam(name = "deny",value = "126.12.22.1"),//黑名单 (存在共同时，deny优先于allow)
        @WebInitParam(name="loginUsername",value="admin"),// 用户名
        @WebInitParam(name="loginPassword",value="123456"),// 密码
        @WebInitParam(name="resetEnable",value="false")// 禁用HTML页面上的“Reset All”功能


})


public class DruidStateViewServlet extends StatViewServlet {
}
package com.sorata.datasource.config;

import com.alibaba.druid.support.http.WebStatFilter;

import javax.servlet.annotation.WebFilter;
import javax.servlet.annotation.WebInitParam;

/**
 * Created by: guobo 17/1/19 下午5:27
 *
 * alibaba druid的过滤
 */
@WebFilter(filterName="druidWebStatFilter",urlPatterns="/*",
        initParams={
                @WebInitParam(name="exclusions",value="*.mp4,*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*")// 忽略资源
        })
public class DruidStatFilter extends WebStatFilter {

}

    8）application.yml

druid:
  datasource:
    name: mydatabases
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false
    username: root
    password: root
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.jdbc.Driver
    filters: stat,wall,log4j
    maxActive: 20
    initialSize: 1
    maxWait: 60000
    minIdle: 1
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: select 'x'
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: false  #如果是oracle 则可以设置为true  如果是mysql则设置false 分库分表较多的数据库，建议配置为false。
    maxOpenPreparedStatements: 20

custom:
  datasource:
      name: mydatabases2
      url: jdbc:mysql://localhost:3306/test1?useUnicode=true&characterEncoding=utf-8&useSSL=false
      username: root
      password: root
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.jdbc.Driver
      filters: stat,wall,log4j
      maxActive: 20
      initialSize: 1
      maxWait: 60000
      minIdle: 1
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      validationQuery: select 'x'
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      poolPreparedStatements: false  #如果是oracle 则可以设置为true  如果是mysql则设置false 分库分表较多的数据库，建议配置为false。
      maxOpenPreparedStatements: 20


pagehelper:
    helperDialect: mysql
    reasonable: true
    supportMethodsArguments: true
    params: count=countSql

     3.总结

        引入通用Mapper的依赖，与PageHelper的依赖之后，它们都是有自动的配置，Pagehelper在多数据源上有自动配置所以不需要在配置SqlSessionFactory的时候添加插件，添加的话会报添加多个分页插件的异常。而且使用ChainedTransactionManager管理多个事务管理器后不需要再显示的注明从库的管理器。

    附：

    Pagehelper:https://github.com/pagehelper/Mybatis-PageHelper

    Mapper:http://git.oschina.net/free/Mapper/blob/master/wiki/mapper3/2.Integration.md

    本项目：http://git.oschina.net/lencer93/mybatis-pagehelper-mapper

转载于:https://my.oschina.net/swiftloop/blog/1036943

上一篇： [LeetCode]Merge Two Sorted Lists(Python)

下一篇： 【LeetCode】4. Median of Two Sorted Arrays

推荐阅读