前言

之前簡單介紹了一下 Dataway 使用，本文繼續介紹一下它的多數據源配置和使用。

核心代碼和配置

yml 配置

# springboot多環境配置
#端口，項目上下文
server:port: 8080servlet:context-path: /springboot-dataway# 全局服務編碼設置encoding:charset: utf-8enabled: trueforce: true# mysql 連接信息配置
spring:# mysql 數據庫連接信息，本地使用 mysql 服務版本為：8.0.28datasource:username: rootpassword: 6tojyh*A3eQ6url: jdbc:mysql://localhost:3306/dataway?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true&allowPublicKeyRetrieval=truedriver-class-name: com.mysql.cj.jdbc.Driver# druid 數據連接池配置druid:initial-size: 3min-idle: 3max-active: 10max-wait: 6000# 配置druid監控頁aop-patterns: com.demo.* #監控springBeanstat-view-servlet: # 配置監控頁功能enabled: true # 默認開啟，這里顯示說明login-username: admin # 登錄名login-password: 6tojyh*A3eQ6 # 登錄密碼reset-enable: false # 禁用重置按鈕web-stat-filter: # 監控 webenabled: trueurl-pattern: /* # 監控所有exclusions: '*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*'  #放行filter:stat: # 對上面 filters 里的 stat 的詳細配置slow-sql-millis: 1000 # 慢 sql 時間是毫秒單位的，執行時間 1 秒以上的為慢 SQLlog-slow-sql: true # 日志記錄enabled: truewall:enabled: trueconfig:drop-table-allow: false # 禁用刪除表的 sql# 除主數據源外，第1個數據源db1:username: rootpassword: 6tojyh*A3eQ6url: jdbc:mysql://localhost:3306/console?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true&allowPublicKeyRetrieval=truedriver-class-name: com.mysql.cj.jdbc.Driver# 除主數據源外，第 2 個數據源db2:username: postgrespassword: UFWOd75qD7url: jdbc:postgresql://localhost:32770/postgres?binaryTransfer=false&forceBinary=false&reWriteBatchedInserts=truedriver-class-name: org.postgresql.Driver# mdataway 配置
# 是否啟用 Dataway 功能（必選：默認false）
HASOR_DATAQL_DATAWAY: true
# 開啟 ui 管理功能（注意生產環境必須要設置為 false，否則會造成嚴重的生產安全事故）
HASOR_DATAQL_DATAWAY_ADMIN: true
# dataway  API工作路徑（可選，默認：/api/）
HASOR_DATAQL_DATAWAY_API_URL: /api/
# dataway-ui 的工作路徑（可選，默認：/interface-ui/）
HASOR_DATAQL_DATAWAY_UI_URL: /interface-ui/
# SQL執行器方言設置（可選，建議設置）
HASOR_DATAQL_FX_PAGE_DIALECT: mysql
# 登陸認證方式在 basic 模式下的時候，配置的登陸賬號
HASOR_DATAQL_DATAWAY_AUTHORIZATION_USERNAME: admin
# 登陸認證方式在 basic 模式下的時候，配置的登陸密碼,默認密碼admin
HASOR_DATAQL_DATAWAY_AUTHORIZATION_PASSWORD: 6tojyh*A3eQ6# 日志輸出配置
logging:level:root: debugorg:springframework:security: WARNweb: ERROR# 設置自己的 com.demo.mapper 目錄 輸出sql日志com.demo.mapper: debugfile:path: ./logsname: './logs/springboot-dataway.log'pattern:file: '%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50}:%L - %msg%n'console: '%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50}:%L - %msg%n'

注入多數據源

