数据库表word表格形式中文名称生成PowerDesigner文件显示中文name

男娘i 2022-12-20 08:56 243阅读 0赞

记一次奇思妙想

客户要求这样的er图

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4Mzc3MTkw_size_16_color_FFFFFF_t_70][]

一开始的想法是，如果我们表结构有备注，那么通过PowerDesigner逆向工程实现导入，再通过name复制comment的方式实现上图。

但是看了一下我们的数据表结构，几乎没有备注。哭了！！！！！！

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4Mzc3MTkw_size_16_color_FFFFFF_t_70 1][]

所幸的是，在前几天的时候，客户要求对word上的数据库表中文名称进行补充。效果如下

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4Mzc3MTkw_size_16_color_FFFFFF_t_70 2][]

就突发奇想思路如下

**1.java程序解析word表格，获取出代码和中文名称的值，存到Map对象中**

**2.连接数据库，获取所有表名，遍历所有表名下的所有字段名**

**3.与Map的Key进行匹配，获取对应的中文名称，执行sql语句===》COMMENT ON COLUMN "TABLENAME"."COLUMNNAME" IS '中文名';**

**4.使用数据库可视化工具生成脚本文件**

**5.PowerDesigner逆向工程，选中脚本工具**

**6.执行PowerDesigner工程VBS程序将comment复制到name**

思路有了，一步一步执行

## 1.先用java程序解析word文档（整个程序写在main方法里） ##

HashMap<String, String> aa = new HashMap<>();
            wordTest test = new wordTest();
    		String filePath = "D:\\new\\测试.doc";
            test.testWord(filePath, aa);
            System.out.println(aa.size());

