写在前面：
因为工作时候经常遇到半路接手项目的情况，由于年代久远，数据库字典这块经常缺失。故写此篇，以便复用，也希望对大家有点帮助。
随笔内容不高级，如有不妥，不吝指正。
20190730-加了一些简单样式，生成的excel文件，只需要人为操作设置列宽度自适应，样式就基本ok了；
------------------------------------------------------------分-割-线------------------------------------------------------------
第一步：查询数据库
查询语句：
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;
结果图示：
第二步：导出查询结果
导出txt：
导出结果：
第三步：一键整合至excel
运行下方代码：
1 import java.io.BufferedReader; 2 import java.io.FileInputStream; 3 import java.io.FileOutputStream; 4 import java.io.InputStreamReader; 5 import java.util.HashMap; 6 import java.util.Map; 7 import java.util.Set; 8 9 import org.apache.commons.collections4.MapUtils; 10 import org.apache.poi.hssf.usermodel.HSSFCell; 11 import org.apache.poi.hssf.usermodel.HSSFRow; 12 import org.apache.poi.hssf.usermodel.HSSFSheet; 13 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 14 import org.apache.poi.ss.usermodel.CellStyle; 15 import org.apache.poi.ss.usermodel.FillPatternType; 16 import org.apache.poi.ss.usermodel.Font; 17 import org.apache.poi.ss.usermodel.HorizontalAlignment; 18 import org.apache.poi.ss.usermodel.IndexedColors; 19 import org.apache.poi.ss.usermodel.VerticalAlignment; 20 import org.apache.poi.ss.usermodel.Workbook; 21 import org.apache.poi.ss.util.CellRangeAddress; 22 import org.apache.poi.xssf.usermodel.XSSFCell; 23 import org.apache.poi.xssf.usermodel.XSSFRow; 24 import org.apache.poi.xssf.usermodel.XSSFSheet; 25 import org.apache.poi.xssf.usermodel.XSSFWorkbook; 26 27 /** 28 * 生成数据库数据结构速查文件（数据库字典） 29 * 30 * @author ruran 31 * @since 2019年7月4日 下午3:25:13 32 */ 33 public class ProduceGuideOfDatabase { 34 35 /* 36 * 数据来源 37 * 38 * SELECT pretab.TABLE_NAME AS 表名,pretab.TABLE_COMMENT AS 表释义, 39 * precol.COLUMN_NAME AS 字段名,precol.COLUMN_TYPE AS 字段类型, 40 * precol.COLUMN_DEFAULT AS 字段默认值,precol.COLUMN_COMMENT AS 表字段释义 FROM 41 * information_schema.`TABLES` AS pretab RIGHT JOIN 42 * information_schema.`COLUMNS` AS precol ON 43 * precol.TABLE_NAME=pretab.TABLE_NAME WHERE pretab.TABLE_SCHEMA ="此处填写库名" 44 * GROUP BY precol.TABLE_NAME,precol.COLUMN_NAME ORDER BY precol.TABLE_NAME; 45 */ 46 public static void main(String[] args) { 47 System.out.println("开始运行程序。。。"); 48 long preTime = System.currentTimeMillis(); 49 // navicat导出txt-程序整理生成字典文件（人工参与步骤多，繁琐，不智能） 50 reArrangeFromSQLtxt(); 51 System.out.println("运行完成，耗时：" + (System.currentTimeMillis() - preTime) + "ms"); 52 } 53 54 /** 55 * 从TXT文件中重整成excel 56 * 57 * @author ruran 58 * @since 2019年7月24日 下午4:40:10 59 */ 60 private static void reArrangeFromSQLtxt() { 61 String url = "F:\\2-ME\\中心+部门\\1-scrs学习整理区\\数据库字典整理\\"; 62 String[] fromFiles = "scrssit-scrssit2-scrssit3-scrssit4-scrssit5-scrssit6-scrssit7-scrssit8-scrssit9-scrssit10-scrssit11" 63 .split("-"); 64 String forFile = "系统数据库结构参考速查表-20190724.xlsx"; 65 Map > database_tables = reDataFromSQLtxt(url, fromFiles, "@"); 66 if (MapUtils.isNotEmpty(database_tables)) { 67 if (forFile.contains(".xlsx")) { 68 arrangeToXLSX(database_tables, url, forFile); 69 } else { 70 arrangeToXLS(database_tables, url, forFile); 71 } 72 } 73 } 74 75 /** 76 * 整理数据库字典 77 * 78 * 可防止分表多次输出 79 * 80 * @author ruran 81 * @since 2019年7月22日 下午2:06:54 82 * @param url 83 * @param fileName 84 * @param splitStr 85 */ 86 private static Map > reDataFromSQLtxt(String url, String[] fromFileNames, 87 String splitStr) { 88 Map > database_table = new HashMap<>(); 89 for (String fromFileName : fromFileNames) { 90 try (FileInputStream fis = new FileInputStream(url + fromFileName + ".txt"); 91 InputStreamReader isr = new InputStreamReader(fis); 92 BufferedReader br = new BufferedReader(isr);) { 93 String readLine = ""; 94 String columnLines = ""; 95 int countAll = 0;// 表总数 96 Map tableNames = new HashMap<>(); 97 String preTableName = ""; 98 String preTableComment = ""; 99 while (isNotBlank((readLine = br.readLine()))) {100 String[] lineSplit = readLine.split(splitStr);101 int lineSplitLenght = lineSplit.length;102 String currentTableName = "";103 if (lineSplitLenght > 0) {104 currentTableName = lineSplit[0];105 }106 if (tableNames.containsKey(getRealTablename(currentTableName))) {107 continue;108 }109 String currentTableComment = "";110 String currentColumnName = "";111 String currentColumnType = "";112 String currentColumnDefault = "";113 String currentColumnComment = "";114 if (lineSplitLenght > 1) {115 currentTableComment = lineSplit[1];116 }117 if (lineSplitLenght > 2) {118 currentColumnName = lineSplit[2];119 }120 if (lineSplitLenght > 3) {121 currentColumnType = lineSplit[3];122 }123 if (lineSplitLenght > 4) {124 currentColumnDefault = lineSplit[4];125 }126 if (lineSplitLenght > 5) {127 currentColumnComment = lineSplit[5];128 }129 if (currentTableName.equals(preTableName)) {130 columnLines += currentColumnName + "#" + currentColumnType + "#" + currentColumnDefault + "#"131 + currentColumnComment + "@";132 continue;133 }134 if (countAll != 0 && !tableNames.containsKey(getRealTablename(preTableName))) {135 TablePojo tablePojo = new TablePojo(preTableName, preTableComment, columnLines.substring(0,136 columnLines.length() - 1));137 tableNames.put(getRealTablename(preTableName), tablePojo);138 }139 countAll++;140 columnLines = currentColumnName + "#" + currentColumnType + "#" + currentColumnDefault + "#"141 + currentColumnComment + "@";142 preTableName = currentTableName;143 preTableComment = currentTableComment;144 }145 // 最后一组数据判断+保存146 if (!tableNames.containsKey(getRealTablename(preTableName))) {147 TablePojo tablePojo = new TablePojo(preTableName, preTableComment, columnLines.substring(0,148 columnLines.length() - 1));149 tableNames.put(getRealTablename(preTableName), tablePojo);150 }151 database_table.put(fromFileName, tableNames);152 } catch (Exception e) {153 e.printStackTrace();154 continue;155 }156 }157 return database_table;158 }159 160 /**161 * 取数据整合到excel-xls162 * 163 * @author ruran164 * @since 2019年7月23日 下午5:32:50165 * @param tableNamesMap166 * @param fos167 */168 private static void arrangeToXLS(Map > database_tables, String url, String forFile) {169 try (FileOutputStream fos = new FileOutputStream(url + forFile);) {170 if (MapUtils.isNotEmpty(database_tables)) {171 HSSFWorkbook currentWorkbook = new HSSFWorkbook();172 // 获取所有样式173 Map cellStyles = getCellStyles(currentWorkbook);174 Set databaseNames = database_tables.keySet();175 for (String databaseName : databaseNames) {176 HSSFSheet currentSheet = currentWorkbook.createSheet(databaseName);177 HSSFRow currentRow = null;178 HSSFCell currentCell = null;179 int rowIndex = -1;180 Map tableNames = database_tables.get(databaseName);181 for (TablePojo tablePojo : tableNames.values()) {182 // 空行183 currentSheet.createRow(++rowIndex);184 // 表头185 currentRow = currentSheet.createRow(++rowIndex);186 currentRow.setHeightInPoints(18);187 currentCell = currentRow.createCell(0);188 currentCell.setCellStyle(cellStyles.get("bluesStyle"));189 currentCell.setCellValue(tablePojo.getTableName() + "(" + tablePojo.getTableComment() + ")");190 CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex, 0, 3);191 currentSheet.addMergedRegion(region);192 // 表-标题栏193 currentRow = currentSheet.createRow(++rowIndex);194 currentRow.setHeightInPoints(18);195 currentCell = currentRow.createCell(0);196 currentCell.setCellStyle(cellStyles.get("blueStyle"));197 currentCell.setCellValue("列名");198 currentCell = currentRow.createCell(1);199 currentCell.setCellStyle(cellStyles.get("blueStyle"));200 currentCell.setCellValue("类型");201 currentCell = currentRow.createCell(2);202 currentCell.setCellStyle(cellStyles.get("blueStyle"));203 currentCell.setCellValue("默认值");204 currentCell = currentRow.createCell(3);205 currentCell.setCellStyle(cellStyles.get("blueStyle"));206 currentCell.setCellValue("释义");207 // 表字段208 String tableColumnsStr = tablePojo.getTableColumns();209 for (String tableColumns : tableColumnsStr.split("@")) {210 currentRow = currentSheet.createRow(++rowIndex);211 currentRow.setHeightInPoints(18);212 String[] tableColumnArr = tableColumns.split("#");213 for (int i = 0; i < tableColumnArr.length; i++) {214 currentCell = currentRow.createCell(i);215 currentCell.setCellStyle(cellStyles.get("baseStyle"));216 currentCell.setCellValue(tableColumnArr[i]);217 }218 }219 }220 }221 currentWorkbook.write(fos);222 }223 } catch (Exception e) {224 e.printStackTrace();225 }226 }227 228 /**229 * 取数据整合到excel-xlsx230 * 231 * @author ruran232 * @since 2019年7月24日 上午11:51:56233 * @param tableNamesMap234 * @param fos235 */236 private static void arrangeToXLSX(Map > database_tables, String url, String forFile) {237 try (FileOutputStream fos = new FileOutputStream(url + forFile);) {238 if (MapUtils.isNotEmpty(database_tables)) {239 XSSFWorkbook currentWorkbook = new XSSFWorkbook();240 // 获取所有样式241 Map cellStyles = getCellStyles(currentWorkbook);242 Set databaseNames = database_tables.keySet();243 for (String databaseName : databaseNames) {244 XSSFSheet currentSheet = currentWorkbook.createSheet(databaseName);245 XSSFRow currentRow = null;246 XSSFCell currentCell = null;247 int rowIndex = -1;248 Map tableNames = database_tables.get(databaseName);249 for (TablePojo tablePojo : tableNames.values()) {250 // 空行251 currentSheet.createRow(++rowIndex);252 // 表头253 currentRow = currentSheet.createRow(++rowIndex);254 currentRow.setHeightInPoints(18);255 currentCell = currentRow.createCell(0);256 currentCell.setCellStyle(cellStyles.get("bluesStyle"));257 currentCell.setCellValue(tablePojo.getTableName() + "(" + tablePojo.getTableComment() + ")");258 CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex, 0, 3);259 currentSheet.addMergedRegion(region);260 // 表-标题栏261 currentRow = currentSheet.createRow(++rowIndex);262 currentRow.setHeightInPoints(18);263 currentCell = currentRow.createCell(0);264 currentCell.setCellStyle(cellStyles.get("blueStyle"));265 currentCell.setCellValue("列名");266 currentCell = currentRow.createCell(1);267 currentCell.setCellStyle(cellStyles.get("blueStyle"));268 currentCell.setCellValue("类型");269 currentCell = currentRow.createCell(2);270 currentCell.setCellStyle(cellStyles.get("blueStyle"));271 currentCell.setCellValue("默认值");272 currentCell = currentRow.createCell(3);273 currentCell.setCellStyle(cellStyles.get("blueStyle"));274 currentCell.setCellValue("释义");275 // 表字段276 String tableColumnsStr = tablePojo.getTableColumns();277 for (String tableColumns : tableColumnsStr.split("@")) {278 currentRow = currentSheet.createRow(++rowIndex);279 currentRow.setHeightInPoints(18);280 String[] tableColumnArr = tableColumns.split("#");281 for (int i = 0; i < tableColumnArr.length; i++) {282 currentCell = currentRow.createCell(i);283 currentCell.setCellStyle(cellStyles.get("baseStyle"));284 currentCell.setCellValue(tableColumnArr[i]);285 }286 }287 }288 }289 currentWorkbook.write(fos);290 }291 } catch (Exception e) {292 e.printStackTrace();293 }294 }295 296 /**297 * 样式集锦298 * 299 * @author ruran300 * @since 2019年7月24日 下午7:32:26301 * @param workbook302 * @return303 */304 private static Map getCellStyles(Workbook workbook) {305 // 实线边框306 // style1.setBorderTop(BorderStyle.THIN);307 // style1.setBorderBottom(BorderStyle.THIN);308 // style1.setBorderLeft(BorderStyle.THIN);309 // style1.setBorderRight(BorderStyle.THIN);310 // 设置自动换行311 // baseStyle.setWrapText(true);312 313 Map cellStylesMap = new HashMap<>();314 // baseStyle315 CellStyle baseStyle = workbook.createCellStyle();316 // 水平对齐方式317 baseStyle.setAlignment(HorizontalAlignment.LEFT);318 // 垂直对齐方式319 baseStyle.setVerticalAlignment(VerticalAlignment.CENTER);320 // 宋体设置321 Font baseFont = workbook.createFont();322 baseFont.setFontName("宋体");323 baseStyle.setFont(baseFont);324 cellStylesMap.put("baseStyle", baseStyle);// 存放样式-baseStyle325 326 // 深蓝色底部、白色字体、加粗327 CellStyle bluesStyle = workbook.createCellStyle();328 bluesStyle.cloneStyleFrom(cellStylesMap.get("baseStyle"));// 继承某样式329 // 背景色330 bluesStyle.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());331 bluesStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 这一行是必须的，不然会得不到想要的结果332 // 白色加粗字体333 Font bluesFont = workbook.createFont();334 bluesFont.setColor(IndexedColors.WHITE.getIndex());335 bluesFont.setBold(true);336 bluesFont.setFontName("宋体");337 bluesStyle.setFont(bluesFont);338 cellStylesMap.put("bluesStyle", bluesStyle);// 存放样式-bluesStyle339 340 // 浅蓝色底部341 CellStyle blueStyle = workbook.createCellStyle();342 blueStyle.cloneStyleFrom(cellStylesMap.get("baseStyle"));// 继承某样式343 // 背景色344 blueStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());345 blueStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 这一行是必须的，不然会得不到想要的结果346 cellStylesMap.put("blueStyle", blueStyle);// 存放样式-blueStyle347 348 return cellStylesMap;349 }350 351 /**352 * 字符串判非空353 * 354 * @author ruran355 * @since 2019年7月23日 下午2:29:38356 * @param str357 * @return358 */359 private static boolean isNotBlank(String str) {360 if (null == str) {361 return false;362 }363 if (str.trim().length() == 0) {364 return false;365 }366 return true;367 }368 369 /**370 * 字符串判非空371 * 372 * @author ruran373 * @since 2019年7月23日 下午3:48:57374 * @param str375 * @return376 */377 private static boolean isBlank(String str) {378 if (null == str) {379 return true;380 }381 if (str.trim().length() == 0) {382 return true;383 }384 return false;385 }386 387 /**388 * 获取真实的表名 - 逻辑是去除末尾的数字389 * 390 * @author ruran391 * @since 2019年7月23日 下午3:51:03392 * @param tableName393 * @return394 */395 private static String getRealTablename(String tableName) {396 if (isBlank(tableName)) {397 return null;398 }399 return tableName.replaceAll("\\d+$", "");404 }405 406 /**407 * 表数据内部类408 * 409 * @author ruran410 * @since 2019年7月23日 下午4:16:28411 */412 @SuppressWarnings("unused")413 private static class TablePojo {414 String tableName = "";415 String tableComment = "";416 String tableColumns = "";417 418 public TablePojo() {419 420 }421 422 public TablePojo(String tablename, String tablecomment, String tablecolumns) {423 tableName = tablename;424 tableComment = tablecomment;425 tableColumns = tablecolumns;426 }427 428 public String getTableName() {429 return tableName;430 }431 432 public void setTableName(String tableName) {433 this.tableName = tableName;434 }435 436 public String getTableComment() {437 return tableComment;438 }439 440 public void setTableComment(String tableComment) {441 this.tableComment = tableComment;442 }443 444 public String getTableColumns() {445 return tableColumns;446 }447 448 public void setTableColumns(String tableColumns) {449 this.tableColumns = tableColumns;450 }451 452 }453 454 } 生成结果：
