前言
本文实现的是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监控