利用java代码一键生成数据库文档

1 screw（螺丝钉）介绍

1.2 特点

• 简洁、轻量、设计良好
• 多数据库支持
• 多种格式文档
• 灵活扩展
• 支持自定义模板

1.2 文档生成支持

• html
• word
• markdown

1.3 文档截图

1.3.1 html

1.3.2 word

1.3.3 markdwon

2 SpringBoot使用

2.1 依赖

<!--数据库文档核心依赖-->
<dependency>
  <groupId>cn.smallbun.screw</groupId>
  <artifactId>screw-core</artifactId>
  <version>1.0.2</version>
</dependency>
<!-- HikariCP -->
<dependency>
  <groupId>com.zaxxer</groupId>
  <artifactId>HikariCP</artifactId>
  <version>3.4.5</version>
</dependency>
<!--mysql driver-->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.20</version>
</dependency>

2.2 测试

package com.example.adminlayui;

import cn.smallbun.screw.core.Configuration;
import cn.smallbun.screw.core.engine.EngineConfig;
import cn.smallbun.screw.core.engine.EngineFileType;
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 org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import javax.sql.DataSource;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.nio.charset.StandardCharsets;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@SpringBootTest
class ScrewApplicationTests {

    @Test
    void contextLoads() throws SQLException, IOException {
        String dbName = "admin2";
        HikariConfig config = new HikariConfig();
        config.setDriverClassName("com.mysql.cj.jdbc.Driver");
        config.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/" + dbName +" ?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=GMT%2B8&useInformationSchema=true");
        config.setUsername("root");
        config.setPassword("123456");
        config.addDataSourceProperty("useInformationSchema", "true");
        config.setMinimumIdle(2);
        config.setMaximumPoolSize(5);
        DataSource ds = new HikariDataSource(config);
        //生成文件路径
        String userDir = System.getProperty("user.dir") + "\\src\\test\\java\\com\\pdool\\";
        System.out.println(userDir);
        SimpleDateFormat dataFormat = new SimpleDateFormat("yyyyMMdd");
        String versionStr = dataFormat.format(new Date());
        //忽略表
        List<String> ignoreTable = new ArrayList<>();
        //忽略表前缀
        List<String> ignorePrefix = new ArrayList<>();
        //忽略表后缀
        List<String> ignoreSuffix = new ArrayList<>();
        ignoreSuffix.add("_test");
        ignoreSuffix.add("test");

        for (int i = 0; i < 10; i++) {
            ignoreSuffix.add(String.valueOf(i));
        }
        //生成HTML模板
        createHtml(ds, userDir, versionStr, ignoreTable, ignorePrefix, ignoreSuffix);
        //生成SQL建表语句
        createSql(dbName, ds, userDir, versionStr, ignoreTable, ignorePrefix, ignoreSuffix);
    }


    /**
     * 创建html
     * @param dataSource
     * @param userDir
     * @param versionStr
     * @param ignoreTable
     * @param ignorePrefix
     * @param ignoreSuffix
     */
    public static void createHtml(DataSource dataSource, String userDir, String versionStr, List<String> ignoreTable, List<String> ignorePrefix, List<String> ignoreSuffix) {
        //生成配置
        EngineConfig engineConfig = EngineConfig.builder()
                //生成文件路径
                .fileOutputDir(userDir)
                //打开目录
                .openOutputDir(false)
                //文件类型
                .fileType(EngineFileType.HTML)
                //生成模板实现
                .produceType(EngineTemplateType.freemarker)
                .build();

        ProcessConfig processConfig = ProcessConfig.builder()
                //忽略表名
                .ignoreTableName(ignoreTable)
                //忽略表前缀
                .ignoreTablePrefix(ignorePrefix)
                //忽略表后缀
                .ignoreTableSuffix(ignoreSuffix)
                .build();

        Configuration config = Configuration.builder()
                //版本
                .version(versionStr)
                //数据库描述
                .description("数据库文档")
                //数据源
                .dataSource(dataSource)
                //生成配置
                .engineConfig(engineConfig)
                //生成配置
                .produceConfig(processConfig).build();

        new DocumentationExecute(config).execute();
    }

