SpringBoot+mysql+mybatis+druid+pagehelper 多数据源整合

最新推荐文章于 2021-05-14 14:54:46 发布

芬达_fenda

最新推荐文章于 2021-05-14 14:54:46 发布

阅读量462

点赞数 1

分类专栏： Java 文章标签： Java SpringBoot Druid Mybatis Pagehelper

本文链接：https://blog.csdn.net/weixin_38538285/article/details/103026425

版权

Java 专栏收录该内容

15 篇文章 7 订阅

订阅专栏

前言

本文实现的是springboot、mybatis、druid、pagehelper 、mysql的多数据源整合。实现多数据源和分页。
springboot 版本为 2.2.1.RELEASE

maven依赖

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<java.version>1.8</java.version>
		<mysql.version>5.1.36</mysql.version>
		<swagger.version>2.6.1</swagger.version>
	</properties>

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

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>${mysql.version}</version>
			<scope>runtime</scope>
		</dependency>

		<!--多数据源事务管理-->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jta-atomikos</artifactId>
		</dependency>

		<!--引入mybatis-->
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.3.0</version>
		</dependency>

		<!--springboot自动配置文件解析注入-->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-configuration-processor</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-lang3</artifactId>
			<version>3.4</version>
		</dependency>

		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-core</artifactId>
		</dependency>
		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-databind</artifactId>
		</dependency>
		<dependency>
			<groupId>com.fasterxml.jackson.datatype</groupId>
			<artifactId>jackson-datatype-joda</artifactId>
		</dependency>
		<dependency>
			<groupId>com.fasterxml.jackson.module</groupId>
			<artifactId>jackson-module-parameter-names</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-aop</artifactId>
		</dependency>

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

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

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

		<!-- atomikos transaction management -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jta-atomikos</artifactId>
		</dependency>

		<!-- swagger -->
		<dependency>
			<groupId>io.springfox</groupId>
			<artifactId>springfox-swagger2</artifactId>
			<version>${swagger.version}</version>
		</dependency>
		<dependency>
			<groupId>io.springfox</groupId>
			<artifactId>springfox-swagger-ui</artifactId>
			<version>${swagger.version}</version>
		</dependency>
		<!-- swagger -->

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
			<exclusions>
				<exclusion>
					<groupId>org.junit.vintage</groupId>
					<artifactId>junit-vintage-engine</artifactId>
				</exclusion>
			</exclusions>
		</dependency>
	</dependencies>

文件详情

公共文件
application.yml：配置文件 DataSourceInfo.java：获取yml文件配置信息的实体类
DruidConfiguration.java: druid数据库连接池配置类 MybatisConfigAbstract.java:
mybatis配置抽象类 DawnController.java:测试的controller

主数据文件：
RingMybatisConfig.java SysPermissionService.java
SysPermissionServiceImpl.java SysPermission.java
SysPermissionMapper.java SysPermissionMapper.xml

第二个数据源文件
FlowerMybatisConfig.java TbContentService.java
TbContentServiceImpl.java TbContent.java TbContentMapper.java
TbContentMapper.xml

在这里插入图片描述

代码详情

application.yml

server:
  port: 8085
  servlet:
    context-path: /dawn

spring:
  datasource:
    ring:
      #监控统计拦截的filters
      filters: stat,wall,slf4j
      #驱动
      driverClassName: com.mysql.jdbc.Driver
      #连接数据库的url
      url: jdbc:mysql://localhost:3306/ring?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
      #连接数据库的账号
      username: root
      #连接数据库的密码
      password: 1234
      #初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时
      initialSize: 1
       #最小连接池数量
      minIdle: 1
      #最大连接池数量
      maxActive: 20
      #获取连接池最大等待时间，单位毫秒
      maxwait: 60000
      #间隔多久进行一次检测，检测需要关闭的空闲连接
      timeBetweenEvictionRunsMillis: 60000
      #连接保持空闲而不被驱逐的最长时间
      minEvictableIdleTimeMillis: 300000
      #用来检测连接是否是有效的SQL，要求是一个查询语句
      validationQuery: SELECT 'x'
      #申请连接的时候检测，如果空闲时间大于time-between-eviction-runs-millis,执行validation检测连接是否有效
      testWhileIdle: true
      #申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
      testOnBorrow: false
      #归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
      testOnReturn: false
      #是否缓存preparedStatement,即PSCache
      poolPreparedStatements: false
      #要启用PSCache，必须配置大于0，当大于0时，pool-prepared-statements自动触发修改为true
      maxPoolPreparedStatementPerConnectionSize: 20
      # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
      # connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

    flower:
      #监控统计拦截的filters
      filters: stat,wall,slf4j
      #驱动
      driverClassName: com.mysql.jdbc.Driver
      #连接数据库的url
      url: jdbc:mysql://localhost:3306/flower?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
      #连接数据库的账号
      username: root
      #连接数据库的密码
      password: 1234
      #初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时
      initialSize: 1
       #最小连接池数量
      minIdle: 1
      #最大连接池数量
      maxActive: 20
      #获取连接池最大等待时间，单位毫秒
      maxwait: 60000
      #间隔多久进行一次检测，检测需要关闭的空闲连接
      timeBetweenEvictionRunsMillis: 60000
      #连接保持空闲而不被驱逐的最长时间
      minEvictableIdleTimeMillis: 300000
      #用来检测连接是否是有效的SQL，要求是一个查询语句
      validationQuery: SELECT 'x'
      #申请连接的时候检测，如果空闲时间大于time-between-eviction-runs-millis,执行validation检测连接是否有效
      testWhileIdle: true
      #申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
      testOnBorrow: false
      #归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
      testOnReturn: false
      #是否缓存preparedStatement,即PSCache
      poolPreparedStatements: false
      #要启用PSCache，必须配置大于0，当大于0时，pool-prepared-statements自动触发修改为true
      maxPoolPreparedStatementPerConnectionSize: 20
      # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
      # connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

