• poi+ResultSet+线程池导出数据库表结构

    1. public class MainTest {
    2.     private static ThreadPoolExecutor pool = new ThreadPoolExecutor(
    3.             5,
    4.             20,
    5.             8,
    6.             TimeUnit.SECONDS,
    7.             new ArrayBlockingQueue<>(5),
    8.             Executors.defaultThreadFactory(),
    9.             new ThreadPoolExecutor.AbortPolicy()
    10.     );
    11.  
    12.     public static void main(String[] args) {
    13.  
    14.         String extSchema = "";
    15.         String driver = "com.mysql.cj.jdbc.Driver";
    16.         //为了保密url暂时不写了
    17.         String url = "";
    18.         String user = "";
    19.         String password = "";
    20.         printTableStructure(driver, url, user, password,extSchema);
    21.  
    22.     }
    23.  
    24.     /**
    25.      * 打印所有表结构
    26.      *
    27.      * @param driver   driver
    28.      * @param url      url
    29.      * @param user     user
    30.      * @param password password
    31.      * @throws Exception exception
    32.      */
    33.     private static void printTableStructure(String driver, String url, String user, String password,String extSchema){
    34.         try{
    35.             Class.forName(driver);
    36.             Connection connection = DriverManager.getConnection(url, user, password);
    37.             DatabaseMetaData metaData = connection.getMetaData();
    38.             ResultSet schemaResultSet = metaData.getCatalogs();
    39.             //同步单个库
    40.             if(!StringUtils.isEmpty(extSchema)){
    41.                 generateFile(connection,metaData,extSchema);
    42.             }else{
    43.                 Long start = System.currentTimeMillis();
    44.                 //CountDownLatch latch = new CountDownLatch(18);
    45.                 //同步所有库
    46.                 while(schemaResultSet.next()){
    47.                     String schema = schemaResultSet.getString("TABLE_CAT");
    48.                     if("information_schema".equals(schema) ||
    49.                             "seata".equals(schema) ||
    50.                             "szcgc".equals(schema) ||
    51.                             "szcgc_project".equals(schema)){
    52.                         continue;
    53.                     }
    54.                     //pool.execute(() -> {
    55.                         generateFile(connection,metaData,schema);
    56.                         //latch.countDown();
    57.                     //});
    58.                 }
    59.                 //pool.shutdown();
    60.                 /*try{
    61.                     latch.await();
    62.                 }catch (InterruptedException exception){
    63.                     exception.getMessage();
    64.                 }*/
    65.                 System.out.println("同步耗时: "+ (System.currentTimeMillis() - start));
    66.             }
    67.         } catch (ClassNotFoundException e) {
    68.             e.printStackTrace();
    69.         } catch (SQLException throwables) {
    70.             throwables.printStackTrace();
    71.         }
    72.     }
    73.  
    74.     private static void generateFile(Connection connection,DatabaseMetaData metaData,String schema){
    75.         try{
    76.             // 获取所有表
    77.             ResultSet tableResultSet = metaData.getTables(schema, schema, "%", new String[]{"TABLE"});
    78.             //poi生成excel
    79.             XSSFWorkbook workbook = new XSSFWorkbook();
    80.             //生成sheet
    81.             XSSFSheet sheetIndex = workbook.createSheet("index");
    82.             //设置列宽
    83.             setColumnWidth(sheetIndex,1);
    84.             //使用线程安全的类
    85.             AtomicInteger i = new AtomicInteger(0);
    86.             while (tableResultSet.next()) {
    87.                 try {
    88.                     String tableName= tableResultSet.getString("TABLE_NAME");
    89.                     String tableRemark = tableResultSet.getString("REMARKS");
    90.                     Row row0 = sheetIndex.createRow(i.get());
    91.                     Cell cell0 = row0.createCell(0);
    92.                     cell0.setCellValue(tableRemark);
    93.                     cell0.setCellStyle(indexCellStyle(workbook,"1"));
    94.                     Cell cell1 = row0.createCell(1);
    95.                     cell1.setCellValue(tableName);
    96.                     cell1.setCellStyle(indexCellStyle(workbook,"2"));
    97.                     //单元格加超链接
    98.                     CreationHelper createHelper = workbook.getCreationHelper();
    99.                     XSSFHyperlink hyperlink = (XSSFHyperlink) createHelper.createHyperlink(HyperlinkType.DOCUMENT);
    100.                     hyperlink.setAddress("#"+tableName+"!A1");
    101.                     cell1.setHyperlink(hyperlink);
    102.                     i.incrementAndGet();
    103.                     //生成表sheet
    104.                     XSSFSheet sheetTable = workbook.createSheet(tableName);
    105.                     setDetailColumnWidth(sheetTable);
    106.                     Row rowTable0 = sheetTable.createRow(0);
    107.                     rowTable0.setHeight((short)400);
    108.                     Cell cellTable00 = rowTable0.createCell(0);
    109.                     cellTable00.setCellValue(tableName);
    110.                     Cell cellTable01 = rowTable0.createCell(1);
    111.                     cellTable01.setCellValue(tableRemark);
    112.                     Row rowTable1 = sheetTable.createRow(1);
    113.                     rowTable1.setHeight((short)600);
    114.                     Cell cellTable10 = rowTable1.createCell(0);
    115.                     cellTable10.setCellValue("键说明");
    116.                     cellTable10.setCellStyle(detailCellStyle(workbook));
    117.                     Cell cellTable11 = rowTable1.createCell(1);
    118.                     cellTable11.setCellValue("字段名称");
    119.                     cellTable11.setCellStyle(detailCellStyle(workbook));
    120.                     Cell cellTable12 = rowTable1.createCell(2);
    121.                     cellTable12.setCellValue("字段类型");
    122.                     cellTable12.setCellStyle(detailCellStyle(workbook));
    123.                     Cell cellTable13 = rowTable1.createCell(3);
    124.                     cellTable13.setCellValue("默认值");
    125.                     cellTable13.setCellStyle(detailCellStyle(workbook));
    126.                     Cell cellTable14 = rowTable1.createCell(4);
    127.                     cellTable14.setCellValue("空值");
    128.                     cellTable14.setCellStyle(detailCellStyle(workbook));
    129.                     Cell cellTable15 = rowTable1.createCell(5);
    130.                     cellTable15.setCellValue("字段描述");
    131.                     cellTable15.setCellStyle(detailCellStyle(workbook,"1"));
    132.                     //获取表所有主键
    133.                     ResultSet pkColumns = connection.getMetaData().getPrimaryKeys(null,null,tableName);
    134.                     // 获取表字段结构
    135.                     ResultSet columnResultSet = metaData.getColumns(schema, schema, tableName, "%");
    136.                     int j = 2;
    137.                     while (columnResultSet.next()) {
    138.                         // 字段名称
    139.                         String columnName = columnResultSet.getString("COLUMN_NAME");
    140.                         // 数据类型
    141.                         String columnType = columnResultSet.getString("TYPE_NAME");
    142.                         // 字段长度
    143.                         int datasize = columnResultSet.getInt("COLUMN_SIZE");
    144.                         // 小数部分位数
    145.                         int digits = columnResultSet.getInt("DECIMAL_DIGITS");
    146.                         // 是否可为空 1代表可空 0代表不可为空
    147.                         int nullable = columnResultSet.getInt("NULLABLE");
    148.                         // 描述
    149.                         String remarks = columnResultSet.getString("REMARKS");
    150.                         //默认值
    151.                         String defValue = columnResultSet.getString("COLUMN_DEF");
    152.                         // 主键
    153.                         String pK = "NO";
    154.                         while(pkColumns.next()){
    155.                             String pkColumnName = pkColumns.getString("COLUMN_NAME");
    156.                             if(columnName.equals(pkColumnName)){
    157.                                 pK = "PK";
    158.                                 break;
    159.                             }
    160.                         }
    161.                         Row rowTable = sheetTable.createRow(j);
    162.                         Cell cellTable0 = rowTable.createCell(0);
    163.                         cellTable0.setCellValue(pK);
    164.                         Cell cellTable1 = rowTable.createCell(1);
    165.                         cellTable1.setCellValue(columnName);
    166.                         Cell cellTable2 = rowTable.createCell(2);
    167.                         cellTable2.setCellValue(columnType+"("+datasize+")");
    168.                         Cell cellTable3 = rowTable.createCell(3);
    169.                         cellTable3.setCellValue(defValue);
    170.                         Cell cellTable4 = rowTable.createCell(4);
    171.                         cellTable4.setCellValue(nullable == 0 ? "NOT NULL":null);
    172.                         Cell cellTable5 = rowTable.createCell(5);
    173.                         cellTable5.setCellValue(remarks);
    174.                         if(!columnResultSet.isLast()){
    175.                             cellTable0.setCellStyle(columnCellStyle(workbook));
    176.                             cellTable1.setCellStyle(columnCellStyle(workbook));
    177.                             cellTable2.setCellStyle(columnCellStyle(workbook));
    178.                             cellTable3.setCellStyle(columnCellStyle(workbook));
    179.                             cellTable4.setCellStyle(columnCellStyle(workbook));
    180.                             cellTable5.setCellStyle(columnCellStyle(workbook,"1"));
    181.                         }else{
    182.                             cellTable0.setCellStyle(columnCellStyle(workbook,"2"));
    183.                             cellTable1.setCellStyle(columnCellStyle(workbook,"2"));
    184.                             cellTable2.setCellStyle(columnCellStyle(workbook,"2"));
    185.                             cellTable3.setCellStyle(columnCellStyle(workbook,"2"));
    186.                             cellTable4.setCellStyle(columnCellStyle(workbook,"2"));
    187.                             cellTable5.setCellStyle(columnCellStyle(workbook,"3"));
    188.                         }
    189.                         j++;
    190.                     }
    191.                     pkColumns.close();
    192.                     columnResultSet.close();
    193.                 } catch (SQLException throwables) {
    194.                     throwables.printStackTrace();
    195.                 }
    196.             }
    197.             OutputStream os = null;
    198.             try {
    199.                 os = new FileOutputStream("d:\\"+schema+".xlsx");
    200.                 workbook.write(os);
    201.                 os.close();
    202.             } catch (Exception e) {
    203.                 e.printStackTrace();
    204.             }
    205.         } catch (SQLException throwables) {
    206.             throwables.printStackTrace();
    207.         }
    208.     }
    209.  
    210.     //索引sheet样式设置
    211.     private static CellStyle indexCellStyle(XSSFWorkbook workbook, String type){
    212.         CellStyle cellStyle = workbook.createCellStyle();
    213.         cellStyle.setBorderBottom(BorderStyle.THIN);
    214.         cellStyle.setBorderLeft(BorderStyle.THIN);
    215.         cellStyle.setBorderRight(BorderStyle.THIN);
    216.         cellStyle.setBorderTop(BorderStyle.THIN);
    217.         //设置字体
    218.         Font font = workbook.createFont();
    219.         font.setFontName("宋体");
    220.         font.setFontHeightInPoints((short) 11);
    221.         if("2".equals(type)){
    222.             font.setUnderline((byte) 1);
    223.             font.setColor(IndexedColors.BLUE.index);
    224.         }
    225.         cellStyle.setFont(font);
    226.         return cellStyle;
    227.     }
    228.  
    229.     //详细表sheet样式设置
    230.     private static CellStyle detailCellStyle(XSSFWorkbook workbook){
    231.         XSSFCellStyle cellStyle = workbook.createCellStyle();
    232.         XSSFColor color=new XSSFColor(new java.awt.Color(155,194,230),new DefaultIndexedColorMap());
    233.         cellStyle.setFillForegroundColor(color);
    234.         cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    235.         cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    236.         cellStyle.setBorderTop(BorderStyle.MEDIUM);
    237.         cellStyle.setTopBorderColor(IndexedColors.BLUE.index);
    238.         //设置字体
    239.         Font font = workbook.createFont();
    240.         font.setFontName("宋体");
    241.         font.setFontHeightInPoints((short) 11);
    242.         cellStyle.setFont(font);
    243.         return  cellStyle;
    244.     }
    245.  
    246.     private static CellStyle detailCellStyle(XSSFWorkbook workbook,String type){
    247.         CellStyle cellStyle = detailCellStyle(workbook);
    248.         cellStyle.setBorderRight(BorderStyle.MEDIUM);
    249.         cellStyle.setRightBorderColor(IndexedColors.BLUE.index);
    250.         return cellStyle;
    251.     }
    252.  
    253.     //设置字段样式
    254.     private static CellStyle columnCellStyle(XSSFWorkbook workbook){
    255.         CellStyle cellStyle = workbook.createCellStyle();
    256.         //设置字体
    257.         Font font = workbook.createFont();
    258.         font.setFontName("宋体");
    259.         font.setFontHeightInPoints((short) 11);
    260.         cellStyle.setFont(font);
    261.         return  cellStyle;
    262.     }
    263.  
    264.     private static CellStyle columnCellStyle(XSSFWorkbook workbook,String type){
    265.         CellStyle cellStyle = columnCellStyle(workbook);
    266.         //右边框
    267.         if("1".equals(type)){
    268.             cellStyle.setBorderRight(BorderStyle.MEDIUM);
    269.             cellStyle.setRightBorderColor(IndexedColors.BLUE.index);
    270.         }
    271.         //底边框
    272.         else if("2".equals(type)){
    273.             cellStyle.setBorderBottom(BorderStyle.MEDIUM);
    274.             cellStyle.setBottomBorderColor(IndexedColors.BLUE.index);
    275.         }
    276.         //表格最后一行需同时设置右边框和底边框
    277.         else{
    278.             cellStyle.setBorderRight(BorderStyle.MEDIUM);
    279.             cellStyle.setRightBorderColor(IndexedColors.BLUE.index);
    280.             cellStyle.setBorderBottom(BorderStyle.MEDIUM);
    281.             cellStyle.setBottomBorderColor(IndexedColors.BLUE.index);
    282.         }
    283.         return  cellStyle;
    284.     }
    285.  
    286.     //设置主页sheet列宽
    287.     private static void setColumnWidth(XSSFSheet sheet,int index){
    288.         for(int i = 0; i<=index; i++){
    289.             sheet.setColumnWidth(i, 30 * 256);
    290.         }
    291.     }
    292.  
    293.     //设置详情sheet列宽
    294.     private static void setDetailColumnWidth(XSSFSheet sheet){
    295.         sheet.setColumnWidth(0, 30 * 256);
    296.         sheet.setColumnWidth(1, 30 * 256);
    297.         sheet.setColumnWidth(2, 15 * 256);
    298.         sheet.setColumnWidth(3, 15 * 256);
    299.         sheet.setColumnWidth(4, 15 * 256);
    300.         sheet.setColumnWidth(5, 30 * 256);
    301.     }
    302.  
    303.     //使用静态内部类（在初始化这个内部类的时候,JLS(Java Language  Sepcification)会保证这个类的线程安全）
    304.     private static class SingleTonHolder{
    305.         public final static XSSFWorkbook singleTon = new XSSFWorkbook();
    306.     }
    307.     public static XSSFWorkbook getInstance(){
    308.         return SingleTonHolder.singleTon;
    309.     }
    310. }

  • 相关阅读:
    快手安全 X 墨菲安全 | 软件供应链安全解决方案完整分享
    nvm安装后node或npm不是内部或外部命令
    web前端课程设计——重庆旅游7页 HTML+CSS+JavaScript
    书生·浦语大模型实战营第二期作业六
    设置网站置灰 css / js
    创建n维空间每个维度（轴）的刻度值numpy.ogrid[]
    视频拉流推流技术梳理
    PC5080USB适配器充电芯片5V/1A输入具有0V充电功能
    过滤器Filter和拦截器Interceptor实现登录校验
    设计模式：命令模式
  • 原文地址：https://blog.csdn.net/jesson_c/article/details/127764206