package com.demo.config;import com.alibaba.druid.pool.DruidDataSource;
import com.demo.chain.*;
import net.hasor.core.ApiBinder;
import net.hasor.core.DimModule;
import net.hasor.core.TypeSupplier;
import net.hasor.dataql.Finder;
import net.hasor.dataql.QueryApiBinder;
import net.hasor.dataql.fx.db.FxSqlCheckChainSpi;
import net.hasor.dataway.spi.LoginPerformChainSpi;
import net.hasor.dataway.spi.LoginTokenChainSpi;
import net.hasor.dataway.spi.PreExecuteChainSpi;
import net.hasor.dataway.spi.ResultProcessChainSpi;
import net.hasor.db.JdbcModule;
import net.hasor.db.Level;
import net.hasor.spring.SpringModule;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.ApplicationContext;
import org.springframework.stereotype.Component;import javax.annotation.Resource;
import javax.sql.DataSource;/*** @Classname DatawayModule* @Description 將Hasor模塊注入spring，并注入數據源* @Date 2023/7/28 11:43* @Created by Leo825*/
@DimModule  // Hasor 中的標簽，表明是一個Hasor的model
@Component  // Spring 中的標簽，表明是一個組件
public class DatawayModule implements SpringModule,TypeSupplier {// 默認數據源@Autowiredprivate DataSource dataSource;// 數據源1的配置信息@Value("${spring.datasource.db1.url}")private String jdbcUrl1;@Value("${spring.datasource.db1.driver-class-name}")private String driver1;@Value("${spring.datasource.db1.username}")private String username1;@Value("${spring.datasource.db1.password}")private String password1;// 數據源2的配置信息@Value("${spring.datasource.db2.url}")private String jdbcUrl2;@Value("${spring.datasource.db2.driver-class-name}")private String driver2;@Value("${spring.datasource.db2.username}")private String username2;@Value("${spring.datasource.db2.password}")private String password2;@Resourceprivate ApplicationContext applicationContext;@Overridepublic <T> T get(Class<? extends T> targetType) {return applicationContext.getBean(targetType);}@Overridepublic <T> boolean test(Class<? extends T> targetType) {return applicationContext.getBeanNamesForType(targetType).length > 0;}@Overridepublic void loadModule(ApiBinder apiBinder) throws Throwable {// .DataSource form Spring boot into HasorapiBinder.installModule(new JdbcModule(Level.Full, this.dataSource));// 注入數據源apiBinder.installModule(new JdbcModule(Level.Full, this.dataSource));apiBinder.installModule(new JdbcModule(Level.Full, "dataSource1", getDataSource(jdbcUrl1, driver1, username1, password1)));apiBinder.installModule(new JdbcModule(Level.Full, "dataSource2", getDataSource(jdbcUrl2, driver2, username2, password2)));// 打印sql日志apiBinder.bindSpiListener(FxSqlCheckChainSpi.class, FxSqlCheckChain.getInstance());// 數據權限參數apiBinder.bindSpiListener(PreExecuteChainSpi.class, PreExecuteChain.getInstance());// 返回結果apiBinder.bindSpiListener(ResultProcessChainSpi.class, ResultProcessChain.getInstance());
//        // 登錄過濾
//        apiBinder.bindSpiListener(LoginPerformChainSpi.class, LoginPerformChain.getInstance());// 登錄 token 過濾
//        apiBinder.bindSpiListener(LoginTokenChainSpi.class, LoginTokenChain.getInstance());// udf/udfSource/import 指令 的類型創建委托給 springQueryApiBinder queryBinder = apiBinder.tryCast(QueryApiBinder.class);queryBinder.bindFinder(Finder.TYPE_SUPPLIER.apply(this));}/*** 注入數據源** @param* @param driver* @param username* @param password* @return*/private DruidDataSource getDataSource(String jdbcUrl, String driver, String username, String password) {DruidDataSource dataSource = new DruidDataSource();dataSource.setUrl(jdbcUrl);dataSource.setUsername(username);dataSource.setPassword(password);dataSource.setDriverClassName(driver);// 用來檢測連接是否有效dataSource.setValidationQuery("SELECT 1");// 借用連接時執行validationQuery檢測連接是否有效，做了這個配置會降低性能dataSource.setTestOnBorrow(false);// 歸還連接時執行validationQuery檢測連接是否有效，做了這個配置會降低性能dataSource.setTestOnReturn(false);// 連接空閑時檢測，如果連接空閑時間大于timeBetweenEvictionRunsMillis指定的毫秒，// 執行validationQuery指定的SQL來檢測連接是否有效// 如果檢測失敗，則連接將被從池中去除dataSource.setTestWhileIdle(true);dataSource.setTimeBetweenEvictionRunsMillis(60000);//1分鐘dataSource.setMaxActive(20);dataSource.setInitialSize(5);return dataSource;}
}