pagehelper:
  #数据库的方言
  helper-dialect: mysql
  #启用合理化，如果pageNum < 1会查询第一页，如果pageNum > pages会查询最后一页
  reasonable: true
  #是否将参数offset作为PageNum使用
  offset-as-page-num: true
  #是否进行count查询
  row-bounds-with-count: true

DataSourceInfo.java

属性名对应application.yml配置的数据库相关的属性名

package com.lh.dawn.config.db.properties;

import lombok.Data;

@Data
public class DataSourceInfo {

    /**
     * 连接数据库的url
     */
    private String url;

    /**
     * 连接数据库的账号
     */
    private String username;

    /**
     * 连接数据库的密码
     */
    private String password;

    /**
     * 驱动
     */
    private String driverClassName;

    /**
     * #监控统计拦截的filters
     */
    private String filters;

    /**
     * 初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时
     */
    private int initialSize;

    /**
     * 最小连接池数量
     */
    private int minIdle;

    /**
     * 最大连接池数量
     */
    private int maxActive;

    /**
     * 获取连接池最大等待时间，单位毫秒
     */
    private int maxwait;

    /**
     * 间隔多久进行一次检测，检测需要关闭的空闲连接
     */
    private int timeBetweenEvictionRunsMillis;

    /**
     * 连接保持空闲而不被驱逐的最长时间
     */
    private int minEvictableIdleTimeMillis;

    /**
     * 用来检测连接是否是有效的SQL，要求是一个查询语句
     */
    private String validationQuery;

    /**
     * 申请连接的时候检测，如果空闲时间大于time-between-eviction-runs-millis,执行validation检测连接是否有效
     */
    private boolean testWhileIdle;

    /**
     * 申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
     */
    private boolean testOnBorrow;

    /**
     * 归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
     */
    private boolean testOnReturn;

    /**
     * 是否缓存preparedStatement,即PSCache
     */
    private boolean poolPreparedStatements;

    /**
     * 要启用PSCache，必须配置大于0，当大于0时，pool-prepared-statements自动触发修改为true
     */
    private int maxPoolPreparedStatementPerConnectionSize;

    /**
     * 通过connectProperties属性来打开mergeSql功能；慢SQL记录
     */
    private String connectionProperties;
}

DruidConfiguration.java

druid不仅仅是数据库连接池，还可以监控数据库访问性能、获得SQL执行日志等
druid监控相关的配置

package com.lh.dawn.config.db;

import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import lombok.extern.slf4j.Slf4j;
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;

@Slf4j
@Configuration
public class DruidConfiguration {

    @Bean
    public ServletRegistrationBean druidStatViewServle() {
        //注册服务
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        // 白名单(为空表示,所有的都可以访问,多个IP的时候用逗号隔开)
        servletRegistrationBean.addInitParameter("allow", "127.0.0.1");
        // IP黑名单 (存在共同时，deny优先于allow)
        servletRegistrationBean.addInitParameter("deny", "127.0.0.2");
        // 设置登录的用户名和密码
        servletRegistrationBean.addInitParameter("loginUsername", "admin");
        servletRegistrationBean.addInitParameter("loginPassword", "123456");
        // 是否能够重置数据.
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        return servletRegistrationBean;
    }

    @Bean
    public FilterRegistrationBean druidStatFilter() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
        // 添加过滤规则
        filterRegistrationBean.addUrlPatterns("/*");
        // 添加不需要忽略的格式信息
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        log.info("druid初始化成功!");
        return filterRegistrationBean;
    }
}

MybatisConfigAbstract.java

package com.lh.dawn.config.db;

