配置文件
在Spring Boot的配置文件中,通过spring.datasource.dynamic属性来设置多个数据源。以下是一个示例:
spring:
datasource:
dynamic:
primary: master
datasource:
master:
username:此处填数据库账号
password:此处填数据库密码
url:此处填数据库连接地址
driver-class-name: dm.jdbc.driver.DmDriver
kdoms3:
username:此处填数据库账号
password:此处填数据库密码
url:此处填数据库连接地址
driver-class-name: dm.jdbc.driver.DmDriver
多数据源操作实现
代码目录结构概览
代码实现详解
首先定义一个接口SqlExecute,提供统一查询方法,让对应的数据源进行实现。
import java.util.List;
import java.util.Map;
/**
* 多数据源接口
*/
public interface SqlExecute {
String getSourceCode();
Map<String,Object> searchOneSql(String sql);
List<Map<String,Object>> searchListSql(String sql);
}
接下来为每个数据源分别实现该接口,并使用@DS注解指定对应的数据源,例如KD_OMS3数据源的实现类:
import cloud.lesscode.common.constant.DsConstant;
import cloud.lesscode.upms.datasource.SqlExecute;
import cloud.lesscode.upms.datasource.SqlExecuteDao;
import com.baomidou.dynamic.datasource.annotation.DS;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import java.util.List;
import java.util.Map;
/**
* KD_OMS3 数据源实现
*/
@DS(DsConstant.KD_OMS3)
@Component
public class Kdoms3SqlExecuteImpl implements SqlExecute {
@Autowired
private SqlExecuteDao sqlExecuteDao;
@Override
public String getSourceCode() {
return DsConstant.KD_OMS3;
}
@Override
public Map<String, Object> searchOneSql(String sql) {
return sqlExecuteDao.searchOneSql(sql);
}
@Override
public List<Map<String, Object>> searchListSql(String sql) {
return sqlExecuteDao.searchListSql(sql);
}
}
接着创建一个统一的DAO层接口SqlExecuteDao,用于执行不同数据源提供的SQL语句:
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
import java.util.Map;
/**
* 多数据源Sql执行dao
*/
@Mapper
public interface SqlExecuteDao{
@Select("${sql}")
Map<String,Object> searchOneSql(@Param("sql") String sql);
@Select("${sql}")
List<Map<String,Object>> searchListSql(@Param("sql") String sql);
}
为了方便管理所有数据源实现类,我们创建一个多数据源工厂实现类SqlExecuteFactory,它实现了ApplicationContextAware接口以获取应用上下文中的所有数据源实现实例:
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;
import java.util.HashMap;
import java.util.Map;
/**
* 多数据源生产工厂
*/
@Component
public class SqlExecuteFactory implements ApplicationContextAware {
private static Map<String, SqlExecute> sqlExecuteMap;
@Override
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
Map<String, SqlExecute> map = applicationContext.getBeansOfType(SqlExecute.class);
sqlExecuteMap = new HashMap<>();
map.forEach((key, value) -> sqlExecuteMap.put(value.getSourceCode(), value));
}
public static <T extends SqlExecute> T getSqlExecute(String code) {
return (T)sqlExecuteMap.get(code);
}
}
拼接Sql执行工具
最后,为了便于拼接和验证SQL语句,编写了一个工具类SqlFoematUtil,里面实现了拼接sql语法检验,防止在直接进行sql拼接查询场景误操,这里使用的是dm数据库,如果大家使用的是mysql也可以把SQL_TYPE 替换成mysql,大家感兴趣的可以参考使用
import cn.hutool.core.map.MapUtil;
import com.alibaba.druid.sql.parser.ParserException;
import com.alibaba.druid.sql.parser.SQLParserUtils;
import com.alibaba.druid.sql.parser.SQLStatementParser;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
/**
* 拼接Sql执行工具
*/
public class SqlFoematUtil {
private static final String SPECIAL = "%";
private static final String SUFFIX = "s";
private static final String CALIDATION = "%s";
private static final String SQL_TYPE = "dm";
/**
* 拼接sql语句条件
* @param format 需要拼接的sql
* @param args 需要拼接的条件
* @return
*/
public static String formatSql(String format, Object... args) {
validationFormat(format, args);
//处理特殊字符%,判断后面是否拼接s,没有单独的%则进行转义
String handleSpecialStr = handleSpecial(format);
String fixedPlateSql = String.format(handleSpecialStr, args);
validationSql(SQL_TYPE, fixedPlateSql);
return fixedPlateSql;
}
private static void validationSql(String sqlType, String specialStr) {
try {
SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(specialStr, sqlType);
parser.parseStatement();
} catch (ParserException e) {
throw new RuntimeException("检测到SQL语句异常,请检查!异常信息为:" + e.getMessage());
}
}
private static void validationFormat(String format, Object[] args) {
if (!format.contains(CALIDATION)) {
throw new RuntimeException("未检测到替换符%s,请检查!");
}
int oldLength = format.length();
String newStr = format.replaceAll(CALIDATION, "");
int newLength = newStr.length();
if ((oldLength - newLength) / 2 != args.length) {
throw new RuntimeException("检测到替换符%s数量与替换参数不一致,请检查!");
}
}
private static String handleSpecial(String format) {
StringBuilder stringBuilder = new StringBuilder(format);
Map<Integer, String> map = new HashMap<>();
for (int i = 0; i < stringBuilder.length(); i++) {
map.put(i, String.valueOf(stringBuilder.charAt(i)));
}
//筛选出特殊符号
map.entrySet().removeIf(s -> !SPECIAL.equals(s.getValue()));
if (MapUtil.isEmpty(map)) {
return stringBuilder.toString();
}
Iterator<Integer> iterator = map.keySet().iterator();
int nextIndex = 0;
while (iterator.hasNext()) {
Integer key = iterator.next();
String value = map.get(key);
char after = stringBuilder.charAt(key + nextIndex + 1);
if (!String.valueOf(after).equals(SUFFIX)) {
int index = key + nextIndex;
stringBuilder.insert(index + value.length(), SPECIAL);
stringBuilder.indexOf(value, index + value.length() + 1);
nextIndex++;
}
}
return stringBuilder.toString();
}
}
测试示例:
@Test
public void queryKdOms3List() {
SqlExecute mode = SqlExecuteFactory.getSqlExecute(DsConstant.KD_OMS3);
String executeSql = SqlFoematUtil.formatSql("select * from KD_OMS3数据源的表 where 条件字段 = '%s'", "对应的条件参数");
List<Map<String, Object>> list = mode.searchListSql(executeSql);
System.out.println("list = " + list);
}