11月28日总结-生成数据字典dao层

package dao;

import domain.col;
import domain.tables;

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

public class tabledao {
    String FORNAME = "com.mysql.cj.jdbc.Driver";
    String url = "jdbc:mysql:///book?serverTimezone=UTC";
    String user = "root";
    String pwd = "123456";

    public tabledao() {
        try {
            Class.forName(FORNAME);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, pwd);
    }

    public List<tables> tableslist(String sql) throws SQLException {
        List<tables> tables = new ArrayList<>();
        Connection c = getConnection();
        PreparedStatement ps = c.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            tables ur = new tables();
            ur.setColname(rs.getString("colname").trim());
            tables.add(ur);
        }
        ps.close();
        c.close();
        return tables;
    }
    public List<col> collist(String sql) throws SQLException {
        List<col> col1 = new ArrayList<>();
        Connection c = getConnection();
        PreparedStatement ps = c.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            col ur = new col();
            ur.setColumn_name(rs.getString("column_name").trim());
            ur.setColumn_def(rs.getString("column_def").trim());
            ur.setDanwei(rs.getString("danwei").trim());
            ur.setIs_auto(rs.getString("is_auto").trim());
            ur.setIs_nullable(rs.getString("is_nullable"));
            ur.setRemarks(rs.getString("remarks"));
            ur.setSize(rs.getString("column_size"));
            ur.setTable_name(rs.getString("table_name"));
            ur.setType_name(rs.getString("type_name"));
            col1.add(ur);
        }
        ps.close();
        c.close();
        return col1;
    }
    // sql2 select * from tablename
    public List<List<String>> datalist(String sql1,String sql2) throws SQLException {
        List<List<String>> table = new ArrayList<>();
        List<String> col1 = new ArrayList<>();
        List<String> col2 = new ArrayList<>();
        Connection c = getConnection();
        PreparedStatement ps = c.prepareStatement(sql1);
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            col1.add(rs.getString("column_name").trim());
            col2.add(rs.getString("remarks").trim());
        }
        table.add(col2);
        table.add(col1);
        ps.close();
        int lengths=col1.size();
        PreparedStatement ps2 = c.prepareStatement(sql2);
        ResultSet rs2 = ps2.executeQuery();
        while (rs2.next()){
            List<String> colx = new ArrayList<>();
            for(int i=1;i<=lengths;i++) {
                colx.add(rs2.getString(i).trim());
            }
            table.add(colx);
        }
        c.close();
        return table;
    }
    public void text(String sql) throws SQLException {
        String lk = null;
        String b =null;
        String a =null;
        Connection c = getConnection();
        PreparedStatement ps = c.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            lk=rs.getString("ba14");
            a=rs.getString("sid");
            System.out.println(lk);
            lk="河北省"+lk;
            String editsql = "update 2018年生产力促进中心年报2018年生产力促进中心年报数据导 set ba14 = '"  +lk+ "' where sid = '"+a+"'";
            System.out.println(editsql);
            PreparedStatement ps1=c.prepareStatement(editsql);
            ps1.execute();

        }

        ps.close();
        c.close();
    }

    public static void main(String[] args) throws SQLException {
        String A="select*from 2018年生产力促进中心年报2018年生产力促进中心年报数据导";
//        text(A);
        tabledao tabledao = new tabledao();
        tabledao.text(A);

    }

}