需求:
1.每张表的SQL算法配置到数据库的配置表中
2.SQL算法中的条件统一使用占位符填充
3.当执行sql时需要将查询出来的SQL算法中的占位符动态赋值
一、真实案例还原
这里拿表进行演示
1. 表结构
create table HERO
(
SNO VARCHAR2(20) not null,
USER_NAME VARCHAR2(20),
AGE NUMBER(3)
);
-- Add comments to the table
comment on table HERO
is '英雄信息表';
-- Add comments to the columns
comment on column HERO.SNO
is '英雄编码';
comment on column HERO.USER_NAME
is '英雄名称';
comment on column HERO.AGE
is '英雄年龄';
-- Create/Recreate primary, unique and foreign key constraints
alter table HERO
add primary key (SNO);
初始化数据:
insert into hero (SNO, USER_NAME, AGE)values ('1', '盖伦', '1');
insert into hero (SNO, USER_NAME, AGE)values ('2', '小丑', '2');
insert into hero (SNO, USER_NAME, AGE)values ('3', '莫甘娜', '3');
insert into hero (SNO, USER_NAME, AGE)values ('4', '寒冰', '4');
insert into hero (SNO, USER_NAME, AGE)values ('5', '剑圣', '5');
insert into hero (SNO, USER_NAME, AGE)values ('6', '剑圣', '6');
2. SQL算法案例:
表名sys_user ,具体的SNO根据不同的场景动态赋值
SELECT * FROM HERO WHERE SNO='?SNO?' and age ='?age?'
3. 动态赋值工具类
package com.gblfy.util;
import com.gblfy.DCAApplication;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* 动态拼接sql中的的占位符对应的值
*
* @author gblfy
* @date 2021-01-20
*/
public class DynamicSql {
private final static Logger logger = LoggerFactory.getLogger(DynamicSql.class);
private String sql;
/**
* 赋值
*
* @param colum 匹配
* @param value 值
*/
public void setValue(String colum, String value) {
this.setSql(this.getSql().replaceAll("\\?" + colum + "\\?", value));
// this.setSql(this.getSql().replaceAll("\\?" + colum + "\\?", "'" + value + "'"));
}
/**
* 拼接sql
*
* @param addSql
*/
public void addSql(String addSql) {
this.setSql(this.getSql() + addSql);
}
public String getSql() {
return sql;
}
public void setSql(String sql) {
this.sql = sql;
}
public DynamicSql(String sql) {
this.sql = sql;
}
public DynamicSql() {
}
}
4. 实现逻辑
1.表+算法 集合初始化
2.根据场景标识和表名 遍历不同的场景下面的表列表
3.将查询出来的表名和对应的SQL算法集合判断
4.动态拼接SQL
5.SQL算法动态赋值
5. 逻辑处理类
package com.gblfy.service.impl;
import com.gblfy.result.ALGResult;
import com.gblfy.service.SQLService;
import com.gblfy.util.DynamicSql;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class SQLServiceImpl implements SQLService {
private final static Logger logger = LoggerFactory.getLogger(SQLServiceImpl.class);
@Override
public void sqlDynamicToValue(String sno, String age, String tableName) {
// 全量场景标识
String flag = "F";
// 1.表+算法 集合初始化
List<ALGResult> algResultList = null;
// 2.根据场景标识和表名 遍历不同的场景下面的表列表
algResultList = algResultMapper.selectALG(tableName, flag);
// 3.将查询出来的表名和对应的SQL算法集合判断
if (algResultList != null && !algResultList.isEmpty()) {
for (ALGResult algResult : algResultList) {
// 4.动态拼接SQL
//算法初始化
String cycleALGSql = "";
//拼接算法+拼接条件=最终的sql
//循环获取 每一个表名称
String cycleTableName = algResult.getHxrulename();
//循环获取 每一个表名称对应的算法拼接条件
if (!StringUtils.isEmpty(algResult.getHxAlgValue1())) {
cycleALGSql += algResult.getHxAlgValue1();
}
if (!StringUtils.isEmpty(algResult.getHxAlgValue2())) {
cycleALGSql += algResult.getHxAlgValue2();
}
DynamicSql dealCycleALGSql = new DynamicSql(cycleALGSql);
dealCycleALGSql.setValue("sno", sno);
dealCycleALGSql.setValue("age", age);
// 5.SQL算法动态赋值
logger.info("动态赋值处理后 :[{}]", dealCycleALGSql.getSql());
}
}
}
}
6. 效果图
```bash
SELECT * FROM HERO WHERE SNO='1' and age ='2'