Springboot实现sql预览

Springboot实现sql预览@TOC

欢迎使用Markdown编辑器

public class SqlDto {
/**
* 数据源id
/
private String id;
/*
*sql语句
*/
private String sql;
}

新的改变

public RestVO getSqlList(SqlDto sqlDto) {
String id = sqlDto.getId();
DataSourceManagement dataSourceManagement = sysDataSourceManagementMapper.selectById(id);
boolean connBoolean = false;
//解密之后的密码
String decrypt = AesUtils.decrypt(dataSourceManagement.getDataPass());
String sql = sqlDto.getSql();
dataSourceManagement.setDataPass(decrypt);
if (DatabaseTypeEnum.MySql.getValue().equals(dataSourceManagement.getDataType())) {
Connection conn = null;
try {
//1 加载驱动
Class.forName(“com.mysql.jdbc.Driver”);
//2拼接url
String url = “jdbc:mysql://” + dataSourceManagement.getDataUrl() + “:” + dataSourceManagement.getDataPort().toString() + “/” + dataSourceManagement.getDataName() + “?useUnicode=true&characterEncoding=UTF8&serverTimezone=UTC&useSSL=false”;
conn = DriverManager.getConnection(url, dataSourceManagement.getDataUser(), dataSourceManagement.getDataPass());
// 3.获取数据库操作对象
Statement statement = conn.createStatement();
// 4.执行sql
ResultSet resultSet = statement.executeQuery(sql);
List list = new ArrayList();
ResultSetMetaData md = resultSet.getMetaData();
int columnCount = md.getColumnCount();
while (resultSet.next()) {
Map rowData = new HashMap();
for (int i = 1; i <= columnCount; i++) {
rowData.put(md.getColumnName(i), resultSet.getObject(i));
}
list.add(rowData);
}
// 5.关闭连接
statement.close();
conn.close();
return RestVO.ok(list);
} catch (Exception e) {
e.printStackTrace();
return RestVO.error(“mysql查询错误”);
}
} else if (DatabaseTypeEnum.Oracle.getValue().equals(dataSourceManagement.getDataType())) {
dataSourceManagement.setDataName(“ORCL”);
Connection conn = null;
try {
//1 加载驱动
Class.forName(“oracle.jdbc.driver.OracleDriver”);
//2拼接url
String url = “jdbc:oracle:thin:@” + dataSourceManagement.getDataUrl() + “:” + dataSourceManagement.getDataPort().toString() + “:” + dataSourceManagement.getDataName();
conn = DriverManager.getConnection(url, dataSourceManagement.getDataUser(), dataSourceManagement.getDataPass());
// 3.获取数据库操作对象
Statement statement = conn.createStatement();
// 4.执行sql
ResultSet resultSet = statement.executeQuery(sql);
List list = new ArrayList();
ResultSetMetaData md = resultSet.getMetaData();
int columnCount = md.getColumnCount();
while (resultSet.next()) {
Map rowData = new HashMap();
for (int i = 1; i <= columnCount; i++) {
rowData.put(md.getColumnName(i), resultSet.getObject(i));
}
list.add(rowData);
}
// 5.关闭连接
statement.close();
conn.close();
return RestVO.ok(list);
} catch (Exception e) {
e.printStackTrace();
return RestVO.error(“Oracle查询错误”);
}
} else if (DatabaseTypeEnum.PostgreSQL.getValue().equals(dataSourceManagement.getDataType())) {

        Connection conn = null;
        try {
            //1 加载驱动
            Class.forName("org.postgresql.Driver");
            //2拼接url
            String url = "jdbc:postgresql://" + dataSourceManagement.getDataUrl() + ":" + dataSourceManagement.getDataPort() + "/" + dataSourceManagement.getDataName()+ "?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true";
            conn = DriverManager.getConnection(url, dataSourceManagement.getDataUser(), dataSourceManagement.getDataPass());
            // 3.获取数据库操作对象
            Statement statement = conn.createStatement();
            // 4.执行sql
            ResultSet resultSet = statement.executeQuery(sql);
            List list = new ArrayList();
            ResultSetMetaData md = resultSet.getMetaData();
            int columnCount = md.getColumnCount();
            while (resultSet.next()) {
                Map rowData = new HashMap();
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(md.getColumnName(i), resultSet.getObject(i));
                }
                list.add(rowData);
            }
            // 5.关闭连接
            statement.close();
            conn.close();
            return RestVO.ok(list);
        } catch (Exception e) {
            e.printStackTrace();
            return RestVO.error("Postgresql查询错误");
        }
    }
    return null;
}

功能快捷键