MyBatis提取带参数的SQL语句

比眉伴天荒 2023-06-15 13:24 3阅读 0赞

本文参照了[https://blog.csdn.net/wooden\_people/article/details/90676121][https_blog.csdn.net_wooden_people_article_details_90676121]中的参数拼接方法

**工具类：**

import com.tydic.common.utils.DateUtils;
    import org.apache.commons.beanutils.BeanMap;
    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.session.SqlSessionFactory;
    
    import java.sql.Timestamp;
    import java.util.*;
    
    public class MyBatisSqlUtils {
    
        public static String execute(String sqlId, Object object, SqlSessionFactory sqlSessionFactory) {
            BoundSql boundSql = sqlSessionFactory.getConfiguration().getMappedStatement(sqlId).getBoundSql(object);
            List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
            String sql = boundSql.getSql();
            Map<?, Object> paramMap = new BeanMap(object);
            List<Object> paramValues = getParamValues(paramMap, parameterMappings);
            String execSql = getExecuteSql(sql, paramValues);
            execSql = execSql.replace("\n","").replaceAll("[ ]+"," ");
            return execSql;
        }
    
        /**
         * 设置查询参数值，返回可直接执行的sql
         */
        private static String getExecuteSql(String sql, List<Object> paramValues) {
            while (sql.indexOf("?") != -1 && paramValues != null && paramValues.size() > 0 && paramValues.get(0) != null) {
                Object paramValue = paramValues.get(0);
                if (paramValue != null) {
                    String value = paramValue.toString();
                    if (paramValue instanceof String) {
                        value = "'" + paramValue.toString() + "'";
                    } else if (paramValue instanceof Date || paramValue instanceof Timestamp) {
                        value = DateUtils.parseDateToStr("yyyy-MM-dd HH:mm:ss", (Date) paramValue);
                        value = "str_to_date('" + value + "','%Y-%m-%d %T')";
                    }
                    sql = sql.replaceFirst("\\?", value);
                    paramValues.remove(0);
                }
            }
    
            return sql;
        }
    
        /**
         * 根据动态查询条件获取查询参数值
         */
        private static List<Object> getParamValues(Map<?, Object> paramMap,
                                                   List<ParameterMapping> parameterMappings) {
            if (parameterMappings == null) {
                return new ArrayList<Object>();
            }
            List<Object> paramValues = new ArrayList<Object>();
            for (ParameterMapping pm : parameterMappings) {
                if (pm.getMode() != ParameterMode.OUT) {
                    String paramName = pm.getProperty();
                    Object paramValue = paramMap.get(paramName);
                    paramValues.add(paramValue);
                }
            }
            return paramValues;
        }
    
    
        /**
         * 初始化查询参数
         */
        private static Map<String, Object> initParamMap(Object[] args) {
            Map<String, Object> paramMap = new HashMap<String, Object>();
            for (Object obj : args) {
                if (obj instanceof Map) {
                    paramMap = (Map<String, Object>) obj;
                }
            }
            return paramMap;
        }
    }

**调用方式Demo:**

@Override
        public Long trialGroup(final DlUserMonth dlUserMonth, CustGroupInfo groupInfo) {
            String statement = "com.tydic.zhApplication.mapper.DlUserMonthMapper.selectDlUserMonthList";
            String sql = "";
            try{
                sql = MyBatisSqlUtils.execute(statement,dlUserMonth,sqlSessionFactory);
            }catch (Exception e){
                e.printStackTrace();
                throw new BusinessException("MyBatis获取sql异常",e);
            }
    //		custGroupInfoMapper.trialGroup();
            return 0L;
        }

[https_blog.csdn.net_wooden_people_article_details_90676121]: https://blog.csdn.net/wooden_people/article/details/90676121