Springboot集成Screw生成数据库表结构文档

痛定思痛。 2022-09-03 13:12 225阅读 0赞

### Springboot集成Screw生成数据库表结构文档 ###

*  1. 业务需求
 *  2. Screw概述
 *   *  2.1 screw简介
     *  2.2 screw特点
     *  2.3 数据库支持
     *  2.4 原理分析
 *  3. 实现验证
 *   *  3.1 代码实现
     *  3.2 文档验证

# 1. 业务需求 #

业务开发中有时候数据库会出现变更，手动维护麻烦，且容易遗漏，screw要解决的问题就是自动生成数据库文档。这点与swagger有异曲同工之处。

# 2. Screw概述 #

## 2.1 screw简介 ##

Screw企业级开发过程中，一颗永不生锈的螺丝钉  
screw (螺丝钉) 英:\[skruː\] ~ 简洁好用的数据库表结构文档生成工具。

## 2.2 screw特点 ##

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

## 2.3 数据库支持 ##

MySQL  
MariaDB  
TIDB  
Oracle  
SqlServer  
PostgreSQL

## 2.4 原理分析 ##

screw实质上是根据数据库中information\_schema数据库下COLUMNS表，查询出指定数据库下的不同表对应的表结构信息，然后生成Word，html，marketdown。封装的相对比较完善，轻巧，便捷。

