200字范文 > JAVA实现导出mysql表结构到Word详细注解版

JAVA实现导出mysql表结构到Word详细注解版

时间：2019-05-05 05:48:52

转自/weixin_42041153/article/details/109739073

本文在原文中一些容易出错的位置做了一些详细注解

JAVA实现导出mysql表结构到Word

1.引入jar包

<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.0.0</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.0.0</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>fastjson</artifactId><version>1.2.47</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>5.1.48</version></dependency>

————————————————

2.编写JAVA实现方法

import com.alibaba.fastjson.JSONObject;import org.apache.poi.xwpf.usermodel.XWPFDocument;import org.apache.poi.xwpf.usermodel.XWPFParagraph;import org.apache.poi.xwpf.usermodel.XWPFRun;import org.apache.poi.xwpf.usermodel.XWPFTable;import java.io.FileOutputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.text.SimpleDateFormat;import java.util.*;public class ConstructionToWord {private final String DRIVER = "com.mysql.jdbc.Driver";//private final String DRIVER = "com.mysql.cj.jdbc.Driver";private final String URL = "jdbc:mysql://localhost:3306/test"+"?useUnicode=true&characterEncoding=utf8&useSSL=false";private final String USER_NAME = "root";private final String PASS_WORD = "123456";//要查询的数据库名称，要和URL中的保持一致，必须写否则查询不到数据private final String database = "test";//这是生成文档的系统盘符，必须和项目工程在同一个盘，比如D:\IdeaProjects，否则不能生成文档private final String reportPath = "D:";// 启动方法public static void main(String[] args) {try {ConstructionToWord rd = new ConstructionToWord();rd.report();}catch (Exception e){e.printStackTrace();}}Connection conn = null;PreparedStatement pst = null;ResultSet rs = null;// 获取查询数据public Map<String, List<TableColumn>> getData() throws Exception{System.out.println("数据生成中，请稍等...");Map<String,List<TableColumn>> map = new HashMap<String,List<TableColumn>>();List<Table> tables = getTables(database);for (Table table : tables) {List<TableColumn> columns = getColumns(database,table.getTableName());map.put(table.getTableName(),columns);}return map;}// 获取表字段信息public List<TableColumn> getColumns(String database,String tableName) throws Exception{String sql = "select column_name,column_comment,column_type,is_nullable, column_key from information_schema.columns where table_schema=? and table_name=? group by column_name";ResultSet rs = getConn(database,tableName,sql);List<TableColumn> tableColumns = new ArrayList<TableColumn>();while (rs.next()){TableColumn tc = new TableColumn();tc.setTableName(tableName);tc.setColumnName(rs.getString("column_name"));tc.setColumnType(rs.getString("column_type"));tc.setColumnKey(rs.getString("column_key"));tc.setIsNullable(rs.getString("is_nullable"));tc.setColumnComment(rs.getString("column_comment"));tableColumns.add(tc);}releaseConn();return tableColumns;}// 获取所有表public List<Table> getTables(String database) throws Exception{String sql = "select table_name,table_comment from information_schema.tables where table_schema=?";ResultSet rs = getConn(database, "",sql);List<Table> tables = new ArrayList<Table>();while(rs.next()){Table table = new Table();table.setTableName(rs.getString( "table_name"));table.setTableCommont(rs.getString("table_comment"));tables.add(table);}releaseConn();return tables;}// 连接数据库private ResultSet getConn(String dataBase,String tableName,String sql){try{Class.forName(DRIVER);conn = DriverManager.getConnection(URL,USER_NAME,PASS_WORD);pst = conn.prepareStatement(sql);pst.setString(1,dataBase);if(!"".equals(tableName)){pst.setString(2,tableName);}rs = pst.executeQuery();return rs;}catch (Exception e){e.printStackTrace();}return null;}// 释放连接private void releaseConn(){try{if(rs != null ){rs.close();}if(pst != null){pst.close();}if(conn != null){conn.close();}}catch (Exception e){e.printStackTrace();}}// 导出数据public void report() throws Exception{Map<String, List<TableColumn>> data = this.getData(); // 表名：表体List<Table> tables = this.getTables(this.database); // 表体(列名、类型、注释)Map<String,String> tableMap = new HashMap<String,String>(); // 表名:中文名JSONObject json = new JSONObject((HashMap)data);for (Table table : tables) {tableMap.put(table.getTableName(),table.getTableCommont());}// 构建表格数据XWPFDocument document = new XWPFDocument();Integer i = 1;for (String tableName : data.keySet()) {XWPFParagraph paragraph = document.createParagraph();// 创建标题对象XWPFRun run = paragraph.createRun(); // 创建文本对象run.setText((i+"、"+tableName+" "+tableMap.get(tableName)));// 标题名称run.setFontSize(14); // 字体大小run.setBold(true); // 字体加粗int j = 0;XWPFTable table = document.createTable(data.get(tableName).size()+1,5);// 第一行table.setCellMargins(10,50,10,200);table.getRow(j).getCell(0).setText("字段名称");table.getRow(j).getCell(1).setText("字段类型");table.getRow(j).getCell(2).setText("约束");table.getRow(j).getCell(3).setText("为空");table.getRow(j).getCell(4).setText("字段含义");j++;for (TableColumn tableColumn : data.get(tableName)) {table.getRow(j).getCell(0).setText(tableColumn.getColumnName());table.getRow(j).getCell(1).setText(tableColumn.getColumnType());table.getRow(j).getCell(2).setText(tableColumn.getColumnKey());table.getRow(j).getCell(3).setText(tableColumn.getIsNullable());table.getRow(j).getCell(4).setText(tableColumn.getColumnComment());j++;}i++;}// 文档输出FileOutputStream out = new FileOutputStream(reportPath + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString()+"_"+database +".docx");document.write(out);out.close();System.out.println("Word生成完成!!!");}// 表class Table{private String tableName;private String tableCommont;public String getTableName() {return tableName;}public void setTableName(String tableName) {this.tableName = tableName;}public String getTableCommont() {return tableCommont;}public void setTableCommont(String tableCommont) {this.tableCommont = tableCommont;}}// 表列信息class TableColumn{// 表名private String tableName;// 字段名private String columnName;// 字段类型private String columnType;// 字段注释private String columnComment;// 可否为空private String isNullable;// 约束private String columnKey;public String getTableName() {return tableName;}public void setTableName(String tableName) {this.tableName = tableName;}public String getColumnName() {return columnName;}public void setColumnName(String columnName) {this.columnName = columnName;}public String getColumnType() {return columnType;}public void setColumnType(String columnType) {this.columnType = columnType;}public String getColumnComment() {return columnComment;}public void setColumnComment(String columnComment) {this.columnComment = columnComment;}public String getIsNullable() {return isNullable;}public void setIsNullable(String isNullable) {this.isNullable = isNullable;}public String getColumnKey() {return columnKey;}public void setColumnKey(String columnKey) {this.columnKey = columnKey;}}}

3.遇到的问题

（1）com.mysql.jdbc.Driver 和 com.mysql.cj.jdbc.Driver的区别

com.mysql.jdbc.Driver 是 mysql-connector-java 5中的

# JDBC连接Mysql5+ --- com.mysql.jdbc.DriverdriverClassName=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=falseusername=rootpassword=

com.mysql.cj.jdbc.Driver 是 mysql-connector-java 6中的

# JDBC连接Mysql6+ ---- com.mysql.cj.jdbc.Driver,需要指定时区driverClassName=com.mysql.cj.jdbc.Driverurl=jdbc:mysql://localhost:3306/test?serverTimezone=UTC&?useUnicode=true&characterEncoding=utf8&useSSL=falseusername=rootpassword=123456

还有一个警告——MySql5+默认开启SSL连接，如不使用需设置useSSL=false

WARN: Establishing SSL connection without server’s identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn’t set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to ‘false’. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

不推荐不使用服务器身份验证来建立SSL连接。

如果未明确设置，MySQL 5.5.45+, 5.6.26+ and 5.7.6+版本默认要求建立SSL连接。

为了符合当前不使用SSL连接的应用程序，verifyServerCertificate属性设置为’false’。

如果你不需要使用SSL连接，你需要通过设置useSSL=false来显式禁用SSL连接。

如果你需要用SSL连接，就要为服务器证书验证提供信任库，并设置useSSL=true。

SSL – Secure Sockets Layer（安全套接层）

（2）MySQL报错：Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre的问题

关于ONLY_FULL_GROUP_BY模式参考：/qq_38234015/article/details/90017695

Expression #2 of SELECT list is not in GROUP BY clause and contains

nonaggregated column ‘sss.month_id’ which is not functionally

dependent on columns in GROUP BY clause; this is incompatible with

sql_mode=only_full_group_by

问题出现的原因：

MySQL 5.7.5及以上功能依赖检测功能。如果启用了ONLY_FULL_GROUP_BY SQL模式（默认情况下），MySQL将拒绝选择列表，HAVING条件或ORDER BY列表的查询引用在GROUP BY子句中既未命名的非集合列，也不在功能上依赖于它们。（5.7.5之前，MySQL没有检测到功能依赖关系，默认情况下不启用ONLY_FULL_GROUP_BY。有关5.7.5之前的行为的说明，请参见“MySQL 5.6参考手册”。）

解决方法一：

打开navcat，

用sql查询：

select @@global.sql_mode

查询出来的值为：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

去掉ONLY_FULL_GROUP_BY，重新设置值。

set @@global.sql_mode

=’STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;

如下图:

解决方法二：

成功的步骤：