将导入文件生成数据字典并导入到mysql数据库中

今天将导入文件生成数据字典并导入到mysql数据库中：

首先是将导入的文件生成数据字典：生成字典后再将生成的字典导入到mysql数据库当中

生成数据字典：

public void Ttable( HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException, SQLException {
    request.setCharacterEncoding("utf-8");
    response.setContentType("text/html;charset=UTF-8");
    System.out.println("连接了生成表格");
    /*
    PrintWriter out = response.getWriter();
    String json="{'code': 0,'msg':'','count':100,'data':[{'id':'1','username':'name','sex':'男'}]}";
    return json;

     */
    List<SqlTableBean> list=dao.getTableAll("firtable");
    JSONArray json=new JSONArray();
    for(int i=0;i<list.size();i++){
        JSONObject ob=new JSONObject();
        ob.put("ywname",list.get(i).getYwname());
        ob.put("zwname",list.get(i).getZwname());
        ob.put("danwei",list.get(i).getDanwei());
        ob.put("zdtype",list.get(i).getZdtype());
        ob.put("beizhu",list.get(i).getBeizhu());
        ob.put("ispri",list.get(i).getIspri());
        ob.put("chtime",list.get(i).getChtime());
        json.add(ob);
    }
    JSONObject ob=new JSONObject();
    ob.put("code", 0);
    ob.put("msg", "");
    ob.put("count",1);
    ob.put("data",json);
    PrintWriter out = response.getWriter();
    out.write(ob.toString());
}
在bean层也是mysql数据库中的字段代码：

public class SqlTableBean {
    private String ywname;
    private String zwname;
    private String danwei;
    private String zdtype;
    private String beizhu;
    private String ispri;
    private String crtime;
    private String chtime;
    private String user;

    public String getYwname() {
        return ywname;
    }

    public void setYwname(String ywname) {
        this.ywname = ywname;
    }

    public String getZwname() {
        return zwname;
    }

    public void setZwname(String zwname) {
        this.zwname = zwname;
    }

    public String getDanwei() {
        return danwei;
    }

    public void setDanwei(String danwei) {
        this.danwei = danwei;
    }

    public String getZdtype() {
        return zdtype;
    }

    public void setZdtype(String zdtype) {
        this.zdtype = zdtype;
    }

    public String getBeizhu() {
        return beizhu;
    }

    public void setBeizhu(String beizhu) {
        this.beizhu = beizhu;
    }

    public String getIspri() {
        return ispri;
    }

    public void setIspri(String ispri) {
        this.ispri = ispri;
    }

    public String getCrtime() {
        return crtime;
    }

    public void setCrtime(String crtime) {
        this.crtime = crtime;
    }

    public String getChtime() {
        return chtime;
    }

    public void setChtime(String chtime) {
        this.chtime = chtime;
    }

    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public SqlTableBean(String ywname, String zwname, String danwei, String zdtype, String beizhu, String ispri, String crtime, String chtime, String user) {
        this.ywname = ywname;
        this.zwname = zwname;
        this.danwei = danwei;
        this.zdtype = zdtype;
        this.beizhu = beizhu;
        this.ispri = ispri;
        this.crtime = crtime;
        this.chtime = chtime;
        this.user = user;
    }
}
导入到mysql的代码：

package classes;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class DatabaseUtil {

    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost:3306/words?serverTimezone=UTC";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "123456";

    private static final String SQL = "SELECT * FROM ";// 数据库操作

    static {
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("静态代码块连接失败");
        }
    }

    /**
     * 获取数据库连接
     *
     * @return
     */
    public static Connection getConnection() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (SQLException e) {
            System.out.println("连接失败");
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 关闭数据库连接
     * @param conn
     */
    public static void closeConnection(Connection conn) {
        if(conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取数据库下的所有表名
     */
    public static List<String> getTableNames() {
        List<String> tableNames = new ArrayList<>();
        Connection conn = getConnection();
        ResultSet rs = null;
        try {
            //获取数据库的元数据
            DatabaseMetaData db = conn.getMetaData();
            //从元数据中获取到所有的表名
            rs = db.getTables("dataanalyze", null, null, new String[] { "TABLE" });
            while(rs.next()) {
                tableNames.add(rs.getString(3));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
                closeConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return tableNames;
    }

    /**
     * 获取表中所有字段名称
     * @param tableName 表名
     * @return
     */
    public static List<String> getColumnNames(String tableName) {
        List<String> columnNames = new ArrayList<>();
        //与数据库的连接
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        try {
            pStemt = conn.prepareStatement(tableSql);
            //结果集元数据
            ResultSetMetaData rsmd = pStemt.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnNames.add(rsmd.getColumnName(i + 1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (pStemt != null) {
                try {
                    pStemt.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return columnNames;
    }

    /**
     * 获取表中所有字段类型
     * @param tableName
     * @return
     */
    public static List<String> getColumnTypes(String tableName) {
        List<String> columnTypes = new ArrayList<>();
        //与数据库的连接
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        try {
            pStemt = conn.prepareStatement(tableSql);
            //结果集元数据
            ResultSetMetaData rsmd = pStemt.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnTypes.add(rsmd.getColumnTypeName(i + 1));

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (pStemt != null) {
                try {
                    pStemt.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return columnTypes;
    }
    public List<Integer> getColumnLength(String tableName){
        List<Integer> columnLength = new ArrayList<>();
        //与数据库的连接
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        try {
            pStemt = conn.prepareStatement(tableSql);
            //结果集元数据
            ResultSetMetaData rsmd = pStemt.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnLength.add(Integer.parseInt(rsmd.getColumnTypeName(i + 1)));

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (pStemt != null) {
                try {
                    pStemt.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return columnLength;
    }
    public static List<String> getColumnComments(String tableName) {
        List<String> columnTypes = new ArrayList<>();
        //与数据库的连接
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        List<String> columnComments = new ArrayList<>();//列名注释集合
        ResultSet rs = null;
        try {
            pStemt = conn.prepareStatement(tableSql);
            rs = pStemt.executeQuery("show full columns from " + tableName);
            while (rs.next()) {
                columnComments.add(rs.getString("Comment"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return columnComments;
    }
    public static void main(String[] args) {
        List<String> tableNames = getTableNames();
        System.out.println("tableNames:" + tableNames);
        for (String tableName : tableNames) {
            System.out.println("ColumnNames:" + getColumnNames(tableName));
            System.out.println("ColumnTypes:" + getColumnTypes(tableName));
            System.out.println("ColumnComments:" + getColumnComments(tableName));
        }
    }
}