常用Spi實現

FxSqlCheckChainSpi，這里主要用來打印 sql

package com.demo.chain;import lombok.extern.slf4j.Slf4j;
import net.hasor.dataql.fx.db.FxSqlCheckChainSpi;
import net.hasor.utils.StringUtils;/*** @Classname FxSqlCheckChain* @Description 打印sql* @Date 2023/8/3 20:35* @Created by Leo825*/
@Slf4j
public class FxSqlCheckChain implements FxSqlCheckChainSpi {public static FxSqlCheckChain getInstance() {return new FxSqlCheckChain();}@Overridepublic int doCheck(FxSqlInfo fxSqlInfo) throws Throwable {String sourceName = fxSqlInfo.getSourceName();if (StringUtils.isNotEmpty(sourceName)) {log.info("【dataway】dataSource ==>:{}", sourceName);}log.info("【dataway】sql ==>:{}", fxSqlInfo.getQueryString().trim());log.info("【dataway】params ==>: {}", fxSqlInfo.getQueryParams());// 如果存在后續，那么繼續執行檢查，否則使用 EXIT 常量控制退出后續的檢查。return FxSqlCheckChainSpi.NEXT;}
}

PreExecuteChainSpi，這里主要是在接口執行前進行一些權限參數控制

package com.demo.chain;import lombok.extern.slf4j.Slf4j;
import net.hasor.dataway.spi.ApiInfo;
import net.hasor.dataway.spi.PreExecuteChainSpi;
import net.hasor.utils.future.BasicFuture;
import org.apache.commons.collections4.MapUtils;import java.util.HashMap;
import java.util.Map;/*** @Classname PreExecuteChain* @Description 接口執行前* @Date 2023/8/3 20:37* @Created by Leo825*/
@Slf4j
public class PreExecuteChain implements PreExecuteChainSpi {public static PreExecuteChain getInstance() {return new PreExecuteChain();}/*** sql 直接前* @param apiInfo* @param basicFuture*/@Overridepublic void preExecute(ApiInfo apiInfo, BasicFuture<Object> basicFuture) {Map<String, Object> parameter = apiInfo.getParameterMap();// 注入用戶權限參數parameter.putAll(MapUtils.emptyIfNull(loginUserAuthParams()));}/*** 獲取登錄用戶權限參數* @return*/private Map<String, Object> loginUserAuthParams () {Map<String, Object> authParams = new HashMap<>();// todo 注入登錄用戶 權限參數return authParams;}
}

ResultProcessChainSpi，主要是返回值進行一些封裝

package com.demo.chain;
import com.demo.common.AjaxResult;
import lombok.extern.slf4j.Slf4j;
import net.hasor.dataway.spi.ApiInfo;
import net.hasor.dataway.spi.ResultProcessChainSpi;
/*** @Classname ReturnProcessChain* @Description 兼* 容 4.1.5之前 dataway版本 dataway 執行結果處理, 讓 structure 配置僅使用于   dataway頁面調試使用* @Date 2023/8/3 20:47* @Created by Leo825*/
@Slf4j
public class ResultProcessChain implements ResultProcessChainSpi {public static ResultProcessChain getInstance() {return new ResultProcessChain();}/*** 對返回結果進行處理* @param formPre* @param apiInfo* @param result* @return*/@Overridepublic Object callAfter(boolean formPre, ApiInfo apiInfo, Object result) {// apiInfo.isPerform() 為 true 表示，API 調用是從 UI 界面發起的。if (apiInfo.isPerform() || apiInfo.getParameterMap().containsKey("SELF_CALL")) {return result;}apiInfo.getOptionMap().put("resultStructure", false);return AjaxResult.success(result);}/*** 異常* @param formPre* @param apiInfo* @param e* @return*/@Overridepublic Object callError(boolean formPre, ApiInfo apiInfo, Throwable e) {if (apiInfo.isPerform()) {return ResultProcessChainSpi.super.callError(formPre, apiInfo, e);}apiInfo.getOptionMap().put("resultStructure", false);return AjaxResult.error( "系統繁忙");}
}

swagger 配置

package com.demo.config;
import org.springframework.context.annotation.Primary;
import org.springframework.stereotype.Component;
import springfox.documentation.swagger.web.SwaggerResource;
import springfox.documentation.swagger.web.SwaggerResourcesProvider;import java.util.ArrayList;
import java.util.List;/*** swagger配置*/
@Component
@Primary
public class SwaggerProvider implements SwaggerResourcesProvider {@Overridepublic List<SwaggerResource> get() {List<SwaggerResource> resources = new ArrayList<>();resources.add(swaggerResource("應用接口", "/v2/api-docs", "1.0"));resources.add(swaggerResource("Dataway接口", "/interface-ui/api/docs/swagger2.json", "1.0"));return resources;}private SwaggerResource swaggerResource(String name, String location, String version) {SwaggerResource swaggerResource = new SwaggerResource();swaggerResource.setName(name);swaggerResource.setLocation(location);swaggerResource.setSwaggerVersion(version);return swaggerResource;}
}

將 Dataway 里面的接口發布到 swagger里面

package com.demo.config;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;
import springfox.documentation.swagger2.annotations.EnableSwagger2;/*** swagger配置*/
@EnableSwagger2
@Configuration()
public class SwaggerConfig {@Beanpublic Docket createRestApi() {return new Docket(DocumentationType.SWAGGER_2)//.apiInfo(apiInfo())//.select()//.apis(RequestHandlerSelectors.basePackage("com.demo"))//.paths(PathSelectors.any())//.build();}private ApiInfo apiInfo() {return new ApiInfoBuilder()//.title("Spring Boot中使用Swagger2構建RESTful APIs")//.description("參考手冊：https://www.hasor.net/doc/display/dataql/")//.termsOfServiceUrl("https://www.hasor.net/doc/display/dataql/")//.contact("zyc@hasor.net").version("1.0")//.build();}
}

自定義 Udf

可以使用自定義udf，并且在管理頁面可以直接訪問

package com.demo.udf;import com.demo.config.SpringContextUtil;
import com.demo.service.MyUdfService;
import lombok.extern.slf4j.Slf4j;
import net.hasor.dataql.DimUdf;
import net.hasor.dataql.Hints;
import net.hasor.dataql.Udf;
import org.springframework.stereotype.Service;import javax.annotation.Resource;/*** 自定義 udf,需要在 DatawayModule 添加以下配置代碼，否則會出現注入為 null 問題* // udf/udfSource/import 指令 的類型創建委托給 spring* QueryApiBinder queryBinder = apiBinder.tryCast(QueryApiBinder.class);* queryBinder.bindFinder(Finder.TYPE_SUPPLIER.apply(this));*/
@Slf4j
@DimUdf("myNameUdf")
@Service
public class MyNameUdf implements Udf {@Resourceprivate MyUdfService myUdfService;@Overridepublic Object call(Hints readOnly, Object... params) {log.info("獲取當前服務信息: " + myUdfService.myName());return "張三";}
}

web管理頁面調用方式如下：

import 'com.demo.udf.MyNameUdf' as myNameUdf;
return myNameUdf();

據官方文檔說可以通過 import ‘bean’ 方式引入，但是嘗試了沒有成功。

指定數據源進行查詢

主要通過 FRAGMENT_SQL_DATA_SOURCE 來指定數據源：

// springboot 整合Dataway 多數據源配置。使用示例：
hint FRAGMENT_SQL_DATA_SOURCE = "dataSource2";
var query = @@sql()<%select * from subjects
%>
return query()