动态数据源查询字段与数据详情

动态数据源查询字段详情

Controller

/**
 * 查询数据库表字段信息
 * @param resourceId
 * @return
 */
@GetMapping("getTableColumnDetails")
public BaseResponse<Object> getTableColumnDetails(String resourceId){
    try {
        List<Map<String,Object>> result = dataTablesService.getTableColumnDetails(resourceId);
        return BaseResponse.onSuccess("数据库表字段查询成功",result);
    }catch (Exception e){
        e.printStackTrace();
        return BaseResponse.onFail("数据库表字段查询失败，错误信息:"+e.getMessage());
    }
}

Service

/**
     * 查询数据库表字段详细信息
     * @param resourceId
     * @return
     */
@Override
public List<Map<String,Object>> getTableColumnDetails(String resourceId){//idealog
    DBHelper dbHelper = null;
        List<Map<String,Object>> columns = new ArrayList<>();
        try {
            //获取数据库连接
            dbHelper = dbUtils.getDbHelper(4,"192.9.30.225:5432/postgres",
                    "postgres","postgres");
            String sql = "";
            List<Object> params = new ArrayList<>();
            params.add("t_id_access_log");
            String bdType = "POSTGRESQL";
            if (bdType.equals("POSTGRESQL")) {
                sql = "SELECT " +
                        "cols.column_name , " +
                        "cols.data_type, " +
                        "cols.character_maximum_length as data_length, " +
                        "cols.numeric_precision, " +
                        "cols.numeric_scale, " +
                        "CASE WHEN pk.column_name IS NOT NULL THEN 'YES' ELSE 'NO' END " +
                        "AS is_primary_key, " +
                        "cols.is_nullable, (SELECT pgd.description FROM pg_catalog.pg_statio_all_tables as st INNER JOIN pg_catalog.pg_description pgd ON (pgd.objoid=st.relid) WHERE st.schemaname=cols.table_schema AND st.relname=cols.table_name AND pgd.objsubid=cols.ordinal_position) " +
                        "AS column_comment FROM information_schema.columns cols LEFT JOIN (SELECT ku.table_schema, ku.table_name, ku.column_name FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS ku ON tc.constraint_name = ku.constraint_name WHERE tc.constraint_type = 'PRIMARY KEY') pk ON cols.table_schema = pk.table_schema AND cols.table_name = pk.table_name AND cols.column_name = pk.column_name " +
                        "WHERE cols.table_name = ? ";
            } else {
                sql = "SELECT " +
                        "cols.column_name," +
                        "cols.data_type," +
                        "cols.data_length," +
                        "cols.data_precision as numeric_precision," +
                        "cols.data_scale  as numeric_scale," +
                        "CASE WHEN pk.column_name IS NOT NULL THEN 'YES' ELSE 'NO' END " +
                        "AS is_primary_key," +
                        "CASE WHEN cols.nullable = 'Y' THEN 'YES' ELSE 'NO' END " +
                        "AS is_nullable," +
                        "com.comments as column_comment " +
                        "FROM all_tab_columns cols LEFT JOIN ( SELECT cols.table_name, cols.column_name FROM all_constraints cons JOIN all_cons_columns cols ON cons.constraint_name = cols.constraint_name WHERE cons.constraint_type = 'P') pk ON cols.table_name = pk.table_name AND cols.column_name = pk.column_name LEFT JOIN all_col_comments com ON cols.table_name = com.table_name AND cols.column_name = com.column_name " +
                        "WHERE cols.table_name = UPPER( ? )";
            }

            columns = dbHelper.queryList(sql, params.toArray());
            columns = toUpperKey(columns);
        }catch(Exception ex){
            ex.printStackTrace();
        }finally {
            dbHelper.closeResource();
        }
        return columns;
}

/**
     * @Author AlphaJunS
     * @Date 21:51 2020/11/11
     * @Description 将map中的所有key转化为大写
     * @param columns
     * @return java.util.Map<java.lang.String,java.lang.String>
     */
    public static List<Map<String,Object>> toUpperKey(List<Map<String,Object>> columns) {
        List<Map<String,Object>> resultListMap = new ArrayList<Map<String,Object>>();
        for (int i = 0; i < columns.size(); i++) {
            Map<String, Object> resultMap = new HashMap<>();
            Map<String, Object> map = columns.get(i);
            Set<String> sets = map.keySet();
            for (String key : sets) {
                resultMap.put(key.toUpperCase(), map.get(key));
            }
            resultListMap.add(resultMap);
        }
        return resultListMap;
    }

