JDBC查询数据库并处理结果集

背景

1、系统中可以添加不同类型的数据库，它们的配置信息都保存在系统数据库中。

2、可以通过读取系统中不同的数据库配置来选择不同的数据。

数据库设计

data_name		varchar	255		数据源名称	
database_type	varchar	255		数据库类型	
server			varchar	255		服务器地址	
port			int4	32		服务器端口
username		varchar	255		用户名
password		varchar	255		密码
database_name	varchar	255		数据库名称
table_name		varchar	255		表名称
id				varchar	40		数据编号

创建数据库对应的实体类

public class Data {
    private String dataName;
    private String databaseType;
    private String server;
    private Integer port;
    private String username;
    private String password;
    private String databaseName;
    private String tableName;

    public Data() {
    }

    public String getDataName() {
        return dataName;
    }

    public void setDataName(String dataName) {
        this.dataName = dataName;
    }

    public String getDatabaseType() {
        return databaseType;
    }

    public void setDatabaseType(String databaseType) {
        this.databaseType = databaseType;
    }

    public String getServer() {
        return server;
    }

    public void setServer(String server) {
        this.server = server;
    }

    public Integer getPort() {
        return port;
    }

    public void setPort(Integer port) {
        this.port = port;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getDatabaseName() {
        return databaseName;
    }

    public void setDatabaseName(String databaseName) {
        this.databaseName = databaseName;
    }

    public String getTableName() {
        return tableName;
    }

    public void setTableName(String tableName) {
        this.tableName = tableName;
    }
}

创建工具类CommonUtils

工具类包括获取连接、解析数据集等方法

@Service
public class CommonUtils {   
    public static Connection getDbConnection(Data data){
        String mysql = "MySQL";
        String postgresql = "PostgreSQL";
        Connection connection = null;
        try {
            if (mysql.equals(data.getDatabaseType())) {
                Class.forName("com.mysql.cj.jdbc.Driver");
                connection = DriverManager.getConnection("jdbc:mysql://" + data.getServer() + ":" + data.getPort() + "/"
                        + data.getDatabaseName() + "?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC", data.getUsername(), data.getPassword());
            }
            if (postgresql.equals(data.getDatabaseType())) {
                Class.forName("org.postgresql.Driver");
                connection = DriverManager.getConnection("jdbc:postgresql://" + data.getServer() + ":"
                        + data.getPort() + "/" + data.getDatabaseName(), data.getUsername(), data.getPassword());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static List<Map<String,Object>> formatDatabaseData(ResultSet resultSet){
        List<Map<String,Object>> resultList = new ArrayList<>();
        List<String> columnNames= new ArrayList<>();
        // 获取列名
        try{
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            for (int i = 1; i < resultSetMetaData.getColumnCount() + 1; i++) {
                System.out.println(resultSetMetaData.getColumnName(i));
                columnNames.add(resultSetMetaData.getColumnName(i));
            }
            // 根据列名获取每一行的数据
            while (resultSet.next()) {
                Map<String,Object> data = new HashMap<>();
                for (String columnName :columnNames){
                    data.put(columnName,resultSet.getString(columnName));
                }
                resultList.add(data);
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        return resultList;
    }
}

创建一个Service

@Service
public class DataService {    
    public String previewData(Data data) {
       	List<Map<String,Object>> resultList= new ArrayList<>();
        String MySQL = "MySQL";
        String PostgreSQL = "PostgreSQL";
        Connection connection = null;
        Statement statement = null;
        try {
            // 获取连接
            connection = CommonUtils.getDbConnection(data);
            // 组装对应不同类型数据库的SQL
            String sql = "";
            if (MySQL.equals(data.getDatabaseType())) {
                sql = "select * from " + connection.getCatalog() + "." + data.getTableName();
            }
            if (PostgreSQL.equals(data.getDatabaseType())) {
                sql = "select * from " + connection.getCatalog() + "." + connection.getSchema() + "." + data.getTableName();
            }
            // 执行SQL并获取结果
            statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            resultList= CommonUtils.formatDatabaseData(resultSet);
        } catch (Exception e) {
            e.printStackTrace();           
        }
        return JSONObject.toJSONString(resultList);
    }
}

创建一个接口

@RestController
public class CommonController {
    @Autowired
    DataService dataService ;

    @RequestMapping("/previewData")
    public R previewData(@RequestBody Data data) {
        return dataService .previewData(data);
    }
}