/**
         * 读取文档中表格
         *
         * @param filePath
         * @param aa
         */
        public void testWord(String filePath, HashMap<String, String> aa) {
            try {
                FileInputStream in = new FileInputStream(filePath);//载入文档
                // 处理docx格式 即office2007以后版本
                if (filePath.toLowerCase().endsWith("docx")) {
                    //word 2007 图片不会被读取， 表格中的数据会被放在字符串的最后
                    XWPFDocument xwpf = new XWPFDocument(in);//得到word文档的信息
                    Iterator<XWPFTable> it = xwpf.getTablesIterator();//得到word中的表格
                    // 设置需要读取的表格  set是设置需要读取的第几个表格，total是文件中表格的总数
                    int set = 2, total = 4;
                    int num = set;
                    // 过滤前面不需要的表格
                    for (int i = 0; i < set - 1; i++) {
                        it.hasNext();
                        it.next();
                    }
                    while (it.hasNext()) {
                        XWPFTable table = it.next();
                        List<XWPFTableRow> rows = table.getRows();
                        //读取每一行数据
                        for (int i = 1; i < rows.size(); i++) {
                            XWPFTableRow row = rows.get(i);
                            //读取每一列数据
                            List<XWPFTableCell> cells = row.getTableCells();
                            HashMap<String, String> bb = new HashMap<>(1);
                            for (int j = 0; j < 2; j++) {
                                XWPFTableCell cell = cells.get(j);
                                //输出当前的单元格的数据
                                System.out.print(cell.getText() + "\t");
                            }
                            aa.put(cells.get(0).getText(), cells.get(1).getText());
                            System.out.println();
                        }
                        // 过滤多余的表格
                        while (num < total) {
                            it.hasNext();
                            it.next();
                            num += 1;
                        }
                    }
                } else {
                    // 处理doc格式 即office2003版本
                    POIFSFileSystem pfs = new POIFSFileSystem(in);
                    HWPFDocument hwpf = new HWPFDocument(pfs);
                    Range range = hwpf.getRange();//得到文档的读取范围
                    TableIterator it = new TableIterator(range);
                    // 迭代文档中的表格
                    // 如果有多个表格只读取需要的一个 set是设置需要读取的第几个表格，total是文件中表格的总数
                    int set = 1, total = 4;
                    int num = set;
                    for (int i = 0; i < set - 1; i++) {
                        it.hasNext();
                        it.next();
                    }
                    while (it.hasNext()) {
                        Table tb = (Table) it.next();
                        System.out.println("这是第" + num + "个表的数据");
                        //迭代行，默认从0开始,可以依据需要设置i的值,改变起始行数，也可设置读取到那行，只需修改循环的判断条件即可
                        for (int i = 0; i < tb.numRows(); i++) {
                            TableRow tr = tb.getRow(i);
                            //迭代列，默认从0开始
                            for (int j = 0; j < tr.numCells(); j++) {
                                TableCell td = tr.getCell(j);//取得单元格
                                //取得单元格的内容
                                for (int k = 0; k < td.numParagraphs(); k++) {
                                    Paragraph para = td.getParagraph(k);
                                    String s = para.text();
                                    //去除后面的特殊符号
                                    if (null != s && !"".equals(s)) {
                                        s = s.substring(0, s.length() - 1);
                                    }
                                    System.out.print(s + "\t");
                                }
                            }
                            System.out.println();
                        }
                        // 过滤多余的表格
                        while (num < total) {
                            it.hasNext();
                            it.next();
                            num += 1;
                        }
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

maven导包如下

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4Mzc3MTkw_size_16_color_FFFFFF_t_70 3][]

等到 key为column值为中文的结构

如下图所示

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4Mzc3MTkw_size_16_color_FFFFFF_t_70 4][]

## 2.连接数据库，查出所有表以及所有列并且匹配存到项目中的map对象，执行提交注释sql语句 ##

static String driverClass = "oracle.jdbc.driver.OracleDriver"; //oracle的驱动
        static String url = "jdbc:oracle:thin:@172.172.172.172:1521:ORCL";  //连接oracle路径方式 “”gfs“”是要建立连接的数据库名 1521端口
        static String user = "user";   //user是数据库的用户名
        static String password = "password";  //用户登录密码

Connection conn = null;
            conn = getConnection(conn);
    
            try {
                DatabaseMetaData meta = conn.getMetaData();
                ResultSet rs = meta.getTables(null, null, null,
                        new String[]{"TABLE"});
                while (rs.next()) {
                    System.out.println("表名：" + rs.getString(3));
                    getTableName(aa, conn, rs);
                }
            } catch (Exception e) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }

private static Connection getConnection(Connection conn) {
            try {
                //首先建立驱动
                Driver driver = new OracleDriver();
                DriverManager.deregisterDriver(driver);
    
                //驱动成功后进行连接
                conn = DriverManager.getConnection(url, user, password);
    
                System.out.println("连接成功");
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            }
            return conn;
        }

private static void getTableName(HashMap<String, String> aa, Connection conn, ResultSet rs) throws SQLException {
            String sql = " select * from " + rs.getString(3);
            // 用于调用存储过程：CallableStatement cs = null;
            PreparedStatement cs = null;
            ResultSetMetaData data = null;
            ResultSet rs1 = null;
            try {
                cs = conn.prepareStatement(sql);
                rs1 = cs.executeQuery(sql);
    
                data = rs1.getMetaData();
                for (int i = 1; i <= data.getColumnCount(); i++) {
                    //获得指定列的列名
                    String columnName = data.getColumnName(i);
                    System.out.println("列名==" + columnName);
                    //匹配
                    String value = aa.get(columnName);
                    if (StringUtils.isEmpty(value)) {
                        System.out.println("没找到对应的字段名======");
                        continue;
                    }
                    System.out.println("执行sql=COMMENT ON COLUMN " + rs.getString(3) + "." + columnName + " IS '" + value + "'");
                    System.out.println(columnName + "  " + value);
                    //执行sql
                    PreparedStatement cs2 = null;
                    cs2 = conn.prepareStatement("COMMENT ON COLUMN " + rs.getString(3) + "." + columnName + " IS '" + value + "'");
                    int count = cs2.executeUpdate();
                    cs2.close();
                    System.out.println(count + "===================");
                }
                cs.close();
                System.out.println("------------------------------");
    
            } catch (Exception e) {
                assert cs != null;
                cs.close();
                System.out.println("报错啦=====" + e.getMessage());
            }
        }

生成后表的效果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4Mzc3MTkw_size_16_color_FFFFFF_t_70 5][]

## 3.使用数据库可视化工具生成sql脚本，用PowerDesigner逆向工程功能实现表格导入 ##

使用Navicat生成sql脚本，操作如下

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4Mzc3MTkw_size_16_color_FFFFFF_t_70 6][]

