1、实现多数据源,并且展示druid界面调用sql,以及swagger接口文档展示,最后实现效果如下:
druid地址:Druid SQL Stat
swagger本地地址:Swagger UI
swagger界面:
druid界面展示:
2、实现方式
(1)pom文件引入
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.5.0</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example.demo</groupId> <!--父子模板保持一致-->
<artifactId>springbootProject</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>springbootProject</name>
<packaging>pom</packaging> <!--为了子模块 能引用主pom-->
<modules>
<module>common</module> <!--add了子模块-->
<module>mybatis-learning</module> <!--add了子模块-->
</modules>
<properties>
<java.version>11</java.version>
<spring-cloud.version>2020.0.2</spring-cloud.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
<version>2.5.0</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!--用于RestController注解使用 必须引入该jar-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>2.4.5</version>
</dependency>
<!--swagger 用于界面显示 -->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.9.2</version>
</dependency>
<!--是注解 @EnableSwagger2-->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.9.2</version>
</dependency>
<!--swagger 用于引入 APIOperation Api注解 用于展示接口文档 -->
<dependency>
<groupId>io.swagger</groupId>
<artifactId>swagger-annotations</artifactId>
<version>1.5.12</version>
</dependency>
<!--用于aop切面编程 使用AOP织入,需要导入一个依赖包-->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
</dependency>
<!--用于JSON.toJSonString 方法使用-->
<dependency>
<groupId>com.saygoer</groupId>
<artifactId>fastjson</artifactId>
<version>1.1-unwrap</version>
</dependency>
<!--redis引入-->
<dependency>
<groupId>redis.clients</groupId>
<artifactId>jedis</artifactId>
</dependency>
<!--mybatis支持包 -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.0</version>
</dependency>
<!--jexl引入 支持算法 -->
<dependency>
<groupId>commons-jexl</groupId>
<artifactId>commons-jexl</artifactId>
<version>1.1</version>
</dependency>
<!--用于服务调用的时候熔断机制-->
<dependency>
<groupId>com.netflix.hystrix</groupId>
<artifactId>hystrix-core</artifactId>
<version>1.5.6</version>
</dependency>
<!--Ribbon提供了服务间调用的客户端负载均衡功能, 等同fegin-->
<dependency>
<groupId>com.netflix.ribbon</groupId>
<artifactId>ribbon</artifactId>
<version>2.2.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!-- Oracle驱动包 不添加,就没有驱动oracle.jdbc.OracleDriver-->
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc8</artifactId>
</dependency>
<!-- druid 数据库连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.6</version>
</dependency>
</dependencies>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-dependencies</artifactId>
<version>${spring-cloud.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
</project>
(2)配置文件多数据源配置
创建数据库用户:
1、sqlplus / as sysdba
2、建立账号密码 create user lpp identified by dev;
lpp :新用户名的用户名
dev: 新用户的密码
3.给用户授权:
grant create session,create table,unlimited tablespace to username;
GRANT CREATE SESSION , CREATE TABLE , CREATE SEQUENCE , CREATE VIEW , CREATE PROCEDURE TO lpp;
server.port=8082
#应用名称
spring.application.name=spring-boot-lpp
##使用eureka
spring.datasource.filters: stat,wall,slf4j
##oracle.jdbc.driver.OracleDriver已经过期,所以去掉
master.datasource.driverClassName=oracle.jdbc.OracleDriver
master.datasource.url=jdbc:oracle:thin:@127.0.0.1:1521:DEV
master.datasource.username=lpp
master.datasource.password=dev
cluster.datasource.driverClassName=oracle.jdbc.OracleDriver
cluster.datasource.url=jdbc:oracle:thin:@127.0.0.1:1521:DEV
cluster.datasource.username=HEC
cluster.datasource.password=dev
(3)配置类
package com.example.config;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
import java.sql.SQLException;
/*
刘佩佩
2021/6/17
*/
@Configuration
// 扫描 Mapper 接口并容器管理
@MapperScan(basePackages = ClusterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "clusterSqlSessionFactory")
public class ClusterDataSourceConfig{
static final String MAPPER_LOCATION = "classpath:mapper/cluster/*.xml";
static final String PACKAGE = "com.example.Dao.second"; //mapper文件夹
@Value("${cluster.datasource.url}")
private String url;
@Value("${cluster.datasource.username}")
private String user;
@Value("${cluster.datasource.password}")
private String password;
@Value("${cluster.datasource.driverClassName}")
private String driverClass;
@Value("${spring.datasource.filters}")
private String filters;
@Bean(name = "clusterDataSource")
public DataSource clusterDataSource() throws SQLException {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(user);
dataSource.setPassword(password);
dataSource.setFilters(filters);
return dataSource;
}
@Bean(name = "clusterTransactionManager")
public DataSourceTransactionManager clusterTransactionManager() throws SQLException {
return new DataSourceTransactionManager(clusterDataSource());
}
@Bean(name = "clusterSqlSessionFactory")
public SqlSessionFactory clusterSqlSessionFactory(@Qualifier("clusterDataSource") DataSource clusterDataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(clusterDataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources(ClusterDataSourceConfig.MAPPER_LOCATION));
return sessionFactory.getObject();
}
}
classpath:mapper/cluster/*.xml指的是:用于读取文件resource下面mapper下的cluster下的.xml文件
com.example.Dao.second 指的是:mapper接口类
同理主的配置类如下:
package com.example.config;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.Collections;
import java.util.HashMap;
import java.util.Map;
/*
注入数据源
*/
@Configuration
// @ConfigurationProperties是springboot的注解,通过这个注解,项目可以获取到对应的配置属性,并返回数据源,
@MapperScan(basePackages = MasterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {
static final String PACKAGE = "com.example.Dao.master"; //dao的地址
static final String MAPPER_LOCATION = "classpath:mapper/master/*.xml"; //mybatis。xml地址
@Value("${master.datasource.url}")
private String url;
@Value("${master.datasource.username}")
private String user;
@Value("${master.datasource.password}")
private String password;
@Value("${master.datasource.driverClassName}")
private String driverClass;
@Value("${spring.datasource.filters}")
private String filters;
@Bean(name = "masterDataSource") //方法1:
@Primary
public DataSource masterDataSource() throws SQLException {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(user);
dataSource.setPassword(password);
dataSource.setFilters(filters);
return dataSource;
}
/* 方法1等价于该方法:
@Bean(name = "masterDataSource")
@ConfigurationProperties(prefix = "master.datasource")
@Primary
public DataSource masterDataSource() {
return DataSourceBuilder.create().build();
}
*/
@Bean(name = "masterTransactionManager")
@Primary
public DataSourceTransactionManager masterTransactionManager() throws SQLException {
return new DataSourceTransactionManager(masterDataSource());
}
@Bean(name = "masterSqlSessionFactory")
@Primary
public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(masterDataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources(MasterDataSourceConfig.MAPPER_LOCATION)); //设置XML地址
return sessionFactory.getObject();
}
}
swagger配置类:
.apis(RequestHandlerSelectors.basePackage("com.example")) 指的是:需要扫描的包下面的类
自定义接口文档类的命名
package com.example.config;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;
import springfox.documentation.swagger2.annotations.EnableSwagger2;
@Configuration
@EnableSwagger2
public class SwaggerConfig {
@Bean
public Docket createRestApi() {
return new Docket(DocumentationType.SWAGGER_2)
.apiInfo(apiInfo())
.select()
.apis(RequestHandlerSelectors.basePackage("com.example")) // 配置需要扫描的包路径
.paths(PathSelectors.any())
.build();
}
private ApiInfo apiInfo() {
return new ApiInfoBuilder()
.title("本地系统服务")
.description("提供统一的身份认证与身份验证服务")
.termsOfServiceUrl("http://www.baidu.com")
.version("0.0.1")
.build();
}
}
实现druid展示:
最后输入Druid SQL Stat地址,然后输入自定义的账号密码登录即可,注意在配置中心文件需要添加filler
spring.datasource.filters: stat,wall,slf4j
package com.example.config;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import com.google.common.collect.Maps;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashMap;
import java.util.Map;
/*
刘佩佩
2021/6/25
只是用于界面展示druid ,目前master cluser文件已经读取主副的连接
原文链接:https://blog.csdn.net/lizhiqiang1217/article/details/90573534
*/
@Configuration
public class DruidConfig {
@Bean
public ServletRegistrationBean staticViewServlet() {
ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
Map<String, String> initParams = Maps.newHashMapWithExpectedSize(4);
initParams.put("loginUsername", "admin");
initParams.put("loginPassword", "1234");
initParams.put("allow", "");
initParams.put("deny", "");
bean.setInitParameters(initParams);
return bean;
}
@Bean
public FilterRegistrationBean webStaticFilter() {
FilterRegistrationBean<WebStatFilter> bean = new FilterRegistrationBean<>();
bean.setFilter(new WebStatFilter());
Map<String, String> map = new HashMap<>();
//移除这些监听
map.put(WebStatFilter.PARAM_NAME_EXCLUSIONS, "*.js,*.css,/druid/*,*.gif,*.jpg,*.png");
bean.setInitParameters(map);
//拦截所有请求,全部都要走druid监听
bean.setUrlPatterns(Collections.singletonList("/*"));
return bean;
}
}
AOP配置类
package com.example.Aop;
import com.alibaba.fastjson.JSON;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.*;
import org.springframework.context.annotation.EnableAspectJAutoProxy;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
@Aspect
@Order(1)//指定切面的优先级
@Component
@EnableAspectJAutoProxy //使aspect起作用 自动匹配代理对象
/*
任意公共方法的执行:execution(public * *(..))
任何一个以“set”开始的方法的执行:execution(* set*(..))
AccountService 接口的任意方法的执行:execution(* com.xyz.service.AccountService.*(..))
定义在service包里的任意方法的执行: execution(* com.xyz.service.*.*(..))
定义在service包和所有子包里的任意类的任意方法的执行:execution(* com.xyz.service..*.*(..))
第一个*表示匹配任意的方法返回值execution(* , ..(两个点)表示零个或多个参数,第一个..表示service包及其子包,第二个*表示所有类, 第三个*表示所有方法,第二个..表示方法的任意参数个数
*/
public class LogAspect {
// @Pointcut("execution(* com.example.springbootproject.Controller.TestController.*(..))")//TestController 接口的任意方法的执行
//@Pointcut("execution(* com.example.springbootproject.Controller.*.*(..))")//定义在Controller包里的任意方法的执行
@Pointcut("execution(* com.example.Controller.*.*(..))")//定义在Controller包里所有类,所有的方法的任意方法的执行
public void apilog() {
}
@Before("apilog()")
public void doBefore() {
System.out.println("Before方法执行");
}
@After("apilog()")
public void doAfter() {
System.out.println("after方法执行");
}
@Around("apilog()") //这里必须有返回对象,否则切面接口 void就会出现无返回
public Object logArround(ProceedingJoinPoint joinPoint) throws Throwable {
Object[] args = joinPoint.getArgs();
System.out.println("环绕前入参:"+JSON.toJSONString(args[0]));
System.out.println(joinPoint.getSignature());
Object proceed = joinPoint.proceed();
System.out.println("出参:"+JSON.toJSONString(proceed));
System.out.println("环绕后");
return proceed;
}
}
(4)Mapper类
package com.example.Dao.master;
import com.example.Response.Student;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
public interface StudentMapper {
List<Student> getStudent(@Param("name") String name);
void insertStudent(Student student);
void updateStudent (Student student);
void deleteStudent(@Param("id") int id);
}
(5)、xml文件指定对应上面的Dao层 mapper接口类
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.Dao.master.StudentMapper">
<select id="getStudent" resultType="com.example.Response.Student" parameterType="string">
select * from student where name = #{name}
</select>
<insert id="insertStudent" parameterType="com.example.Response.Student">
insert into student (id,name,age,course) values(student_seq.Nextval,#{name,jdbcType=VARCHAR},#{age,jdbcType=NUMERIC},#{course,jdbcType=VARCHAR})
</insert>
<update id="updateStudent" parameterType="com.example.Response.Student">
update student a set a.name=#{name},a.age=#{age},a.course=#{course} where a.id=#{id}
</update>
<delete id="deleteStudent">
delete from student a where a.id=#{id}
</delete>
</mapper>
(6)、controller层,这里可以用@Resource注入到该类
package com.example.Controller;
import com.example.Response.Student;
import com.example.Service.DoService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;
import javax.annotation.Resource;
@Api(tags = "本地接口文档", value = "/aa", description = "本地接口文档")
@RestController
public class Controller {
@Resource
private DoService doService;
@ApiOperation(value = "新增学生", httpMethod = "POST", notes = "新增学生信息")
@PostMapping("/addStudent")
public void insertStudent(@RequestBody @Validated Student student) {
doService.insertStudent(student);
}
@ApiOperation(value = "修改学生", httpMethod = "POST", notes = "修改学生信息")
@PostMapping("/updateStudent")
public void updateStudent(@RequestBody @Validated Student student) {
doService.updateStudent(student);
}
@ApiOperation(value = "删除学生信息", httpMethod = "POST")
@PostMapping("/deleteStudent/{id}")
public void deleteStudent(@PathVariable(value = "id") int id) {
doService.deleteStudent(id);
}
}
测试:
postman调用
通过切面控制打印日志:
git地址:
GitHub - liupeipei1/lpp-multiple-moudle: 主要本地使用springboot+springcloud框架
欢迎一起探讨~