SpringBoot+MyBatis实现动态（Sql存在表中）获取Sql

本文链接：https://blog.csdn.net/messilion/article/details/117355884

在前端传参中携带SQLID，例如：

 this.bidm.request('get-result-by-sql-Key', {SQLID:'test', USERACCOUNT:'10047396'}).then(
      function (res) {
      console.log(res);  
    });

在Controler中通过SqlID获取Sql表中的Sql语句

@BidmMethod(name = "get-result-by-sql-Key")
public BidmResponse getResultBySqlKey(BidmRequest request) throws Exception
{	
        //request包含sqlid和sql中需要的参数
	String sqlId = BidmUtils.getRequestParam(request, "SQLID");
	Map<String, Object> map=new HashMap<>();
	map=request.getParams();
	map.remove("SQLID");
	BidmResponse response = new BidmResponse();
	BidmSQL sql = this.bidmService.getSqlByKey(sqlId);
	//以上通过Mapper传统方式获取sql语句，以下通过SelectProvider动态组建Sql语句
	List<Map<String,Object>> resultMap=queryService.getQueryResult(sql.getSql(), map);
	if(resultMap.size()>0) {
		response.setErrorCode(ErrorCode.BIDM_SUCCESS);
		response.addParam("list", JsonUtils.toJsonList(resultMap));
	}
	else {
		response.setErrorCode(ErrorCode.BIDM_ITEM_NOT_EXISTS);
	}
	return response;
}

Service与Impl无需变更

Mapper.java:

package com.boe.bidm.project.mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.SelectProvider;
import com.boe.bidm.project.selectProvider.SqlProviderUtil;

@Mapper
public interface CommonQueryMapper {

	@SelectProvider(type = SqlProviderUtil.class, method = "commonQuery")
        //注解需声明SqlProviderUtil和方法名，执行返回查询结果
	List<Map<String, Object>> getQueryResult(String sql,Map<String, Object> map) throws Exception;
	
}

SqlProviderUtil工具类

package com.boe.bidm.project.selectProvider;

import java.util.Map;
import java.util.Map.Entry;

public class SqlProviderUtil {
	public String commonQuery(String sql,Map<String, Object> map) 
	{
                //替换Sql语句中的变量
		if(sql.indexOf(":")!=-1)
		{
			for(Entry<String, Object> vo : map.entrySet())
			{
				sql=sql.replaceAll(":"+vo.getKey(), "'"+vo.getValue().toString()+"'");
			}
		}
		else
		{
			
		}
		System.out.println("SQL Content:"+sql);
		return sql;
	}
}

以上，便可通过查询表中的sql，替换变量后即可实现sql查询，这样的好处是如果sql需要变更，且参数不增加，就可只改sql，而不需要发布系统