Export mysql database to word document
pom.xml
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.76</version>
</dependency>
package com.ruoyi.test;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.xwpf.usermodel.*;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.*;
import java.io.File;
import java.io.FileOutputStream;
import java.math.BigInteger;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.xwpf.usermodel.ParagraphAlignment;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFRun;
public class GeneratorDataToWordUtil {<!-- -->
public static final String driverUrl = "jdbc:mysql://192.168.110.45:3306/manlan_scm?useUnicode=true & amp;useJDBCCompliantTimezoneShift=true & amp;useLegacyDatetimeCode=false & amp;useSSL=false & amp;serverTimezone=Asia/Shanghai ";
public static final String username = "root";
public static final String password = "TestDb@gs";
/**
* The exported database needs to be consistent with the database connected in driverUrl
*/
public static final String dataBase = "manlan_scm";
/**
* Tables that do not need to be exported can be null
*/
public static final String notTbales = "'gen_table','gen_table_column','sys_config','sys_dict_data','sys_dict_type','sys_oss_config','sys_menu','sys_oss','sys_post','sys_role_dept','sys_role_menu' ,'sys_user_post','sys_user_role'";
/**
* Matching prefix is not exported and can be null
*/
public static final String notLike = "'qrtz_%'";
/**
* Document title
*/
public static final String title = "Computing power system platform database design detailed instructions";
/**
* Output document address
*/
public static final String path = "d:";
/**
* Output document name
*/
public static final String fileName = "Computing power system platform database design detailed instructions V1.0.docx";
public static void main(String[] args)throws Exception {<!-- -->
System.out.println("Start generating document~~");
//Blank Document
XWPFDocument document= new XWPFDocument();
//Add title
XWPFParagraph titleParagraph = document.createParagraph();
//Set the paragraph to be centered
titleParagraph.setAlignment(ParagraphAlignment.CENTER);
XWPFRun titleParagraphRun = titleParagraph.createRun();
titleParagraphRun.setText(title);
titleParagraphRun.setColor("000000");
titleParagraphRun.setFontSize(20);
GeneratorDataToWordUtil we = new GeneratorDataToWordUtil();
List<JSONObject> list= we.getTables(dataBase);
for (JSONObject json : list) {<!-- -->
List<String[]> columns = we.getTablesDetail(dataBase, json.getString("name"));
addTable(document, json.getString("name"), json.getString("remark"), columns);
}
//Write the Document in file system
FileOutputStream out = new FileOutputStream(new File(path + fileName));
document.write(out);
out.close();
System.out.println("create_table document written success.");
}
private List<String[]> getTablesDetail(String schema, String tableName){<!-- -->
List<String[]> list = new ArrayList<>();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {<!-- -->
//Load database driver
Class.forName("com.mysql.cj.jdbc.Driver");
//Get the database link through the driver management class
connection = DriverManager.getConnection(driverUrl, username, password);
//Define sql statement? Represents placeholder
String sql = "SELECT COLUMN_NAME , COLUMN_TYPE , COLUMN_DEFAULT , if(is_nullable='YES','Yes','No') IS_NULLABLE ,if(column_key='PRI','Yes','No' ) COLUMN_KEY, COLUMN_COMMENT "
+ " FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = ? and table_name = ? order by ordinal_position ";
//Get preprocessing statement
preparedStatement = connection.prepareStatement(sql);
//Set parameters. The first parameter is the serial number of the parameter in the sql statement (starting from 1), and the second parameter is the set parameter value.
preparedStatement.setString(1, schema);
preparedStatement.setString(2, tableName);
//Issue sql execution query to the database and query the result set
resultSet = preparedStatement.executeQuery();
int i = 1;
//Traverse the query result set
while(resultSet.next()){<!-- -->
String[] str = new String[4];
str[0] = i + "";
str[1] = resultSet.getString("COLUMN_NAME");
str[2] = resultSet.getString("COLUMN_TYPE");
//str[3] = resultSet.getString("COLUMN_DEFAULT");
//str[3] = resultSet.getString("IS_NULLABLE");
//str[5] = resultSet.getString("COLUMN_KEY");
str[3] = resultSet.getString("COLUMN_COMMENT");
list.add(str);
i + + ;
}
} catch (Exception e) {<!-- -->
e.printStackTrace();
}finally{<!-- -->
//Release resources
if(resultSet!=null){<!-- -->
try {<!-- -->
resultSet.close();
} catch (SQLException e) {<!-- -->
e.printStackTrace();
}
}
if(preparedStatement!=null){<!-- -->
try {<!-- -->
preparedStatement.close();
} catch (SQLException e) {<!-- -->
e.printStackTrace();
}
}
if(connection!=null){<!-- -->
try {<!-- -->
connection.close();
} catch (SQLException e) {<!-- -->
e.printStackTrace();
}
}
}
return list;
}
private List<JSONObject> getTables(String schema){<!-- -->
List<JSONObject> list = new ArrayList<>();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {<!-- -->
//Load database driver
Class.forName("com.mysql.cj.jdbc.Driver");
//Get the database link through the driver management class
connection = DriverManager.getConnection(driverUrl, username, password);
//Define sql statement? Represents placeholder
StringBuffer sql = new StringBuffer();
sql.append("select TABLE_NAME,TABLE_COMMENT from information_schema.tables where table_schema= ? ");
if(null != notLike){<!-- -->
sql.append(" AND table_name NOT LIKE " + notLike);
}
if(null != notTbales){<!-- -->
sql.append(" AND table_name NOT IN (" + notTbales + ")");
}
//Get preprocessing statement
preparedStatement = connection.prepareStatement(sql.toString());
//Set parameters. The first parameter is the serial number of the parameter in the sql statement (starting from 1), and the second parameter is the set parameter value.
preparedStatement.setString(1, schema);
//Issue sql execution query to the database and query the result set
resultSet = preparedStatement.executeQuery();
//Traverse the query result set
while(resultSet.next()){<!-- -->
JSONObject j = new JSONObject();
j.put("name", resultSet.getString("TABLE_NAME"));
j.put("remark", resultSet.getString("TABLE_COMMENT"));
list.add(j);
}
} catch (Exception e) {<!-- -->
e.printStackTrace();
}finally{<!-- -->
//Release resources
if(resultSet!=null){<!-- -->
try {<!-- -->
resultSet.close();
} catch (SQLException e) {<!-- -->
e.printStackTrace();
}
}
if(preparedStatement!=null){<!-- -->
try {<!-- -->
preparedStatement.close();
} catch (SQLException e) {<!-- -->
e.printStackTrace();
}
}
if(connection!=null){<!-- -->
try {<!-- -->
connection.close();
} catch (SQLException e) {<!-- -->
e.printStackTrace();
}
}
}
return list;
}
private static void addTable(XWPFDocument document,String tableName,String remark, List<String[]> columns){<!-- -->
//Add a line break between the two tables
document.createParagraph().createRun().setText("\r");
//Title 1, level 1 outline
document.createParagraph().createRun().setText(remark + "(" + tableName + ")");
//Work experience form
XWPFTable ComTable = document.createTable();
// //Column width automatically splits
CTTblWidth comTableWidth = ComTable.getCTTbl().addNewTblPr().addNewTblW();
comTableWidth.setType(STTblWidth.DXA);
comTableWidth.setW(BigInteger.valueOf(9072));
/* CTTbl ttbl = ComTable.getCTTbl();
int[] COLUMN_WIDTHS = new int[] {572,2072,1372,872,672,672,2572};
CTTblGrid tblGrid = ttbl.getTblGrid() != null ? ttbl.getTblGrid()
:ttbl.addNewTblGrid();
for (int j = 0, len = COLUMN_WIDTHS.length; j < len; j + + ) {
CTTblGridCol gridCol = tblGrid.addNewGridCol();
gridCol.setW(new BigInteger(String.valueOf(COLUMN_WIDTHS[j])));
}*/
//First row of table
XWPFTableRow comTableRowOne = ComTable.getRow(0);
setCellvalue(comTableRowOne.getCell(0), "serial number", "DCDCDC");
setCellvalue(comTableRowOne.addNewTableCell(),"field name","DCDCDC");
setCellvalue(comTableRowOne.addNewTableCell(),"type","DCDCDC");
//setCellvalue(comTableRowOne.addNewTableCell(),"Default value","DCDCDC");
//setCellvalue(comTableRowOne.addNewTableCell(),"Is it empty","DCDCDC");
//setCellvalue(comTableRowOne.addNewTableCell(),"Whether it is the primary key","DCDCDC");
setCellvalue(comTableRowOne.addNewTableCell(),"Comments","DCDCDC");
for (String[] str : columns) {<!-- -->
//Second row of table
XWPFTableRow comTableRowTwo = ComTable.createRow();
for (int j = 0; j < str.length; j + + ) {<!-- -->
//if(j==0 || j==3 || j==4 || j==5){<!-- -->
if(j==0 || j==3){<!-- -->
setCellvalue(comTableRowTwo.getCell(j),str[j]);
}else{<!-- -->
if(j==1){<!-- -->
comTableRowTwo.getCell(j).setText(changeColumn(str[j]));
}else {<!-- -->
comTableRowTwo.getCell(j).setText(str[j]);
}
}
}
}
}
//Convert database fields to java fields
private static String changeColumn(String column) {<!-- -->
String name = column;
if (name.indexOf("_") > 0 & amp; & amp; name.length() != name.indexOf("_") + 1) {<!-- -->
int lengthPlace = name.indexOf("_");
name = name.replaceFirst("_", "");
String s = name.substring(lengthPlace, lengthPlace + 1);
s = s.toUpperCase();
column = name.substring(0, lengthPlace) + s + name.substring(lengthPlace + 1);
} else {<!-- -->
return column;
}
return changeColumn(column);
}
private static void setCellvalue(XWPFTableCell cell, String text){<!-- -->
cell.setText(text);
//center vertically
cell.setVerticalAlignment(XWPFTableCell.XWPPFvertAlign.CENTER);
CTTc cttc = cell.getCTTc();
CTP ctp = cttc.getPList().get(0);
CTPPr ctppr = ctp.getPPr();
if (ctppr == null) {<!-- -->
ctppr = ctp.addNewPPr();
}
CTJc ctjc = ctppr.getJc();
if (ctjc == null) {<!-- -->
ctjc = ctppr.addNewJc();
}
//center horizontally
ctjc.setVal(STJc.CENTER);
}
private static void setCellvalue(XWPFTableCell cell, String text,String color){<!-- -->
cell.setText(text);
//center vertically
cell.setVerticalAlignment(XWPFTableCell.XWPPFvertAlign.CENTER);
CTTc cttc = cell.getCTTc();
cttc.addNewTcPr().addNewShd().setFill(color);
CTP ctp = cttc.getPList().get(0);
CTPPr ctppr = ctp.getPPr();
if (ctppr == null) {<!-- -->
ctppr = ctp.addNewPPr();
}
CTJc ctjc = ctppr.getJc();
if (ctjc == null) {<!-- -->
ctjc = ctppr.addNewJc();
}
//center horizontally
ctjc.setVal(STJc.CENTER);
}
}
Notice:
order by ordinal_position is generated according to the sorting of the data table
The effect is as follows:
