写在前面：
因为工作时候经常遇到半路接手项目的情况，由于年代久远，数据库字典这块经常缺失。故写此篇，以便复用，也希望对大家有点帮助。
随笔内容不高级，如有不妥，不吝指正。
ps：有另一篇详细随笔可以参考【】。
------------------------------------------------------------分-割-线------------------------------------------------------------
以下为代码，只需要改动部分参数，就可以运行，生成excel文件。文件生成后，设置列宽自适应即可。
1 import java.io.FileOutputStream; 2 import java.sql.Connection; 3 import java.sql.DriverManager; 4 import java.sql.PreparedStatement; 5 import java.sql.ResultSet; 6 import java.util.HashMap; 7 import java.util.Map; 8 import java.util.Set; 9 10 import org.apache.commons.collections4.MapUtils; 11 import org.apache.poi.hssf.usermodel.HSSFCell; 12 import org.apache.poi.hssf.usermodel.HSSFRow; 13 import org.apache.poi.hssf.usermodel.HSSFSheet; 14 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 15 import org.apache.poi.ss.usermodel.CellStyle; 16 import org.apache.poi.ss.usermodel.FillPatternType; 17 import org.apache.poi.ss.usermodel.Font; 18 import org.apache.poi.ss.usermodel.HorizontalAlignment; 19 import org.apache.poi.ss.usermodel.IndexedColors; 20 import org.apache.poi.ss.usermodel.VerticalAlignment; 21 import org.apache.poi.ss.usermodel.Workbook; 22 import org.apache.poi.ss.util.CellRangeAddress; 23 import org.apache.poi.xssf.usermodel.XSSFCell; 24 import org.apache.poi.xssf.usermodel.XSSFRow; 25 import org.apache.poi.xssf.usermodel.XSSFSheet; 26 import org.apache.poi.xssf.usermodel.XSSFWorkbook; 27 28 /** 29 * 生成数据库数据结构速查文件（数据库字典） 30 * 31 * @author ruran 32 * @since 2019年7月4日 下午3:25:13 33 */ 34 public class ProduceGuideOfDatabase { 35 36 /* 37 * 数据来源 38 * 39 * SELECT pretab.TABLE_NAME AS 表名,pretab.TABLE_COMMENT AS 表释义, 40 * precol.COLUMN_NAME AS 字段名,precol.COLUMN_TYPE AS 字段类型, 41 * precol.COLUMN_DEFAULT AS 字段默认值,precol.COLUMN_COMMENT AS 表字段释义 FROM 42 * information_schema.`TABLES` AS pretab RIGHT JOIN 43 * information_schema.`COLUMNS` AS precol ON 44 * precol.TABLE_NAME=pretab.TABLE_NAME WHERE pretab.TABLE_SCHEMA ="此处填写库名" 45 * GROUP BY precol.TABLE_NAME,precol.COLUMN_NAME ORDER BY precol.TABLE_NAME; 46 */ 47 public static void main(String[] args) { 48 System.out.println("开始运行程序。。。"); 49 long preTime = System.currentTimeMillis(); 50 // 程序访问数据库拉取字典数据-程序整合成字典文件（配置数据库连接、要拉取得库名，一键运行代码即可） 51 reArrangeFromSQL(); 52 System.out.println("运行完成，耗时：" + (System.currentTimeMillis() - preTime) + "ms"); 53 } 54 55 /** 56 * 直接从SQL中读取数据进行重整成excel 57 * 58 * @author ruran 59 * @since 2019年7月29日 下午7:41:50 60 */ 61 private static void reArrangeFromSQL() { 62 String ip = "xxxxxxxx", user = "xxxx", password = "xxxxxxxx", database = "information_schema"; 63 Map > database_tables = new HashMap<>(); 64 try { 65 String sqlStr = "SELECT pretab.TABLE_NAME AS 表名,pretab.TABLE_COMMENT AS 表释义,precol.COLUMN_NAME AS 字段名,precol.COLUMN_TYPE AS 字段类型, precol.COLUMN_DEFAULT AS 字段默认值,precol.COLUMN_COMMENT AS 表字段释义 FROM information_schema.`TABLES` AS pretab RIGHT JOIN information_schema.`COLUMNS` AS precol ON precol.TABLE_NAME=pretab.TABLE_NAME WHERE pretab.TABLE_SCHEMA =? GROUP BY precol.TABLE_NAME,precol.COLUMN_NAME ORDER BY precol.TABLE_NAME;"; 66 Connection connection = getConnection(ip, user, password, database); 67 PreparedStatement pstmt = connection.prepareStatement(sqlStr); 68 ResultSet rs = null; 69 String[] databaseNames = "scrssit-scrssit2-scrssit3-scrssit4-scrssit5-scrssit6-scrssit7-scrssit8-scrssit9-scrssit10-scrssit11" 70 .split("-"); 71 for (String databaseName : databaseNames) { 72 pstmt.setString(1, databaseName); 73 rs = pstmt.executeQuery();// 获取数据 74 String columnLines = ""; 75 int countAll = 0;// 表总数 76 Map tableNames = new HashMap<>(); 77 String preTableName = ""; 78 String preTableComment = ""; 79 while (rs.next()) { 80 String currentTableName = isBlank(rs.getString(1)) ? "" : rs.getString(1); 81 if (tableNames.containsKey(getRealTablename(currentTableName))) { 82 continue; 83 } 84 String currentTableComment = isBlank(rs.getString(2)) ? "" : rs.getString(2); 85 String currentColumnName = isBlank(rs.getString(3)) ? "" : rs.getString(3); 86 String currentColumnType = isBlank(rs.getString(4)) ? "" : rs.getString(4); 87 String currentColumnDefault = isBlank(rs.getString(5)) ? "" : rs.getString(5); 88 String currentColumnComment = isBlank(rs.getString(6)) ? "" : rs.getString(6); 89 if (currentTableName.equals(preTableName)) { 90 columnLines += currentColumnName + "#" + currentColumnType + "#" + currentColumnDefault + "#" 91 + currentColumnComment + "@"; 92 continue; 93 } 94 if (countAll != 0 && !tableNames.containsKey(getRealTablename(preTableName))) { 95 TablePojo tablePojo = new TablePojo(preTableName, preTableComment, columnLines.substring(0, 96 columnLines.length() - 1)); 97 tableNames.put(getRealTablename(preTableName), tablePojo); 98 } 99 countAll++;100 columnLines = currentColumnName + "#" + currentColumnType + "#" + currentColumnDefault + "#"101 + currentColumnComment + "@";102 preTableName = currentTableName;103 preTableComment = currentTableComment;104 }105 // 最后一组数据判断+保存106 if (!tableNames.containsKey(getRealTablename(preTableName))) {107 TablePojo tablePojo = new TablePojo(preTableName, preTableComment, columnLines.substring(0,108 columnLines.length() - 1));109 tableNames.put(getRealTablename(preTableName), tablePojo);110 }111 database_tables.put(databaseName, tableNames);112 }113 rs.close();114 pstmt.close();115 connection.close();116 } catch (Exception e) {117 e.printStackTrace();118 }119 String url = "F:\\2-ME\\中心+部门\\1-scrs学习整理区\\";120 String forFile = "系统数据库结构参考速查表-20190729.xlsx";121 if (MapUtils.isNotEmpty(database_tables)) {122 if (forFile.contains(".xlsx")) {123 arrangeToXLSX(database_tables, url, forFile);124 } else {125 arrangeToXLS(database_tables, url, forFile);126 }127 }128 }129 130 /**131 * 取数据整合到excel-xls132 * 133 * @author ruran134 * @since 2019年7月23日 下午5:32:50135 * @param tableNamesMap136 * @param fos137 */138 private static void arrangeToXLS(Map > database_tables, String url, String forFile) {139 try (FileOutputStream fos = new FileOutputStream(url + forFile);) {140 if (MapUtils.isNotEmpty(database_tables)) {141 HSSFWorkbook currentWorkbook = new HSSFWorkbook();142 // 获取所有样式143 Map cellStyles = getCellStyles(currentWorkbook);144 Set databaseNames = database_tables.keySet();145 for (String databaseName : databaseNames) {146 HSSFSheet currentSheet = currentWorkbook.createSheet(databaseName);147 HSSFRow currentRow = null;148 HSSFCell currentCell = null;149 int rowIndex = -1;150 Map tableNames = database_tables.get(databaseName);151 for (TablePojo tablePojo : tableNames.values()) {152 // 空行153 currentSheet.createRow(++rowIndex);154 // 表头155 currentRow = currentSheet.createRow(++rowIndex);156 currentRow.setHeightInPoints(18);157 currentCell = currentRow.createCell(0);158 currentCell.setCellStyle(cellStyles.get("bluesStyle"));159 currentCell.setCellValue(tablePojo.getTableName() + "(" + tablePojo.getTableComment() + ")");160 CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex, 0, 3);161 currentSheet.addMergedRegion(region);162 // 表-标题栏163 currentRow = currentSheet.createRow(++rowIndex);164 currentRow.setHeightInPoints(18);165 currentCell = currentRow.createCell(0);166 currentCell.setCellStyle(cellStyles.get("blueStyle"));167 currentCell.setCellValue("列名");168 currentCell = currentRow.createCell(1);169 currentCell.setCellStyle(cellStyles.get("blueStyle"));170 currentCell.setCellValue("类型");171 currentCell = currentRow.createCell(2);172 currentCell.setCellStyle(cellStyles.get("blueStyle"));173 currentCell.setCellValue("默认值");174 currentCell = currentRow.createCell(3);175 currentCell.setCellStyle(cellStyles.get("blueStyle"));176 currentCell.setCellValue("释义");177 // 表字段178 String tableColumnsStr = tablePojo.getTableColumns();179 for (String tableColumns : tableColumnsStr.split("@")) {180 currentRow = currentSheet.createRow(++rowIndex);181 currentRow.setHeightInPoints(18);182 String[] tableColumnArr = tableColumns.split("#");183 for (int i = 0; i < tableColumnArr.length; i++) {184 currentCell = currentRow.createCell(i);185 currentCell.setCellStyle(cellStyles.get("baseStyle"));186 currentCell.setCellValue(tableColumnArr[i]);187 }188 }189 }190 }191 currentWorkbook.write(fos);192 }193 } catch (Exception e) {194 e.printStackTrace();195 }196 }197 198 /**199 * 取数据整合到excel-xlsx200 * 201 * @author ruran202 * @since 2019年7月24日 上午11:51:56203 * @param tableNamesMap204 * @param fos205 */206 private static void arrangeToXLSX(Map > database_tables, String url, String forFile) {207 try (FileOutputStream fos = new FileOutputStream(url + forFile);) {208 if (MapUtils.isNotEmpty(database_tables)) {209 XSSFWorkbook currentWorkbook = new XSSFWorkbook();210 // 获取所有样式211 Map cellStyles = getCellStyles(currentWorkbook);212 Set databaseNames = database_tables.keySet();213 for (String databaseName : databaseNames) {214 XSSFSheet currentSheet = currentWorkbook.createSheet(databaseName);215 XSSFRow currentRow = null;216 XSSFCell currentCell = null;217 int rowIndex = -1;218 Map tableNames = database_tables.get(databaseName);219 for (TablePojo tablePojo : tableNames.values()) {220 // 空行221 currentSheet.createRow(++rowIndex);222 // 表头223 currentRow = currentSheet.createRow(++rowIndex);224 currentRow.setHeightInPoints(18);225 currentCell = currentRow.createCell(0);226 currentCell.setCellStyle(cellStyles.get("bluesStyle"));227 currentCell.setCellValue(tablePojo.getTableName() + "(" + tablePojo.getTableComment() + ")");228 CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex, 0, 3);229 currentSheet.addMergedRegion(region);230 // 表-标题栏231 currentRow = currentSheet.createRow(++rowIndex);232 currentRow.setHeightInPoints(18);233 currentCell = currentRow.createCell(0);234 currentCell.setCellStyle(cellStyles.get("blueStyle"));235 currentCell.setCellValue("列名");236 currentCell = currentRow.createCell(1);237 currentCell.setCellStyle(cellStyles.get("blueStyle"));238 currentCell.setCellValue("类型");239 currentCell = currentRow.createCell(2);240 currentCell.setCellStyle(cellStyles.get("blueStyle"));241 currentCell.setCellValue("默认值");242 currentCell = currentRow.createCell(3);243 currentCell.setCellStyle(cellStyles.get("blueStyle"));244 currentCell.setCellValue("释义");245 // 表字段246 String tableColumnsStr = tablePojo.getTableColumns();247 for (String tableColumns : tableColumnsStr.split("@")) {248 currentRow = currentSheet.createRow(++rowIndex);249 currentRow.setHeightInPoints(18);250 String[] tableColumnArr = tableColumns.split("#");251 for (int i = 0; i < tableColumnArr.length; i++) {252 currentCell = currentRow.createCell(i);253 currentCell.setCellStyle(cellStyles.get("baseStyle"));254 currentCell.setCellValue(tableColumnArr[i]);255 }256 }257 }258 }259 currentWorkbook.write(fos);260 }261 } catch (Exception e) {262 e.printStackTrace();263 }264 }265 266 /**267 * 样式集锦268 * 269 * @author ruran270 * @since 2019年7月24日 下午7:32:26271 * @param workbook272 * @return273 */274 private static Map getCellStyles(Workbook workbook) {275 // 实线边框276 // style1.setBorderTop(BorderStyle.THIN);277 // style1.setBorderBottom(BorderStyle.THIN);278 // style1.setBorderLeft(BorderStyle.THIN);279 // style1.setBorderRight(BorderStyle.THIN);280 // 设置自动换行281 // baseStyle.setWrapText(true);282 283 Map cellStylesMap = new HashMap<>();284 // baseStyle285 CellStyle baseStyle = workbook.createCellStyle();286 // 水平对齐方式287 baseStyle.setAlignment(HorizontalAlignment.LEFT);288 // 垂直对齐方式289 baseStyle.setVerticalAlignment(VerticalAlignment.CENTER);290 // 宋体设置291 Font baseFont = workbook.createFont();292 baseFont.setFontName("宋体");293 baseStyle.setFont(baseFont);294 cellStylesMap.put("baseStyle", baseStyle);// 存放样式-baseStyle295 296 // 深蓝色底部、白色字体、加粗297 CellStyle bluesStyle = workbook.createCellStyle();298 bluesStyle.cloneStyleFrom(cellStylesMap.get("baseStyle"));// 继承某样式299 // 背景色300 bluesStyle.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());301 bluesStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 这一行是必须的，不然会得不到想要的结果302 // 白色加粗字体303 Font bluesFont = workbook.createFont();304 bluesFont.setColor(IndexedColors.WHITE.getIndex());305 bluesFont.setBold(true);306 bluesFont.setFontName("宋体");307 bluesStyle.setFont(bluesFont);308 cellStylesMap.put("bluesStyle", bluesStyle);// 存放样式-bluesStyle309 310 // 浅蓝色底部311 CellStyle blueStyle = workbook.createCellStyle();312 blueStyle.cloneStyleFrom(cellStylesMap.get("baseStyle"));// 继承某样式313 // 背景色314 blueStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());315 blueStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 这一行是必须的，不然会得不到想要的结果316 cellStylesMap.put("blueStyle", blueStyle);// 存放样式-blueStyle317 318 return cellStylesMap;319 }320 321 /**322 * 字符串判非空323 * 324 * @author ruran325 * @since 2019年7月23日 下午2:29:38326 * @param str327 * @return328 */329 private static boolean isNotBlank(String str) {330 if (null == str) {331 return false;332 }333 if (str.trim().length() == 0) {334 return false;335 }336 return true;337 }338 339 /**340 * 字符串判非空341 * 342 * @author ruran343 * @since 2019年7月23日 下午3:48:57344 * @param str345 * @return346 */347 private static boolean isBlank(String str) {348 if (null == str) {349 return true;350 }351 if (str.trim().length() == 0) {352 return true;353 }354 return false;355 }356 357 /**358 * 获取真实的表名 - 逻辑是去除末尾的数字359 * 360 * @author ruran361 * @since 2019年7月23日 下午3:51:03362 * @param tableName363 * @return364 */365 private static String getRealTablename(String tableName) {366 if (isBlank(tableName)) {367 return null;368 }369 return tableName.replaceAll("\\d+$", "");374 }375 376 /**377 * 获取数据连接378 * 379 * @author ruran380 * @since 2019年7月29日 下午7:38:47381 * @param ip382 * @param user383 * @param password384 * @param database385 * @return386 */387 private static Connection getConnection(String ip, String user, String password, String database) {388 try {389 Class.forName("com.mysql.jdbc.Driver");390 System.out.println("成功加载MySQL驱动程序...");391 Connection connention = DriverManager.getConnection("jdbc:mysql://" + ip + ":3306/" + database, user,392 password);393 System.out.println("成功建立MySQL连接...");394 return connention;395 } catch (Exception e) {396 e.printStackTrace();397 }398 return null;399 }400 401 /**402 * 表数据内部类403 * 404 * @author ruran405 * @since 2019年7月23日 下午4:16:28406 */407 @SuppressWarnings("unused")408 private static class TablePojo {409 String tableName = "";410 String tableComment = "";411 String tableColumns = "";412 413 public TablePojo() {414 415 }416 417 public TablePojo(String tablename, String tablecomment, String tablecolumns) {418 tableName = tablename;419 tableComment = tablecomment;420 tableColumns = tablecolumns;421 }422 423 public String getTableName() {424 return tableName;425 }426 427 public void setTableName(String tableName) {428 this.tableName = tableName;429 }430 431 public String getTableComment() {432 return tableComment;433 }434 435 public void setTableComment(String tableComment) {436 this.tableComment = tableComment;437 }438 439 public String getTableColumns() {440 return tableColumns;441 }442 443 public void setTableColumns(String tableColumns) {444 this.tableColumns = tableColumns;445 }446 447 }448 449 }