每日日报2021.10.19

完成内容：

1.编写大作业第一阶段

dao层

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class Dao {
    //login Interface validation
    public String login(int Id, String Password) throws SQLException {
        String sql = "select * from user ";
        Connection conn = Dbutil.getConnection();
        Statement st = null;
        List<UserBean> list = new ArrayList<>();
        UserBean bean = null;
        ResultSet rs = null;
        try {
            st = conn.createStatement();
            st.executeQuery(sql);
            rs = st.executeQuery(sql);
            while (rs.next()) {
                int id = rs.getInt("id");
                String pass = rs.getString("password");
                String type = rs.getString("type");
                bean = new UserBean(id, pass, type);
                list.add(bean);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Dbutil.close(st, conn);
        }
        for(int i=0;i<list.size();i++){
            if(list.get(i).getId()==Id&&list.get(i).getPassword().equals(Password)){
                return list.get(i).getType();
            }
        }
        return "登录错误";
    }
    //creataTab Generate data dictionary
    public void CreataTab(String TableName) throws SQLException {
        String sql="create table "+TableName+" (ywname varchar(40),zwname varchar(40),danwei varchar(10),zdtype varchar(10),beizhu varchar(50),ispri varchar(2),crtime varchar(20),chtime varchar(20),user varchar(20))";
        Connection conn= Dbutil.getConnection();
        PreparedStatement pt = conn.prepareStatement(sql);
        Statement st=null;
        ResultSet rs=null;
        try
        {
            st=conn.createStatement();
            st.executeUpdate(sql);
        }catch(Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            Dbutil.close(rs, st, conn);
        }
    }
    //Data dictionary details
    public void FirInputData(List<List<Object>> rowlist,String TableName) throws SQLException, InterruptedException {
        int len=rowlist.get(0).size();
        Utils utils=new Utils();
        String sql="";
        Connection conn=null;
        conn= Dbutil.getConnection();
        Statement st=null;
        ResultSet rs=null;
        String danwei="";
        String type="";
        String ispri="是";
        SimpleDateFormat formatter= new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        Date date = new Date(System.currentTimeMillis());
        String crtime=formatter.format(date);
        for(int i=0;i<len;i++){
            if(rowlist.get(1).get(i).equals("")){
                danwei="无";
            }
            else danwei=(String)rowlist.get(1).get(i);
            if(utils.isInteger((String)rowlist.get(3).get(i))){
                type="Int";
            }
            else if(utils.isDouble((String)rowlist.get(3).get(i))){
                type="float";
            }
            else if(utils.isValidDate((String)rowlist.get(3).get(i))){
                type="datatime";
            }
            else type="varchar";
            sql="insert into "+TableName+" values ('"+rowlist.get(2).get(i)+"','"+rowlist.get(0).get(i)+"','"+danwei+"','"+type+"','"+"无"+"','"+ispri+"','"+crtime+"','"+crtime+"','"+"user"+"')";


            try
            {
                st=conn.createStatement();
                st.executeUpdate(sql);
            }catch(Exception e)
            {
                e.printStackTrace();
            }
            finally
            {



            }
            if(ispri.equals("是"))ispri="否";
        }
        Dbutil.close(rs, st, conn);


    }
    //inquire
    public List<SqlTableBean> getTableAll(String TableName){
        String sql="select * from  "+TableName+"";
        Connection conn = Dbutil.getConnection();
        Statement st=null;
        List<SqlTableBean> list=new ArrayList<>();
        ResultSet rs=null;
        SqlTableBean bean=null;
        try {
            st=conn.createStatement();
            st.executeQuery(sql);
            rs=st.executeQuery(sql);
            while(rs.next()) {
               String ywname=rs.getString("ywname");
               String zwname=rs.getString("zwname");
               String danwei=rs.getString("danwei");
               String zdtype=rs.getString("zdtype");
               String beizhu=rs.getString("beizhu");
               String ispri=rs.getString("ispri");
               String crtime=rs.getString("crtime");
               String chtime=rs.getString("chtime");
               String user=rs.getString("user");
               bean =new SqlTableBean(ywname,zwname,danwei,zdtype,beizhu,ispri,crtime,chtime,user);
               list.add(bean);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally{
            Dbutil.close(st, conn);
        }
        return list;
    }
    //time
    public void FirInsertToHis(String TableName,String user) throws SQLException {
        SimpleDateFormat formatter= new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        Date date = new Date(System.currentTimeMillis());
        String crtime=formatter.format(date);
        String sql="insert into hisdata (tabname,crtime,user) values('"+TableName+"','"+crtime+"','"+user+"')";
        Connection conn= Dbutil.getConnection();
        PreparedStatement pt = conn.prepareStatement(sql);
        Statement st=null;
        ResultSet rs=null;
        try
        {
            st=conn.createStatement();
            st.executeUpdate(sql);
        }catch(Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            Dbutil.close(rs, st, conn);
        }
    }
    //table hisdata
    public List<HisTabBean> HisTab(){
        String sql="select * from hisdata";
        Connection conn = Dbutil.getConnection();
        Statement st=null;
        List<HisTabBean> list=new ArrayList<>();
        ResultSet rs=null;
        HisTabBean bean=null;
        try {
            st=conn.createStatement();
            st.executeQuery(sql);
            rs=st.executeQuery(sql);
            while(rs.next()) {
                int id=rs.getInt("tabid");
                String tabname=rs.getString("tabname");
                String crtime=rs.getString("crtime");
                int user=rs.getInt("user");
                bean =new HisTabBean(id,tabname,crtime,user);
                list.add(bean);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally{
            Dbutil.close(st, conn);
        }
        return list;
    }
    //Change PopWindow Beizhu from table
    public void ChangePopWindowBeizhu(String TabName,String ywname,String Value){
        String sql="update "+TabName+" set beizhu='"+Value+"' where ywname='"+ywname+"'";
        Connection conn = Dbutil.getConnection();
        Statement st=null;
        try {
            st=conn.createStatement();
            st.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally{
            Dbutil.close(st, conn);
        }
        SimpleDateFormat formatter= new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        Date date = new Date(System.currentTimeMillis());
        String chtime=formatter.format(date);
        sql="update "+TabName+" set chtime='"+chtime+"' where ywname='"+ywname+"'";
        conn = Dbutil.getConnection();
        st=null;
        try {
            st=conn.createStatement();
            st.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally{
            Dbutil.close(st, conn);
        }
    }
    //Delete OnHistory
    public void DeleteOnHistory(String TabId,String Tabname){
        String sql="drop table "+Tabname;
        Connection conn = Dbutil.getConnection();
        Statement st=null;
        try {
            st=conn.createStatement();
            st.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally{
            Dbutil.close(st, conn);
        }
        sql="delete from hisdata where tabid ="+TabId;
        conn = Dbutil.getConnection();
        st=null;
        try {
            st=conn.createStatement();
            st.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally{
            Dbutil.close(st, conn);
        }
    }
    //Change TabName
    public void ChangeTabName(String TabName,String NewName){
        String sql="alter table "+TabName+" rename to "+NewName;
        Connection conn = Dbutil.getConnection();
        Statement st=null;
        try {
            st=conn.createStatement();
            st.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally{
            Dbutil.close(st, conn);
        }
        sql="update hisdata set tabname = '"+NewName+"' where tabname ='"+TabName+"'";
        conn = Dbutil.getConnection();
        st=null;
        try {
            st=conn.createStatement();
            st.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally{
            Dbutil.close(st, conn);
        }
    }
    //Out Data
    public void OutData(String TName){
        SimpleDateFormat formatter= new SimpleDateFormat("yyyyMMddHHmmss");
        Date date = new Date(System.currentTimeMillis());
        String OutTime=formatter.format(date);
        String OutName=OutTime;
        String sql="select *  into outfile 'D:/frist/"+OutName+".csv' fields terminated by ',' lines terminated by '\r\n' from(select '英文名称','中文名称','单位','字段类型','备注','主键' union select ywname,zwname,danwei,zdtype,beizhu,ispri from "+TName+")b";
        Connection conn = Dbutil.getConnection();
        Statement st=null;
        PreparedStatement ps = null;
        ResultSet rs=null;
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
        } catch(SQLException se) {
            se.printStackTrace();
        }catch(Exception e) {
            e.printStackTrace();
        }finally {
            try {
                if(rs!=null)
                    rs.close();
                if(ps!=null)
                    ps.close();
                if(conn!=null)
                    conn.close();
            }catch(SQLException se) {
                se.printStackTrace();
            }
        }
    }
    //Delete One ZiDuan
    public void DeleteOneZiDuan(String TName,String YWName){
        String sql="delete from "+TName+" where ywname = '"+YWName+"'";
        Connection conn = Dbutil.getConnection();
        Statement st=null;
        try {
            st=conn.createStatement();
            st.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally{
            Dbutil.close(st, conn);
        }
    }
}
util

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

//connect or close mysql database immobilization
public class DatabaseUtil {

    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost:3306/jichang?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

    **/
    //connect
    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
     */
    //close
    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));
        }
    }
}

遇到问题：

 无

目标：继续完成任务