根据 动态url 查询数据库中所有的 表 根据表查字段 根据表查数据

动态url查询数据库中所有的表

Controller

/**
     * 根据数据库信息查询所有表
     * @param userName 用户名
     * @param password 密码
     * @param driverName 驱动名称
     * @param databaseUrl 链接url
     * @return
     */
    @GetMapping("queryAllTable")
    public BaseResponse<List<TableDto>> queryAllTable(@RequestParam("userName") String userName,
                                                  @RequestParam("password") String password,
                                                  @RequestParam("driverName") String driverName,
                                                  @RequestParam("databaseType") String databaseType,
                                                  @RequestParam("databaseUrl") String databaseUrl){
        try {
            List<TableDto> allTable = dataConnectionService.queryAllTable(userName,password,databaseType,driverName,databaseUrl);
            return BaseResponse.onSuccess("查询数据库表成功",allTable);
        }catch (Exception e){
            e.printStackTrace();
            return BaseResponse.onFail("查询数据库表异常，错误信息:"+e.getMessage());
        }
    }

Service

/**
     * 根据数据库信息查询所有表
     *
     * @param userName    用户名
     * @param password    密码
     * @param driverName  驱动名称
     * @param databaseUrl 链接url
     * @return
     */
    @Override
    public List<TableDto> queryAllTable(String userName, String password, String databaseType, String driverName, String databaseUrl) {
        SQLHelper helper = new SQLHelper(userName, password, driverName, databaseUrl);
        List<TableDto> list = new ArrayList<>();
        try {
            ResultSet resultSet = getAllTablesSet(databaseType, helper);
            //读取
            while (resultSet.next()) {
                TableDto dto = new TableDto();
                dto.setTableName(resultSet.getString("table_name"));
                dto.setObjDescription(resultSet.getString("obj_description"));
                dto.setTableSize(resultSet.getString("table_size"));
                dto.setTableCatalog(resultSet.getString("table_catalog"));
                dto.setTableSchema(resultSet.getString("table_schema"));
                list.add(dto);
            }
            //关闭
            SQLHelper.close(resultSet, helper.getPs(), helper.getCt());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

private ResultSet getAllTablesSet(String type, SQLHelper helper) {
        ResultSet resultSet = null;
        switch (type.toUpperCase()) {
            case "ORACLE":
                resultSet = helper.executeQuery("select \n" +
                        "    a.table_name,\n" +
                        "    a.tablespace_name as table_catalog,\n" +
                        "    a.tablespace_name as table_schema,\n" +
                        "    a.sample_size as table_size,\n" +
                        "    b.COMMENTS as obj_description\n" +
                        "from user_tables a,user_tab_comments b\n" +
                        "where a.TABLE_NAME=b.TABLE_NAME\n" +
                        "ORDER BY TABLE_NAME");
                break;
            case "POSTGRESQL":
                resultSet = helper.executeQuery("\n" +
                        "SELECT\n" +
                        "\t\"table_name\",\n" +
                        "\ttable_catalog,\n" +
                        "\ttable_schema,\n" +
//                        "\tpg_size_pretty ( pg_relation_size ( \"table_name\" ) ) as table_size,\n" +
                        "pg_size_pretty (pg_total_relation_size(('\"' || table_schema || '\".\"' || table_name || '\"'))) as table_size," +
                        "\tobj_description ( oid, 'pg_class' ) \n" +
                        "FROM\n" +
                        "\tinformation_schema.tables t1,\n" +
                        "\tpg_class t2 \n" +
                        "WHERE\n" +
                        "\ttable_schema = 'public' \n" +
                        "\tAND t1.\"table_name\" = t2.relname;");
                break;
            case "KINGBASE8":
                resultSet = helper.executeQuery("SELECT \n" +
                        "\ttable_name,\n" +
                        "\t'' as table_catalog,\n" +
                        "\t'' as table_schema,\n" +
                        "\t'' as table_size,\n" +
                        "\t'' as obj_description\n" +
                        "from information_schema.TABLES WHERE  \n" +
                        "table_schema='public';");
                break;
            default:
                break;
        }
        return resultSet;
    }

动态url查询表中所有的字段

Controller

/**
     * 根据数据表查询字段信息
     * @param userName 用户名
     * @param password 密码
     * @param driverName 驱动名称
     * @param databaseUrl 链接url
     * @param tableName 数据表名
     * @return
     */
    @GetMapping("queryAllColumn")
    public BaseResponse<List<ColumnDto>> queryAllColumn(@RequestParam("userName") String userName,
                                                      @RequestParam("password") String password,
                                                      @RequestParam("driverName") String driverName,
                                                      @RequestParam("databaseType") String databaseType,
                                                      @RequestParam("databaseUrl") String databaseUrl,
                                                       @RequestParam("tableName") String tableName){
        try {
            List<ColumnDto> allColumn = dataConnectionService.queryAllColumn(userName,password,databaseType,driverName,databaseUrl,tableName);
            return BaseResponse.onSuccess("查询数据库表成功",allColumn);
        }catch (Exception e){
            e.printStackTrace();
            return BaseResponse.onFail("查询数据库表异常，错误信息:"+e.getMessage());
        }
    }

Service

/**
     * 根据数据表查询字段信息
     *
     * @param userName    用户名
     * @param password    密码
     * @param driverName  驱动名称
     * @param databaseUrl 链接url
     * @return
     */
    @Override
    public List<ColumnDto> queryAllColumn(String userName, String password,
                                          String databaseType, String driverName,
                                          String databaseUrl, String tableName) {
        List<ColumnDto> list = new ArrayList<>();
        SQLHelper helper = new SQLHelper(userName, password, driverName, databaseUrl);
        try {
            ResultSet resultSet = getTableColumnSet(databaseType, tableName, helper);
            //读取
            while (resultSet.next()) {
                ColumnDto dto = new ColumnDto();
                dto.setColumnName(resultSet.getString("column_name"));
                dto.setColDescription(resultSet.getString("col_description"));
                dto.setUdtName(resultSet.getString("udt_name"));
                dto.setField_size(resultSet.getString("field_size"));
                dto.setIsPrimary(resultSet.getInt("is_primary"));
                list.add(dto);
            }
            //关闭
            SQLHelper.close(resultSet, helper.getPs(), helper.getCt());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }


private ResultSet getTableColumnSet(String type, String tableName, SQLHelper helper) {
        ResultSet resultSet = null;
        switch (type.toUpperCase()) {
            case "ORACLE":
                resultSet = helper.executeQuery("SELECT \n" +
                        "\n" +
                        "    lower(b.column_name) column_name \n" +
                        "    ,a.comments col_description \n" +
                        "    ,b.data_type udt_name \n" +
                        "    ,b.data_length field_size \n" +
                        "    ,b.data_precision is_primary \n" +
                        "    \n" +
                        "FROM all_col_comments a \n" +
                        "    ,all_tab_columns b \n" +
                        "WHERE a.table_name = b.table_name \n" +
                        "and a.OWNER = b.OWNER \n" +
                        "and a.Column_name = b.Column_name \n" +
                        " and a.table_name = '" + tableName + "'");
                break;
            case "POSTGRESQL":
                resultSet = helper.executeQuery("\n" +
                        "SELECT\n" +
                        "base.\"column_name\",\n" +
                        "col_description ( t1.oid, t2.attnum ),\n" +
                        "base.udt_name,\n" +
                        "COALESCE(character_maximum_length, numeric_precision, datetime_precision) as field_size,\n" +
                        "(CASE\n" +
                        "\tWHEN ( SELECT t2.attnum = ANY ( conkey ) FROM pg_constraint WHERE conrelid = t1.oid AND contype = 'p' ) = 't' \n" +
                        "\tTHEN 1 ELSE 0 \n" +
                        "END ) as  is_primary\n" +
                        "FROM\n" +
                        "information_schema.COLUMNS base,\n" +
                        "pg_class t1,\n" +
                        "pg_attribute t2 \n" +
                        "WHERE\n" +
                        "base.\"table_name\" = '" + tableName + "' \n" +
                        "AND t1.relname = base.\"table_name\" \n" +
                        "AND t2.attname = base.\"column_name\" \n" +
                        "AND t1.oid = t2.attrelid \n" +
                        "AND t2.attnum > 0;\n");
                break;
            case "KINGBASE8":
                resultSet = helper.executeQuery("SELECT \n" +
                        "\n" +
                        "    lower(b.column_name) column_name \n" +
                        "    ,a.comments col_description \n" +
                        "    ,b.data_type udt_name \n" +
                        "    ,b.data_length field_size \n" +
                        "    ,b.data_precision is_primary \n" +
                        "    \n" +
                        "FROM all_col_comments a \n" +
                        "    ,all_tab_columns b \n" +
                        "WHERE a.table_name = b.table_name \n" +
                        "and a.OWNER = b.OWNER \n" +
                        "and a.Column_name = b.Column_name \n" +
                        " and UPPER(a.table_name) = '" + tableName.toUpperCase() + "'");
                break;
            default:
                break;
        }
        return resultSet;
    }

根据动态url分页查询表中信息

Controller

/**
     * 根据数据表分页查询信息
     * @param userName 用户名
     * @param password 密码
     * @param driverName 驱动名称
     * @param databaseUrl 链接url
     * @param tableName 数据表名
     * @param page 当前页
     * @param size 每页数量
     * @return
     */
    @GetMapping("queryDataPage")
    public BaseResponse<Object> queryDataPage(@RequestParam("userName") String userName,
                                                        @RequestParam("password") String password,
                                                        @RequestParam("driverName") String driverName,
                                                        @RequestParam("databaseType") String databaseType,
                                                        @RequestParam("databaseUrl") String databaseUrl,
                                                        @RequestParam("tableName") String tableName,
                                                        @RequestParam("page") Integer page,
                                                        @RequestParam("size") Integer size){
        try {
            Object json = dataConnectionService.queryDataPage(userName,password,databaseType,driverName,databaseUrl,tableName,page,size);
            return BaseResponse.onSuccess("查询数据库表成功",json);
        }catch (Exception e){
            e.printStackTrace();
            return BaseResponse.onFail("查询数据库表异常，错误信息:"+e.getMessage());
        }
    }

Service

/**
     * 根据数据表分页查询信息
     *
     * @param userName    用户名
     * @param password    密码
     * @param driverName  驱动名称
     * @param databaseUrl 链接url
     * @param tableName   数据表名
     * @param page        当前页
     * @param size        每页数量
     * @return
     */
    @Override
    public Object queryDataPage(String userName, String password, String databaseType, String driverName, String databaseUrl, String tableName, Integer page, Integer size) throws SQLException {
        if (StringUtils.isEmpty(page) || page < 0) {
            page = 0;
        }
        if (StringUtils.isEmpty(size) || size <= 0) {
            size = 10;
        }
        page = page * size;
        ResultSet resultSet = null;
        SQLHelper helper = new SQLHelper(userName, password, driverName, databaseUrl);
        ResultSet columnSet = getTableColumnSet(databaseType, tableName, helper);
        List<String> columnList = new ArrayList<>();

        //读取
        while (columnSet.next()) {
            columnList.add(columnSet.getString("column_name"));
        }
        resultSet = getDataPage(databaseType, tableName, helper, page, size);
        //读取
        List<Map<String, String>> resultMapList = new ArrayList<>();
        while (resultSet.next()) {
            Map map = new HashMap();
            for (String column : columnList) {
                map.put(column,resultSet.getString(column));
            }
            resultMapList.add(map);
        }
        //关闭
        SQLHelper.close(resultSet, helper.getPs(), helper.getCt());
        return resultMapList;
    }


private ResultSet getDataPage(String type, String tableName, SQLHelper helper, Integer page, Integer size) {
        ResultSet resultSet = null;
        switch (type.toUpperCase()) {
            case "ORACLE":
                Integer num = page +size;
                resultSet = helper.executeQuery("SELECT * FROM "+tableName+" WHERE ROWNUM < "+num+" MINUS SELECT * FROM "+tableName+" WHERE ROWNUM < "+size+"");
                break;
            case "POSTGRESQL":
                resultSet = helper.executeQuery("SELECT * FROM "+tableName+ " LIMIT "+ page + " OFFSET "+size);
                break;
            case "KINGBASE8":
                Integer numKINGBASE8 = page +size;
                resultSet = helper.executeQuery("SELECT * FROM "+tableName+" WHERE ROWNUM < "+numKINGBASE8+" MINUS SELECT * FROM "+tableName+" WHERE ROWNUM < "+size+"");
                break;
            default:
                break;
        }
        return resultSet;
    }

SQLHelper.java

package diit.resourcemanage.utils;

import lombok.extern.slf4j.Slf4j;

import java.sql.*;

@Slf4j
public class SQLHelper {
   //定义三个变量
   private Connection ct=null;
   private PreparedStatement ps=null;
   private ResultSet rs=null;

   //连接数据库的用户名，密码，url，驱动
   //说明：在实际开发中，我们往往把这些变量写到一个外部文件中
   //当程序启动时，我们读入这些配置信息。java.util.Properites
   private String username;
   private String password;
   private String driver;
   private String url;

   //使用静态块加载驱动(驱动只需要加载一次)
   public SQLHelper(String username, String password, String driver, String url){
           //获取dbinfo.properties文件内信息
           this.username=username;
           this.password=password;
           this.driver=driver;
           this.url=url;
           try{
               //获得驱动
               Class.forName(driver);
           }catch (Exception e){
               e.printStackTrace();
               log.error("加载驱动和链接信息出错：{}",e.getMessage());
           }

   }

   //统一的curd操作
   public void executeUpdate(String sql,String[] parameters){
       try {
           ct=DriverManager.getConnection(url,username,password);
           ps=ct.prepareStatement(sql);
           if(parameters!=null){
               for(int i=0;i<parameters.length;i++){
                   ps.setString(i+1, parameters[i]);
               }
           }
           //执行
           ps.executeUpdate();

       } catch (Exception e) {
           e.printStackTrace();
           throw new RuntimeException(e.getMessage());
       }finally{
           close(rs,ps,ct);
       }
   }

   //写一个方法，完成查询任务
   //sql表示要执行的sql语句
   //sql select * from emp where ename=?
   public ResultSet executeQuery(String sql,String ... parameters){
       try {
           //根据实际情况我们对sql语句?赋值
           //得到连接
           ct=DriverManager.getConnection(url,username,password);
           //创建ps对象，得到sql语句对象
           ps=ct.prepareStatement(sql);
           //如果parameters不为null，才赋值
           if(parameters!=null){
               for(int i=0;i<parameters.length;i++){
                   ps.setString(i+1, parameters[i]);
               }
           }
           rs=ps.executeQuery();
       } catch (SQLException e) {
           e.printStackTrace();
           log.error("执行查询出错：{}",e.getMessage());
           //抛出运行异常
           throw new RuntimeException(e.getMessage());
       } finally{
//			close(rs,ps,ct);
       }
       return rs;
   }

   //把关闭资源写成函数
   public static void close(ResultSet rs,Statement ps,Connection ct){
       //关闭资源
       if(rs!=null){
           try {
               rs.close();
           } catch (SQLException e) {
               e.printStackTrace();
           }
           rs=null;
       }
       if(ps!=null){
           try {
               ps.close();
           } catch (SQLException e) {
               e.printStackTrace();
           }
           ps=null;
       }
       if(ct!=null){
           try {
               ct.close();
           } catch (SQLException e) {
               e.printStackTrace();
           }
           ct=null;
       }
   }

   public Connection getCt() {
       return ct;
   }

   public PreparedStatement getPs() {
       return ps;
   }
}