mysql查看数据库字典sql_[功能集锦] 002 - mysql查询数据库字典+导出+样式一键整合至excel...

1 importjava.io.BufferedReader;2 importjava.io.FileInputStream;3 importjava.io.FileOutputStream;4 importjava.io.InputStreamReader;5 importjava.util.HashMap;6 importjava.util.Map;7 importjava.util.Set;8

9 importorg.apache.commons.collections4.MapUtils;10 importorg.apache.poi.hssf.usermodel.HSSFCell;11 importorg.apache.poi.hssf.usermodel.HSSFRow;12 importorg.apache.poi.hssf.usermodel.HSSFSheet;13 importorg.apache.poi.hssf.usermodel.HSSFWorkbook;14 importorg.apache.poi.ss.usermodel.CellStyle;15 importorg.apache.poi.ss.usermodel.FillPatternType;16 importorg.apache.poi.ss.usermodel.Font;17 importorg.apache.poi.ss.usermodel.HorizontalAlignment;18 importorg.apache.poi.ss.usermodel.IndexedColors;19 importorg.apache.poi.ss.usermodel.VerticalAlignment;20 importorg.apache.poi.ss.usermodel.Workbook;21 importorg.apache.poi.ss.util.CellRangeAddress;22 importorg.apache.poi.xssf.usermodel.XSSFCell;23 importorg.apache.poi.xssf.usermodel.XSSFRow;24 importorg.apache.poi.xssf.usermodel.XSSFSheet;25 importorg.apache.poi.xssf.usermodel.XSSFWorkbook;26

27 /**

28 * 生成数据库数据结构速查文件(数据库字典)29 *30 *@authorruran31 *@since2019年7月4日 下午3:25:1332 */

33 public classProduceGuideOfDatabase {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 表字段释义 FROM41 * information_schema.`TABLES` AS pretab RIGHT JOIN42 * information_schema.`COLUMNS` AS precol ON43 * 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 voidmain(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文件中重整成excel56 *57 *@authorruran58 *@since2019年7月24日 下午4:40:1059 */

60 private static voidreArrangeFromSQLtxt() {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 *@authorruran81 *@since2019年7月22日 下午2:06:5482 *@paramurl83 *@paramfileName84 *@paramsplitStr85 */

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 = newInputStreamReader(fis);92 BufferedReader br = newBufferedReader(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 returndatabase_table;158 }159

160 /**

161 * 取数据整合到excel-xls162 *163 *@authorruran164 *@since2019年7月23日 下午5:32:50165 *@paramtableNamesMap166 *@paramfos167 */

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 = newHSSFWorkbook();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 *@authorruran232 *@since2019年7月24日 上午11:51:56233 *@paramtableNamesMap234 *@paramfos235 */

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 = newXSSFWorkbook();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 *@authorruran300 *@since2019年7月24日 下午7:32:26301 *@paramworkbook302 *@return

303 */

304 private static MapgetCellStyles(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 //baseStyle

315 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);//存放样式-blueStyle

347

348 returncellStylesMap;349 }350

351 /**

352 * 字符串判非空353 *354 *@authorruran355 *@since2019年7月23日 下午2:29:38356 *@paramstr357 *@return

358 */

359 private static booleanisNotBlank(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 *@authorruran373 *@since2019年7月23日 下午3:48:57374 *@paramstr375 *@return

376 */

377 private static booleanisBlank(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 *@authorruran391 *@since2019年7月23日 下午3:51:03392 *@paramtableName393 *@return

394 */

395 private staticString getRealTablename(String tableName) {396 if(isBlank(tableName)) {397 return null;398 }399 return tableName.replaceAll("\\d+$", "");404 }405

406 /**

407 * 表数据内部类408 *409 *@authorruran410 *@since2019年7月23日 下午4:16:28411 */

412 @SuppressWarnings("unused")413 private static classTablePojo {414 String tableName = "";415 String tableComment = "";416 String tableColumns = "";417

418 publicTablePojo() {419

420 }421

422 publicTablePojo(String tablename, String tablecomment, String tablecolumns) {423 tableName =tablename;424 tableComment =tablecomment;425 tableColumns =tablecolumns;426 }427

428 publicString getTableName() {429 returntableName;430 }431

432 public voidsetTableName(String tableName) {433 this.tableName =tableName;434 }435

436 publicString getTableComment() {437 returntableComment;438 }439

440 public voidsetTableComment(String tableComment) {441 this.tableComment =tableComment;442 }443

444 publicString getTableColumns() {445 returntableColumns;446 }447

448 public voidsetTableColumns(String tableColumns) {449 this.tableColumns =tableColumns;450 }451

452 }453

454 }