mysql数据库导出工具-screw（可根据注释过滤数据表）
1. idea 创建springboot 项目
包名：com.ling.mysql.file
2. pom文件配置
<!-- screw核心 -->
<dependency>
<groupId>cn.smallbun.screw</groupId>
<artifactId>screw-core</artifactId>
<version>1.0.3</version>
</dependency>
<!-- HikariCP -->
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>3.4.5</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
2.1 java-main版
pom配置：
<plugin>
<groupId>cn.smallbun.screw</groupId>
<artifactId>screw-maven-plugin</artifactId>
<version>1.0.3</version>
<dependencies>
<!-- 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>
</dependencies>
</plugin>
java-main代码：
package com.ling.mysql.file;
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.springframework.boot.autoconfigure.SpringBootApplication;
import javax.sql.DataSource;
import java.util.Arrays;
import java.util.List;
@SpringBootApplication
public class MysqlFileApplication {
public static void main(String[] args) {
// 数据源
HikariConfig hikariConfig = new HikariConfig();
hikariConfig.setDriverClassName("com.mysql.cj.jdbc.Driver");
hikariConfig.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai");
hikariConfig.setUsername("root");
hikariConfig.setPassword("root");
// 设置可以获取tables remarks信息
hikariConfig.addDataSourceProperty("useInformationSchema", "true");
hikariConfig.setMinimumIdle(2);
hikariConfig.setMaximumPoolSize(5);
DataSource dataSource = new HikariDataSource(hikariConfig);
// 1、生成文件配置
EngineConfig engineConfig = EngineConfig.builder()
// 生成文件路径
.fileOutputDir("C:\\Users\\Administrator\\Desktop")
// 打开目录
.openOutputDir(false)
// 文件类型
.fileType(EngineFileType.HTML)
// 生成模板实现
.produceType(EngineTemplateType.freemarker).build();
// 忽略表名
List<String> ignoreTableName = Arrays.asList("test");
// 忽略表前缀
List<String> ignorePrefix = Arrays.asList("test_", "test");
// 忽略表后缀
List<String> ignoreSuffix = Arrays.asList("_test", "test");
// 2、配置想要忽略的表
ProcessConfig processConfig = ProcessConfig.builder().ignoreTableName(ignoreTableName)
.ignoreTablePrefix(ignorePrefix).ignoreTableSuffix(ignoreSuffix).build();
// 3、生成文档配置（包含以下自定义版本号、描述等配置连接）
Configuration config = Configuration.builder().version("1.0.0").description("数据库文档").dataSource(dataSource)
.engineConfig(engineConfig).produceConfig(processConfig).build();
new DocumentationExecute(config).execute();
}
}
2.2 maven生成版
pom配置：
<plugin>
<groupId>cn.smallbun.screw</groupId>
<artifactId>screw-maven-plugin</artifactId>
<version>1.0.3</version>
<dependencies>
<!-- 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>
</dependencies>
<!--可注释掉运行 main版 -->
<configuration>
<!--username-->
<username>root</username>
<!--password-->
<password>root</password>
<!--driver-->
<driverClassName>com.mysql.cj.jdbc.Driver</driverClassName>
<!--jdbc url-->
<jdbcUrl>jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai</jdbcUrl>
<!--生成文件类型-->
<fileType>HTML</fileType>
<!--打开文件输出目录-->
<openOutputDir>true</openOutputDir>
<!--生成模板-->
<produceType>freemarker</produceType>
<!--文档名称 为空时:将采用[数据库名称-描述-版本号]作为文档名称-->
<!-- <docName>测试文档名称</docName>-->
<!--描述-->
<description>数据库文档生成</description>
<!--版本-->
<version>${project.version}</version>
<!--标题-->
<title>fire数据库文档</title>
</configuration>
<executions>
<execution>
<phase>compile</phase>
<goals>
<goal>run</goal>
</goals>
</execution>
</executions>
</plugin>
启动：
如果maven中未显示，可以剪切pom内容保存，再撤回。pom文件会重新加载。
3. 根据注释过滤数据库并进行注释排序
3.1 注释过滤配置类：
package com.ling.mysql.file.config;
import java.io.Serializable;
import java.util.List;
import java.util.Objects;
/**
* <p>
* 根据注释过滤表格
* </p>
*
* @Auther: wangChaoHua
* @Date: 2021/3/5 15:33
* @Description:
*/
public class TableAnnotationFilterConfig implements Serializable {
// 指定注释
private List<String> designatedAnnotation;
// 忽略注释
private List<String> ignoreAnnotation;
// 是否根据注释排序
private boolean sortFlag = true;
public TableAnnotationFilterConfig(List<String> designatedAnnotation, List<String> ignoreAnnotation, boolean sortFlag) {
this.designatedAnnotation = designatedAnnotation;
this.ignoreAnnotation = ignoreAnnotation;
this.sortFlag = sortFlag;
}
/***
* @Description 注释过滤配置
* @Date 2021/3/5 16:01
*
* @return: com.mysql.file.config.TableFiletAnnotationConfig.TableFiletAnnotationConfigBuilder
**/
public static TableAnnotationFilterConfig.TableFiletAnnotationConfigBuilder builder() {
return new TableAnnotationFilterConfig.TableFiletAnnotationConfigBuilder();
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
TableAnnotationFilterConfig that = (TableAnnotationFilterConfig) o;
return sortFlag == that.sortFlag &&
Objects.equals(designatedAnnotation, that.designatedAnnotation) &&
Objects.equals(ignoreAnnotation, that.ignoreAnnotation);
}
@Override
public int hashCode() {
return Objects.hash(designatedAnnotation, ignoreAnnotation, sortFlag);
}
public List<String> getDesignatedAnnotation() {
return designatedAnnotation;
}
public void setDesignatedAnnotation(List<String> designatedAnnotation) {
this.designatedAnnotation = designatedAnnotation;
}
public List<String> getIgnoreAnnotation() {
return ignoreAnnotation;
}
public void setIgnoreAnnotation(List<String> ignoreAnnotation) {
this.ignoreAnnotation = ignoreAnnotation;
}
public boolean isSortFlag() {
return sortFlag;
}
public void setSortFlag(boolean sortFlag) {
this.sortFlag = sortFlag;
}
public static class TableFiletAnnotationConfigBuilder {
// 指定注释
private List<String> designatedAnnotation;
// 忽略注释
private List<String> ignoreAnnotation;
// 是否根据注释排序
private boolean sortFlag = true;
TableFiletAnnotationConfigBuilder() {}
public TableAnnotationFilterConfig.TableFiletAnnotationConfigBuilder designatedAnnotation(List<String> designatedAnnotation) {
this.designatedAnnotation = designatedAnnotation;
return this;
}
public TableAnnotationFilterConfig.TableFiletAnnotationConfigBuilder ignoreAnnotation(List<String> ignoreAnnotation) {
this.ignoreAnnotation = ignoreAnnotation;
return this;
}
public TableAnnotationFilterConfig.TableFiletAnnotationConfigBuilder sortFlag(boolean sortFlag) {
this.sortFlag = sortFlag;
return this;
}
public TableAnnotationFilterConfig build() {
return new TableAnnotationFilterConfig(this.designatedAnnotation, this.ignoreAnnotation, this.sortFlag);
}
public String toString() {
return "TableFiletAnnotationConfig.TableFiletAnnotationConfigBuilder(designatedAnnotation=" + this.designatedAnnotation + ", ignoreAnnotation=" + this.ignoreAnnotation + ", sortFlag=" + this.sortFlag + ")";
}
}
}
3.2 重载文件：
package com.ling.mysql.file.process;
import cn.smallbun.screw.core.Configuration;
import cn.smallbun.screw.core.metadata.Column;
import cn.smallbun.screw.core.metadata.Database;
import cn.smallbun.screw.core.metadata.PrimaryKey;
import cn.smallbun.screw.core.metadata.Table;
import cn.smallbun.screw.core.metadata.model.ColumnModel;
import cn.smallbun.screw.core.metadata.model.DataModel;
import cn.smallbun.screw.core.metadata.model.TableModel;
import cn.smallbun.screw.core.process.DataModelProcess;
import cn.smallbun.screw.core.query.DatabaseQuery;
import cn.smallbun.screw.core.query.DatabaseQueryFactory;
import cn.smallbun.screw.core.util.StringUtils;
import com.ling.mysql.file.config.TableAnnotationFilterConfig;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.text.Collator;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.stream.Collectors;
/**
* <p>
* 重载数据模型
* </p>
*
* @Auther: wangChaoHua
* @Date: 2021/3/5 14:37
* @Description:
*/
public class DataModelProcessOverload extends DataModelProcess {
Logger logger = LoggerFactory.getLogger(this.getClass());
public DataModelProcessOverload(Configuration configuration, TableAnnotationFilterConfig filetAnnotationConfig) {
super(configuration);
this.filetAnnotationConfig = filetAnnotationConfig;
}
Map<String, List<? extends Table>> tablesCaching = new ConcurrentHashMap();
Map<String, List<Column>> columnsCaching = new ConcurrentHashMap();
Map<String, List<PrimaryKey>> primaryKeysCaching = new ConcurrentHashMap();
TableAnnotationFilterConfig filetAnnotationConfig;
public DataModelProcessOverload(Configuration configuration) {
super(configuration);
}
public DataModel process() {
DatabaseQuery query = (new DatabaseQueryFactory(this.config.getDataSource())).newInstance();
DataModel model = new DataModel();
model.setTitle(this.config.getTitle());
model.setOrganization(this.config.getOrganization());
model.setOrganizationUrl(this.config.getOrganizationUrl());
model.setVersion(this.config.getVersion());
model.setDescription(this.config.getDescription());
long start = System.currentTimeMillis();
Database database = query.getDataBase();
this.logger.debug("query the database time consuming:{}ms", System.currentTimeMillis() - start);
model.setDatabase(database.getDatabase());
start = System.currentTimeMillis();
List<? extends Table> tables = query.getTables();
this.logger.debug("query the table time consuming:{}ms", System.currentTimeMillis() - start);
start = System.currentTimeMillis();
List<? extends Column> columns = query.getTableColumns();
this.logger.debug("query the column time consuming:{}ms", System.currentTimeMillis() - start);
start = System.currentTimeMillis();
List<? extends PrimaryKey> primaryKeys = query.getPrimaryKeys();
this.logger.debug("query the primary key time consuming:{}ms", System.currentTimeMillis() - start);
start = System.currentTimeMillis();
List<TableModel> tableModels = new ArrayList();
this.tablesCaching.put(database.getDatabase(), tables);
Iterator var10 = tables.iterator();
Table table;
while(var10.hasNext()) {
table = (Table)var10.next();
Table finalTable = table;
this.columnsCaching.put(table.getTableName(), columns.stream().filter((i) -> {
return i.getTableName().equals(finalTable.getTableName());
}).collect(Collectors.toList()));
Table finalTable1 = table;
this.primaryKeysCaching.put(table.getTableName(), primaryKeys.stream().filter((i) -> {
return i.getTableName().equals(finalTable1.getTableName());
}).collect(Collectors.toList()));
}
var10 = tables.iterator();
while(var10.hasNext()) {
table = (Table)var10.next();
TableModel tableModel = new TableModel();
tableModel.setTableName(table.getTableName());
tableModel.setRemarks(table.getRemarks());
tableModels.add(tableModel);
List<ColumnModel> columnModels = new ArrayList();
List<PrimaryKey> list_pk = this.primaryKeysCaching.get(table.getTableName());
List<String> keyList = list_pk.stream().map(PrimaryKey::getColumnName).collect(Collectors.toList());
Iterator var15 = ((List)this.columnsCaching.get(table.getTableName())).iterator();
while(var15.hasNext()) {
Column column = (Column)var15.next();
this.packageColumn(columnModels, keyList, column);
}
tableModel.setColumns(columnModels);
}
// 原始过滤条件
tableModels = this.filterTables(tableModels);
/** 根据注释过滤 start *****************************/
tableModels = this.filterTablesByAnnotationAndSort(tableModels);
/** 根据注释过滤 end *****************************/
model.setTables(tableModels);
this.optimizeData(model);
this.logger.debug("encapsulation processing data time consuming:{}ms", System.currentTimeMillis() - start);
return model;
}
/***
* @Description 注释过滤并排序
* 根据数据库的注释进行过滤并排序
* @Date 2021/3/5 15:04
* @param tables:
* @return: java.util.List<cn.smallbun.screw.core.metadata.model.TableModel>
**/
protected List<TableModel> filterTablesByAnnotationAndSort(List<TableModel> tables) {
TableAnnotationFilterConfig annotationConfig = filetAnnotationConfig;
if (annotationConfig!=null && annotationConfig.getDesignatedAnnotation()!=null) {
for(String flag : annotationConfig.getDesignatedAnnotation()){
tables = tables.stream().filter(i->i.getRemarks().indexOf(flag)!=-1).collect(Collectors.toList());
}
}
if(annotationConfig!=null && annotationConfig.getIgnoreAnnotation()!=null){
for(String flag : annotationConfig.getIgnoreAnnotation()){
tables = tables.stream().filter(i->i.getRemarks().indexOf(flag)==-1).collect(Collectors.toList());
}
}
List<TableModel> dataList = tables;
if(annotationConfig.isSortFlag()){
//按照名称排序
Collections.sort(dataList, new Comparator<TableModel>() {
@Override
public int compare(TableModel o1, TableModel o2) {
//排序规则：按照汉字拼音首字母排序
Comparator<Object> com = Collator.getInstance(java.util.Locale.CHINA);
return com.compare(o1.getRemarks(), o2.getRemarks());
}
});
}
return dataList;
}
private void packageColumn(List<ColumnModel> columnModels, List<String> keyList, Column column) {
ColumnModel columnModel = new ColumnModel();
columnModel.setOrdinalPosition(column.getOrdinalPosition());
columnModel.setColumnName(column.getColumnName());
columnModel.setTypeName(column.getTypeName().toLowerCase());
columnModel.setColumnSize(column.getColumnSize());
columnModel.setDecimalDigits(StringUtils.defaultString(column.getDecimalDigits(), "0"));
columnModel.setNullable("0".equals(column.getNullable()) ? "N" : "Y");
columnModel.setPrimaryKey(keyList.contains(column.getColumnName()) ? "Y" : "N");
columnModel.setColumnDef(column.getColumnDef());
columnModel.setRemarks(column.getRemarks());
columnModels.add(columnModel);
}
}
3.3 启动文件：
package com.ling.mysql.file;
import cn.smallbun.screw.core.Configuration;
import cn.smallbun.screw.core.engine.*;
import cn.smallbun.screw.core.metadata.model.DataModel;
import cn.smallbun.screw.core.process.ProcessConfig;
import cn.smallbun.screw.core.util.ExceptionUtils;
import cn.smallbun.screw.core.util.StringUtils;
import com.ling.mysql.file.config.TableAnnotationFilterConfig;
import com.ling.mysql.file.process.DataModelProcessOverload;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import javax.sql.DataSource;
import java.util.Arrays;
import java.util.List;
@SpringBootApplication
public class MysqlFileApplication {
static Logger logger = LoggerFactory.getLogger(MysqlFileApplication.class);
public static void main(String[] args) {
// 数据源
HikariConfig hikariConfig = new HikariConfig();
hikariConfig.setDriverClassName("com.mysql.cj.jdbc.Driver");
hikariConfig.setJdbcUrl("jdbc:mysql://125.124.101.16:3306/demo_db_lianhehuanjing?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai");
hikariConfig.setUsername("root");
hikariConfig.setPassword("hd20200808");
// 设置可以获取tables remarks信息
hikariConfig.addDataSourceProperty("useInformationSchema", "true");
hikariConfig.setMinimumIdle(2);
hikariConfig.setMaximumPoolSize(5);
DataSource dataSource = new HikariDataSource(hikariConfig);
// 1、生成文件配置
EngineConfig engineConfig = EngineConfig.builder()
// 生成文件路径
.fileOutputDir("C:\\Users\\Administrator\\Desktop")
// 打开目录
.openOutputDir(false)
// 文件类型
.fileType(EngineFileType.HTML)
// 生成模板实现
.produceType(EngineTemplateType.freemarker).build();
// 忽略表名
List<String> ignoreTableName = Arrays.asList("test");
// 忽略表前缀
List<String> ignorePrefix = Arrays.asList("test_", "test");
// 忽略表后缀
List<String> ignoreSuffix = Arrays.asList("_test", "test");
// 2、配置想要忽略的表
ProcessConfig processConfig = ProcessConfig.builder().ignoreTableName(ignoreTableName)
.ignoreTablePrefix(ignorePrefix).ignoreTableSuffix(ignoreSuffix).build();
// 3、生成文档配置（包含以下自定义版本号、描述等配置连接）
Configuration config = Configuration.builder().version("1.0.0").description("数据库文档").dataSource(dataSource)
.engineConfig(engineConfig).produceConfig(processConfig).build();
// 4、执行生成
// new DocumentationExecute(config).execute();
// 4. 根据表注释过滤表格
/** 根据表注释过滤表格信息 start *******************************/
// 字符串关键字匹配
// 忽略特定注释
List<String> ignoreAnnotation = Arrays.asList("暂废弃");
// 指定特定注释
// List<String> designatedAnnotation = Arrays.asList("废弃");
// 是否根据注释排序
boolean sortFlag = true;
TableAnnotationFilterConfig annotationConfig = TableAnnotationFilterConfig.builder()
.designatedAnnotation(null).ignoreAnnotation(ignoreAnnotation).sortFlag(sortFlag).build();
/** 根据表注释过滤表格信息 end *******************************/
// 执行生成
try {
long start = System.currentTimeMillis();
DataModel dataModel = (new DataModelProcessOverload(config,annotationConfig)).process();
TemplateEngine produce = (new EngineFactory(config.getEngineConfig())).newInstance();
String docName = dataModel.getDatabase() + "_" + config.getDescription() + (StringUtils.isBlank(config.getVersion()) ? "" :"_"+config.getVersion());
produce.produce(dataModel, docName);
logger.debug("文件生成耗时:{}ms", System.currentTimeMillis() - start);
} catch (Exception var5) {
throw ExceptionUtils.mpe(var5);
}
}
}
源码版
screw源码地址：# https://toscode.gitee.com/leshalv/screw/tree/master
图片: 
3185
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