import com.alibaba.druid.pool.DruidDataSource;
import com.lh.dawn.config.db.properties.DataSourceInfo;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

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

public abstract class MybatisConfigAbstract {

    abstract DataSourceInfo dataSourceConfig();

    DataSource getDataSource() throws SQLException {
        DataSourceInfo dataSourceInfo = dataSourceConfig();
        DruidDataSource druid = new DruidDataSource();
        // 监控统计拦截的filters
        druid.setFilters(dataSourceInfo.getFilters());

        // 配置基本属性
        druid.setDriverClassName(dataSourceInfo.getDriverClassName());
        druid.setUsername(dataSourceInfo.getUsername());
        druid.setPassword(dataSourceInfo.getPassword());
        druid.setUrl(dataSourceInfo.getUrl());

        //初始化时建立物理连接的个数
        druid.setInitialSize(dataSourceInfo.getInitialSize());
        //最大连接池数量
        druid.setMaxActive(dataSourceInfo.getMaxActive());
        //最小连接池数量
        druid.setMinIdle(dataSourceInfo.getMinIdle());
        //获取连接时最大等待时间，单位毫秒。
        druid.setMaxWait(dataSourceInfo.getMaxwait());
        //间隔多久进行一次检测，检测需要关闭的空闲连接
        druid.setTimeBetweenEvictionRunsMillis(dataSourceInfo.getTimeBetweenEvictionRunsMillis());
        //一个连接在池中最小生存的时间
        druid.setMinEvictableIdleTimeMillis(dataSourceInfo.getMinEvictableIdleTimeMillis());
        //用来检测连接是否有效的sql
        druid.setValidationQuery(dataSourceInfo.getValidationQuery());
        //建议配置为true，不影响性能，并且保证安全性。
        druid.setTestWhileIdle(dataSourceInfo.isTestWhileIdle());
        //申请连接时执行validationQuery检测连接是否有效
        druid.setTestOnBorrow(dataSourceInfo.isTestOnBorrow());
        druid.setTestOnReturn(dataSourceInfo.isTestOnReturn());
        //是否缓存preparedStatement，也就是PSCache，oracle设为true，mysql设为false。分库分表较多推荐设置为false
        druid.setPoolPreparedStatements(dataSourceInfo.isPoolPreparedStatements());
        // 打开PSCache时，指定每个连接上PSCache的大小
        druid.setMaxPoolPreparedStatementPerConnectionSize(dataSourceInfo.getMaxPoolPreparedStatementPerConnectionSize());
        return druid;
    }

    SqlSessionFactory getSqlSessionFactory(final DataSource datasource,
                                           final String mappingFolder,
                                           final String basePackage) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(datasource);// 设置数据源bean
        bean.setTypeAliasesPackage(basePackage);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mappingFolder));// 设置mybatis的xml所在位置
        return bean.getObject();
    }

}

RingMybatisConfig.java

@Primary ：标志这个 Bean 如果在多个同类 Bean 候选时，该 Bean
优先被考虑。多数据源配置的时候注意，必须要有一个主数据源，用 @Primary 标志该 Bean。

@MapperScan：扫描 Mapper 接口并容器管理。

sqlSessionFactoryRef 表示定义一个唯一 SqlSessionFactory 实例。

package com.lh.dawn.config.db;

import com.lh.dawn.config.db.properties.DataSourceInfo;
import org.apache.ibatis.session.SqlSessionFactory;
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.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

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

@Configuration
@MapperScan(basePackages = "com.lh.dawn.dao.ring", sqlSessionFactoryRef = "ringSqlSessionFactory")
public class RingMybatisConfig extends MybatisConfigAbstract {

    private static final String MAPPING_FOLDER = "classpath*:mapping/ring/*.xml";
    private static final String BASE_PACKAGE = "com.lh.dawn.entity.ring";


    @Bean("ringDataSourceInfo")
    @ConfigurationProperties(prefix = "spring.datasource.ring")
    @Override
    public DataSourceInfo dataSourceConfig() {
        return new DataSourceInfo();
    }

    @Primary
    @Bean(name = "ringDataSource")
    public DataSource ringDataSource() throws SQLException {
        return this.getDataSource();
    }

    // 表示这个数据源是默认数据源
    @Primary
    @Bean(name = "ringSqlSessionFactory")
    public SqlSessionFactory ringSqlSessionFactory(@Qualifier("ringDataSource") DataSource datasource) throws Exception {
        return this.getSqlSessionFactory(datasource, MAPPING_FOLDER, BASE_PACKAGE);
    }

