一、功能展示：

PostgreSQL教程--實現(xiàn)類似于MySQL的show create table功能實現(xiàn)見末尾

效果如圖：

DB連接配置維護：

Schema功能：集成Screw生成文檔，導(dǎo)出庫的表結(jié)構(gòu)，導(dǎo)出表結(jié)構(gòu)和數(shù)據(jù)

?表對象操作：翻頁查詢，查看創(chuàng)建SQL，生成代碼

可以單個代碼文件下載，也可以全部下載（打成zip包下載返回）

可以自定義文件名稱，包路徑和代碼信息

二、實現(xiàn)過程

一開始只是想實現(xiàn)若依的生成代碼功能：

然后我發(fā)現(xiàn)若依的這個只能使用SpringBoot配置的單例數(shù)據(jù)源

實際上來說應(yīng)該可以像Navicat一樣配置多個連接訪問多個數(shù)據(jù)源

1、解決數(shù)據(jù)源問題

開動態(tài)數(shù)據(jù)源太麻煩了，我只是針對這個功能需要動態(tài)訪問，所以第一步要解決的是訪問DB的問題

在默認(rèn)數(shù)據(jù)源下建了一張連接配置表來維護：

CREATE TABLE `db_conn` (
  `id` int NOT NULL AUTO_INCREMENT,
  `conn_name` varchar(32) COLLATE utf8mb4_general_ci NOT NULL,
  `username` varchar(32) COLLATE utf8mb4_general_ci NOT NULL,
  `password` varchar(32) COLLATE utf8mb4_general_ci NOT NULL,
  `host` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `port` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `creator` varchar(24) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `updater` varchar(24) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_idx_conn_name` (`conn_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

除了Mybatis，輕量級的操作工具類我選擇了Hutool的DB操作

使用容器對象保管使用的數(shù)據(jù)源，每次動態(tài)操作都需要從容器存取

package cn.cloud9.server.tool.ds;
 
import cn.cloud9.server.struct.constant.ResultMessage;
import cn.cloud9.server.struct.file.FileProperty;
import cn.cloud9.server.struct.file.FileUtil;
import cn.cloud9.server.struct.spring.SpringContextHolder;
import cn.cloud9.server.struct.util.Assert;
import cn.cloud9.server.struct.util.DateUtils;
import cn.hutool.core.io.IoUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.Page;
import cn.hutool.db.PageResult;
import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.context.annotation.Lazy;
import org.springframework.stereotype.Service;
 
import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
 
@Lazy
@Slf4j
@Service("dbConnService")
public class DbConnServiceImpl extends ServiceImpl<DbConnMapper, DbConnDTO> implements IDbConnService {
 
    /* 數(shù)據(jù)源管理容器 */
    private static final Map<Integer, DataSource> dsContainer = new ConcurrentHashMap<>();
    private static final DbConnMapper dbConnMapper = SpringContextHolder.getBean("dbConnMapper", DbConnMapper.class);
 
    /**
     * 獲取數(shù)據(jù)源對象的方法 （所有的接口操作都需要經(jīng)過這個步驟）
     * @param dbId 連接參數(shù)實體ID
     * @return 參數(shù)對應(yīng)的數(shù)據(jù)源對象
     */
    public static DataSource getDsById(Integer dbId) {
        DataSource dataSource = dsContainer.get(dbId);
        boolean isEmpty = Objects.isNull(dataSource);
        if (isEmpty) {
            DbConnDTO dbConn = dbConnMapper.selectById(dbId);
            Assert.isTrue(Objects.isNull(dbConn), ResultMessage.NOT_FOUND_ERROR, "連接配置");
            DruidDataSource druidDs = new DruidDataSource();
            druidDs.setUrl("jdbc:mysql://" + dbConn.getHost() + ":" + dbConn.getPort() + "/sys");
            druidDs.setUsername(dbConn.getUsername());
            druidDs.setPassword(dbConn.getPassword());
            druidDs.addConnectionProperty("useInformationSchema", "true");
            druidDs.addConnectionProperty("characterEncoding", "utf-8");
            druidDs.addConnectionProperty("useSSL", "false");
            druidDs.addConnectionProperty("serverTimezone", "UTC");
            druidDs.addConnectionProperty("useAffectedRows", "true");
            dataSource = druidDs;
            /* 置入容器 */
            dsContainer.put(dbId, druidDs);
        }
        return dataSource;
    }
 
}

2、界面信息的獲取

只要有訪問?information_schema 的權(quán)限就可以獲取db的大部分信息了

【后面什么觸發(fā)器，視圖都沒寫，只是列在那留個坑以后來填】

就是hutool的翻頁用的人很少沒什么資料，我這里是自己看API注釋琢磨的寫的

這個pageResult我一開始以為和mp的page對象一樣，結(jié)果發(fā)現(xiàn)是繼承了ArrayList，序列化之后輸出的只有結(jié)果集合，沒有翻頁的信息

所以要自己提取出來，他會算好放在里面

Entity就理解為一個Map對象，返回就是存鍵值對，按鍵值對取就行了，也可以作為SQL查詢條件的封裝對象

@SuppressWarnings("all")
@Override
public IPage<Entity> getTablePage(Integer dbId, String schemaName, DbTableDTO dto) throws SQLException {
    Db db = Db.use(getDsById(dbId));
 
    Entity condition = Entity.create();
    condition.setTableName("`information_schema`.`TABLES`");
    condition.set("`TABLE_TYPE`", "BASE TABLE");
    condition.set("`TABLE_SCHEMA`", schemaName);
    if (StringUtils.isNotBlank(dto.getTableName()))
        condition.put("`TABLE_NAME`", "LIKE '%" + dto.getTableName() + "%'");
    if (StringUtils.isNotBlank(dto.getTableComment()))
        condition.put("`TABLE_COMMENT`", "LIKE '%" + dto.getTableComment() + "%'");
    if (StringUtils.isNotBlank(dto.getStartCreateTime()) && StringUtils.isNotBlank(dto.getEndCreateTime())) {
        condition.put("`CREATE_TIME`", "BETWEEN '" + dto.getStartCreateTime() + "' AND '" + dto.getEndCreateTime() + "'");
    }
 
    IPage<Entity> page = dto.getPage();
    PageResult<Entity> pageResult = db.page(condition, new Page((int)page.getCurrent() - 1, (int)page.getSize()));
 
    page.setRecords(pageResult);
    page.setTotal(pageResult.getTotal());
    page.setPages(pageResult.getTotalPage());
    return page;
}

3、生成代碼的思路

生成這種模板文件肯定是用模板引擎來做的，若依用的是velocity好像，那我這里就還是用freemarker，因為對freemarker接觸的多一點

這還外加了一個starter，不過我沒怎么用到，有原始支持包就行了

<!-- https://mvnrepository.com/artifact/org.freemarker/freemarker -->
<dependency>
    <groupId>org.freemarker</groupId>
    <artifactId>freemarker</artifactId>
    <version>2.3.32</version>
</dependency>
 
<!--        <dependency>-->
<!--            <groupId>org.springframework.boot</groupId>-->
<!--            <artifactId>spring-boot-starter-freemarker</artifactId>-->
<!--        </dependency>-->

先寫好模板文件，里面標(biāo)記的變量名稱，F(xiàn)reeMarker會通過一個Map參數(shù)去取出來打在模板文件里渲染

?我先開了一個簡單的測試Controller檢查生成情況：

package cn.cloud9.server.test.controller;
 
import cn.cloud9.server.struct.common.BaseController;
import cn.cloud9.server.struct.file.FileUtil;
import cn.cloud9.server.struct.util.DateUtils;
import freemarker.cache.FileTemplateLoader;
import freemarker.cache.TemplateLoader;
import freemarker.template.Configuration;
import freemarker.template.Template;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
 
import java.io.BufferedWriter;
import java.io.File;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.nio.charset.StandardCharsets;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
 
@RestController
@RequestMapping("/test/ftl")
public class TemplateTestController extends BaseController {
 
    @GetMapping("/getTemplate")
    public void getCodeFile() throws Exception {
        Map<String, Object> paramMap = new ConcurrentHashMap<>();
 
        paramMap.put("modelPath", "cn.cloud9.sever.test.model");
        paramMap.put("author", "cloud9");
        paramMap.put("tableName", "info_account");
        paramMap.put("description", "測試模板代碼生成");
        paramMap.put("projectName", "tt-server");
        paramMap.put("dateTime", DateUtils.format(LocalDateTime.now(), DateUtils.DEFAULT_DATE_TIME_FORMAT));
        paramMap.put("modelName", "InfoAccount");
 
        List<Map<String, String>> columns = new ArrayList<>();
 
        Map<String, String> field1 = new ConcurrentHashMap<>();
        field1.put("columnName", "id");
        field1.put("fieldName", "id");
        field1.put("type", "Integer");
        columns.add(field1);
 
        Map<String, String> field2 = new ConcurrentHashMap<>();
        field2.put("columnName", "field_2");
        field2.put("fieldName", "field2");
        field2.put("type", "String");
        columns.add(field2);
 
        paramMap.put("columns", columns);
 
        String path = this.getClass().getResource("/code-template").getFile();
        File file = new File(path);
 
        Configuration config = new Configuration();
        TemplateLoader loader = new FileTemplateLoader(file);
        config.setTemplateLoader(loader);
 
        Template template = config.getTemplate("/java/Model.java.ftl", "UTF-8");
 
        FileUtil.setDownloadResponseInfo(response, "TestDTO.java");
        Writer out = new BufferedWriter(new OutputStreamWriter(response.getOutputStream(), StandardCharsets.UTF_8));
        template.process(paramMap, out);
        out.flush();
        out.close();
    }
}

模板文件內(nèi)容：

package ${modelPath};
 
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.Date;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import lombok.EqualsAndHashCode;
 
/**
 * @author ${author}
 * @description ${tableName} 實體類 ${modelDescription!}
 * @project ${projectName}
 * @date ${dateTime}
 */
@Data
@EqualsAndHashCode(callSuper = false)
@TableName("${tableName}")
public class ${modelName} {
 
<#list columns as column>
    @TableField("${column.columnName}")
    private ${column.type} ${column.fieldName};
 
</#list>
 
    @TableField(exist = false)
    private Page<${modelName}> page;
}

然后要解決輸出文件和輸出內(nèi)容的兩個方式：

兩個方法基本是一樣的，就是輸出對象不一樣，一個流，一個Writter

/**
 * 下載模板生成文件
 * @param response 響應(yīng)對象
 * @param tp 模板參數(shù)
 * @throws Exception IO異常
 */
public void downloadTemplateFile(HttpServletResponse response, TemplateParam tp) throws Exception {
    TemplateType tt = tp.getTemplateTypeByCode();
    tp.setDateTime();
    if (TemplateType.MODEL.equals(tt)) getColumnsMetaData(tp);
    Assert.isTrue(TemplateType.NONE.equals(tt), ResultMessage.NOT_FOUND_ERROR, "模板類型編號");
    Map<String, Object> paramMap = BeanUtil.beanToMap(tp);
 
    Template template = configuration.getTemplate(tt.getPath(), "UTF-8");
    FileUtil.setDownloadResponseInfo(response, paramMap.get(tt.getFilenameKey()) + tt.getSuffix());
    Writer out = new BufferedWriter(new OutputStreamWriter(response.getOutputStream(), StandardCharsets.UTF_8));
    /* 輸出成型的文件 */
    template.process(paramMap, out);
    out.flush();
    out.close();
}
 
/**
 * 獲取模板生成的輸出內(nèi)容
 * @param tp 模板參數(shù)
 * @return 輸出內(nèi)容
 * @throws Exception IO異常
 */
public String getTemplateContent(TemplateParam tp, TemplateType tt) throws Exception {
    tp.setDateTime();
    if (TemplateType.MODEL.equals(tt)) getColumnsMetaData(tp);
    Assert.isTrue(TemplateType.NONE.equals(tt), ResultMessage.NOT_FOUND_ERROR, "模板類型編號");
    Map<String, Object> paramMap = BeanUtil.beanToMap(tp);
    Template template = configuration.getTemplate(tt.getPath(), "UTF-8");
 
    /* 代替方法 String content = FreeMarkerTemplateUtils.processTemplateIntoString(template, paramMap); */
    StringWriter result = new StringWriter(1024);
    template.process(paramMap, result);
    return result.toString();
}

下載全部文件，返回的是zip包，就要先把文件在服務(wù)器上創(chuàng)建好打包返回，然后再刪除緩存的文件：

/**
* 下載全部模板文件
* @param response
* @param tp
*/
public void downloadAllTemplateFile(HttpServletResponse response, TemplateParam tp) throws Exception {
   final String cachePath = fileProperty.getBaseDirectory() + ROOT_PATH;
   File file = new File(cachePath);
   if (!file.exists()) file.mkdirs();
 
   List<TemplateType> templateTypes = TemplateType.getAvailableTypes();
   List<File> cacheFiles = new ArrayList<>(templateTypes.size());
   for (TemplateType templateType : templateTypes) {
       /* 獲取輸出的文件名 */
       String fileName = ReflectUtil.getFieldValue(tp, templateType.getFilenameKey()).toString();
       fileName = fileName + templateType.getSuffix();
 
       /* 創(chuàng)建模板文件 */
       String fullPath = cachePath + File.separator + fileName;
       File templateFile = new File(fullPath);
       if (!templateFile.exists()) file.createNewFile();
 
       /* 獲取輸出的成型內(nèi)容 */
       tp.setTemplateCode(templateType.getCode());
       String content = this.getTemplateContent(tp, templateType);
 
       /* 寫入本地文件 */
       FileOutputStream fos = new FileOutputStream(fullPath);
       fos.write(content.getBytes());
       fos.close();
       cacheFiles.add(templateFile);
   }
 
   /* 創(chuàng)建壓縮文件 */
   String zipFileName = cachePath + File.separator + tp.getTableName() + ".zip";
   File zipFile = new File(zipFileName);
   ZipUtil.zip(zipFile, false, cacheFiles.toArray(new File[]{}));
 
   /* 給壓縮文件設(shè)置信息 */
   FileUtil.setDownloadResponseInfo(response, zipFile, zipFile.getName());
 
   /* 輸出壓縮文件 */
   BufferedInputStream in = cn.hutool.core.io.FileUtil.getInputStream(zipFile.getAbsolutePath());
   ServletOutputStream os = response.getOutputStream();
   IoUtil.copy(in, os, IoUtil.DEFAULT_BUFFER_SIZE);
   in.close();
   os.close();
 
   /* 刪除本地緩存 */
   zipFile.delete();
   cacheFiles.forEach(File::delete);
}

上面的模板類型用枚舉區(qū)分，枚舉順便存儲一下模板的信息，這樣取參數(shù)就方便了

一些沒有寫好的模板暫時設(shè)置null，邏輯就會跳過

package cn.cloud9.server.tool.template;
 
import lombok.Getter;
 
import java.util.Arrays;
import java.util.List;
import java.util.Objects;
import java.util.stream.Collectors;
 
@Getter
public enum TemplateType {
    /* 沒有文件 */
    NONE(null, null, null, null),
 
    /* 后臺文件 */
    MODEL(1001, "/java/Model.java.ftl", "modelName", ".java"),
    MAPPER(1002, "/java/Mapper.java.ftl", "mapperName", ".java"),
    I_SERVICE(1003, "/java/IService.java.ftl", "iServiceName", ".java"),
    SERVICE_IMPL(1004, "/java/ServiceImpl.java.ftl", "serviceImplName", ".java"),
    CONTROLLER(1005, "/java/Controller.java.ftl", "controllerName", ".java"),
 
    /* 前端文件 */
    VUE_API(2001, "/vue/Api.js.ftl", "apiName", ".js"),
    /* VUE_VIEW(2002, "/vue/View.vue.ftl", "viewName", ".vue"), */
    VUE_VIEW(2002, "/vue/View.vue.ftl", null, null),
    ;
 
    /* 編號和模板文件路徑 */
    private final Integer code;
    private final String path;
    private final String filenameKey;
    private final String suffix;
 
    TemplateType(Integer code, String path, String filenameKey, String suffix) {
        this.code = code;
        this.path = path;
        this.filenameKey = filenameKey;
        this.suffix = suffix;
    }
     
    /**
     * 獲取可用的模板類型集合
     * @return List<TemplateType>
     */
    public static List<TemplateType> getAvailableTypes() {
        return Arrays
            .stream(values())
            .filter(t -> Objects.nonNull(t.code) && Objects.nonNull(t.path) && Objects.nonNull(t.filenameKey) && Objects.nonNull(t.suffix))
            .collect(Collectors.toList());
    }
 
}

所有的模板參數(shù)統(tǒng)一用一個對象接受：

package cn.cloud9.server.tool.template;
 
import cn.cloud9.server.struct.util.DateUtils;
import cn.hutool.db.Entity;
import lombok.Data;
 
import java.time.LocalDateTime;
import java.util.List;
 
@Data
public class TemplateParam {
 
    /* 注釋信息部分 */
    private String author;
    private String projectName;
    private String dateTime;
    private String modelDescription;
    private String mapperDescription;
    private String iServiceDescription;
    private String serviceImplDescription;
    private String controllerDescription;
 
    /* 包路徑 */
    private String modelPath;
    private String mapperPath;
    private String iServicePath;
    private String serviceImplPath;
    private String controllerPath;
 
    /* 類名稱 & 文件名 */
    private String modelName;
    private String mapperName;
    private String iServiceName;
    private String serviceImplName;
    private String controllerName;
    private String apiName;
 
    /* controller接口與路徑 */
    private String urlPath;
 
    /* 元數(shù)據(jù)信息 */
    private Integer dbConnId;
    private String schemaName;
    private String tableName;
    private List<Entity> columns;
 
    /* 模板類型枚舉編號 */
    private Integer templateCode;
 
    /**
     * 設(shè)置當(dāng)前時間
     */
    public void setDateTime() {
        dateTime = DateUtils.format(LocalDateTime.now(), DateUtils.DEFAULT_DATE_TIME_FORMAT);
    }
 
    public TemplateType getTemplateTypeByCode() {
        return TemplateType.getAvailableTypes().stream()
                .filter(tt -> tt.getCode().equals(templateCode))
                .findFirst()
                .orElse(TemplateType.NONE);
    }
 
    public static TemplateType getTemplateTypeByCode(Integer ttCode) {
        return TemplateType.getAvailableTypes().stream()
                .filter(tt -> tt.getCode().equals(ttCode))
                .findFirst()
                .orElse(TemplateType.NONE);
    }
}

解決字段列的模板參數(shù)問題：

/**
* 獲取列的元數(shù)據(jù)
* @param tp
*/
private void getColumnsMetaData(TemplateParam tp) throws Exception {
   Db db = Db.use(DbConnServiceImpl.getDsById(tp.getDbConnId()));
   String querySql =
       "SELECT\n" +
       "\tCOLUMN_NAME AS `columnName`,\n" +
       "\tREPLACE(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(\n" +
       "\tLOWER(COLUMN_NAME), '_a','A'),'_b','B'),'_c','C'),'_d','D'),'_e','E'),'_f','F'),'_g','G'),'_h','H'),'_i','I'),'_j','J'),'_k','K'),'_l','L'),'_m','M'),'_n','N'),'_o','O'),'_p','P'),'_q','Q'),'_r','R'),'_s','S'),'_t','T'),'_u','U'),'_v','V'),'_w','W'),'_x','X'),'_y','Y'),'_z','Z') AS `fieldName`,\n" +
       "\tCASE\n" +
       "\t\tWHEN DATA_TYPE ='bit' THEN 'Boolean'\n" +
       "\t\tWHEN DATA_TYPE ='tinyint' THEN 'Byte'\n" +
       "\t\tWHEN LOCATE(DATA_TYPE, 'smallint,mediumint,int,integer') > 0 THEN 'Integer'\n" +
       "\t\tWHEN DATA_TYPE = 'bigint' THEN 'Long'\n" +
       "\t\tWHEN LOCATE(DATA_TYPE, 'float, double') > 0 THEN 'Double'\n" +
       "\t\tWHEN DATA_TYPE = 'decimal' THEN 'BigDecimal'\n" +
       "\t\tWHEN LOCATE(DATA_TYPE, 'time, date, year, datetime, timestamp') > 0 THEN 'LocalDateTime'\n" +
       "\t\tWHEN LOCATE(DATA_TYPE, 'json, char, varchar, tinytext, text, mediumtext, longtext') > 0 THEN 'String'\n" +
       "\tELSE 'String' END AS `type`\n" +
       "FROM\n" +
       "\t`information_schema`.`COLUMNS`\n" +
       "WHERE\n" +
       "\t`TABLE_SCHEMA` = ? \n" +
       "\tAND `TABLE_NAME` = ? ";
   List<Entity> entityList = db.query(querySql, tp.getSchemaName(), tp.getTableName());
   tp.setColumns(entityList);
}

數(shù)據(jù)類型可以用CASE WHEN 解決，但是頭疼的問題是，怎么解決MySQL的下劃線轉(zhuǎn)駝峰處理

大部分提供的方法都是所有字母全部Replace一邊，辦法笨歸笨，但是有效，所有我這里也是用這個辦法，沒有寫成函數(shù)處理

但是牛逼的群友提供了一個子查詢的辦法來實現(xiàn)：

SET @target = 'ABC_DEF_gh_asdqw';
SELECT group_concat(
                concat(
                upper(substr(SUBSTRING_INDEX(SUBSTRING_index(@target,'_',help_topic_id+1),'_',-1) ,1,1)),
                substr(lower(SUBSTRING_INDEX(SUBSTRING_index(@target,'_',help_topic_id+1),'_',-1)) ,2)
                )
                separator '') AS num
FROM mysql.help_topic
WHERE help_topic_id <= length(@target)-LENGTH(replace(@target,'_',''))
order by help_topic_id desc

4、頁面展示代碼的幾個功能問題：

一、展示代碼塊：

我看了若依源碼是用了高亮JS，然后百度搜了下

"highlight.js": "^11.7.0",
"vue-highlightjs": "^1.3.3",

全局注冊：

/* 掛載highlightJS */
import VueHighlightJS from 'vue-highlightjs'
import 'highlight.js/styles/monokai-sublime.css'
Vue.use(VueHighlightJS)

然后使用：

<pre v-loading="loadingFlag" v-highlightjs>
    <code class="sql" v-text="createSQL" />
</pre>

二、復(fù)制粘貼功能：

這個再vue-admin后臺框架里面已經(jīng)裝了，而且有案例：

標(biāo)簽添加粘貼板指令：

<div class="dialog-bottom-bar">
  <el-button v-clipboard:copy="createSQL" v-clipboard:success="clipboardSuccess" type="primary" size="mini" icon="el-icon-document">復(fù)制</el-button>
  <el-button type="default" size="mini" icon="el-icon-close" @click="closeDialog">取消</el-button>
</div>

這里我沒有全局注冊，因為案例也沒這么做，應(yīng)該是個別功能用用

在使用的組件中導(dǎo)入指令并注冊：

import clipboard from '@/directive/clipboard' // use clipboard by v-directive
 
export default {
    name: 'CreateSqlView',
    directives: { clipboard }
}

復(fù)制成功事件綁定一個方法：

clipboardSuccess() {
  this.$message.success('復(fù)制成功')
},

三、擴展延申

一、集成Screw文檔生成：

這個之前寫過一份固定的，用讀取ini配置文件方式生成的

我想反正我都解決動態(tài)數(shù)據(jù)源的問題了，干脆把文檔生成集成進來正好

文檔參數(shù)對象：

package cn.cloud9.server.tool.doc;
 
import cn.smallbun.screw.core.engine.EngineFileType;
import lombok.Data;
 
import java.util.List;
 
@Data
public class DbDocDTO {
 
    /* 文檔名稱 */
    private String docName;
    /* 文檔標(biāo)題 */
    private String docTitle;
    /* 組織名稱 */
    private String organization;
    /* 組織連接 */
    private String organizationUrl;
 
    private String version;
    private String description;
    private Integer docType; /* 1,HTML 2,WORD 3,MD */
 
    /* 按指定的表名生成 */
    private List<String> specifyTables;
    /* 按指定的表前綴生成 */
    private List<String> specifyTablePrefixes;
    /* 按指定的表后綴生成 */
    private List<String> specifyTableSuffixes;
    /* 需要忽略的表名 */
    private List<String> ignoreTables;
    /* 需要忽略的表前綴 */
    private List<String> ignoreTablePrefixes;
    /* 需要忽略的表后綴 */
    private List<String> ignoreTableSuffixes;
 
 
    public EngineFileType getDocType() {
        switch (docType) {
            case 2: return EngineFileType.WORD;
            case 3: return EngineFileType.MD;
            default: return EngineFileType.HTML;
        }
    }
}

文檔生成邏輯，就用官方Demo隨便改改參數(shù)組合就OK了

package cn.cloud9.server.tool.doc;
 
import cn.cloud9.server.struct.file.FileProperty;
import cn.cloud9.server.struct.file.FileUtil;
import cn.cloud9.server.tool.ds.DbConnDTO;
import cn.cloud9.server.tool.ds.DbConnMapper;
import cn.hutool.core.io.IoUtil;
import cn.smallbun.screw.core.Configuration;
import cn.smallbun.screw.core.engine.EngineConfig;
import cn.smallbun.screw.core.engine.EngineTemplateType;
import cn.smallbun.screw.core.execute.DocumentationExecute;
import cn.smallbun.screw.core.process.ProcessConfig;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.springframework.stereotype.Service;
 
import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.IOException;
import java.util.Collections;
 
@Slf4j
@Service
public class DbDocService {
 
    @Resource
    private FileProperty fileProperty;
 
    @Resource
    private DbConnMapper dbConnMapper;
 
    /**
     * 生成mysql文檔
     * @param dbId 連接實體
     * @param schemaName 約束名
     * @param dto 文檔參數(shù)實體
     * @param response 響應(yīng)對象
     * @throws IOException IO異常
     */
    public void getDbSchemaDoc(Integer dbId, String schemaName, DbDocDTO dto, HttpServletResponse response) throws IOException {
        Configuration config = Configuration.builder()
                .organization(dto.getOrganization()) // 組織機構(gòu)
                .organizationUrl(dto.getOrganizationUrl()) // 組織鏈接
                .title(dto.getDocTitle()) // 文檔標(biāo)題
                .version(dto.getVersion()) // 版本
                .description(dto.getDescription()) // 描述
                .dataSource(buildDataSource(dbId, schemaName)) // 數(shù)據(jù)源
                .engineConfig(buildEngineConfig(dto)) // 引擎配置
                .produceConfig(buildProcessConfig(dto)) // 處理配置
                .build();
        new DocumentationExecute(config).execute();
 
        /* 從服務(wù)器本地讀取生成的文檔文件下載 */
        final String fileName =  dto.getDocName() + dto.getDocType().getFileSuffix();
        final File docFile = new File(fileProperty.getBaseDirectory() + File.separator + fileName);
        if (!docFile.exists()) return;
        FileUtil.setDownloadResponseInfo(response, docFile, fileName);
 
        /* 下載 */
        BufferedInputStream in = cn.hutool.core.io.FileUtil.getInputStream(docFile.getAbsolutePath());
        ServletOutputStream os = response.getOutputStream();
        long copySize = IoUtil.copy(in, os, IoUtil.DEFAULT_BUFFER_SIZE);
        log.info("文檔生成成功！ {}bytes", copySize);
        in.close();
        os.close();
         
        /* 必須要等到其他IO流使用完畢資源釋放了才能操作刪除 */
        docFile.delete();
    }
 
    private HikariDataSource buildDataSource(Integer dbId, String schemaName) {
        DbConnDTO dbConn = dbConnMapper.selectById(dbId);
        // 創(chuàng)建 HikariConfig 配置類
        HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setDriverClassName("com.mysql.cj.jdbc.Driver");
        hikariConfig.setJdbcUrl("jdbc:mysql://" + dbConn.getHost() + ":" + dbConn.getPort() + "/" + schemaName);
        hikariConfig.setUsername(dbConn.getUsername());
        hikariConfig.setPassword(dbConn.getPassword());
        // 設(shè)置可以獲取 tables remarks 信息
        hikariConfig.addDataSourceProperty("useInformationSchema", "true");
        hikariConfig.addDataSourceProperty("characterEncoding", "utf-8");
        hikariConfig.addDataSourceProperty("useSSL", "false");
        hikariConfig.addDataSourceProperty("serverTimezone", "UTC");
        hikariConfig.addDataSourceProperty("useAffectedRows", "true");
        hikariConfig.setMinimumIdle(2);
        hikariConfig.setMaximumPoolSize(5);
        // 創(chuàng)建數(shù)據(jù)源
        return new HikariDataSource(hikariConfig);
    }
 
    private ProcessConfig buildProcessConfig(DbDocDTO dto) {
        return ProcessConfig.builder()
            // 根據(jù)名稱指定表生成
            .designatedTableName(CollectionUtils.isEmpty(dto.getSpecifyTables()) ? Collections.emptyList() : dto.getSpecifyTables())
            // 根據(jù)表前綴生成
            .designatedTablePrefix(CollectionUtils.isEmpty(dto.getSpecifyTablePrefixes()) ? Collections.emptyList() : dto.getSpecifyTablePrefixes())
            // 根據(jù)表后綴生成
            .designatedTableSuffix(CollectionUtils.isEmpty(dto.getSpecifyTableSuffixes()) ? Collections.emptyList() : dto.getSpecifyTableSuffixes())
            // 忽略數(shù)據(jù)庫中address這個表名
            .ignoreTableName(CollectionUtils.isEmpty(dto.getIgnoreTables()) ? Collections.emptyList() : dto.getIgnoreTables())
            // 忽略表前綴，就是db1數(shù)據(jù)庫中表名是t_開頭的都不生產(chǎn)數(shù)據(jù)庫文檔(t_student,t_user這兩張表)
            .ignoreTablePrefix(CollectionUtils.isEmpty(dto.getIgnoreTablePrefixes()) ? Collections.emptyList() : dto.getIgnoreTablePrefixes())
            // 忽略表后綴(就是db1數(shù)據(jù)庫中表名是_teacher結(jié)尾的都不生產(chǎn)數(shù)據(jù)庫文檔：stu_teacher)
            .ignoreTableSuffix(CollectionUtils.isEmpty(dto.getIgnoreTableSuffixes()) ? Collections.emptyList() : dto.getIgnoreTableSuffixes())
            .build();
    }
 
    /**
     * 引擎配置創(chuàng)建
     * @param dto 文檔參數(shù)實體
     * @return EngineConfig
     */
    private EngineConfig buildEngineConfig(DbDocDTO dto) {
        return EngineConfig.builder()
                // 生成文件路徑
                .fileOutputDir(fileProperty.getBaseDirectory())
                // 打開目錄
                .openOutputDir(false)
                // 文件類型
                .fileType(dto.getDocType())
                // 文件類型
                .produceType(EngineTemplateType.freemarker)
                // 自定義文件名稱
                .fileName(dto.getDocName())
                .build();
    }
}

Vue前端表單view:

<template>
  <div>
    <el-form :ref="formRef" :model="form" :rules="formRules" size="small">
      <el-row :gutter="$ui.layout.gutter.g10">
        <el-col :span="$ui.layout.span.two">
          <el-form-item label="文件名" prop="docName">
            <el-input v-model="form.docName" placeholder="文件名" clearable maxlength="32" show-word-limit />
          </el-form-item>
        </el-col>
 
        <el-col :span="$ui.layout.span.two">
          <el-form-item label="文檔標(biāo)題" prop="docTitle">
            <el-input v-model="form.docTitle" placeholder="文檔標(biāo)題" clearable maxlength="32" show-word-limit />
          </el-form-item>
        </el-col>
 
        <el-col :span="$ui.layout.span.two">
          <el-form-item label="文檔版本" prop="version">
            <el-input v-model="form.version" placeholder="文檔版本" clearable maxlength="32" show-word-limit />
          </el-form-item>
        </el-col>
 
        <el-col :span="$ui.layout.span.two">
          <el-form-item label="文檔描述" prop="description">
            <el-input v-model="form.description" placeholder="文檔描述" clearable maxlength="32" show-word-limit />
          </el-form-item>
        </el-col>
 
        <el-col :span="$ui.layout.span.two">
          <el-form-item label="組織機構(gòu)" prop="organization">
            <el-input v-model="form.organization" placeholder="組織機構(gòu)" clearable maxlength="32" show-word-limit />
          </el-form-item>
        </el-col>
 
        <el-col :span="$ui.layout.span.two">
          <el-form-item label="組織機構(gòu)鏈接" prop="organizationUrl">
            <el-input v-model="form.organizationUrl" placeholder="組織機構(gòu)鏈接" clearable maxlength="64" show-word-limit />
          </el-form-item>
        </el-col>
 
        <el-col :span="$ui.layout.span.two">
          <el-form-item label="指定生成的表" prop="specifyTables">
            <el-input v-model="form.specifyTables" placeholder="指定生成的表" clearable maxlength="128" show-word-limit />
          </el-form-item>
        </el-col>
 
        <el-col :span="$ui.layout.span.two">
          <el-form-item label="指定生成的表前綴" prop="specifyTablePrefixes">
            <el-input v-model="form.specifyTablePrefixes" placeholder="指定生成的表前綴" clearable maxlength="128" show-word-limit />
          </el-form-item>
        </el-col>
 
        <el-col :span="$ui.layout.span.two">
          <el-form-item label="指定生成的表后綴" prop="specifyTableSuffixes">
            <el-input v-model="form.specifyTableSuffixes" placeholder="指定生成的表后綴" clearable maxlength="128" show-word-limit />
          </el-form-item>
        </el-col>
 
        <el-col :span="$ui.layout.span.two">
          <el-form-item label="要忽略的表" prop="specifyTables">
            <el-input v-model="form.ignoreTables" placeholder="要忽略的表" clearable maxlength="128" show-word-limit />
          </el-form-item>
        </el-col>
 
        <el-col :span="$ui.layout.span.two">
          <el-form-item label="要忽略的表前綴" prop="specifyTablePrefixes">
            <el-input v-model="form.ignoreTablePrefixes" placeholder="要忽略的表前綴" clearable maxlength="128" show-word-limit />
          </el-form-item>
        </el-col>
 
        <el-col :span="$ui.layout.span.two">
          <el-form-item label="要忽略的表后綴" prop="specifyTableSuffixes">
            <el-input v-model="form.ignoreTableSuffixes" placeholder="要忽略的表后綴" clearable maxlength="128" show-word-limit />
          </el-form-item>
        </el-col>
 
        <el-col :span="$ui.layout.span.two">
          <el-form-item label="文件類型" prop="docType">
            <el-select v-model="form.docType" placeholder="請選擇" clearable style="width: 100%">
              <el-option v-for="item in docTypes" :key="item.value" :label="item.label" :value="item.value" />
            </el-select>
          </el-form-item>
        </el-col>
      </el-row>
    </el-form>
 
    <div align="center">
      <el-button size="mini" type="primary" icon="el-icon-check" @click="formSubmit">確定</el-button>
      <el-button size="mini" type="default" plain icon="el-icon-close" @click="formCancel">取消</el-button>
    </div>
  </div>
</template>
 
<script>
import { getDbSchemaDoc } from '@/api/tt-server/tool-lib/mysql-visualize'
import { axiosDownloadFile } from '@/utils'
 
export default {
  name: 'CreateDocView',
  props: {
    param: {
      type: Object,
      required: true,
      default: () => {}
    }
  },
  data() {
    return {
      formRef: 'formRefKey',
      form: {
        docName: '',
        docTitle: '',
        version: 'V1.0.0',
        docType: 1,
        description: '',
        organization: 'OnCloud9',
        organizationUrl: 'https://www.cnblogs.com/mindzone/',
 
        /* 輸出的表指定參數(shù) */
        specifyTables: '',
        specifyTablePrefixes: '',
        specifyTableSuffixes: '',
        ignoreTables: '',
        ignoreTablePrefixes: '',
        ignoreTableSuffixes: ''
      },
      formRules: {
        docName: [{ required: true, message: '請?zhí)顚懳募?, trigger: 'blur' }],
        docTitle: [{ required: true, message: '請?zhí)顚懳臋n標(biāo)題', trigger: 'blur' }],
        version: [{ required: true, message: '請?zhí)顚懳臋n版本', trigger: 'blur' }],
        description: [{ required: true, message: '請?zhí)顚懳臋n描述', trigger: 'blur' }],
        organization: [{ required: true, message: '請?zhí)顚懡M織機構(gòu)', trigger: 'blur' }],
        organizationUrl: [{ required: true, message: '請?zhí)顚懡M織機構(gòu)鏈接', trigger: 'blur' }],
        docType: [{ required: true, message: '請選擇文件類型', trigger: 'blur' }]
      },
      docTypes: [
        { label: 'html | .html', value: 1 },
        { label: 'word | .docx', value: 2 },
        { label: 'markdown | .md', value: 3 }
      ]
    }
  },
  methods: {
    formSubmit() {
      this.$refs[this.formRef].validate(async(isValid) => {
        if (!isValid) return
        const docParam = JSON.parse(JSON.stringify(this.form))
        docParam.specifyTables = docParam.specifyTables ? docParam.specifyTables.split(',') : ''
        docParam.specifyTablePrefixes = docParam.specifyTablePrefixes ? docParam.specifyTablePrefixes.split(',') : ''
        docParam.specifyTableSuffixes = docParam.specifyTableSuffixes ? docParam.specifyTableSuffixes.split(',') : ''
        docParam.ignoreTables = docParam.ignoreTables ? docParam.ignoreTables.split(',') : ''
        docParam.ignoreTablePrefixes = docParam.ignoreTablePrefixes ? docParam.ignoreTablePrefixes.split(',') : ''
        docParam.ignoreTableSuffixes = docParam.ignoreTableSuffixes ? docParam.ignoreTableSuffixes.split(',') : ''
        const res = await getDbSchemaDoc(this.param.dbConnId, this.param.schemaName, docParam)
        axiosDownloadFile(res)
      })
    },
    formCancel() {
      this.$parent.$parent['closeAllDrawer']()
    }
  }
}
</script>

二、實現(xiàn)導(dǎo)出SQL腳本

先看導(dǎo)出表結(jié)構(gòu)，表結(jié)構(gòu)的SQL是有SQL可以直接查詢的

mysql> SHOW CREATE TABLE sys_role;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sys_role | CREATE TABLE `sys_role` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '角色主鍵',
  `role_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '角色名稱',
  `role_value` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '角色值',
  `creator` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '創(chuàng)建人',
  `create_time` datetime DEFAULT NULL COMMENT '創(chuàng)建時間',
  `updater` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '更新人',
  `update_time` datetime DEFAULT NULL COMMENT '更新時間',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='系統(tǒng)角色表' |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.10 sec)

但是導(dǎo)出數(shù)據(jù)腳本就沒有，這個SQL腳本難就難在這里，于是我百度了下看看別人的思路：

然后參考了這位的：

他的辦法就是從查詢的結(jié)果集里面提取key和value一個個拼過去

但是我們都知道，前面的INSERT的句式是固定的，只是值動態(tài)的，所以我不太接受他這個拼的做法

因為既然是固定的東西就不應(yīng)該浪費資源去反復(fù)做，一開始就拼好固定句式再加動態(tài)值就行了

1、提取表的所有字段：

SQL我就不細說過程了，直接貼：

mysql> SELECT GROUP_CONCAT(DISTINCT CONCAT('`', COLUMN_NAME, '`') ORDER BY `ORDINAL_POSITION` ASC SEPARATOR ', ') AS `fields` FROM `information_schema`.`COLUMNS` WHERE TABLE_SCHEMA = 'tt' AND TABLE_NAME = 'sys_role';
+-------------------------------------------------------------------------------------+
| fields                                                                              |
+-------------------------------------------------------------------------------------+
| `id`, `role_name`, `role_value`, `creator`, `create_time`, `updater`, `update_time` |
+-------------------------------------------------------------------------------------+

通過上面的SQL你就可以直接得到SELECT要查詢的字段，

再把字段用SEPARATOR的字符分割成數(shù)組，就得到結(jié)果集需要按順序遍歷提取的字段了

由于Hutool的查詢默認(rèn)小寫處理，所以這個結(jié)果默認(rèn)也小處理，順便移除反引號（解決key取不到的問題）

/* 獲取這個表的字段, 按原始定位排序， 并列為一行, 逗號空格分隔 */
Entity fieldsEntity= db.queryOne("SELECT GROUP_CONCAT(DISTINCT CONCAT('`', COLUMN_NAME, '`') ORDER BY `ORDINAL_POSITION` ASC SEPARATOR ', ') AS `fields` FROM `information_schema`.`COLUMNS` WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?", schemaName, tableName);
String fields = fieldsEntity.get("fields").toString();
String[] columns = fields.trim().toLowerCase().replaceAll("`", "").split(", ");

有前面的字段，就有了固定句式了

?再通過翻頁查詢，動態(tài)組裝值就可以了

如果值是NULL，就寫NULL，數(shù)值和時間都可以是字符型來存

/**
* 翻頁查詢來組裝INSERT腳本 緩解IO壓力
* @param builder 腳本組裝對象
* @param db sql執(zhí)行對象
* @param schemaName 庫名
* @param tableName 表明
* @param insertSqlPrePart INSERT前半部分
* @param columns 列名數(shù)組
*/
public void batchBuildInsertSql(StringBuilder builder, Db db, String schemaName, String tableName, String insertSqlPrePart, String[] columns) {
   try {
       int pageIdx = 0;
       int pageSize = 1000;
       int pageTotal = 1;
 
       final Entity condition = Entity.create();
       condition.setTableName("`" + schemaName + "`.`" + tableName + "`");
 
       while(pageIdx < pageTotal) {
           PageResult<Entity> pageResult = db.page(condition, new Page(pageIdx, pageSize));
           if (CollectionUtils.isEmpty(pageResult)) return;
 
           pageTotal = pageResult.getTotalPage();
           ++ pageIdx;
 
           /* 組裝INSERT語句 */
           for (Entity recordEntity : pageResult) {
               builder.append(insertSqlPrePart);
               List<String> valList = new ArrayList<>();
               for (String column : columns) {
                   Object val = recordEntity.get(column.trim());
                   boolean isEmpty = Objects.isNull(val);
                   valList.add(isEmpty ? "NULL" : "'" + val + "'");
               }
               String joinedValues = String.join(", ", valList);
               builder.append(joinedValues).append(");\n");
           }
           builder.append("\n\n");
       }
   } catch (Exception e) {
       e.printStackTrace();
   }
}

三、Axios下載文件的問題

1、文件的接口都需要聲明響應(yīng)類型是blob，這要才能認(rèn)定為文件

/**
 * 下載模板文件
 * @param data
 * @returns {*}
 */
export function downloadTemplate(data) {
  return request({
    url: `${PATH}/download`,
    method: 'post',
    responseType: 'blob',
    data
  })
}
 
/**
 * 下載全部文件
 * @param data
 * @returns {*}
 */
export function downloadAllTemplate(data) {
  return request({
    url: `${PATH}/download-all`,
    method: 'post',
    responseType: 'blob',
    data
  })
}

2、改寫Request.js，因為默認(rèn)的axios是不接受任何文件形式的響應(yīng)對象

只要沒有code就直接報錯，但是接口實際上是正常返回

?改寫就是追加前置條件，如果請求頭說明了content-type信息，并且能在以下type中找到，就直接返回整個response對象

const fileHeaders = [
  'text/plain',
  'text/html',
  'application/vnd.ms-word2006ml',
  'text/x-web-markdown',
  'application/x-rar-compressed',
  'application/x-zip-compressed',
  'application/octet-stream',
  'application/zip',
  'multipart/x-zip',
  'text/x-sql'
]

?然后axios包裝的response，不能直接下載，要給瀏覽器包裝之后通過超鏈接點擊實現(xiàn)下載：

/**
 * 轉(zhuǎn)成文件流之后，可以通過模擬點擊實現(xiàn)下載效果
 * axios下載文件， 請求接口需要追加屬性：responseType: 'blob'
 * @param response
 */
export function axiosDownloadFile(response) {
  /* js創(chuàng)建一個a標(biāo)簽 */
  const element = document.createElement('a')
  /* 文檔流轉(zhuǎn)化成Base64 */
  const href = window.URL.createObjectURL(response.data)
  element.href = href
  /* 下載后文件名 */
  element.download = decodeURIComponent(response.headers['content-disposition'].match(/filename=(.*)/)[1])
  document.body.appendChild(element)
  /* 點擊下載 */
  element.click()
  /* 下載完成移除元素 */
  document.body.removeChild(element)
  window.URL.revokeObjectURL(href)
}

四、postgresql數(shù)據(jù)庫實現(xiàn)類似于MySQL的show create table功能

在MySQL數(shù)據(jù)庫中，可以通過show create table查詢表的create sql 語句，
但在PostgreSQL數(shù)據(jù)庫中，沒有類似的命令，但可以通過function 來實現(xiàn)

在PostgreSQL 11中測試
實現(xiàn)后，使用示例如下：

select 'movie' AS table, showcreatetable('public','movie') AS create_table

運行結(jié)果如下

?在pg數(shù)據(jù)庫新建兩個函數(shù)：showcreatetable和findattname函數(shù)，如下截圖

showcreatetable函數(shù)

CREATE OR REPLACE FUNCTION "public"."showcreatetable"("namespace" varchar, "tablename" varchar)
  RETURNS "pg_catalog"."varchar" AS $BODY$
declare 
tableScript character varying default '';
 
begin
-- columns
tableScript:=tableScript || ' CREATE TABLE '|| tablename|| ' ( '|| chr(13)||chr(10) || array_to_string(
  array(
select '   ' || concat_ws('  ',fieldName, fieldType, fieldLen, indexType, isNullStr, fieldComment ) as column_line
from (
select a.attname as fieldName,format_type(a.atttypid,a.atttypmod) as fieldType,' ' as fieldLen,
(case  when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='p')>0 then 'PRI' 
when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='u')>0 then 'UNI' 
when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='f')>0 then 'FRI' 
 else '' end) as indexType,
(case when a.attnotnull=true then 'not null' else 'null' end) as isNullStr,
' 'as fieldComment 
from pg_attribute a where attstattarget=-1 and attrelid = (select c.oid from pg_class c,pg_namespace n where c.relnamespace=n.oid and n.nspname =namespace and relname =tablename)

) as string_columns
),','||chr(13)||chr(10)) || ',';
 
 
-- 約束
tableScript:= tableScript || chr(13)||chr(10) || array_to_string(
array(
	select concat('   CONSTRAINT ',conname ,c ,u,p,f)   from (
		select conname,
		case when contype='c' then  ' CHECK('|| ( select findattname(namespace,tablename,'c') ) ||')' end  as c  ,
		case when contype='u' then  ' UNIQUE('|| ( select findattname(namespace,tablename,'u') ) ||')' end as u ,
		case when contype='p' then ' PRIMARY KEY ('|| ( select findattname(namespace,tablename,'p') ) ||')' end  as p  ,
		case when contype='f' then ' FOREIGN KEY('|| ( select findattname(namespace,tablename,'u') ) ||') REFERENCES '|| 
		(select p.relname from pg_class p where p.oid=c.confrelid )  || '('|| ( select findattname(namespace,tablename,'u') ) ||')' end as  f
		from pg_constraint c
		where contype in('u','c','f','p') and conrelid=( 
			select oid  from pg_class  where relname=tablename and relnamespace =(
			select oid from pg_namespace where nspname = namespace
			)
		 )
	) as t  
) ,',' || chr(13)||chr(10) ) || chr(13)||chr(10) ||' ); ';