    /**
     * 生成建表sql
     * @param dbName
     * @param dataSource
     * @param userDir
     * @param versionStr
     * @param ignoreTable
     * @param ignorePrefix
     * @param ignoreSuffix
     * @throws IOException
     * @throws SQLException
     */
    public static void createSql(String dbName, DataSource dataSource, String userDir, String versionStr, List<String> ignoreTable, List<String> ignorePrefix, List<String> ignoreSuffix) throws IOException, SQLException {
        Statement tmt = null;
        PreparedStatement pstmt = null;
        List<String> createSqlList = new ArrayList<>();
        String sql = "select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = '"+dbName+"' and TABLE_TYPE = 'BASE TABLE'";
        tmt = dataSource.getConnection().createStatement();
        pstmt = dataSource.getConnection().prepareStatement(sql);
        ResultSet res = tmt.executeQuery(sql);
        while (res.next()) {
            String tableName = res.getString(1);
            if (tableName.contains("`")) {
                continue;
            }
            if (ignoreTable.contains(tableName)) {
                continue;
            }
            boolean isContinue = false;
            for (String prefix : ignorePrefix) {

                if (tableName.startsWith(prefix)) {
                    isContinue = true;
                    break;
                }
            }
            if (isContinue) {
                continue;
            }
            for (String suffix : ignoreSuffix) {
                if (tableName.startsWith(suffix)) {
                    isContinue = true;
                    break;
                }
            }
            if (isContinue) {
                continue;
            }
            ResultSet rs = pstmt.executeQuery("show create Table `" + tableName + "`");

            while (rs.next()) {
                createSqlList.add("DROP TABLE IF EXISTS '" + tableName + "'");
                createSqlList.add(rs.getString(2));
            }
        }

        String head = "-- 数据库建表语句 \r\n";
        head += "-- db:" + dbName + " version: " + versionStr + "\r\n";
        String collect = String.join(";\r\n", createSqlList);
        collect = head + collect + ";";
        string2file(collect, userDir + dbName + "_" + versionStr + ".sql");
    }

    public static void string2file(String collect, String dirStr) throws IOException {
        System.out.println("文件地址  "+ dirStr);
        OutputStreamWriter osw = null;
        try {
            osw = new OutputStreamWriter(new FileOutputStream(new File(dirStr)), StandardCharsets.UTF_8);
            osw.write(collect);
            osw.flush();
        } finally {
            if (osw != null) {
                osw.close();
            }
        }
    }

}

2.3 结果

3 生成类型

3.1 生成HTML

//生成配置
EngineConfig engineConfig = EngineConfig.builder()
    //生成文件路径
    .fileOutputDir(userDir)
    //打开目录
    .openOutputDir(false)
    //文件类型
    .fileType(EngineFileType.HTML)
    //生成模板实现
    .produceType(EngineTemplateType.freemarker)
    .build();

3.2 生成Word

//生成配置
EngineConfig engineConfig = EngineConfig.builder()
    //生成文件路径
    .fileOutputDir(userDir)
    //打开目录
    .openOutputDir(false)
    //文件类型
    .fileType(EngineFileType.WORD)
    //生成模板实现
    .produceType(EngineTemplateType.freemarker)
    .build();

3.3 生成Markdwon

//生成配置
EngineConfig engineConfig = EngineConfig.builder()
    //生成文件路径
    .fileOutputDir(userDir)
    //打开目录
    .openOutputDir(false)
    //文件类型
    .fileType(EngineFileType.MD)
    //生成模板实现
    .produceType(EngineTemplateType.freemarker)
    .build();

4 扩展pojo生成

4.1 依赖

<dependency>    
  <groupId>cn.smallbun.screw</groupId>    
  <artifactId>screw-extension</artifactId>    
  <version>${lastVersion}</version>    
</dependency>    

4.2 代码

/**    
 * pojo生成    
 */    
void pojoGeneration() {    
    //数据源    
    HikariConfig hikariConfig = new HikariConfig();    
    hikariConfig.setDriverClassName("com.mysql.cj.jdbc.Driver");    
    hikariConfig.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/screw");    
    hikariConfig.setUsername("screw");    
    hikariConfig.setPassword("screw");    
    //设置可以获取tables remarks信息    
    hikariConfig.addDataSourceProperty("useInformationSchema", "true");    
    hikariConfig.setMinimumIdle(2);    
    hikariConfig.setMaximumPoolSize(5);    
    DataSource dataSource = new HikariDataSource(hikariConfig);    
    
    ProcessConfig processConfig = ProcessConfig.builder()    
        //指定生成逻辑、当存在指定表、指定表前缀、指定表后缀时，将生成指定表，其余表不生成、并跳过忽略表配置    
        //根据名称指定表生成    
        .designatedTableName(new ArrayList<>())    
        //根据表前缀生成    
        .designatedTablePrefix(new ArrayList<>())    
        //根据表后缀生成    
        .designatedTableSuffix(new ArrayList<>()).build();    
    
    //设置生成pojo相关配置    
    PojoConfiguration config = new PojoConfiguration();    
    //设置文件存放路径    
    config.setPath("/cn/smallbun/screw/");    
    //设置包名    
    config.setPackageName("cn.smallbun.screw");    
    //设置是否使用lombok    
    config.setUseLombok(false);    
    //设置数据源    
    config.setDataSource(dataSource);    
    //设置命名策略    
    config.setNameStrategy(new HumpNameStrategy());    
    //设置表过滤逻辑    
    config.setProcessConfig(processConfig);    
    //执行生成    
    new PojoExecute(config).execute();    
}