描述：java使用springBoot+layui框架，从前端页面传值到后端，根据传参实现动态切换数据源，查询数据库所有的表。
具体需求是：项目启动后，进入页面，在页面中填写数据库地址、数据库类型、数据库驱动名、数据库用户名和密码，先查询该数据库下所有表，分页展示。
前端页面展示：
首先选择数据库类型，数据库驱动根据数据库类型变化，选择mysql，则数据库驱动即为com.mysql.jdbc.Driver ，填写数据库url，用户名和密码，点击查询，后端实现查询逻辑，即可查询该数据库下所有表。代码如下：
前端layui页面代码：
<table class="layui-table" id="genTable" lay-filter="genTable"></table>
<button id="btnSelect" class="layui-btn icon-btn">查询</button>
var dbTypeTemp = 'mysql'
// 点击查询，获取数据库表数据，然后才能生成代码
$('#btnSelect').click(function () {
var driverClassName = $("#driverName").val();
var dburl = $("#url").val();
var username = $("#userName").val();
var password = $("#password").val();
var dbtype = dbTypeTemp;
if (driverClassName==""){
layer.msg('请填写数据库驱动', {icon: 5});
return false;
}
if (dburl==""){
layer.msg('请填写数据库地址', {icon: 5});
return false;
}
if (username==""){
layer.msg('请填写数据库账号', {icon: 5});
return false;
}
if (password==""){
layer.msg('请填写数据库密码', {icon: 5});
return false;
}
table.render({
elem: '#genTable',
url: '/list',
page: true,
where:{
'driverClassName': driverClassName,
'url': dburl,
'username': username,
'password': password,
'dbtype': dbtype
},
cols: [[
{type: 'numbers'},
{type: 'checkbox'},
{field: 'tableName', sort: true, title: '表名'},
{field: 'comments', sort: true, title: '表备注'}
]],
parseData: function(res){ //res 即为原始返回的数据
if(res.code===0){
return {
"code": res.data.code, //解析接口状态
"msg": res.msg, //解析提示文本
"count": res.data.count, //解析数据长度
"data": res.data.data //解析数据列表
};
}else {
return {
"code": res.code, //解析接口状态
"msg": res.msg, //解析提示文本
};
}
},
done:function (res){
if (res.code === 0) {
layer.msg(res.msg, {icon: 1, time: 5500});
} else {
layer.msg(res.msg, {icon: 2, time: 5500});
}
}
});
});后端java代码，会根据前端传值的数据库类型来动态切换数据源，通过设置默认数据源配置，然后继承AbstractRoutingDataSource类，通过determineCurrentLookupKey方法实现动态切换数据源。具体代码如下。
1.首先是pox.xml
<properties>
<java.version>1.8</java.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<springboot.version>2.6.7</springboot.version>
<druid.version>1.1.18</druid.version>
<!-- mybatis-plus -->
<mybatisplus.version>3.4.2</mybatisplus.version>
<!-- mybatis-plus-generator -->
<mybatisplus.generator.version>3.4.1</mybatisplus.generator.version>
<org.mapstruct.version>1.4.2.Final</org.mapstruct.version>
<commons.io.version>2.5</commons.io.version>
<!-- 达梦版本号-->
<dm.version>1.0.1</dm.version>
<!-- 人大金仓版本号 -->
<kingbase.verison>8.2.0</kingbase.verison>
<mysql.version>8.0.29</mysql.version>
<beetl.version>1.2.16.RELEASE</beetl.version>
<velocity.version>2.1</velocity.version>
</properties>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>${mybatisplus.version}</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>${mybatisplus.generator.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-tomcat</artifactId>
<scope>provided</scope>
</dependency>
<!-- alibaba的druid数据库连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>${druid.version}</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>${commons.io.version}</version>
</dependency>
<!-- dm驱动 -->
<dependency>
<groupId>com.dm</groupId>
<artifactId>dm8</artifactId>
<version>${dm.version}</version>
</dependency>
<!-- 人大金仓 -->
<dependency>
<groupId>com.kingbase</groupId>
<artifactId>kingbase8</artifactId>
<version>${kingbase.verison}</version>
</dependency>
<!-- mysql驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
<version>${mysql.version}</version>
</dependency>
<dependency>
<groupId>com.ibeetl</groupId>
<artifactId>beetl-framework-starter</artifactId>
<version>${beetl.version}</version>
</dependency>
<!-- 源代码生成:velocity可以用来基于模板生成java源代码-->
<dependency>
<groupId>org.apache.velocity</groupId>
<artifactId>velocity-engine-core</artifactId>
<version>${velocity.version}</version>
</dependency>
2. springBoot启动类，exclude={DataSourceAutoConfiguration.class}意思：禁止
SpringBoot
自动注入数据源配置。application.yaml中其实还是需要配置数据库的连接信息的。
spring:
datasource:
dbtype: mysql
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/dbname?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai
username: root
password: root@SpringBootApplication(exclude={DataSourceAutoConfiguration.class})
public class GeneratorApplication extends SpringBootServletInitializer {
private final static Logger logger = LoggerFactory.getLogger(GeneratorApplication.class);
public static void main(String[] args) {
SpringApplication.run(GeneratorApplication.class, args);
logger.info("============服务启动完成!=============");
}
/**
* 为了打包springboot项目部署tomcat
*/
@Override
protected SpringApplicationBuilder configure(
SpringApplicationBuilder builder) {
return builder.sources(this.getClass());
}
}
3.数据源配置类和动态切换数据源实现类
//数据源默认配置类
@Configuration
//事务管理，数据库连接这里涉及到事务的提交
@EnableTransactionManagement
public class DataSourceConfig {
// 动态注入数据库信息
@Value("${spring.datasource.url}")
private String url;
@Value("${spring.datasource.username}")
private String username;
@Value("${spring.datasource.password}")
private String password;
@Value("${spring.datasource.driver-class-name}")
private String driverClassName;
@Value("${spring.datasource.dbtype}")
private String dataType;
// 创建DynamicDataSource的bean交给SpringIOC容器管理
@Bean(name = "dynamicDataSource")
public DynamicDataSource dataSource() {
// 配置默认数据源
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(url);
datasource.setUsername(username);
datasource.setPassword(password);
datasource.setDriverClassName(driverClassName);
datasource.setTestWhileIdle(false);
datasource.setName("default");
DynamicDataSource dynamicDataSource = new DynamicDataSource();
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put("defaultDataSource",datasource);
dynamicDataSource.setTargetDataSources(targetDataSources);
// 将该数据源设置成默认数据源
dynamicDataSource.setDefaultTargetDataSource(datasource);
return dynamicDataSource;
}
}
//动态切换数据源具体实现类
@EnableTransactionManagement //涉及到数据源一定要加事务管理注解
public class DynamicDataSource extends AbstractRoutingDataSource {
// 通过ThreadLocal线程隔离的优势线程存储线程，当前线程只能操作当前线程的局部变量
private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
// 把已有的数据源封装在Map里
private Map<Object, Object> dynamicTargetDataSources = new HashMap<>();
//通过重写AbstractRoutingDataSource的内置函数，来通过当前连接的数据源的key，进行数据源的获取
@Override
protected Object determineCurrentLookupKey() {
if (StringUtils.isEmpty(getDataSource())) {
return "default";
}
return getDataSource();
}
// 设置默认数据源（必须要有，否则无法启动）
@Override
public void setDefaultTargetDataSource(Object defaultTargetDataSource) {
super.setDefaultTargetDataSource(defaultTargetDataSource);
}
// 通过设置数据源
@Override
public void setTargetDataSources(Map<Object, Object> targetDataSources) {
super.setTargetDataSources(targetDataSources);
}
// 切换数据源,更改ThreadLocal中的局部变量
public static void setDataSource(String dataSource) {
contextHolder.set(dataSource);
}
// 获取数据源
public static String getDataSource() {
return contextHolder.get();
}
// 删除数据源(每次切换数据源的时候都应先移除已有数据源)
public static void clearDataSource() {
contextHolder.remove();
}
/**
* 创建一个新的数据源连接，并且设置此数据源为我们要用的数据源
*
* @param genConfig
* @return
* @throws NoSuchFieldException
* @throws IllegalAccessException
*/
public boolean changeDataSource(GenConfig genConfig) throws NoSuchFieldException, IllegalAccessException {
String url = genConfig.url;
String driverClassName = genConfig.driverClassName;
String username = genConfig.username;
String password = genConfig.password;
String dbtype = genConfig.dbtype;
String dbName;
if ("oracle".equals(dbtype)) {
dbName = url.substring(url.lastIndexOf(":") + 1);
} else {
dbName = url.substring(url.lastIndexOf("/") + 1);
}
// 测试连接
testConnection(driverClassName, url, username, password);
// 通过Druid数据库连接池连接数据库
DruidDataSource dataSource = new DruidDataSource();
//接收前端传递的参数并且注入进去
dataSource.setName(dbName);
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
dataSource.setDriverClassName(driverClassName);
// 设置最大连接等待时间
dataSource.setMaxWait(4000);
// 数据源初始化
try {
dataSource.init();
} catch (SQLException e) {
// 创建失败则抛出异常
throw new RuntimeException();
}
//获取当前数据源的键值对存入Map
this.dynamicTargetDataSources.put(dbtype, dataSource);
// 设置数据源
this.setTargetDataSources(this.dynamicTargetDataSources);
// 解析数据源
super.afterPropertiesSet();
// 切换数据源
setDataSource(dbtype);
/*
** 修改mybatis的数据源
* ！！！重要，不修改mybatis的数据源的话，
* 即使切换了数据源之后还是会出现默认数据源的情况
*/
SqlSessionFactory sqlSessionFactory = (SqlSessionFactory) SpringContextUtils.getBean(SqlSessionFactory.class);
Configuration configuration = sqlSessionFactory.getConfiguration();
/*
* ！！！重要，设置databaseId,用于在mapper.xml中找到对应的_databaseId,
* 此处不设置databaseId，即使数据源切换了，但是在mapper.xml中还是对应不上
*/
configuration.setDatabaseId(dbtype);
Environment environment = configuration.getEnvironment();
Field dataSourceField = environment.getClass().getDeclaredField("dataSource");
//跳过检验
dataSourceField.setAccessible(true);
//修改mybatis的数据源
dataSourceField.set(environment, dataSource);
//修改完成后所有线程使用此数据源
return true;
}
// 测试数据源连接的方法
public void testConnection(String driveClass, String url, String username, String password) {
try {
Class.forName(driveClass);
DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException();
}
}
}
/**
* 代码生成的查询参数
*/
public class GenConfig {
/**
* 项目地址
*/
private String projectPath;
/**
* 作者
*/
private String author;
/**
* 项目的包
*/
private String projectPackage;
/**
* 核心模块的包
*/
private String corePackage;
/**
* 表名称
*/
private String tableName;
/**
* 忽略的表前缀
*/
private String ignoreTabelPrefix;
/**
* 业务名称
*/
private String bizName;
/**
* 模块名
*/
private String moduleName;
/**
* 父级菜单名称
*/
private String parentMenuName;
/**
* driver-class
*/
public String driverClassName;
/**
* 数据库url
*/
public String url;
/**
* 数据库用户名
*/
public String username;
/**
* 数据库密码
*/
public String password;
/**
* 数据库类型：mysql、oracle、dm、kingbasees、h2、sqlserver、postgresql等
*/
public String dbtype;
public String getProjectPath() {
return projectPath;
}
public void setProjectPath(String projectPath) {
this.projectPath = projectPath;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public String getCorePackage() {
return corePackage;
}
public void setCorePackage(String corePackage) {
this.corePackage = corePackage;
}
public String getProjectPackage() {
return projectPackage;
}
public void setProjectPackage(String projectPackage) {
this.projectPackage = projectPackage;
}
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public String getIgnoreTabelPrefix() {
return ignoreTabelPrefix;
}
public void setIgnoreTabelPrefix(String ignoreTabelPrefix) {
this.ignoreTabelPrefix = ignoreTabelPrefix;
}
public String getBizName() {
return bizName;
}
public void setBizName(String bizName) {
this.bizName = bizName;
}
public String getModuleName() {
return moduleName;
}
public void setModuleName(String moduleName) {
this.moduleName = moduleName;
}
public String getParentMenuName() {
return parentMenuName;
}
public void setParentMenuName(String parentMenuName) {
this.parentMenuName = parentMenuName;
}
public String getDriverClassName() {
return driverClassName;
}
public void setDriverClassName(String driverClassName) {
this.driverClassName = driverClassName;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getDbtype() {
return dbtype;
}
public void setDbtype(String dbtype) {
this.dbtype = dbtype;
}
}
4. controller
package com.generator.controller;
import cn.hutool.core.io.FileUtil;
import com.common.config.DynamicDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.Map;
@Controller
@RequestMapping("/genCode")
public class GeneratorController extends BaseController {
private String PREFIX = "module/generator/";
@Autowired
private IGeneratorService generatorService;
@Autowired
private DynamicDataSource dynamicDataSource;
@Value("d:\generator\code")//#代码生成路径,此处是从application.yml中读取的
private String codePath;
@Value("d:\generator\code.zip") //#压缩包位置
private String codeZipPath;
/**
* 跳转到代码生成首页
*/
@RequestMapping("")
public String generator(Model model, HttpServletRequest request) {
System.out.println("进入代码生成页面");
model.addAttribute("projectPath", codePath);
return PREFIX + "gen";
}
/**
* 动态切换数据源方法，根据数据库连接信息查询数据库中的表信息
*/
@RequestMapping(value = "/list")
@ResponseBody
public JsonResult list(@RequestParam Map<String, Object> params) throws NoSuchFieldException, IllegalAccessException, SQLException {
Query query = new Query(params);
if (query.containsKey("driverClassName") && StringUtils.isNotBlank(query.get("driverClassName").toString())
&& query.containsKey("url") && StringUtils.isNotBlank(query.get("url").toString())
&& query.containsKey("username") && StringUtils.isNotBlank(query.get("username").toString())
&& query.containsKey("password") && StringUtils.isNotBlank(query.get("password").toString())
&& query.containsKey("dbtype") && StringUtils.isNotBlank(query.get("dbtype").toString())
) {
GenConfig genConfig = new GenConfig();
genConfig.setDbtype(query.get("dbtype").toString());
genConfig.setDriverClassName(query.get("driverClassName").toString());
genConfig.setUrl(query.get("url").toString());
genConfig.setUsername(query.get("username").toString());
genConfig.setPassword(query.get("password").toString());
//切换数据源之前先清空
DynamicDataSource.clearDataSource();
//切换数据源
dynamicDataSource.changeDataSource(genConfig);
System.out.println("当前数据源:" + dynamicDataSource.getConnection());
}
return JsonResult.ok(0, "查询成功", generatorService.queryList(new Query(params)));
}
/**
* 生成代码
*/
@RequestMapping(value = "/generate", method = RequestMethod.POST)
@ResponseBody
public void generate(GenConfig genConfig, HttpServletRequest request, HttpServletResponse response) throws Exception {
GeneratorConfig webGeneratorConfig = new GeneratorConfig(genConfig);
webGeneratorConfig.doMpGeneration(request, response);
}
/**
* 下载代码
*/
@RequestMapping(value = "/download")
public void download(HttpServletRequest request, HttpServletResponse response) throws Exception {
String filePath = codeZipPath;
String path = codePath;
File file = new File(filePath);
String fileName = file.getName();
InputStream fis;
try {
fis = new FileInputStream(file);
request.setCharacterEncoding("UTF-8");
response.reset();
response.setCharacterEncoding("UTF-8");
response.setContentType("application/octet-stream");
response.setHeader("content-disposition", "attachement;fileName=" + new String(fileName.getBytes("utf-8"), "ISO-8859-1"));
byte[] bytes = new byte[1024];
int len;
while ((len = fis.read(bytes)) != -1) {
response.getOutputStream().write(bytes, 0, len);
}
response.flushBuffer();
fis.close();
//下载完成之后，删掉这个zip包
File fileTempZip = new File(filePath);
FileUtil.del(fileTempZip);
//下载完成之后，删掉这个生成代码的包
File codeFile = new File(path);
FileUtil.del(codeFile);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
}
5. service
@Service
public class GeneratorServiceImpl implements IGeneratorService {
private final static Logger log = LoggerFactory.getLogger(GeneratorServiceImpl.class);
@Autowired(required = false)
private GeneratorMapper genMapper;
/**
* 查询数据库表信息
*
* @return 数据库表列表
*/
@Override
public PageResult<TableInfo> queryList(Query query) {
QueryWrapper<TableInfo> warper = Wrappers.<TableInfo>query();
if (query.containsKey("dbtype") && StringUtils.isNotBlank(query.get("dbtype").toString())
&& query.containsKey("username") && StringUtils.isNotBlank(query.get("username").toString())) {
String dbtype = query.get("dbtype").toString();
String username = query.get("username").toString();
if ("dm".equals(dbtype)) {
warper.eq("owner", username);
}
if (query.containsKey("tableName") && StringUtils.isNotBlank(query.get("tableName").toString())) {
warper.eq("t.table_name", query.get("tableName").toString());
}
if (query.containsKey("comments") && StringUtils.isNotBlank(query.get("comments").toString())) {
if ("dm".equals(dbtype)) {
//此处加上一个 u.comments，因为mapper.xml中_databaseId == 'dm'时写的有别名u,如果不加，就报错
warper.eq("u.comments", query.get("comments").toString());
}else if ("mysql".equals(dbtype)){
warper.eq("comments", query.get("comments").toString());
}else if("kingbasees".equals(dbtype)){
warper.eq("comments", query.get("comments").toString());
}
}
}
List<TableInfo> pages = genMapper.queryList(new RowBounds(query.getOffset(), query.getLimit()), warper);
int total = genMapper.queryCount(warper);
PageResult result = new PageResult();
result.setCount(total);
result.setData(pages);
return result;
}
}
6. mapper
package com.generator.mapper;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.session.RowBounds;
import java.util.List;
/**
* <p>
* 代码生成器 Mapper 接口
* </p>
*/
@MyBatisDao
@Mapper
public interface GeneratorMapper extends BaseMapper<TableInfo> {
/**
* 查询数据库表信息
*
* @return 数据库表列表
*/
List<TableInfo> queryList(RowBounds rowBounds, @Param(Constants.WRAPPER) Wrapper wrapper);
int queryCount(@Param(Constants.WRAPPER) Wrapper wrapper);
}
TableInfo的实体类如下：
public class TableInfo implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 表名称
*/
private String tableName;
/**
* 表描述
*/
private String comments;
/**
* 类名(第一个字母大写)
*/
private String className;
/**
* 类名(第一个字母小写)
*/
private String classname;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date createTime;
/**
* 更新时间
*/
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date updateTime;
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public String getComments() {
return comments;
}
public void setComments(String comments) {
this.comments = comments;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public Date getUpdateTime() {
return updateTime;
}
public void setUpdateTime(Date updateTime) {
this.updateTime = updateTime;
}
public String getClassName() {
return className;
}
public void setClassName(String className) {
this.className = className;
}
public String getClassname() {
return classname;
}
public void setClassname(String classname) {
this.classname = classname;
}
}
7. mapper.xml，目前只实现了mysql、达梦数据库、人大金仓数据库的sql。
<?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.generator.mapper.GeneratorMapper">
<resultMap type="com.generator.entity.TableInfo" id="TableInfoResult">
<result property="tableName" column="table_name"/>
<result property="comments" column="comments"/>
<result property="createTime" column="create_time"/>
<result property="updateTime" column="update_time"/>
</resultMap>
<select id="queryList" resultMap="TableInfoResult">
<if test="_databaseId == 'mysql'">
select * from (
select table_name, table_comment as comments, create_time, update_time
from information_schema.tables
where table_schema = (select database()) ) t
${ew.customSqlSegment}
</if>
<if test="_databaseId == 'kingbasees'">
SELECT t.table_name,t.comments FROM (
SELECT DISTINCT tab.table_name table_name, d.description as comments FROM
( SELECT table_name from information_schema.TABLES WHERE table_schema='public' ORDER BY table_name ) AS tab
LEFT JOIN sys_class c ON c.relname = tab.table_name
LEFT JOIN sys_attribute a ON a.attrelid = c.oid
LEFT JOIN sys_description d ON (a.attrelid = d.objoid AND d.objsubid=0 )
WHERE a.attnum > 0
ORDER BY tab.table_name
) t
${ew.customSqlSegment}
</if>
<if test="_databaseId == 'dm'">
SELECT t.TABLE_NAME ,u.COMMENTS
FROM DBA_TABLES t
left JOIN USER_TAB_COMMENTS u
ON t.TABLE_NAME = u.TABLE_NAME
${ew.customSqlSegment}
</if>
</select>
<select id="queryCount" resultType="java.lang.Integer">
<if test="_databaseId == 'mysql'">
select count(*)
from (select table_name, table_comment as comments from information_schema.tables
where table_schema = (select database())) as t
${ew.customSqlSegment}
</if>
<if test="_databaseId == 'kingbasees'">
SELECT count(DISTINCT t.table_name) FROM (
SELECT DISTINCT tab.table_name table_name, d.description as comments
FROM
( SELECT table_name from information_schema.TABLES WHERE table_schema='public' ORDER BY table_name ) AS tab
LEFT JOIN sys_class c ON c.relname = tab.table_name
LEFT JOIN sys_attribute a ON a.attrelid = c.oid
LEFT JOIN sys_description d ON (a.attrelid = d.objoid AND d.objsubid=0 )
WHERE a.attnum > 0
ORDER BY tab.table_name
) t
${ew.customSqlSegment}
</if>
<if test="_databaseId == 'dm'">
SELECT COUNT(*)
FROM DBA_TABLES t
left JOIN USER_TAB_COMMENTS u
ON t.TABLE_NAME = u.TABLE_NAME
${ew.customSqlSegment}
</if>
</select>
</mapper>
8. 针对第7条中的_databaseId是有相对应的配置类的，
@Configuration
public class DatabaseIdConfig {
/**
* 自动识别使用的数据库类型
* 在mapper.xml中databaseId的值就是跟这里对应，
* 如果没有databaseId选择则说明该sql适用所有数据库
* */
@Bean
public DatabaseIdProvider getDatabaseIdProvider(){
DatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider();
Properties properties = new Properties();
properties.setProperty("Oracle","oracle");
properties.setProperty("MySQL","mysql");
properties.setProperty("DB2","db2");
properties.setProperty("Derby","derby");
properties.setProperty("H2","h2");
properties.setProperty("HSQL","hsql");
properties.setProperty("Informix","informix");
properties.setProperty("MS-SQL","ms-sql");
properties.setProperty("PostgreSQL","postgresql");
properties.setProperty("Sybase","sybase");
properties.setProperty("Hana","hana");
properties.setProperty("DM","dm");
properties.setProperty("KingBase","kingbase");
properties.setProperty("KingBase8","kingbasees");
databaseIdProvider.setProperties(properties);
return databaseIdProvider;
}
}
至此通过前端页面传值，即可实现动态切换数据源查询数据库的全部表。
版权声明：本文为qq_42752478原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。