[功能集锦] 003 - 一键生成mysql数据字典/数据库速查表
写在前面：
因为工作时候经常遇到半路接手项目的情况，由于年代久远，数据库字典这块经常缺失。故写此篇，以便复用，也希望对大家有点帮助。
随笔内容不高级，如有不妥，不吝指正。
ps：有另一篇详细随笔可以参考【[功能集锦] 002 - mysql查询数据库字典+导出+样式一键整合至excel】。
------------------------------------------------------------分-割-线------------------------------------------------------------
以下为代码，只需要改动部分参数，就可以运行，生成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<String, Map<String, TablePojo>> 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<String, TablePojo> 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-xls 132 * 133 * @author ruran 134 * @since 2019年7月23日 下午5:32:50 135 * @param tableNamesMap 136 * @param fos 137 */ 138 private static void arrangeToXLS(Map<String, Map<String, TablePojo>> 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<String, CellStyle> cellStyles = getCellStyles(currentWorkbook); 144 Set<String> 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<String, TablePojo> 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-xlsx 200 * 201 * @author ruran 202 * @since 2019年7月24日 上午11:51:56 203 * @param tableNamesMap 204 * @param fos 205 */ 206 private static void arrangeToXLSX(Map<String, Map<String, TablePojo>> 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<String, CellStyle> cellStyles = getCellStyles(currentWorkbook); 212 Set<String> 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<String, TablePojo> 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 ruran 270 * @since 2019年7月24日 下午7:32:26 271 * @param workbook 272 * @return 273 */ 274 private static Map<String, CellStyle> 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<String, CellStyle> cellStylesMap = new HashMap<>(); 284 // baseStyle 285 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);// 存放样式-baseStyle 295 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);// 存放样式-bluesStyle 309 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);// 存放样式-blueStyle 317 318 return cellStylesMap; 319 } 320 321 /** 322 * 字符串判非空 323 * 324 * @author ruran 325 * @since 2019年7月23日 下午2:29:38 326 * @param str 327 * @return 328 */ 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 ruran 343 * @since 2019年7月23日 下午3:48:57 344 * @param str 345 * @return 346 */ 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 ruran 361 * @since 2019年7月23日 下午3:51:03 362 * @param tableName 363 * @return 364 */ 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 ruran 380 * @since 2019年7月29日 下午7:38:47 381 * @param ip 382 * @param user 383 * @param password 384 * @param database 385 * @return 386 */ 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 ruran 405 * @since 2019年7月23日 下午4:16:28 406 */ 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 }