PowerDesigner逆向工程实现表结构导入，操作如下

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4Mzc3MTkw_size_16_color_FFFFFF_t_70 7][]

导入成功后，生成效果如下图。（还差一步就成功了）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4Mzc3MTkw_size_16_color_FFFFFF_t_70 8][]

## 4.执行PowerDesigner程序，将comment复制到name上 ##

点开如下功能

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4Mzc3MTkw_size_16_color_FFFFFF_t_70 9][]

代码如下

Option   Explicit   
    ValidationMode   =   True   
    InteractiveMode   =   im_Batch  
      
    Dim   mdl   '   the   current   model  
      
    '   get   the   current   active   model   
    Set   mdl   =   ActiveModel   
    If   (mdl   Is   Nothing)   Then   
          MsgBox   "There   is   no   current   Model "   
    ElseIf   Not   mdl.IsKindOf(PdPDM.cls_Model)   Then   
          MsgBox   "The   current   model   is   not   an   Physical   Data   model. "   
    Else   
          ProcessFolder   mdl   
    End   If  
      
    Private   sub   ProcessFolder(folder)   
    On Error Resume Next  
          Dim   Tab   'running     table   
          for   each   Tab   in   folder.tables   
                if   not   tab.isShortcut   then   
                      tab.name   =   tab.comment  
                      Dim   col   '   running   column   
                      for   each   col   in   tab.columns   
                      if col.comment="" then  
                      else  
                            col.name=   col.comment   
                      end if  
                      next   
                end   if   
          next  
      
          Dim   view   'running   view   
          for   each   view   in   folder.Views   
                if   not   view.isShortcut   then   
                      view.name   =   view.comment   
                end   if   
          next  
      
          '   go   into   the   sub-packages   
          Dim   f   '   running   folder   
          For   Each   f   In   folder.Packages   
                if   not   f.IsShortcut   then   
                      ProcessFolder   f   
                end   if   
          Next   
    end   sub

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4Mzc3MTkw_size_16_color_FFFFFF_t_70 10][]

效果如下

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4Mzc3MTkw_size_16_color_FFFFFF_t_70 11][]

**大功告成，撒花。**

**代码放在这里：**[https://gitee.com/zhengkaixing/work-test.git][https_gitee.com_zhengkaixing_work-test.git]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4Mzc3MTkw_size_16_color_FFFFFF_t_70]: /images/20221120/e231f742acfa41fe8918c2a8fc40827a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4Mzc3MTkw_size_16_color_FFFFFF_t_70 1]: /images/20221120/a04a78cfce204eb78ea6bd9d4a9039b4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4Mzc3MTkw_size_16_color_FFFFFF_t_70 2]: /images/20221120/934c8d4a8339474ca71d77da262ffa1d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4Mzc3MTkw_size_16_color_FFFFFF_t_70 3]: /images/20221120/ff15eaa63b1341aaafae1e131828f017.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4Mzc3MTkw_size_16_color_FFFFFF_t_70 4]: /images/20221120/7e3198c675a2424fbab066fba3a6cd9e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4Mzc3MTkw_size_16_color_FFFFFF_t_70 5]: /images/20221120/ad3e4b391ee4418498cddef349358c36.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4Mzc3MTkw_size_16_color_FFFFFF_t_70 6]: /images/20221120/8d80006f407c4eea96ee0693b6b30fba.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4Mzc3MTkw_size_16_color_FFFFFF_t_70 7]: /images/20221120/def118b749d24b8a9d6e4782dfad4d7c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4Mzc3MTkw_size_16_color_FFFFFF_t_70 8]: /images/20221120/0457acd721764349a4188f53e08ca004.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4Mzc3MTkw_size_16_color_FFFFFF_t_70 9]: /images/20221120/8d6518709061434ab0aff3eb02651fa6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4Mzc3MTkw_size_16_color_FFFFFF_t_70 10]: /images/20221120/65f51e51549742d9a18ff6d1a4d6dd33.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4Mzc3MTkw_size_16_color_FFFFFF_t_70 11]: /images/20221120/f25c3ce01978430281cf9fd5f015b883.png
[https_gitee.com_zhengkaixing_work-test.git]: https://gitee.com/zhengkaixing/work-test.git