word版表结构转为mysql DDL

最新推荐文章于 2024-10-16 11:05:26 发布

java_小怪兽

最新推荐文章于 2024-10-16 11:05:26 发布

阅读量738

点赞数

分类专栏： mysql 文章标签： mysql java 数据库

本文链接：https://blog.csdn.net/qq_24491115/article/details/125671657

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

这篇博客介绍了如何将包含MySQL表结构的Word文档转换为建表语句。作者通过Java代码实现了从Word解析表结构，然后生成对应的MySQL建表语句，包括处理字段名、数据类型、主键、非空和默认值等信息。

摘要由CSDN通过智能技术生成

昨天接到个任务，领导给了一份word文档，里面都是mysql表结构。让我把表结构转为mysql建表语句。此刻内心真是一万个。。。

一.解决思路：

1.解析word表结构

2.解析的数据转为建表语句

二. word结构

#	字段	名称	数据类型	主键	非空	默认值	备注说明
1		状态	VARCHAR(30)	√	√

三.代码部分

package com.zkj.ac.general.api.test;
import java.io.*;
import java.rmi.RemoteException;
import java.util.*;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.xwpf.extractor.XWPFWordExtractor;
import org.apache.poi.xwpf.usermodel.*;

public class ReaderWord {
    public static void main(String[] args) throws IOException{
        String sql = "E:\\word转sql文件\\sql.sql";
        Writer writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(sql), "GB2312"));
        String docx = "E:\\word转sql文件\\333.docx";
        XWPFDocument document = read_file(docx);
        read(document, writer);
    }

    public static void read(XWPFDocument document, Writer w) throws IOException {

        int ind = 0;
        Iterator<IBodyElement> it = document.getBodyElementsIterator();
        StringBuilder result = new StringBuilder();
        String tableName = "";
        String tabComment = "";
        while (it.hasNext()) {
            IBodyElement ibody = it.next();
            if (ibody instanceof XWPFParagraph) { //如果是标题
                XWPFParagraph paragraph = (XWPFParagraph) ibody;
                //run表示相同区域属性相同的字符，结果以‘，’分隔；
                List<XWPFRun> runs =paragraph.getRuns();// paragraph.getRuns();
                StringBuilder sb = new StringBuilder();
                for (int i = 0; i < runs.size(); i++){
                    String oneparaString = runs.get(i).getText(runs.get(i).getTextPosition());
                    System.out.print(oneparaString);
                    sb.append(oneparaString);
                }
                System.out.println();
                String title = sb.toString();
                String[] arr = title.split(" ");
                if (Pattern.matches("\\w+",arr[0])) {
                    tableName = arr[0];
                    tabComment = title.substring(title.indexOf(' '));
                }
            }
            Map<String, Column> cmap = new LinkedHashMap();
            if (ibody instanceof XWPFTable) { //如果是表格
                if (tableName.equals("")) {
                    System.out.println(tabComment);
                    throw new RemoteException("表名称为空");
                }
                XWPFTable table = (XWPFTable) ibody;
                //行
                int rcount = table.getNumberOfRows();
                int nameid = 0, typeid = 0, commentid = 0,keyid=0,isNullid=0,morenzhiId=0;
                for (int i = 0; i < rcount; i++){
                    XWPFTableRow row = table.getRow(i);
                    //列
                    List<XWPFTableCell> cells = row.getTableCells();
                    if (i == 0) {
                        for (int j = 0; j < cells.size(); j++) {
                            if ("字段".equals(cells.get(j).getText()) || "字段名".equals(cells.get(j).getText())) {
                                nameid = j;
                            }
                            if ("数据类型".equals(cells.get(j).getText())) {
                                typeid = j;
                            }
                            if ("名称".equals(cells.get(j).getText()) || "中文名称".equals(cells.get(j).getText())) {
                                commentid = j;
                            }
                            if ("主键".equals(cells.get(j).getText())) {
                                keyid = j;
                            }
                            if ("非空".equals(cells.get(j).getText())) {
                                isNullid = j;
                            }
                            if ("默认值".equals(cells.get(j).getText())) {
                                morenzhiId = j;
                            }
                        }
                    } else {
                        Column c = new Column();
                        XWPFTableCell n = cells.get(nameid);
                        String name = n.getText();
                        if (name == null || name.length()==0|| name.contains(" ")) {
                            throw new RuntimeException(tableName + "表的字段名不合法，" + name);
                        }
                        c.name = name;
                        XWPFTableCell t = cells.get(typeid);
                        String type = t.getText();
                        c.type = type;
                        XWPFTableCell ct = cells.get(commentid);
                        String comment = ct.getText();
                        c.comment = comment;
                        XWPFTableCell key = cells.get(keyid);
                        String PRIMARYKEY = key.getText();
                        c.PRIMARYKEY = PRIMARYKEY;
                        XWPFTableCell isNullCell = cells.get(isNullid);
                        String isNull = isNullCell.getText();
                        c.isNull = isNull;
                        XWPFTableCell morenCell = cells.get(morenzhiId);
                        String morenCellText = morenCell.getText();
                        c.morenzhi = morenCellText;
                        cmap.put(name, c);
                    }
                }
                w.write("\r\n");
                w.write("DROP TABLE IF EXISTS `");
                w.write(tableName);
                w.write("`;\r\n");
                w.write("CREATE TABLE `");
                w.write(tableName);
                w.write("`");
                w.write("\r\n(\r\n");
                String PRIMARYKEYString = "";
                String PRIMARYKEYType = "";
                List<String> PRIMARYKEYStringList = new ArrayList<>();
                int i = 0;
                for (Column c : cmap.values()) {
                    if (i == 0) {
                        //字段名称
                        w.write("  " + c.name + "  ");
                    } else {
                        w.write(",\r\n  " + c.name + "  ");
                    }
                    if (c.type.contains("INT")){
                        PRIMARYKEYType = "AUTO_INCREMENT=0";
                    }
                    //类型
                    w.write(c.type);
                    if (StringUtils.isNotEmpty(c.morenzhi)){
                        w.write(" DEFAULT "+c.morenzhi);
                    }

                    //是否为空
                    if (StringUtils.isNotEmpty(c.isNull) && "√".equals(c.isNull)){
                        w.write(" NOT NULL");
                    }else {
                        w.write(" DEFAULT NULL");
                    }
                    //备注
                    if (StringUtils.isNotEmpty(c.comment)){
                        w.write(" COMMENT '"+c.comment+"'");
                    }
                    //主键
                    if (StringUtils.isNotEmpty(c.PRIMARYKEY) && "√".equals(c.PRIMARYKEY)){
                       // PRIMARYKEYStringList.add("  PRIMARY KEY (`"+c.name+"`) USING BTREE");
                        PRIMARYKEYStringList.add(c.name);
                       // PRIMARYKEYString = " PRIMARY KEY (`"+c.name+"`) USING BTREE,";
                    }
                    System.out.println(c);
                    i++;
                }
                if (PRIMARYKEYStringList.size() > 0){
                    w.write(",");
                }
                w.write("\r\n");
                //w.write(PRIMARYKEYString.replaceFirst(".$", ""));
                w.write( "  PRIMARY KEY ("+PRIMARYKEYStringList.stream().map(String::valueOf).collect(Collectors.joining(",")) + ") USING BTREE");

                w.write("\r\n)");
                w.write("ENGINE=InnoDB "+PRIMARYKEYType+" DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='"+ tabComment.replace(" ","") +"';");
                w.write("\r\n");

                cmap.clear();
                tableName = "";
                ind++;
            }

        }
        w.flush();
        w.close();
    }

    /**
     * 读取文件
     * @param srcPath
     * @return XWPFDocument
     */
    public static XWPFDocument read_file(String srcPath)
    {
        String[] sp = srcPath.split("\\.");
        if ((sp.length > 0) && sp[sp.length - 1].equalsIgnoreCase("docx"))
        {
            try {
                FileInputStream fis = new FileInputStream(srcPath);
                XWPFDocument xdoc = new XWPFDocument(fis);
                XWPFWordExtractor extractor = new XWPFWordExtractor(xdoc);
                return xdoc;
            } catch (IOException e) {
                System.out.println("读取文件出错！");
                e.printStackTrace();
                return null;
            }
        }
        return null;
    }

    static class Column{
        String name;
        String type;
        String comment;
        String PRIMARYKEY;
        String isNull;
        String morenzhi;

        @Override
        public String toString() {
            return "Column{" +
                    "name='" + name + '\'' +
                    ", type='" + type + '\'' +
                    ", PRIMARYKEY='" + PRIMARYKEY + '\'' +
                    ", isNull='" + isNull + '\'' +
                    ", morenzhi='" + morenzhi + '\'' +
                    ", comment='" + comment + '\'' +
                    '}';
        }
    }
}