1 背景介绍
如何把数据库中的表结构
image.png
转化成像这样的数据设计文档
image.png
这里用到的springboot jdbc poi lombok等工具，多说多益上代码吧
2 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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.gzz</groupId>
<artifactId>spring-boot-jdbc-poi</artifactId>
<version>1.0</version>
<packaging>jar</packaging>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.7.RELEASE</version>
<relativePath />
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>24.0-jre</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
3 logback.xml
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
<encoder>
<pattern>%d[%F:%L][%p]:%m%n</pattern>
</encoder>
</appender>
<logger name="org.springframework" level="ERROR"/>
<logger name="com.netflix" level="ERROR"/>
<root level="info">
<appender-ref ref="STDOUT"/>
</root>
</configuration>
4 application.yml
spring:
datasource:
url: jdbc:mysql://192.168.20.19:3306/robottest?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: root
5 Application.java
package com.gzz;
import java.io.File;
import java.io.IOException;
import javax.annotation.PostConstruct;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import com.gzz.sys.table.TableDao;
@SpringBootApplication
public class Application {
private int row = 0;
public static void main(String[] args) throws Exception {
SpringApplication.run(Application.class, args);
}
@Autowired
private TableDao dao;
@PostConstruct
public void run() throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象
HSSFSheet sheet = workbook.createSheet("数据库表"); // 创建工作表
dao.queryTables().forEach(table -> {
row++;// 表信息
HSSFRow rowm = sheet.createRow(row);
rowm.createCell(0).setCellValue("表名");
rowm.createCell(1).setCellValue(table.getTable_name());
rowm.createCell(3).setCellValue("描述");
rowm.createCell(4).setCellValue(table.getTable_comment());
row++;// 表头
rowm = sheet.createRow(row);
rowm.createCell(0).setCellValue("字段名");
rowm.createCell(1).setCellValue("字段描述");
rowm.createCell(2).setCellValue("数据类型");
rowm.createCell(3).setCellValue("可为空");
rowm.createCell(4).setCellValue("是主键");
rowm.createCell(5).setCellValue("规则");
dao.queryFields(table.getTable_name()).forEach(field -> {
row++;// 字段
HSSFRow rowf = sheet.createRow(row);
rowf.createCell(0).setCellValue(field.getColumn_name());
rowf.createCell(1).setCellValue(field.getColumn_comment());
rowf.createCell(2).setCellValue(field.getColumn_type());
rowf.createCell(3).setCellValue(field.getIs_nullable());
rowf.createCell(4).setCellValue(field.getPri());
rowf.createCell(5).setCellValue("无");
});
row++;
});
workbook.write(new File("d:/数据库.xls"));
workbook.close();
}
}
6 TableDao.java
package com.gzz.sys.table;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
/**
* @author https://www.jianshu.com/u/3bd57d5f1074
* @date 2019-12-24 14:50:00
*/
@Repository
public class TableDao {
@Autowired
protected JdbcTemplate jdbcTemplate;
public List<Field> queryFields(String tableName) {
StringBuilder sb = new StringBuilder();
sb.append(" SELECT column_name, column_comment, column_type, is_nullable, IF (column_key = 'pri', '是', '') pri ");
sb.append(" FROM information_schema.columns WHERE table_name = ? AND table_schema = (SELECT DATABASE())");
return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(Field.class), tableName);
}
public List<Table> queryTables() {
String sql = "SELECT table_name,table_comment FROM information_schema.TABLES WHERE table_schema =(SELECT DATABASE())";
return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Table.class));
}
}
6 Field.java
package com.gzz.sys.table;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class Field {
private String column_name;
private String column_comment;
private String column_type;
private String is_nullable;
private String pri;
}
6 TableDao.java
package com.gzz.sys.table;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class Table {
private String table_name;
private String table_comment;
}
7 代码结构
image.png
网友评论