报表系统-连接数据库操作

本专栏用于解析自己开源的项目代码，作为复盘和学习使用。欢迎大家一起交流
本样例说明源码开源在：
ruoyi-reoprt gitee仓库
ruoyi-report github仓库
欢迎大家到到项目中多给点star支持，对项目有建议或者有想要了解的欢迎一起讨论

连接数据库这一模块整体参考的是AJ-report的模块：去掉了目前不支持的数据库格式，仅保留了mysql和sqlserver

https://gitee.com/anji-plus/report

1. 存储数据库连接

1.1 数据库连接表单设计

create table cwu_data_source
(source_code   varchar(100)  null comment '数据源编码',source_name   varchar(100)  null comment '数据源名称',source_desc   varchar(255)  null comment '数据源描述',source_type   varchar(50)   null comment '数据源类型 DIC_NAME=SOURCE_TYPE; mysql，orace，sqlserver，elasticsearch，接口，javaBean，数据源类型字典中item-extend动态生成表单',source_config varchar(2048) null comment '数据源连接配置json：关系库{ jdbcUrl:'''', username:'''', password:'''' } ES{ hostList:''ip1:9300,ip2:9300,ip3:9300'', clusterName:''elasticsearch_cluster'' }  接口{ apiUrl:''http://ip:port/url'', method:'''' } javaBean{ beanNamw:''xxx'' }',enable_flag   int default 1 null comment '0--已禁用 1--已启用  DIC_NAME=ENABLE_FLAG',id            int auto_incrementprimary key,create_time   bigint        null comment '创建时间',update_time   bigint        null comment '更新时间',update_user   bigint        null comment '更新者id',create_user   bigint        null comment '创建者id',create_org    bigint        null comment '创建的组织id',update_org    bigint        null comment '更新的组织id',is_delete     int default 0 null comment '1删除0有效',version       int           null,constraint unique_source_codeunique (source_code)
)comment '数据源管理' charset = utf8row_format = DYNAMIC;

1.2 测试连接配置是否能连接成功

在写入存储阶段先进行一次测试连接，给用户及时反馈配置是否正确。

使用Hikari进行数据库连接，获取到一个连接，判断是否能正常获取到

// 传入连接所需的参数
public class DataSourceDto extends BaseClass implements Serializable {/** 数据源编码 */private String sourceCode;/** 数据源名称 */private String sourceName;/** 数据源描述 */private String sourceDesc;/** 数据源类型 DIC_NAME=SOURCE_TYPE; mysql，orace，sqlserver，elasticsearch，接口，javaBean，数据源类型字典中item-extend动态生成表单 */private String sourceType;/** 数据源连接配置json：关系库{ jdbcUrl:'', username:'', password:'','driverName':''}ES-sql{ apiUrl:'http://127.0.0.1:9092/_xpack/sql?format=json','method':'POST','body':'{"query":"select 1"}' }  接口{ apiUrl:'http://ip:port/url', method:'' } javaBean{ beanNamw:'xxx' } */private String sourceConfig;/** 0--已禁用 1--已启用  DIC_NAME=ENABLE_FLAG */private Integer enableFlag;/** 0--未删除 1--已删除 DIC_NAME=DELETE_FLAG */private Integer deleteFlag;/**************************************************************//**关系型数据库jdbcUrl */private String jdbcUrl;/** 关系型数据库用户名 */private String username;/** 关系型数据库密码 */private String password;/** 关系型数据库驱动类 */private String driverName;/** 关系型数据库sql */private String sql;/** http requestUrl */private String apiUrl;/** http method */private String method;/** http header */private String header;/** http 请求体 */private String body;/** 动态查询sql或者接口中的请求体 */private String dynSentence;/** 传入的自定义参数，解决url中存在的动态参数*/private Map<String, Object> contextData;}public HikariPool getJdbcConnectionPool(DataSourceDto dataSource) {if (map.containsKey(dataSource.getId())) {return map.get(dataSource.getId());} else {try {synchronized (lock) {if (!map.containsKey(dataSource.getId())) {HikariPool pool = hikariPoolProperties.dataSource(dataSource.getJdbcUrl(),dataSource.getUsername(), dataSource.getPassword(), dataSource.getDriverName());map.put(dataSource.getId(), pool);log.info("创建连接池成功：{}", dataSource.getJdbcUrl());}}return map.get(dataSource.getId());} finally {}}}

