Java-SQL获取执行语句并将实际参数替换占位符
1.引入工具包
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.7</version>
</dependency>
2.引入内存镜像
import org.apache.ibatis.session.SqlSessionFactory;
@Autowired
SqlSessionFactory sqlSessionFactory;
3.获取并转换
//获取执行语句语句
MyBatisSql myBatisSql = MyBatisSqlUtils.getMyBatisSql("com.ruoyi.business.mapper.IZlxxMapper.updateXW", DataLogUtil.objectToMap(map), sqlSessionFactory);
String specialSql = String.valueOf(myBatisSql);
//转换语句
String changeSql = DataLogUtil.changeSql(specialSql);
//添加日志记录
DataLogUtil.addDataLog(tZlxxEntity.getZlNo(), 1, tZlxxEntity.getItm().toString(), "指令管理-箱位修改-zlxxEdit", changeSql, tZlxxEntity.getCzNo());
DataLogUtil.addChgData("ZE", tZlxxEntity.getTdNo(), tZlxxEntity.getBoxNo(), tZlxxEntity.getZlNo()+":"+UUID.randomUUID().toString().toUpperCase(), 1, 1, null, "指令管理-箱位修改-zlxxEdit", "xw:" + tZlxxEntity.getXw(), map.toString(), SecurityUtils.getUsr(), SecurityUtils.getCompno());
DataLogUtil.addPerformSQLStoredProcedure(tZlxxEntity.getZlNo(), 1, tZlxxEntity.getItm().toString(), "指令管理-箱位修改-zlxxEdit", changeSql, tZlxxEntity.getCzNo());
4.DataLogUtil.java
import cn.hutool.core.util.ReUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.TypeReference;
import com.ruoyi.business.domain.entity.TDataTbNewWithBLOBsEntity;
import com.ruoyi.business.domain.vo.ChgData;
import com.ruoyi.business.service.dataLog.IChgDataService;
import com.ruoyi.business.service.dataLog.ITDataTbNewService;
import com.ruoyi.common.utils.ValueUtil;
import com.ruoyi.common.utils.uuid.UUID;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.stereotype.Component;
import javax.annotation.PostConstruct;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
//同步sql记录
@Component
public class DataLogUtil {
@Autowired
private ITDataTbNewService itDataTbNewService;
@Autowired
private IChgDataService iChgDataService;
public static DataLogUtil dataLogUtil;
@PostConstruct
public void init(){
dataLogUtil = this;
dataLogUtil.itDataTbNewService = this.itDataTbNewService;
dataLogUtil.iChgDataService = this.iChgDataService;
}
/**
*
* @param bilNo 同步表主键
* @param bilNo1 同步表主键
* @param bilTable 操作表名或方法说明
* @param specialSql 同步语句
* @param czNo 站点号
* @return
*/
public static Boolean addDataLog(String bilNo,Integer bilItm, String bilNo1, String bilTable, String specialSql, String czNo){
try {
TDataTbNewWithBLOBsEntity tDataTbNewWithBLOBsEntity = new TDataTbNewWithBLOBsEntity();
SimpleDateFormat sdf = new SimpleDateFormat("YYYYMMddHHmmssSSS");
tDataTbNewWithBLOBsEntity.setBilNo("BS"+sdf.format(new Date()));
tDataTbNewWithBLOBsEntity.setBilItm(bilItm==null?1:bilItm);
String bil = "";
if(bilNo != null ){
bil = bilNo;
}
if(bilNo1 != null && !"null".equals(bilNo1) && !"NULL".equals(bilNo1)){
if(bil.trim().length() > 0){
bil = bil +"/"+bilNo1;
}else{
bil = bilNo1;
}
}
tDataTbNewWithBLOBsEntity.setBilNo1(bil);
if(ValueUtil.stringOfNullAndEmpty(bilTable)){
tDataTbNewWithBLOBsEntity.setBilTable("");
}else {
tDataTbNewWithBLOBsEntity.setBilTable(bilTable);
}
if(ValueUtil.stringOfNullAndEmpty(specialSql)){
tDataTbNewWithBLOBsEntity.setSpecialSql("");
}else {
tDataTbNewWithBLOBsEntity.setSpecialSql(specialSql);
}
if(ValueUtil.stringOfNullAndEmpty(czNo)){
tDataTbNewWithBLOBsEntity.setCzNo("");
}else {
tDataTbNewWithBLOBsEntity.setCzNo(czNo);
}
return dataLogUtil.itDataTbNewService.add1(tDataTbNewWithBLOBsEntity);
}catch (Exception e) {
e.getMessage();
return false;
}
}
/**
* @param bilId
* @param tdNo
* @param boxNo
* @param bilNo
* @param preItm
* @param itm
* @param fldNo
* @param fldName
* @param oldvalue
* @param newvalue
* @param usr
* @param compno
* @return
*/
public static Boolean addChgData(String bilId,String tdNo, String boxNo, String bilNo, Integer preItm, Integer itm, String fldNo,String fldName,String oldvalue,String newvalue,String usr,String compno){
try {
ChgData chgData = new ChgData();
chgData.setBIL_ID(bilId);
if(ValueUtil.stringOfNullAndEmpty(tdNo)){
chgData.setTD_NO("");
}else{
chgData.setTD_NO(tdNo);
}
if(ValueUtil.stringOfNullAndEmpty(boxNo)){
chgData.setBOX_NO("");
}else{
chgData.setBOX_NO(boxNo);
}
if(ValueUtil.stringOfNullAndEmpty(bilNo)){
chgData.setBIL_NO(UUID.randomUUID().toString().toUpperCase());
}else{
chgData.setBIL_NO("<"+bilNo+">"+UUID.randomUUID().toString().toUpperCase());
}
if(preItm == null){
chgData.setPRE_ITM(1);
}else{
chgData.setPRE_ITM(preItm);
}
if(itm == null){
chgData.setITM(1);
}else{
chgData.setITM(itm);
}
if(ValueUtil.stringOfNullAndEmpty(fldNo)){
chgData.setFLD_NO("");
}else{
chgData.setFLD_NO(fldNo);
}
if(ValueUtil.stringOfNullAndEmpty(fldName)){
chgData.setFLD_NAME("");
}else{
chgData.setFLD_NAME(fldName);
}
if(ValueUtil.stringOfNullAndEmpty(oldvalue)){
chgData.setOLDVALUE("");
}else{
chgData.setOLDVALUE(oldvalue);
}
if(ValueUtil.stringOfNullAndEmpty(newvalue)){
chgData.setNEWVALUE("");
}else{
chgData.setNEWVALUE(newvalue);
}
if(ValueUtil.stringOfNullAndEmpty(usr)){
chgData.setUSR("");
}else{
chgData.setUSR(usr);
}
if(ValueUtil.stringOfNullAndEmpty(compno)){
chgData.setCOMPNO("");
}else{
chgData.setCOMPNO(compno);
}
return dataLogUtil.iChgDataService.addChgData(chgData);
}catch (Exception e) {
e.getMessage();
return false;
}
}
public static String mapToStr(Map<String,Object> map){
//将MAP转换为字符串
String str="";
Collection values = map.values();
for(Object object:values){
str+=object+"||";
}
return str;
}
public static String changeSql(String val){
String newVal="";
if(!ValueUtil.stringOfNullAndEmpty(val)){
//去除换行和空格
// val= val.replaceAll("\r|\n","").replaceAll("\\s*","");
val= val.replaceAll("\\r|\n","");
if(val.contains("{call")){//不适用转换JSON数据的存储过程，传JSON数据请参考 saveSP_ENTERBOX() 方法
//先转换单引号
val = val.replaceAll("'","").replaceAll("\"","").replaceAll("/","");
//第二步，掐头去尾
String before = val.substring(0,val.indexOf("(")+1);
val = val.replace(before,"");
String after = val.substring(val.lastIndexOf(")"),val.length());
val = val.replace(after,"");
//第三步
String str1 = "";
String[] strArr = val.split(",");
for (String forStr:strArr) {
str1 = str1 + " '" +forStr.trim() + "' ,";
}
str1 = str1.substring(0,str1.length()-1);
newVal = "exec "+ before.replace("{call","").replace("(","").trim()+ " " + str1;
} else if(val.contains("update") || val.contains("Update") || val.contains("UPDATE")){//修改
//第一步 清掉单引号
val = val.replaceAll("'","");
//第二步 将set和where转为小写
val = val.replaceAll("SET","set").replaceAll("WHERE","where").replaceAll("AND","and").replaceAll("And","and");
//第三步 获取set之前的字符串
String str1 = val.substring(0,val.indexOf("set"));
newVal = val.replace(str1,"");
//第四步 获取set之后，where之前的字符串(不含set)
String str2 = newVal.substring(0,newVal.indexOf("where"));
newVal = newVal.replace(str2,"");
str2 = str2.replace("set","");
//第五步 获取where之后的字符串(不含where)
String str3 = newVal.replace("where","");
//处理str2
String[] strArr = str2.split(",");
String str21 = "";
for (String forStr1:strArr) {
if (forStr1.contains("GETDATE") || forStr1.contains("getdate")){
str21 += forStr1.trim()+",";
}else{
String[] strArr2 = forStr1.split("=");
String str22 = "";
for (String forStr2:strArr2) {
//判断=之后的值如果是时间戳则需要转换时间格式
Boolean isTime = false;
forStr2 = forStr2.trim();
if(forStr2.trim().length() == 13){
Pattern pattern = Pattern.compile("\\d{13}");
Matcher isNum = pattern.matcher(forStr2);
if(isNum.matches()){
if("16,17,18,19,20".contains(forStr2.trim().substring(0,2))){
isTime = true;
}
}
}
if(isTime){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date millisecondDate= new Date(Long.valueOf(forStr2.trim()));
forStr2 = sdf.format(millisecondDate);
}
str22 += forStr2.trim() + "=";
}
str22 = str22.substring(0,str22.length()-1);
str22 = str22.replace("=","='");
str21 += str22.trim()+"',";
}
}
//去掉最后一位
str21 = str21.substring(0,str21.length()-1);
//处理str3
String[] strArr2 = str3.split("and");
String str31 = "";
for (String forStr1:strArr2) {
if (forStr1.contains("GETDATE") || forStr1.contains("getdate")){
str31 += forStr1.trim()+" and ";
}else{
String[] strArr3 = forStr1.split("=");
String str32 = "";
for (String forStr2:strArr3) {
//判断=之后的值如果是时间戳则需要转换时间格式
Boolean isTime = false;
forStr2 = forStr2.trim();
if(forStr2.trim().length() == 13){
Pattern pattern = Pattern.compile("\\d{13}");
Matcher isNum = pattern.matcher(forStr2);
if(isNum.matches()){
if("16,17,18,19,20".contains(forStr2.trim().substring(0,2))){
isTime = true;
}
}
}
if(isTime){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date millisecondDate= new Date(Long.valueOf(forStr2.trim()));
forStr2 = sdf.format(millisecondDate);
}
str32 += forStr2.trim() + "=";
}
str32 = str32.substring(0,str32.length()-1);
str32 = str32.replace("=","='");
str31 += str32.trim()+"' and ";
}
}
//去掉最后四位
str31 = str31.substring(0,str31.length()-4);
str1 = str1.replace("update","UPDATE ").replace("UPDATE","UPDATE ").replace("Update","UPDATE ");
newVal = str1 + " set " + str21 + " where " + str31;
} else if(val.contains("insert") || val.contains("INSERT") || val.contains("Insert")){//新增
//替换insert语句中的select
if(val.contains("select") || val.contains("SELECT") || val.contains("Select")){
val = val.replaceAll("select","VALUES(").replaceAll("SELECT","VALUES(");
val = val + ")";
}
String str = val.replaceAll("'","");
String beforeStr = "";
if(val.contains("VALUES")){
beforeStr = val.substring(0,val.indexOf("VALUES"));
}
if(val.contains("values")){
beforeStr = val.substring(0,val.indexOf("values"));
}
str = val.replace(beforeStr,"");
str = str.substring(str.indexOf("(")+1,str.length()).trim();
str = str.substring(0,str.lastIndexOf(")")).trim();
String[] strArr1 = str.split(",");
String str1 = "";
for (String forStr1:strArr1) {
Boolean isTime = false;
forStr1 = forStr1.trim();
if(forStr1.trim().length() == 13){
Pattern pattern = Pattern.compile("\\d{13}");
Matcher isNum = pattern.matcher(forStr1);
if(isNum.matches()){
if("16,17,18,19,20".contains(forStr1.trim().substring(0,2))){
isTime = true;
}
}
}
if(isTime){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date millisecondDate= new Date(Long.valueOf(forStr1.trim()));
forStr1 = sdf.format(millisecondDate);
}
if(forStr1.contains("VALUES") || forStr1.contains("values")){
forStr1 = forStr1.replace("VALUES","").replace("values","").trim();
}
if (forStr1.contains("GETDATE") || forStr1.contains("getdate")){
str1 += forStr1.trim()+",";
}else{
str1 += "'"+forStr1.trim()+"',";
}
}
str1 = str1.substring(0,str1.length()-1);
newVal =beforeStr + "VALUES("+ str1 +")" ;
} else if(val.contains("delete") || val.contains("DELETE") || val.contains("Delete")){//删除
String str = val.replaceAll("'","");
if(val.contains("delete")){
val = val.replace("delete","DELETE ").replace("DELETE","DELETE ").replace("Delete","DELETE ");
}
String beforeStr = "";
if(val.contains("WHERE")){
beforeStr = val.substring(0,val.indexOf("WHERE"));
}
if(val.contains("where")){
beforeStr = val.substring(0,val.indexOf("where"));
}
if(val.contains("Where")){
beforeStr = val.substring(0,val.indexOf("Where"));
}
str = val.replace(beforeStr,"");
str = str.replace("WHERE","");
str = str.replace("where","");
str = str.replace("Where","");
String[] strArr1 = str.replaceAll("AND","and").replaceAll("And","and").split("and");
String str1 = "";
for (String forStr1:strArr1) {
if (forStr1.contains("GETDATE") || forStr1.contains("getdate")){
str1 += forStr1.trim()+",";
}else{
String[] strArr2 = forStr1.split("=");
String str2 = "";
for (String forStr2:strArr2) {
//判断=之后的值如果是时间戳则需要转换时间格式
Boolean isTime = false;
forStr2 = forStr2.trim();
if(forStr2.trim().length() == 13){
Pattern pattern = Pattern.compile("\\d{13}");
Matcher isNum = pattern.matcher(forStr2);
if(isNum.matches()){
if("16,17,18,19,20".contains(forStr2.trim().substring(0,2))){
isTime = true;
}
}
}
if(isTime){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date millisecondDate= new Date(Long.valueOf(forStr2.trim()));
forStr2 = sdf.format(millisecondDate);
}
str2 += forStr2.trim() + "=";
}
str2 = str2.substring(0,str2.length()-1);
str2 = str2.replace("=","='");
str1 += str2.trim()+"' and ";
}
}
str1 = str1.substring(0,str1.length()-4);
newVal = beforeStr + " where " + str1;
}
}
return newVal;
}
//区位设置转换
public static String changeSqlByQW(String val){
String newVal="";
if(!ValueUtil.stringOfNullAndEmpty(val)){
//去除换行和空格
val= val.replaceAll("\\r|\n","");
newVal = val.replace("{call","exec")
.replace("exec","exec ")
.replace(",","',")
.replace("=","='")
.replace("where","' where")
.replace("and","' and")
.replace(")}","'");
}
return newVal;
}
public static String changeSqlByQW2(String val){
String newVal="";
if(!ValueUtil.stringOfNullAndEmpty(val)){
//去除换行和空格
val= val.replaceAll("\\r|\n","");
newVal = val.replace("{call","exec")
.replace("exec","exec ")
.replace(")}","'");
}
return newVal;
}
//出场确认转换
public static String changeSqlByvalidating(String val){
String newVal="";
if(!ValueUtil.stringOfNullAndEmpty(val)){
val= val.replaceAll("\\r|\n","");
newVal = val.replace("{call","exec ")
.replaceFirst("\\(","'")
.replaceFirst("\\'","")
.replaceFirst("\\'","")
.replace(")}","'");
}
return newVal;
}
public static String changeJsonSql(String val){
String newVal="";
if(!ValueUtil.stringOfNullAndEmpty(val)){
val= val.replaceAll("\\r|\n","");
newVal = val.replace("{call","exec")
.replace("exec","exec ")
.replace("(","'")
.replace("},{","}','{")
.replace("},","}','")
.replace(")}","'");
}
System.out.println("转换"+newVal);
return newVal;
}
//对象转map
public static Map<String,Object> objectToMap(Object object){
Map<String,Object> retMap = new HashMap<>();
retMap = JSON.parseObject(JSON.toJSONString(object), new TypeReference<Map<String,Object>>() {});
return retMap;
}
/**
*
* @param bilNo 同步表主键
* @param bilNo1 同步表主键
* @param bilTable 操作表名或方法说明
* @param specialSql 同步语句
* @param czNo 站点号
* @return
*/
public static Boolean addPerformSQLStoredProcedure(String bilNo,Integer bilItm, String bilNo1, String bilTable, String specialSql, String czNo){
try {
Map<String,Object> map = new HashMap<>();
SimpleDateFormat sdf = new SimpleDateFormat("YYYYMMddHHmmssSSS");
map.put("BIL_NO","BS"+sdf.format(new Date()));
map.put("BIL_ITM",bilItm==null?1:bilItm);
String bil = "";
if(bilNo != null ){
bil = bilNo;
}
if(bilNo1 != null ){
if(bil.trim().length() > 0){
bil = bil +"/"+bilNo1;
}else{
bil = bilNo1;
}
}
map.put("BIL_NO1",bil);
map.put("BIL_TABLE",bilTable);
map.put("Specail_SQL",specialSql);
map.put("Specail_SQL1","");
map.put("DNAME","");
map.put("CZ_NO",czNo);
map.put("MOD",0);
return dataLogUtil.itDataTbNewService.performSQLStoredProcedure(map);
}catch (Exception e) {
e.getMessage();
return false;
}
}
}
5.MyBatisSqlUtils.java
import java.util.List;
import java.util.Map;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ParameterMode;
import org.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.ReflectorFactory;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.reflection.property.PropertyTokenizer;
import org.apache.ibatis.scripting.xmltags.ForEachSqlNode;
import org.apache.ibatis.session.SqlSessionFactory;
public class MyBatisSqlUtils {
/**
* 运行期获取MyBatis执行的SQL及参数
* @param id Mapper xml 文件里的select Id
* @param parameterMap 参数
* @param sqlSessionFactory
* @return
*/
public static MyBatisSql getMyBatisSql(String id, Map<String,Object> parameterMap, SqlSessionFactory sqlSessionFactory) {
MyBatisSql ibatisSql = new MyBatisSql();
MappedStatement ms = sqlSessionFactory.getConfiguration().getMappedStatement(id);
BoundSql boundSql = ms.getBoundSql(parameterMap);
ibatisSql.setSql(boundSql.getSql());
List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
if (parameterMappings != null)
{
Object[] parameterArray = new Object[parameterMappings.size()];
ParameterMapping parameterMapping = null;
Object value = null;
Object parameterObject = null;
MetaObject metaObject = null;
PropertyTokenizer prop = null;
String propertyName = null;
String[] names = null;
for (int i = 0; i < parameterMappings.size(); i++)
{
parameterMapping = parameterMappings.get(i);
if (parameterMapping.getMode() != ParameterMode.OUT)
{
propertyName = parameterMapping.getProperty();
names = propertyName.split("\\.");
if(propertyName.indexOf(".") != -1 && names.length == 2)
{
parameterObject = parameterMap.get(names[0]);
propertyName = names[1];
}
else if(propertyName.indexOf(".") != -1 && names.length == 3)
{
parameterObject = parameterMap.get(names[0]); // map
if(parameterObject instanceof Map)
{
parameterObject = ((Map)parameterObject).get(names[1]);
}
propertyName = names[2];
}
else
{
parameterObject = parameterMap.get(propertyName);
}
metaObject = parameterMap == null ? null : MetaObject.forObject(parameterObject, SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY,new DefaultReflectorFactory());
prop = new PropertyTokenizer(propertyName);
if (parameterObject == null)
{
value = null;
}
else if (ms.getConfiguration().getTypeHandlerRegistry().hasTypeHandler(parameterObject.getClass()))
{
value = parameterObject;
}
else if (boundSql.hasAdditionalParameter(propertyName))
{
value = boundSql.getAdditionalParameter(propertyName);
}
else if (propertyName.startsWith(ForEachSqlNode.ITEM_PREFIX) && boundSql.hasAdditionalParameter(prop.getName()))
{
value = boundSql.getAdditionalParameter(prop.getName());
if (value != null)
{
value = MetaObject.forObject(value,SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY,new DefaultReflectorFactory()).getValue(propertyName.substring(prop.getName().length()));
}
}
else
{
value = metaObject == null ? null : metaObject.getValue(propertyName);
}
parameterArray[i] = value;
}
}
ibatisSql.setParameters(parameterArray);
}
return ibatisSql;
}
}
6.MyBatisSql.java
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
public class MyBatisSql {
/**
* 运行期 sql
*/
private String sql;
/**
* 参数 数组
*/
private Object[] parameters;
public void setSql(String sql) {
this.sql = sql;
}
public String getSql() {
return sql;
}
public void setParameters(Object[] parameters) {
this.parameters = parameters;
}
public Object[] getParameters() {
return parameters;
}
@Override
public String toString() {
if(parameters == null || sql == null)
{
return "";
}
List<Object> parametersArray = Arrays.asList(parameters);
List<Object> list = new ArrayList<Object>(parametersArray);
while(sql.indexOf("?") != -1 && list.size() > 0 && parameters.length > 0)
{
sql = sql.replaceFirst("\\?", list.get(0).toString());
list.remove(0);
}
return sql.replaceAll("(\r?\n(\\s*\r?\n)+)", "\r\n");
}
}
4954
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