**screw官网地址：**[https://github.com/pingfangushi/screw][https_github.com_pingfangushi_screw]

# 3. 实现验证 #

## 3.1 代码实现 ##

**项目架构：springboot+screw+swagger**  
pom.xml

<?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.3</version>
            <relativePath/> 
        </parent>
        <groupId>com.zrj</groupId>
        <artifactId>screw</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>screw</name>
        <description>Demo project for Spring Boot</description>
        <properties>
            <java.version>1.8</java.version>
        </properties>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>
            
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>2.1.4</version>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <scope>runtime</scope>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
            </dependency>
            
            <dependency>
                <groupId>org.freemarker</groupId>
                <artifactId>freemarker</artifactId>
                <version>2.3.30</version>
            </dependency>
            <dependency>
                <groupId>cn.smallbun.screw</groupId>
                <artifactId>screw-core</artifactId>
                <version>1.0.5</version>
            </dependency>
            
            <dependency>
                <groupId>com.google.guava</groupId>
                <artifactId>guava</artifactId>
                <version>30.1.1-jre</version>
            </dependency>
            <dependency>
                <groupId>cn.hutool</groupId>
                <artifactId>hutool-all</artifactId>
                <version>5.6.3</version>
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <optional>true</optional>
            </dependency>
            <dependency>
                <groupId>org.aspectj</groupId>
                <artifactId>aspectjweaver</artifactId>
            </dependency>
            
            <dependency>
                <groupId>io.springfox</groupId>
                <artifactId>springfox-swagger2</artifactId>
                <version>2.6.1</version>
            </dependency>
            <dependency>
                <groupId>io.springfox</groupId>
                <artifactId>springfox-swagger-ui</artifactId>
                <version>2.6.1</version>
            </dependency>
            <dependency>
                <groupId>commons-io</groupId>
                <artifactId>commons-io</artifactId>
                <version>2.6</version>
            </dependency>
            
            <dependency>
                <groupId>javax.validation</groupId>
                <artifactId>validation-api</artifactId>
                <version>2.0.1.Final</version>
            </dependency>
            <dependency>
                <groupId>org.hibernate.validator</groupId>
                <artifactId>hibernate-validator</artifactId>
                <version>6.0.13.Final</version>
            </dependency>
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
        </build>
    
    </project>

application.properties

server.port=8080
    
    # 数据库连接诶
    spring.datasource.url=jdbc:mysql://localhost:3306/category?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
    spring.datasource.username=root
    spring.datasource.password=123456
    spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
    spring.datasource.xa.properties.useInformationSchema=true

ExceptionHandlerAdvice

package com.zrj.screw.aop;
    
    import com.fasterxml.jackson.databind.JsonMappingException;
    import com.zrj.screw.entity.Response;
    import lombok.extern.slf4j.Slf4j;
    import org.springframework.http.converter.HttpMessageNotReadableException;
    import org.springframework.util.CollectionUtils;
    import org.springframework.validation.ObjectError;
    import org.springframework.web.bind.MethodArgumentNotValidException;
    import org.springframework.web.bind.MissingServletRequestParameterException;
    import org.springframework.web.bind.annotation.ControllerAdvice;
    import org.springframework.web.bind.annotation.ExceptionHandler;
    import org.springframework.web.bind.annotation.ResponseBody;
    import org.springframework.web.method.HandlerMethod;
    import org.springframework.web.method.annotation.MethodArgumentTypeMismatchException;
    
    import javax.validation.ConstraintViolation;
    import javax.validation.ConstraintViolationException;
    import java.util.List;
    import java.util.Set;
    
    /** * 异常统一处理 * * @author zrj * @since 2021/7/31 **/
    @ControllerAdvice
    @ResponseBody
    @Slf4j
    public class ExceptionHandlerAdvice { 
        /** * Exception */
        @ExceptionHandler(Exception.class)
        public Response<String> handleGlobalException(Exception exception, HandlerMethod handlerMethod) { 
            if (exception instanceof MethodArgumentNotValidException) { 
                List<ObjectError> errors = ((MethodArgumentNotValidException) exception).getBindingResult().getAllErrors();
                StringBuilder sb = new StringBuilder();
                if (!CollectionUtils.isEmpty(errors)) { 
                    for (ObjectError error : errors) { 
                        if (sb.length() != 0) { 
                            sb.append("，");
                        }
    
                        sb.append(error.getDefaultMessage());
                    }
                }
                return Response.fail(sb.toString());
            }
    
            // 约束异常
            if (exception instanceof ConstraintViolationException) { 
                Set<ConstraintViolation<?>> exceptionSet = ((ConstraintViolationException) exception).getConstraintViolations();
                StringBuilder sb = new StringBuilder();
                if (!CollectionUtils.isEmpty(exceptionSet)) { 
                    for (ConstraintViolation<?> set : exceptionSet) { 
                        if (sb.length() != 0) { 
                            sb.append("，");
                        }
    
                        sb.append(set.getMessageTemplate());
                    }
                }
    
                return Response.fail(sb.toString());
            }
    
            // 参数类型转换异常处理
            if (exception instanceof MethodArgumentTypeMismatchException) { 
                return Response.fail(((MethodArgumentTypeMismatchException) exception).getName() + " 类型不匹配");
            }
    
            if (exception instanceof JsonMappingException) { 
                return Response.fail("JSON格式错误, " + exception.getLocalizedMessage());
            }
    
            if (exception instanceof HttpMessageNotReadableException) { 
                return Response.fail("请求体格式错误, " + exception.getLocalizedMessage());
            }
            if (exception instanceof MissingServletRequestParameterException) { 
                String paramName = ((MissingServletRequestParameterException) exception).getParameterName();
                return Response.fail(paramName + " 不能为空");
            }
    
            //if (exception instanceof MarketingException) { 
            // MarketingException marketingException = (MarketingException) exception;
            // return RdfaResult.fail(marketingException.getErrorCodeEnum().getCode(), exception.getMessage());
            //}
    
            // 其他异常打印日志
            log.error("{}.{} error, ", handlerMethod.getBeanType().getSimpleName(), handlerMethod.getMethod().getName(), exception);
    
            //if (exception instanceof RpcException) { 
            // return RdfaResult.fail(ErrorCodeEnum.RPC_ERROR.getCode(), "RPC调用错误，请稍后重试");
            //}
    
            return Response.fail("服务器内部错误，请联系开发人员！");
        }
    }

SwaggerConfig

package com.zrj.screw.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.service.Contact;
    import springfox.documentation.spi.DocumentationType;
    import springfox.documentation.spring.web.plugins.Docket;
    import springfox.documentation.swagger2.annotations.EnableSwagger2;
    
    /** * swagger2的配置文件 * * @author zrj * @date 2021/7/18 * @since V1.0 **/
    @Configuration
    @EnableSwagger2
    public class SwaggerConfig { 
        /** * swagger2的配置文件，这里可以配置swagger2的一些基本的内容，比如扫描的包等等 * * @return */
        @Bean
        public Docket createRestApi() { 
            return new Docket(DocumentationType.SWAGGER_2)
                    .apiInfo(apiInfo())
                    .select()
                    // 为当前包路径
                    .apis(RequestHandlerSelectors.basePackage("com.zrj.screw.controller")).paths(PathSelectors.any())
                    .build();
        }
    
        /** * 构建 api文档的详细信息函数,注意这里的注解引用的是哪个 * * @return */
        private ApiInfo apiInfo() { 
            return new ApiInfoBuilder()
                    // 页面标题
                    .title("数据库文档管理")
                    // 创建人信息
                    .contact(new Contact("jerry", "http://jerry.com", "jerry@163.com"))
                    // 版本号
                    .version("Screw1.0")
                    // 描述
                    .description("数据库文档生成工具")
                    .build();
        }
    }

Response

package com.zrj.screw.entity;
    
    import lombok.Data;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Component;
    
    /** * 结果集封装 * * @author zrj * @date 2021/6/2 * @since V1.0 **/
    @Data
    @Component
    public class Response<T> { 
        private static ResponseCode responseCode;
        /** * 提示消息 */
        private String message;
    
        /** * 具体返回的数据 */
        private T data;
    
        /** * 状态码 */
        private String code;
    
        private Response(String code, String message, T data) { 
            this.message = message;
            this.code = code;
            this.data = data;
        }
    
        private Response(String code, String msg) { 
            this.message = msg;
            this.code = code;
        }
    
        @Autowired
        public Response(ResponseCode responseCode) { 
            Response.responseCode = responseCode;
        }
    
        /** * 返回成功Response对象 */
        public static <T> Response<T> success(String successMessage, T data) { 
            return new Response<>(responseCode.getSuccessCode(), successMessage, data);
        }
    
        /** * 返回错误Response对象 */
        public static <T> Response<T> fail(String errorMessage) { 
            return new Response<>(responseCode.getErrorCode(), errorMessage);
        }
    }

ResponseCode

package com.zrj.screw.entity;
    
    import lombok.Data;
    import org.springframework.stereotype.Component;
    
    /** * 响应码 * * @author zrj * @date 2021/6/2 * @since V1.0 **/
    @Data
    @Component
    public class ResponseCode { 
        public String successCode = "200";
    
        public String errorCode = "500";
    
        public String authErrorCode = "300";
    }

ScrewService

package com.zrj.screw.service;
    
    /** * 生成数据库文档接口 * * @author zrj * @since 2021/8/7 **/
    public interface ScrewService { 
    
        boolean contextLoads();
    }

ScrewServiceImpl

package com.zrj.screw.service.impl;
    
    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.zrj.screw.service.ScrewService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.context.ApplicationContext;
    import org.springframework.stereotype.Service;
    
    import javax.sql.DataSource;
    import java.util.ArrayList;
    import java.util.Arrays;
    import java.util.List;
    
    /** * 生成数据库文档实现类 * * @author zrj * @since 2021/8/7 **/
    @Service
    public class ScrewServiceImpl implements ScrewService { 
    
        @Autowired
        ApplicationContext applicationContext;
    
        @Override
        public boolean contextLoads() { 
            try { 
                DataSource dataSourceMysql = applicationContext.getBean(DataSource.class);
    
                // 生成文件配置
                EngineConfig engineConfig = EngineConfig.builder()
                        // 生成文件路径，自己mac本地的地址，这里需要自己更换下路径
                        .fileOutputDir("D:\\work\\ennwork\\test\\screw\\db")
                        // 打开目录
                        .openOutputDir(false)
                        // 文件类型
                        .fileType(EngineFileType.HTML)
                        // 生成模板实现
                        .produceType(EngineTemplateType.freemarker)
                        // 自定义文件名，默认：数据库+描述（db_marketing_生成文档信息描述_1.0.0.html）
                        //.fileName("自定义数据库文档")
                        .build();
    
                // 生成文档配置（包含以下自定义版本号、描述等配置连接）
                Configuration config = Configuration.builder()
                        .version("1.0.0")
                        .description("生成文档信息描述")
                        .dataSource(dataSourceMysql)
                        .engineConfig(engineConfig)
                        .produceConfig(getProcessConfig())
                        .build();
    
                // 执行生成
                new DocumentationExecute(config).execute();
    
                return true;
            } catch (Exception e) { 
                System.out.println("生成数据库文档异常：" + e);
            }
            return false;
        }
    
    
        /** * 配置想要生成的表+ 配置想要忽略的表 * * @return 生成表配置 */
        public static ProcessConfig getProcessConfig() { 
            // 忽略表名
            List<String> ignoreTableName = Arrays.asList("aa", "test_group");
            // 忽略表前缀，如忽略a开头的数据库表
            List<String> ignorePrefix = Arrays.asList("a", "t");
            // 忽略表后缀
            List<String> ignoreSuffix = Arrays.asList("_test", "czb_");
    
            return ProcessConfig.builder()
                    //根据名称指定表生成
                    .designatedTableName(new ArrayList<>())
                    //根据表前缀生成
                    .designatedTablePrefix(new ArrayList<>())
                    //根据表后缀生成
                    .designatedTableSuffix(new ArrayList<>())
                    //忽略表名
                    .ignoreTableName(ignoreTableName)
                    //忽略表前缀
                    .ignoreTablePrefix(ignorePrefix)
                    //忽略表后缀
                    .ignoreTableSuffix(ignoreSuffix).build();
        }
    
    }

ScrewController

package com.zrj.screw.controller;
    
    import com.zrj.screw.entity.Response;
    import com.zrj.screw.service.ScrewService;
    import io.swagger.annotations.Api;
    import io.swagger.annotations.ApiOperation;
    import org.springframework.web.bind.annotation.*;
    
    import javax.annotation.Resource;
    
    /** * 数据库文档控住器 * * @author zrj * @since 2021/8/7 **/
    @RestController
    @RequestMapping("/screw")
    @Api(tags = "数据库文档管理", description = "生成数据库文档")
    public class ScrewController { 
    
        @Resource
        private ScrewService screwService;
    
        /** * 测试服务 */
        @GetMapping("/test")
        @ApiOperation(value = "测试", notes = "测试方法的备注说明", httpMethod = "GET")
        public Response test() { 
            return Response.success("查询成功", "我是测试服务");
        }
    
        /** * 生成数据库文档 */
        @GetMapping("/contextLoads")
        @ApiOperation(value = "生成数据库文档", notes = "生成数据库文档", httpMethod = "GET")
        public Response contextLoads() { 
            boolean contextLoads = screwService.contextLoads();
            if (contextLoads) { 
                return Response.success("查询成功", contextLoads);
            }
            return Response.fail("查询失败");
        }
    
    }

## 3.2 文档验证 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70]![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70 1]

[https_github.com_pingfangushi_screw]: https://github.com/pingfangushi/screw
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/53810e6fb6fa45a8bc8d30032048aad1.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70 1]: /images/20220829/2e3c78aa79194d81897ab14ede971b45.png