2. 执行查询语句

目前能执行的数据库都是关系型数据库，所以直接执行到对应的方法中，先从连接池中根据数据库id取出对应的连接，如果没有的话新建一个。然后执行对应的sql语句.

查询结果用

 List<Map<String,Object>>

的结构返回，list中的一个元素表示一行的值，map中的key，value为数据库中对应的字段和值

 public List<Map<String,Object>> executeRelationalDb(DataSourceDto dto) {analysisRelationalDbConfig(dto);Connection pooledConnection = null;try {pooledConnection = jdbcService.getPooledConnection(dto);PreparedStatement statement = pooledConnection.prepareStatement(dto.getDynSentence());ResultSet rs = statement.executeQuery();int columnCount = rs.getMetaData().getColumnCount();List<String> columns = new ArrayList<>();for (int i = 1; i <= columnCount; i++) {String columnName = rs.getMetaData().getColumnLabel(i);columns.add(columnName);}List<Map<String,Object>> list = new ArrayList<>();while (rs.next()) {Map<String,Object> jo = new HashMap<>();columns.forEach(t -> {try {Object value = rs.getObject(t);//数据类型转换Object result = dealResult(value);jo.put(t, result);} catch (SQLException throwable) {log.error("error",throwable);throw new GlobalException(ResponseCode.EXECUTE_SQL_ERROR, throwable.getMessage());}});list.add(jo);}return list;} catch (Exception throwable) {log.error("error",throwable);throw new GlobalException(ResponseCode.EXECUTE_SQL_ERROR, throwable.getMessage());} finally {try {if (pooledConnection != null) {pooledConnection.close();}} catch (SQLException throwable) {log.error("error",throwable);throw new GlobalException(ResponseCode.DATA_SOURCE_CONNECTION_FAILED, throwable.getMessage());}}}

3. 获取数据库中的表和字段结构

对于mysql和sqlserver来说，传入表名字，获取表中的字段结构的sql语句存在不同，所以需要分别处理，执行拿到的数据结构统一格式返回

// 传入表名和数据库id，返回对应的表字段数据，包括字段名、类型、备注
public List<Map> getTableFile(String tableName,Integer dataSourceId) {DataSource dataSource = dataSourceService.getById(dataSourceId);List<Map<String,Object>> list = dataSourceService.execute(getSqlByType(dataSource.getSourceType(),tableName),dataSourceId);List<Map> mapList = new ArrayList<>();for (Map<String,Object> map : list) {Map<String,Object> map1 = new HashMap<>();map1.put("field",map.get("Field"));map1.put("type",map.get("Type"));if(map.get("Comment") != null && !map.get("Comment").equals("")){map1.put("comment",map.get("Comment"));}else {map1.put("comment",map.get("Field"));}mapList.add(map1);}return mapList;}
// 根据不同的类型返回不同的sql执行语句
private String getSqlByType(String type,String tableName){switch (type) {case JdbcConstants.MYSQL:return "show full fields from " + tableName; case JdbcConstants.SQL_SERVER:return "SELECT \n" +"    c.name AS Field,\n" +"    ty.name AS Type,\n" +"    c.is_nullable,\n" +"    ep.value AS Comment\n" +"FROM \n" +"    sys.columns c\n" +"    INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id\n" +"    INNER JOIN sys.tables t ON c.object_id = t.object_id\n" +"    LEFT JOIN sys.extended_properties ep ON c.object_id = ep.major_id \n" +"                                        AND c.column_id = ep.minor_id \n" +"                                        AND ep.name = 'MS_Description'\n" +"                                        AND ep.class = 1\n" +"WHERE \n" +"    t.name = '" + tableName + "'\n" +"    AND ty.is_user_defined = 0; ";default:throw new RuntimeException("不支持该类型");}}

4. 对应系统功能展示