DBUtils

package diit.resourcemanage.utils.db;

import diit.resourcemanage.DruidConfig;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

@Component
public class DBUtils {
    @Autowired
    DruidConfig druidConfig;
    /**
     * 连接数据库
     * @param dbtype
     * @param url
     * @param username
     * @param password
     * @return
     */
    public DBHelper getDbHelper(int dbtype, String url, String username, String password){
        DBHelperType dbHelperType = DBHelperType.getDbType(dbtype);
        if(dbHelperType==null){
            return null;
        }
        //连接数据源
        DBHelper db = DBHelperFactory.getDBHelper(dbHelperType);
        try {
            db.createConnection(druidConfig,url,username,password);
            return db;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}

DBHelperType

package diit.resourcemanage.utils.db;

public enum DBHelperType{
    ORACLE(2,"ORACLE"),
    ORACLESDE(3,"ORACLESDE"),
    POSTGRESQL(4,"POSTGRESQL"),
    POSTGRESQLSDE(5,"POSTGRESQLSDE"),
    POSTGIS(9,"POSTGIS");

    int code;
    String name;

    /**
     * 构造方法
     * @param code
     * @param name
     */
    DBHelperType(int code, String name) {
        this.code = code;
        this.name = name;
    }

    public static DBHelperType getDbType(int code) {
        for (DBHelperType dbHelperType : DBHelperType.values()) {
            if (code == dbHelperType.getCode()) {
                return dbHelperType;
            }
        }
        return null;
    }
    public static boolean contains(String _name) {
        DBHelperType[] season = values();
        for (DBHelperType s : season) {
            if (s.name().equals(_name)) {
                return true;
            }
        }
        return false;
    }

    public int getCode() {
        return code;
    }

    public void setCode(int code) {
        this.code = code;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

DBHelperFactory

package diit.resourcemanage.utils.db;

import java.util.HashMap;
import java.util.Map;

/**
 * 数据库帮助类工厂
 */
public class DBHelperFactory {

    private static Map<String, DBHelper> dbHelperMap = new HashMap<>();

    static {
        dbHelperMap.put("ORACLE", new OracleDBHelper());
        dbHelperMap.put("ORACLESDE", new OracleDBHelper());
        dbHelperMap.put("POSTGRESQL", new PostgreSQLDBHelper());
        dbHelperMap.put("POSTGRESQLSDE", new PostgreSQLDBHelper());
        dbHelperMap.put("POSTGIS", new PostgreSQLDBHelper());

    }
    /**
     * 获取数据库帮助类
     * @param dbHelperType 数据库类型枚举
     * @return DBHelper
     */
    public static DBHelper getDBHelper(DBHelperType dbHelperType){
        if(dbHelperType==null){
            return null;
        }
       return dbHelperMap.get(dbHelperType.getName());
    }
}

DBHelper

package diit.resourcemanage.utils.db;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public interface DBHelper {
    /**
     * 创建数据库连接
     * @param url
     * @param username
     * @param password
     */
     void createConnection(BaseDruidConfig druidConfig, String url, String username, String password) throws SQLException;

    /**
     * 获取数据库连接
     * @return
     */
     Connection getConnection();

    /**
     * 关闭数据库连接
     */
    void closeConnection();

    /**
     * 保存操作
     * @param sql
     * @param params
     * @return
     */
    int save(String sql, Object... params)throws SQLException;

    /**
     * 保存操作，不关闭连接
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    int saveNotClose(String sql, Object... params)throws SQLException;
    /**
     * 执行语句
     * @param sql
     * @param params
     * @return 影响的行数
     * @throws SQLException
     */
    int exec(String sql, Object... params)throws SQLException;
    /**
     * 更新操作
     * @param sql
     * @param params
     * @return
     */
    int update(String sql, Object... params)throws SQLException;

    /**
     * 删除操作
     * @param sql
     * @param params
     * @return
     */
    int delete(String sql, Object... params)throws SQLException;

    /**
     * 查询一条记录
     * @param sql
     * @param params
     * @return
     */
    Map<String, Object> queryOne(String sql, Object... params)throws SQLException;

    /**
     * 查询多条记录
     * @param sql
     * @param params
     * @return
     */
    List<Map<String, Object>> queryList(String sql, Object... params)throws SQLException;

    int queryCount(String sql);

    /**
     * 关闭资源
     */
    void closeResource();
}

OracleDBHelper

package diit.resourcemanage.utils.db;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class OracleDBHelper implements DBHelper {
    private static final String dirverClassName="oracle.jdbc.driver.OracleDriver";
    private static final String validationQuery="select 'x' from dual";
    private Connection connection=null;
    private PreparedStatement preparedStatement = null;
    private CallableStatement callableStatement = null;
    private ResultSet resultSet = null;
    private DataSource dataSource;
    private static final String urlPrefix = "jdbc:oracle:thin:@";

    BaseDruidConfig druidConfig;

    public OracleDBHelper(){}

    @Override
    public void createConnection(BaseDruidConfig druidConfig, String url, String username, String password) throws SQLException {
        this.druidConfig = druidConfig;
            url = urlPrefix+url;
            this.dataSource=druidConfig.getDataSource(dirverClassName,validationQuery,url,username,password);
            this.connection=this.dataSource.getConnection();
    }

    @Override
    public Connection getConnection() {
        if (connection == null) {
            try {
                this.connection=this.dataSource.getConnection();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return connection;
    }

    @Override
    public void closeConnection() {
        try {
            if (resultSet != null) {
                resultSet.close();
            }

            if (preparedStatement != null) {
                preparedStatement.close();
            }

            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    /**
     * 设置参数
     */
    private void initParams(PreparedStatement pstm, Object... params) throws SQLException {
        if(params!=null){
            for (int i = 0; i < params.length; i++) {
                try {
                    pstm.setObject(i + 1, params[i]);
                } catch (SQLException e) {
                    throw e;
                }
            }
        }
    }
    private int common(String sql, Object... params) throws SQLException {
        int res = 0;
        try {
            preparedStatement = connection.prepareStatement(sql);
            initParams(preparedStatement, params);
            res = preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeResource();
        }
        return res;
    }

    private int commonAlways(String sql, Object... params) throws SQLException {
        preparedStatement=connection.prepareStatement(sql);
        initParams(preparedStatement,params);
        int res = preparedStatement.executeUpdate();
        return res;
    }
    @Override
    public int saveNotClose(String sql, Object... params)throws SQLException {
        return commonAlways(sql,params);
    }
    @Override
    public int save(String sql, Object... params)throws SQLException {
           return common(sql,params);
    }
    @Override
    public int exec(String sql, Object... params)throws SQLException {
        return common(sql,params);
    }

    @Override
    public int update(String sql, Object... params)throws SQLException {
            return common(sql,params);
    }

    @Override
    public int delete(String sql, Object... params)throws SQLException {
            return common(sql,params);
    }

    @Override
    public Map<String, Object> queryOne(String sql, Object... params)throws SQLException {
            Map<String, Object> map = new HashMap<>();
            preparedStatement = getConnection().prepareStatement(sql);
            initParams(preparedStatement, params);
            resultSet = preparedStatement.executeQuery();  //返回单笔查询结果
            ResultSetMetaData metaData = resultSet.getMetaData();  //获取结果集对象的列的属性
            int columnCount = metaData.getColumnCount();   //获取列的个数
            while (resultSet.next()) {
                for (int i = 0; i < columnCount; i++) {
                    String columnName = metaData.getColumnName(i + 1);
                    Object columnValue = resultSet.getObject(columnName);
                    if (columnValue == null) {
                        columnValue = "";
                    }
                    map.put(columnName, columnValue);
                }
            }
//        closeResource();
        return map;
    }

    @Override
    public List<Map<String, Object>> queryList(String sql, Object... params) {
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            preparedStatement = getConnection().prepareStatement(sql);
            initParams(preparedStatement, params);
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<>();
                for (int i = 0; i < columnCount; i++) {
                    String columnName = metaData.getColumnName(i + 1);
                    Object columnValue = resultSet.getObject(columnName);
                    if (columnValue == null) {
                        columnValue = "";
                    }
                    map.put(columnName, columnValue);
                }
                list.add(map);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            //closeResource();
        }
        return list;
    }
    @Override
    public int queryCount(String sql){
        int count = 0;
        try {
            preparedStatement = getConnection().prepareStatement(sql);
            ResultSet rs=preparedStatement.executeQuery();

            while(rs.next())
            {
                //打印的就是总记录数。把检索结果看成只有一条记录一个字段的表
                count = rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return count;
    }
    // 提供关闭资源的方法【connection是归还到连接池】
    @Override
    public  void closeResource() {
        // 关闭结果集对象
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }

        // 关闭PreparedStatement对象
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }

        // 关闭CallableStatement 对象
        if (callableStatement != null) {
            try {
                callableStatement.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }

        // 关闭Connection 对象
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
    }
}

PostgreSQLDBHelper

package diit.resourcemanage.utils.db;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class PostgreSQLDBHelper implements DBHelper {
    private String dirverClassName="org.postgresql.Driver";
    private String validationQuery="select 'x'";
    private Connection connection=null;
    private  PreparedStatement preparedStatement = null;
    private CallableStatement callableStatement = null;
    private  ResultSet resultSet = null;
    private DataSource dataSource;
    private static final String urlPrefix = "jdbc:postgresql://";

    BaseDruidConfig druidConfig;

    public PostgreSQLDBHelper(){
    }
    @Override
    public void createConnection(BaseDruidConfig druidConfig, String url, String username, String password) throws SQLException {
        this.druidConfig = druidConfig;

            url=urlPrefix+url;
            this.dataSource=druidConfig.getDataSource(dirverClassName,validationQuery,url,username,password);
            this.connection=this.dataSource.getConnection();

    }

    @Override
    public Connection getConnection() {
        try {
            if(this.connection==null){
                this.connection=this.dataSource.getConnection();
            }
            if(connection.isClosed()){
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return this.connection;
    }

    @Override
    public void closeConnection() {
        try {
            if (resultSet != null) {
                resultSet.close();
            }

            if (preparedStatement != null) {
                preparedStatement.close();
            }

            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    /**
     * 设置参数
     */
    private void initParams(PreparedStatement pstm, Object... params) {
        if(params!=null){
            for (int i = 0; i < params.length; i++) {
                try {
                    pstm.setObject(i + 1, params[i]);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }
    private int common(String sql, Object... params) throws SQLException {
        int res = 0;
        try {
            preparedStatement = connection.prepareStatement(sql);
            initParams(preparedStatement, params);
            res = preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeResource();
        }
        return res;
    }
    private int commonAlways(String sql, Object... params) throws SQLException {
        preparedStatement=connection.prepareStatement(sql);
        initParams(preparedStatement,params);
        int res = preparedStatement.executeUpdate();
        return res;
    }
    @Override
    public int saveNotClose(String sql, Object... params)throws SQLException {
        return commonAlways(sql,params);
    }
    @Override
    public int save(String sql, Object... params)throws SQLException {
        try {
            return common(sql,params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
    @Override
    public int exec(String sql, Object... params)throws SQLException {
        try {
            return common(sql,params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int update(String sql, Object... params)throws SQLException {
        try {
            return common(sql,params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int delete(String sql, Object... params)throws SQLException {
        try {
            return common(sql,params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public Map<String, Object> queryOne(String sql, Object... params)throws SQLException {
        Map<String, Object> map = new HashMap<>();
        try {
            preparedStatement = getConnection().prepareStatement(sql);
            initParams(preparedStatement, params);
            resultSet = preparedStatement.executeQuery();  //返回单笔查询结果
            ResultSetMetaData metaData = resultSet.getMetaData();  //获取结果集对象的列的属性
            int columnCount = metaData.getColumnCount();   //获取列的个数
            while (resultSet.next()) {
                for (int i = 0; i < columnCount; i++) {
                    String columnName = metaData.getColumnName(i + 1);
                    Object columnValue = resultSet.getObject(columnName);
                    if (columnValue == null) {
                        columnValue = "";
                    }
                    map.put(columnName, columnValue);
                }
            }
//            closeResource();
        }catch (SQLException e){
            e.printStackTrace();
            return null;
        }
        return map;
    }

    @Override
    public List<Map<String, Object>> queryList(String sql, Object... params){
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            preparedStatement = getConnection().prepareStatement(sql);
            initParams(preparedStatement, params);
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<>();
                for (int i = 0; i < columnCount; i++) {
                    String columnName = metaData.getColumnName(i + 1);
                    Object columnValue = resultSet.getObject(columnName);
                    if (columnValue == null) {
                        columnValue = "";
                    }
                    map.put(columnName, columnValue);
                }
                list.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
//            closeResource();
        }
        return list;
    }

    @Override
    public int queryCount(String sql) {
        return 0;
    }

    @Override
    public  void closeResource() {
        // 关闭结果集对象
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }

        // 关闭PreparedStatement对象
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }

        // 关闭CallableStatement 对象
        if (callableStatement != null) {
            try {
                callableStatement.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }

        // 关闭Connection 对象
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
    }
}

动态数据源查询数据表数据

Controller

/**
     * 数据库表记录查询
     * @param resourceId
     * @param page
     * @param limit
     * @return
     */
    @GetMapping("getTableInfos")
    public BaseResponse<Object> getTableInfos(String resourceId,int page,int limit){
        try {
            Map<String, Object> result = dataTablesService.getTableInfos(resourceId,page,limit);
            List<HashMap<String,Object>> resList = (List<HashMap<String, Object>>) result.get("res");
            //  删除图形字段，jackson不能转换
            Iterator<HashMap<String, Object>> iterator = resList.iterator();
            while(iterator.hasNext()){
                HashMap<String, Object> map = iterator.next();
                Iterator<String> iter = map.keySet().iterator();
                while(iter.hasNext()){
                    String key = iter.next();
                    if (key.indexOf("SHAPE")!=-1||key.indexOf("Shape")!=-1||key.indexOf("shape")!=-1){
                        iter.remove();
                    }
                }
            }
            return BaseResponse.onSuccess("数据库表记录查询成功",result);
        }catch (Exception e){
            e.printStackTrace();
            return BaseResponse.onFail("数据库表记录查询失败，错误信息:"+e.getMessage());
        }
    }

Service

//获取数据表的记录信息
    @Override
    public Map<String, Object> getTableInfos(int page, int limit){
        Map<String,Object> result = new HashMap<>();
        List<Map<String, Object>> tables = new ArrayList<>();
        DBHelper dbHelper = null;
        try {
            //获取数据库连接
            int type = 2;
            //分页查询数据记录信息
            List<Object> params = new ArrayList<>();
            String sql = "";
            int max = page * limit;
            int min = (page-1)*limit;
            if(type == 2){
                sql = "select * from (select ROWNUM as rowno,t.* from "+getTablename()+" t where ROWNUM<=?) t2 where t2.rowno>=?";
                params.add(max);
                params.add(min);
            }else{
                sql = "select * from " + getTablename() + " limit ? offset ?";
                params.add(limit);
                params.add(min);
            }
            String username = "postgres";
            String password = "postgres";
            dbHelper = dbUtils.getDbHelper(4,"192.9.30.225:5432/postgres",
                    username,password);
            tables = dbHelper.queryList(sql, params.toArray());

            String sql2 = "select count(*) from "+getTablename();
            int count = dbHelper.queryCount(sql2);
            result.put("res",tables);
            result.put("count",count);
        }catch(Exception ex){
            ex.printStackTrace();
        }finally {
            dbHelper.closeResource();
        }
        return result;
    }

本栏目推荐文章
• Jmeter 如何连接mysql数据库？
• SQL Server 检测是不是数字型数据的两种方法
• 如何通过Navicat把MySQL远程数据库导入到本地数据库
• 和利时MACS6通过dat文件实现任意时间范围与测点的历史数据提取与科研分析
• 深入理解spring框架：剖析多线程模式下数据库连接
• 数据可视化是如何帮助大型企业提高效率的？
• redis 数据库
• openpyxl模块--------------------------------重复数据
• Django客户端应用1向服务端应用2发送POST请求并接收解析数据
• 数据可视化是如何帮助我们高效了解数据的？

数据 数据源 字段 详情 动态数据 乱码 字段 详情 数据 数据源 字段 详情 字段 动态 数据sql 接口 详情 商品 数据 商品 店铺 详情 数据 数据 详情 商品 示例 详情 商品item_get数据 商品 详情 数据 属性 详情 数据采集 实例 接口 数据 字段 索引 类型