-- indexs 
-- CREATE UNIQUE INDEX pg_language_oid_index ON pg_language USING btree (oid); -- table pg_language
 
 
-- 
/** **/
--- 獲取非約束索引 column
-- CREATE UNIQUE INDEX pg_language_oid_index ON pg_language USING btree (oid); -- table pg_language
tableScript:= tableScript || chr(13)||chr(10) || chr(13)||chr(10) || array_to_string(
	array(
		select 'CREATE INDEX ' || indexrelname || ' ON ' || tablename || ' USING btree '|| '(' || attname || ');' from (
		 SELECT 
		    i.relname AS indexrelname ,  x.indkey, 
		    
		    ( select array_to_string (
			array( 
				select a.attname from pg_attribute a where attrelid=c.oid and a.attnum in ( select unnest(x.indkey) )
 
			     ) 
		     ,',' ) )as attname
		    
		   FROM pg_class c
		   JOIN pg_index x ON c.oid = x.indrelid
		   JOIN pg_class i ON i.oid = x.indexrelid
		   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
		   WHERE  c.relname=tablename and i.relname not in
			  ( select constraint_name from information_schema.key_column_usage  where  table_name=tablename  )
		)as t
) ,','|| chr(13)||chr(10));
			
 
-- COMMENT COMMENT ON COLUMN sys_activity.id IS '主鍵';
tableScript:= tableScript || chr(13)||chr(10) || chr(13)||chr(10) || array_to_string(
array(
SELECT 'COMMENT ON COLUMN ' || tablename || '.' || a.attname ||' IS  '|| ''''|| d.description ||''''
FROM pg_class c
JOIN pg_description d ON c.oid=d.objoid
JOIN pg_attribute a ON c.oid = a.attrelid
WHERE c.relname=tablename
AND a.attnum = d.objsubid),';'|| chr(13)||chr(10)) ;
 
return tableScript;
 
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

findattname函數(shù)

查詢表某一個約束的列名并轉(zhuǎn)換為一個字符串

CREATE OR REPLACE FUNCTION findattname(namespace character varying, tablename character varying, ctype character varying)
  RETURNS character varying as $BODY$
 
declare
tt oid ;
aname character varying default '';
 
begin
       tt := oid from pg_class where relname= tablename and relnamespace =(select oid from pg_namespace  where nspname=namespace) ;
       aname:=  array_to_string(
		array(
		       select a.attname  from pg_attribute  a 
				where a.attrelid=tt and  a.attnum   in (		
				select unnest(conkey) from pg_constraint c where contype=ctype 
				and conrelid=tt  and array_to_string(conkey,',') is not null  
			) 
		),',');
	
	return aname;
end
$BODY$ LANGUAGE plpgsql;

源碼倉庫，已開放訪問：

https://download.csdn.net/download/askuld/88216937文章來源地址http://www.zghlxwxcb.cn/news/detail-650514.html

到了這里，關(guān)于Springboot + Vue ElementUI 實現(xiàn)MySQL&&Postgresql可視化的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！