    // 创建该数据源的事务管理
    @Primary
    @Bean(name = "ringTransactionManager")
    public DataSourceTransactionManager ringTransactionManager(@Qualifier("ringDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Primary
    @Bean("ringSqlSessionTemplate")
    public SqlSessionTemplate ringSqlSessionTemplate(@Qualifier("ringSqlSessionFactory") SqlSessionFactory sessionFactory) {
        return new SqlSessionTemplate(sessionFactory);
    }

}

FlowerMybatisConfig.java

package com.lh.dawn.config.db;

import com.lh.dawn.config.db.properties.DataSourceInfo;
import org.apache.ibatis.session.SqlSessionFactory;
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.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.lh.dawn.dao.flower", sqlSessionFactoryRef = "flowerSqlSessionFactory")
public class FlowerMybatisConfig extends MybatisConfigAbstract {

    private static final String MAPPING_FOLDER = "classpath*:mapping/flower/*.xml";
    private static final String BASE_PACKAGE = "com.lh.dawn.entity.flower";


    @Bean("flowerDataSourceInfo")
    @Override
    @ConfigurationProperties(prefix = "spring.datasource.flower")
    public DataSourceInfo dataSourceConfig() {
        return new DataSourceInfo();
    }

    @Bean(name = "flowerDataSource")
    public DataSource flowerDataSource() throws SQLException {
        return this.getDataSource();
    }

    // 表示这个数据源是默认数据源
    @Bean(name = "flowerSqlSessionFactory")
    public SqlSessionFactory flowerSqlSessionFactory(@Qualifier("flowerDataSource") DataSource datasource) throws Exception {
        return this.getSqlSessionFactory(datasource, MAPPING_FOLDER, BASE_PACKAGE);
    }

    // 创建该数据源的事务管理
    @Bean(name = "flowerTransactionManager")
    public DataSourceTransactionManager flowerTransactionManager(@Qualifier("flowerDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean("flowerSqlSessionTemplate")
    public SqlSessionTemplate flowerSqlSessionTemplate(@Qualifier("flowerSqlSessionFactory") SqlSessionFactory sessionfactory) {
        return new SqlSessionTemplate(sessionfactory);
    }

}

SysPermissionService.java

package com.lh.dawn.service;

import com.lh.dawn.entity.ring.SysPermission;

import java.util.Map;

public interface SysPermissionService {

    SysPermission selectSysPermissionById();

    Map<String, Object> selectList();
}

SysPermissionServiceImpl.java

package com.lh.dawn.service.impl;


import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.lh.dawn.dao.ring.SysPermissionMapper;
import com.lh.dawn.entity.ring.SysPermission;
import com.lh.dawn.service.SysPermissionService;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class SysPermissionServiceImpl implements SysPermissionService {

    @Resource
    private SysPermissionMapper sysPermissionMapper;

    @Override
    public SysPermission selectSysPermissionById() {
        return sysPermissionMapper.selectByPrimaryKey(1);
    }

    @Override
    public Map<String, Object> selectList() {
        Page page = PageHelper.startPage(1, 10);
        List<SysPermission> list = sysPermissionMapper.selectSysPermissionListByParams(null);
        Map<String, Object> result = new HashMap<>();
        result.put("data", list);
        result.put("total", page.getTotal());
        result.put("index", page.getPageNum());
        result.put("pageSize", page.getPageSize());
        return result;
    }
}

TbContentMapper.java

package com.lh.dawn.dao.flower;

import com.lh.dawn.entity.flower.TbContent;

import java.util.List;

public interface TbContentMapper {

    TbContent selectByPrimaryKey(Long id);

    List<TbContent> selectList(TbContent tbContent);

}

TbContentMapper.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.lh.dawn.dao.ring.SysPermissionMapper">
    <resultMap id="BaseResultMap" type="com.lh.dawn.entity.ring.SysPermission">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="parent_id" jdbcType="INTEGER" property="parentId"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="url" jdbcType="VARCHAR" property="url"/>
        <result column="icon" jdbcType="VARCHAR" property="icon"/>
        <result column="type" jdbcType="INTEGER" property="type"/>
        <result column="permission" jdbcType="VARCHAR" property="permission"/>
        <result column="rank" jdbcType="INTEGER" property="rank"/>
        <result column="status" jdbcType="INTEGER" property="status"/>
        <result column="creadted_by" jdbcType="VARCHAR" property="creadtedBy"/>
        <result column="creadted_time" jdbcType="TIMESTAMP" property="creadtedTime"/>
        <result column="updated_by" jdbcType="VARCHAR" property="updatedBy"/>
        <result column="updated_time" jdbcType="TIMESTAMP" property="updatedTime"/>
    </resultMap>

    <sql id="Base_Column_List">
        id, parent_id, name, url, icon, type, permission, rank, status, creadted_by, creadted_time, updated_by, updated_time
    </sql>

    <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from sys_permission
        where id = #{id,jdbcType=INTEGER}
    </select>

    <select id="selectSysPermissionListByRoleId" parameterType="java.lang.String" resultMap="BaseResultMap">
        SELECT
            a.id,
            a.parent_id,
            a.NAME,
            a.url,
            a.icon,
            a.type,
            a.permission,
            a.rank,
            a.status,
            a.creadted_by,
            a.creadted_time,
            a.updated_by,
            a.updated_time
        FROM
            sys_permission a
        LEFT JOIN sys_role_permission b
        on a.id = b.permission_id
        where b.role_id = #{roleId,jdbcType=VARCHAR}
    </select>

    <select id="selectSysPermissionListByParams" parameterType="com.lh.dawn.entity.ring.SysPermission" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from sys_permission
        <where>
            <if test="id != null">
                id = #{id,jdbcType=INTEGER}
            </if>
            <if test="parentId != null">
                AND parentId = #{parentId,jdbcType=INTEGER}
            </if>
            <if test="name != null">
                AND name = #{name,jdbcType=VARCHAR}
            </if>
            <if test="url != null">
                AND url = #{url,jdbcType=VARCHAR}
            </if>
            <if test="icon != null">
                AND icon = #{icon,jdbcType=VARCHAR}
            </if>
            <if test="type != null">
                AND type = #{type,jdbcType=INTEGER}
            </if>
            <if test="permission != null">
                AND permission = #{permission,jdbcType=VARCHAR}
            </if>
            <if test="rank != null">
                AND rank = #{rank,jdbcType=INTEGER}
            </if>
            <if test="status != null">
                AND status = #{status,jdbcType=INTEGER}
            </if>
        </where>
    </select>

    <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
        delete from sys_permission
        where id = #{id,jdbcType=INTEGER}
    </delete>

    <insert id="insert" parameterType="com.lh.dawn.entity.ring.SysPermission">
        insert into sys_permission (id, parent_id, name,
        url, icon, type, permission, rank, status, creadted_by,
        creadted_time, updated_by, updated_time
        )
        values (#{id,jdbcType=INTEGER}, #{parentId,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR},
        #{url,jdbcType=VARCHAR}, #{icon,jdbcType=VARCHAR}, #{type,jdbcType=INTEGER}, #{permission,jdbcType=VARCHAR}, #{rank,jdbcType=INTEGER}, #{status,jdbcType=INTEGER},
        #{creadtedBy,jdbcType=VARCHAR}, #{creadtedTime,jdbcType=TIMESTAMP}, #{updatedBy,jdbcType=VARCHAR}, #{updatedTime,jdbcType=TIMESTAMP}
        )
    </insert>

    <insert id="insertSelective" parameterType="com.lh.dawn.entity.ring.SysPermission">
        insert into sys_permission
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">
                id,
            </if>
            <if test="parentId != null">
                parent_id,
            </if>
            <if test="name != null">
                name,
            </if>
            <if test="url != null">
                url,
            </if>
            <if test="icon != null">
                icon,
            </if>
            <if test="type != null">
                type,
            </if>
            <if test="permission != null">
                permission,
            </if>
            <if test="rank != null">
                rank,
            </if>
            <if test="status != null">
                status,
            </if>
            <if test="creadtedBy != null">
                creadted_by,
            </if>
            <if test="creadtedTime != null">
                creadted_time,
            </if>
            <if test="updatedBy != null">
                updated_by,
            </if>
            <if test="updatedTime != null">
                updated_time,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">
                #{id,jdbcType=INTEGER},
            </if>
            <if test="parentId != null">
                #{parentId,jdbcType=INTEGER},
            </if>
            <if test="name != null">
                #{name,jdbcType=VARCHAR},
            </if>
            <if test="url != null">
                #{url,jdbcType=VARCHAR},
            </if>
            <if test="icon != null">
                #{icon,jdbcType=VARCHAR},
            </if>
            <if test="type != null">
                #{type,jdbcType=INTEGER},
            </if>
            <if test="permission != null">
                #{permission,jdbcType=VARCHAR},
            </if>
            <if test="rank != null">
                #{rank,jdbcType=INTEGER},
            </if>
            <if test="status != null">
                #{status,jdbcType=INTEGER},
            </if>
            <if test="creadtedBy != null">
                #{creadtedBy,jdbcType=VARCHAR},
            </if>
            <if test="creadtedTime != null">
                #{creadtedTime,jdbcType=TIMESTAMP},
            </if>
            <if test="updatedBy != null">
                #{updatedBy,jdbcType=VARCHAR},
            </if>
            <if test="updatedTime != null">
                #{updatedTime,jdbcType=TIMESTAMP},
            </if>
        </trim>
    </insert>

    <update id="updateByPrimaryKeySelective" parameterType="com.lh.dawn.entity.ring.SysPermission">
        update sys_permission
        <set>
            <if test="parentId != null">
                parent_id = #{parentId,jdbcType=INTEGER},
            </if>
            <if test="name != null">
                name = #{name,jdbcType=VARCHAR},
            </if>
            <if test="url != null">
                url = #{url,jdbcType=VARCHAR},
            </if>
            <if test="icon != null">
                icon = #{icon,jdbcType=VARCHAR},
            </if>
            <if test="type != null">
                type = #{type,jdbcType=INTEGER},
            </if>
            <if test="permission != null">
                permission = #{permission,jdbcType=VARCHAR},
            </if>
            <if test="rank != null">
                rank = #{rank,jdbcType=INTEGER},
            </if>
            <if test="status != null">
                status = #{status,jdbcType=INTEGER},
            </if>
            <if test="creadtedBy != null">
                creadted_by = #{creadtedBy,jdbcType=VARCHAR},
            </if>
            <if test="creadtedTime != null">
                creadted_time = #{creadtedTime,jdbcType=TIMESTAMP},
            </if>
            <if test="updatedBy != null">
                updated_by = #{updatedBy,jdbcType=VARCHAR},
            </if>
            <if test="updatedTime != null">
                updated_time = #{updatedTime,jdbcType=TIMESTAMP},
            </if>
        </set>
        where id = #{id,jdbcType=INTEGER}
    </update>

    <update id="updateByPrimaryKey" parameterType="com.lh.dawn.entity.ring.SysPermission">
        update sys_permission
        set parent_id = #{parentId,jdbcType=INTEGER},
        name = #{name,jdbcType=VARCHAR},
        url = #{url,jdbcType=VARCHAR},
        icon = #{icon,jdbcType=VARCHAR},
        type = #{type,jdbcType=INTEGER},
        permission = #{permission,jdbcType=VARCHAR},
        rank = #{rank,jdbcType=INTEGER},
        status = #{status,jdbcType=INTEGER},
        creadted_by = #{creadtedBy,jdbcType=VARCHAR},
        creadted_time = #{creadtedTime,jdbcType=TIMESTAMP},
        updated_by = #{updatedBy,jdbcType=VARCHAR},
        updated_time = #{updatedTime,jdbcType=TIMESTAMP}
        where id = #{id,jdbcType=INTEGER}
    </update>
</mapper>

TbContentService.java

package com.lh.dawn.service;

import com.lh.dawn.entity.flower.TbContent;

import java.util.Map;

public interface TbContentService {

    TbContent selectTbContent();

    Map<String, Object> selectList();

}

TbContentServiceImpl.java

package com.lh.dawn.service.impl;

import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.lh.dawn.dao.flower.TbContentMapper;
import com.lh.dawn.entity.flower.TbContent;
import com.lh.dawn.entity.ring.SysPermission;
import com.lh.dawn.service.TbContentService;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class TbContentServiceImpl implements TbContentService {

    @Resource
    private TbContentMapper tbContentMapper;

    @Override
    public TbContent selectTbContent() {
        return tbContentMapper.selectByPrimaryKey(33l);
    }

    @Override
    public Map<String, Object> selectList() {
        Page page = PageHelper.startPage(1, 10);
        List<TbContent> list = tbContentMapper.selectList(null);
        Map<String, Object> result = new HashMap<>();
        result.put("data", list);
        result.put("total", page.getTotal());
        result.put("index", page.getPageNum());
        result.put("pageSize", page.getPageSize());
        return result;
    }
}

TbContentMapper.java

package com.lh.dawn.dao.flower;

import com.lh.dawn.entity.flower.TbContent;

import java.util.List;

public interface TbContentMapper {

    TbContent selectByPrimaryKey(Long id);

    List<TbContent> selectList(TbContent tbContent);

}

TbContentMapper.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.lh.dawn.dao.flower.TbContentMapper">
    <resultMap id="BaseResultMap" type="com.lh.dawn.entity.flower.TbContent">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="category_id" property="categoryId" jdbcType="BIGINT"/>
        <result column="title" property="title" jdbcType="VARCHAR"/>
        <result column="sub_title" property="subTitle" jdbcType="VARCHAR"/>
        <result column="title_desc" property="titleDesc" jdbcType="VARCHAR"/>
        <result column="url" property="url" jdbcType="VARCHAR"/>
        <result column="pic" property="pic" jdbcType="VARCHAR"/>
        <result column="pic2" property="pic2" jdbcType="VARCHAR"/>
        <result column="created" property="created" jdbcType="TIMESTAMP"/>
        <result column="updated" property="updated" jdbcType="TIMESTAMP"/>
    </resultMap>

    <sql id="Base_Column_List">
        id, category_id, title, sub_title, title_desc, url, pic, pic2, created, updated
    </sql>

    <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Long">
        select
        <include refid="Base_Column_List"/>
        from tb_content
        where id = #{id,jdbcType=BIGINT}
    </select>
    <select id="selectList" parameterType="com.lh.dawn.entity.flower.TbContent" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from tb_content
        <where>
            <if test="id != null">
                id = #{id,jdbcType=BIGINT}
            </if>
            <if test="title != null">
                AND title = #{title,jdbcType=VARCHAR}
            </if>
            <if test="subTitle != null">
                AND sub_title = #{subTitle,jdbcType=VARCHAR}
            </if>
        </where>
    </select>

</mapper>

DawnController.java

package com.lh.dawn.controller;

import com.lh.dawn.entity.flower.TbContent;
import com.lh.dawn.entity.ring.SysPermission;
import com.lh.dawn.service.SysPermissionService;
import com.lh.dawn.service.TbContentService;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.util.Map;

@RestController
@RequestMapping(value = "/dawn")
public class DawnController {

    @Resource
    private SysPermissionService sysPermissionService;

    @Resource
    private TbContentService tbContentService;

    @RequestMapping(value = "/hello", method = RequestMethod.GET)
    public String hello() {
        return "hello dawn!!!!!!";
    }

    @RequestMapping(value = "/selectSysPermissionById", method = RequestMethod.GET)
    public SysPermission selectSysPermissionById() {
        return sysPermissionService.selectSysPermissionById();
    }

    @RequestMapping(value = "/selectTbContent", method = RequestMethod.GET)
    public TbContent selectTbContent() {
        return tbContentService.selectTbContent();
    }

    @RequestMapping(value = "/selectListRing", method = RequestMethod.GET)
    public Map<String, Object> selectListRing() {
        return sysPermissionService.selectList();
    }

    @RequestMapping(value = "/selectListFlower", method = RequestMethod.GET)
    public Map<String, Object> selectListFlower() {
        return tbContentService.selectList();
    }

}

测试详情

测试一

请求信息在这里插入图片描述
响应信息

{
    "total": 19,
    "data": [
        {
            "id": 1,
            "parentId": 0,
            "name": "系统首页",
            "url": "dashboard",
            "icon": "el-icon-lx-home",
            "type": 0,
            "permission": null,
            "rank": 1,
            "status": 0,
            "creadtedBy": "admin",
            "creadtedTime": "2019-07-05T15:24:21.000+0000",
            "updatedBy": "admin",
            "updatedTime": "2019-07-05T15:24:27.000+0000"
        },
        {
            "id": 2,
            "parentId": 0,
            "name": "基础表格",
            "url": "table",
            "icon": "el-icon-lx-cascades",
            "type": 0,
            "permission": null,
            "rank": 1,
            "status": 1,
            "creadtedBy": "admin",
            "creadtedTime": "2019-07-05T15:25:35.000+0000",
            "updatedBy": "admin",
            "updatedTime": "2019-07-05T15:25:38.000+0000"
        },
        {
            "id": 3,
            "parentId": 0,
            "name": "用户管理",
            "url": "user",
            "icon": "el-icon-lx-people",
            "type": 0,
            "permission": null,
            "rank": 1,
            "status": 1,
            "creadtedBy": "admin",
            "creadtedTime": "2019-07-05T15:26:48.000+0000",
            "updatedBy": "admin",
            "updatedTime": "2019-07-05T15:26:51.000+0000"
        },
        {
            "id": 4,
            "parentId": 0,
            "name": "tab选项卡",
            "url": "tabs",
            "icon": "el-icon-lx-copy",
            "type": 0,
            "permission": null,
            "rank": 1,
            "status": 1,
            "creadtedBy": "admin",
            "creadtedTime": "2019-07-05T15:29:02.000+0000",
            "updatedBy": "admin",
            "updatedTime": "2019-07-05T15:29:05.000+0000"
        },
        {
            "id": 5,
            "parentId": 0,
            "name": "表单相关",
            "url": "3",
            "icon": "el-icon-lx-calendar",
            "type": 1,
            "permission": null,
            "rank": 1,
            "status": 1,
            "creadtedBy": "admin",
            "creadtedTime": "2019-07-05T15:30:56.000+0000",
            "updatedBy": "admin",
            "updatedTime": "2019-07-05T15:31:07.000+0000"
        },
        {
            "id": 6,
            "parentId": 5,
            "name": "基本表单",
            "url": "form",
            "icon": null,
            "type": 0,
            "permission": null,
            "rank": 2,
            "status": 1,
            "creadtedBy": "admin",
            "creadtedTime": "2019-07-05T15:31:56.000+0000",
            "updatedBy": "admin",
            "updatedTime": "2019-07-05T15:32:03.000+0000"
        },
        {
            "id": 7,
            "parentId": 5,
            "name": "三级菜单",
            "url": "3-2",
            "icon": null,
            "type": 1,
            "permission": null,
            "rank": 2,
            "status": 1,
            "creadtedBy": "admin",
            "creadtedTime": "2019-07-05T15:34:04.000+0000",
            "updatedBy": "admin",
            "updatedTime": "2019-07-05T15:34:08.000+0000"
        },
        {
            "id": 8,
            "parentId": 7,
            "name": "富文本编辑器",
            "url": "editor",
            "icon": null,
            "type": 0,
            "permission": null,
            "rank": 3,
            "status": 1,
            "creadtedBy": "admin",
            "creadtedTime": "2019-07-05T15:34:53.000+0000",
            "updatedBy": "admin",
            "updatedTime": "2019-07-05T15:34:57.000+0000"
        },
        {
            "id": 9,
            "parentId": 7,
            "name": "markdown编辑器",
            "url": "markdown",
            "icon": null,
            "type": 0,
            "permission": null,
            "rank": 3,
            "status": 1,
            "creadtedBy": "admin",
            "creadtedTime": "2019-07-05T15:36:02.000+0000",
            "updatedBy": "admin",
            "updatedTime": "2019-07-05T15:36:05.000+0000"
        },
        {
            "id": 10,
            "parentId": 5,
            "name": "文件上传",
            "url": "upload",
            "icon": null,
            "type": 0,
            "permission": null,
            "rank": 2,
            "status": 1,
            "creadtedBy": "admin",
            "creadtedTime": "2019-07-05T15:37:03.000+0000",
            "updatedBy": "admin",
            "updatedTime": "2019-07-05T15:37:06.000+0000"
        }
    ],
    "index": 1,
    "pageSize": 10
}

测试二

请求信息
在这里插入图片描述
响应信息

{
    "total": 4,
    "data": [
        {
            "id": 33,
            "categoryId": 89,
            "title": "男裤",
            "subTitle": "男裤1",
            "titleDesc": "圣诞节",
            "url": "www.baidu.com",
            "pic": "http://192.168.25.133/group1/M00/00/00/wKgZhVtwIQWAO0ggAAFdmfWSMTY091.jpg",
            "pic2": "http://192.168.25.133/group1/M00/00/00/wKgZhVtwIQyAF-bzAAFdmfWSMTY617.jpg",
            "created": "2018-08-12T12:00:24.000+0000",
            "updated": "2018-08-12T12:08:23.000+0000",
            "content": null
        },
        {
            "id": 34,
            "categoryId": 89,
            "title": "鞋",
            "subTitle": "鞋",
            "titleDesc": "山东分开了",
            "url": "www.baidu.com",
            "pic": "http://192.168.25.133/group1/M00/00/00/wKgZhVtwIYiAX54CAAFSgTVIQAw068.jpg",
            "pic2": "http://192.168.25.133/group1/M00/00/00/wKgZhVtwIY6AfKvgAAFSgTVIQAw906.jpg",
            "created": "2018-08-12T12:02:08.000+0000",
            "updated": "2018-08-12T12:02:08.000+0000",
            "content": null
        },
        {
            "id": 35,
            "categoryId": 89,
            "title": "电水壶",
            "subTitle": "电水壶",
            "titleDesc": "大恒科技按时发货",
            "url": "www.baidu.com",
            "pic": "http://192.168.25.133/group1/M00/00/00/wKgZhVtwIeKAbL_3AAH3jbZuoWM576.jpg",
            "pic2": "http://192.168.25.133/group1/M00/00/00/wKgZhVtwIemAHvL_AAH3jbZuoWM784.jpg",
            "created": "2018-08-12T12:04:13.000+0000",
            "updated": "2018-08-12T12:04:13.000+0000",
            "content": null
        },
        {
            "id": 36,
            "categoryId": 89,
            "title": "男裤",
            "subTitle": "男裤2",
            "titleDesc": "司法所了解到廊坊",
            "url": "www.baidu.com",
            "pic": "http://192.168.25.133/group1/M00/00/00/wKgZhVtwIu-AW7KYAAEPcuuKvug100.jpg",
            "pic2": "http://192.168.25.133/group1/M00/00/00/wKgZhVtwIvWAF136AAEPcuuKvug856.jpg",
            "created": "2018-08-12T12:07:27.000+0000",
            "updated": "2018-08-12T12:08:35.000+0000",
            "content": null
        }
    ],
    "index": 1,
    "pageSize": 10
}

druid监控

访问 http://localhost:8085/dawn/druid/login.html
登录，账户密码在DruidConfiguration.java类中设置
在这里插入图片描述
首页

sql监控

芬达_fenda

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SpringBoot+mysql+mybatis+druid+pagehelper 多数据源整合

前言本文实现的是springboot、mybatis、druid、pagehelper 、mysql的多数据源整合。实现多数据源和分页。springboot ：2.2.1.RELEASEmaven依赖 <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding...
复制链接

扫一扫