记一次大物流查询插件的开发

来项目组已经一个多月了，oms、wms、tms都有涉及，大物流系统采用原始的hibernate操作数据库，并进行了一系列的封装。单表增删改查比较方便，直接操作对象，和jpa、mybaits plus类似。涉及到多表查询，用供应商的话来讲得写三件套，如下：

作为比较懒的我，这是一种煎熬，也就是上周五我萌生了，依托现有框架，进行无侵入性扩展。类似jpa、mybaits plus，实行注解式sql查询。
我的思路是：

1.弄一个aop切面，切入包名下的包含@Query的接口方法。
2.通过反射获取方法的返回值，和方法的参数对象。
3.实现变量式传参，和判断脚本的识别，用于拼接sql。
4.实现springboot代理，自动实现接口类的实现方法，从而减少写实现类的步骤。
5.那就是开干。

在这里吟诗一首：路漫漫其修远兮，吾将上下而求索！！！！

实现用于aop切面的切点的方法主键Query*

package com.sinoservices.tro.annotation;

/**
 *
 * @author zhanqi
 * @since 2021/7/2 20:37
 */

import java.lang.annotation.*;

@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD})
public @interface Query {
    String value();
}

参数Param("")用于sql参数引用

package com.sinoservices.tro.annotation;

import java.lang.annotation.*;

/**
 * @author zhanqi
 * @since 2021/7/2 20:39
 */
@Target({ElementType.PARAMETER})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Param {
    String value();
}

实现aop类,里面包含了map大写转驼峰,sql变量,sql处理js逻辑脚本
主要的技术就是反射

package com.sinoservices.tro.aspect;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.sinoservices.tro.annotation.Param;
import com.sinoservices.tro.annotation.Query;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.beanutils.BeanUtils;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.script.Invocable;
import javax.script.ScriptEngine;
import javax.script.ScriptEngineManager;
import javax.script.ScriptException;
import java.lang.annotation.Annotation;
import java.lang.reflect.Method;
import java.lang.reflect.Type;
import java.util.*;
import java.util.concurrent.atomic.AtomicBoolean;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author zhanqi
 * @since 2021/7/2 11:07
 * 啥也不说了，就是秀
 */
@Aspect
@Component
@Slf4j
public class QueryAspect {
    private static final char UNDERLINE = '_';
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Pointcut("execution(* com.sinoservices..*(..))&&@annotation(com.sinoservices.tro.annotation.Query)")
    public void fun() {
    }

    @Around("fun()")
    public Object around(ProceedingJoinPoint pjp) throws Throwable {
        MethodSignature methodSignature = (MethodSignature) pjp.getSignature();
        Method method = methodSignature.getMethod();
        Query query = method.getAnnotation(Query.class);
        String sqlTemplate = query.value();
        List<String> sqlParamList = getSqlParamList(sqlTemplate);//获取sql需要替换的参数
        // 参数值
        Object[] args = pjp.getArgs();
        AtomicBoolean isParamBeanModelFlag = new AtomicBoolean(false);//判断是否参数是对象
        if (checkIsBeanModel(args[0]) && args.length == 1) {
            isParamBeanModelFlag.set(true);
        }

        LinkedHashMap<String, Object> map = new LinkedHashMap<>();
        sqlParamList.forEach(sqlParam -> {
            if (isParamBeanModelFlag.get()) {
                JSONObject json = (JSONObject) JSON.toJSON(args[0]);
                putMapByParamBeanModel(json, sqlParam, map);
            } else {
                createSqlReplaceMap(method, sqlParam, map, args);
            }

        });
        for (String key : map.keySet()) {
            sqlTemplate = sqlTemplate.replace(key, map.get(key).toString());
        }
        sqlTemplate = redJsTemp(sqlTemplate);//读取js模版
        System.out.println("sqlTemplate:    " + sqlTemplate);
        Type returnType = method.getGenericReturnType();

        //判断是否是list
        if (returnType.getTypeName().contains("java.util.List")) {
            List list = new ArrayList();
            List<?> jdbcResult = jdbcTemplate.queryForList(sqlTemplate);
            if (jdbcResult != null && !jdbcResult.isEmpty()) {
                for (Object e : jdbcResult) {
                    Object bean = createListBean(returnType);
                    BeanUtils.populate(bean, formatHumpName(keyToLowerCase((Map<String, Object>) e)));
                    list.add(bean);
                }
            }
            return list;
        } else {
            Object obj = createBean(returnType);
            List<?> jdbcResult = jdbcTemplate.queryForList(sqlTemplate);
            if (jdbcResult != null && !jdbcResult.isEmpty()) {
                Map<String, Object> bankMap = formatHumpName(keyToLowerCase((Map<String, Object>) jdbcResult.get(0)));
                BeanUtils.populate(obj, bankMap);
                return obj;
            }
        }
        return null;
    }

    private Map<String, Object> formatHumpName(Map<String, Object> map) {
        Map<String, Object> newMap = new HashMap<>();
        Iterator<Map.Entry<String, Object>> it = map.entrySet().iterator();
        while (it.hasNext()) {
            Map.Entry<String, Object> entry = it.next();
            String key = entry.getKey();
            String newKey = underlineToCamel(key);
            newMap.put(newKey, entry.getValue());
        }
        return newMap;
    }

    /**
     * 下划线转驼峰
     *
     * @param param
     * @return
     */
    private String underlineToCamel(String param) {
        if (param == null || "".equals(param.trim())) {
            return "";
        }
        int len = param.length();
        StringBuilder sb = new StringBuilder(len);
        for (int i = 0; i < len; i++) {
            char c = param.charAt(i);
            if (c == UNDERLINE) {
                if (++i < len) {
                    sb.append(Character.toUpperCase(param.charAt(i)));
                }
            } else {
                sb.append(Character.toLowerCase(param.charAt(i)));
            }
        }
        return sb.toString();
    }


    /**
     * map Key转小写
     *
     * @param orgMap
     * @return
     */
    private Map<String, Object> keyToLowerCase(Map<String, Object> orgMap) {
        Map<String, Object> resultMap = new HashMap<>();

        if (orgMap == null || orgMap.isEmpty()) {
            return resultMap;
        }

        Set<Map.Entry<String, Object>> entrySet = orgMap.entrySet();
        for (Map.Entry<String, Object> entry : entrySet) {
            String key = entry.getKey();
            Object value = entry.getValue();
            resultMap.put(key.toLowerCase(), value);
        }

        return resultMap;
    }
    

    /**
     * @param sqlTemplate 注解里面的sql模版
     * @return 返回需要替换的参数list
     */
    private List<String> getSqlParamList(String sqlTemplate) {
        List<String> sqlParamList = new ArrayList<>();
        String reg = "\\#\\{(.*?)\\}";//定义正则表达式
        Pattern patten = Pattern.compile(reg);//编译正则表达式
        Matcher matcher = patten.matcher(sqlTemplate);// 指定要匹配的字符串

        while (matcher.find()) { //此处find（）每次被调用后，会偏移到下一个匹配
            sqlParamList.add(matcher.group().trim());//获取当前匹配的值
        }
        return sqlParamList;
    }

    /**
     * 创建方法参数是对象的逻辑处理
     *
     * @param json     对象转的json
     * @param sqlParam sql参数
     * @param map      需要替换的sql模版对应的值
     */
    private void putMapByParamBeanModel(JSONObject json, String sqlParam, LinkedHashMap<String, Object> map) {
        String matcherStr = getMatcher(sqlParam, 1);
        String[] filedByIns = matcherStr.split(":");
        if (filedByIns.length > 1) {
            json.forEach((k, v) -> {
                createSqlReplaceMapByIn(sqlParam, map, filedByIns, k, v);
            });
        } else {
            json.forEach((k, v) -> {
                if (k.equals(matcherStr)) {
                    map.put(sqlParam.trim(), replaceSqlByObjType(v));
                }
            });
        }
    }

    /**
     * 创建需要替换的sql模版对应的值(数类型是普通类型))
     *
     * @param isNotBeanValue 如果不是bean,传入的值(isBean=true,此处为null)
     * @param sqlParam       sql参数
     * @param map            需要替换的sql模版对应的值
     */
    private void createSqlReplaceMap(Object isNotBeanValue, String sqlParam, String matcherStr, String paramas, LinkedHashMap<String, Object> map) {
        if (matcherStr.equals(paramas)) {
            map.put(sqlParam.trim(), replaceSqlByObjType(isNotBeanValue));
        }
    }

    /**
     * 创建需要替换的sql模版对应的值(数类型是集合类型的)in(121,212)
     *
     * @param sqlParam   sql参数
     * @param map        需要替换的sql模版对应的值
     * @param filedByIns 参数类型是集合
     * @param key
     * @param value
     */
    private void createSqlReplaceMapByIn(String sqlParam, LinkedHashMap<String, Object> map, String[] filedByIns, Object key, Object value) {
        if (key.equals(filedByIns[1])) {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.append("(");
            JSONArray arr = JSON.parseArray(JSON.toJSONString(value));
            AtomicInteger index = new AtomicInteger();
            arr.forEach(o -> {
                index.set(index.getAndIncrement() + 1);
                stringBuilder.append(replaceSqlByObjType(o));
                if (arr.size() - index.get() > 0) {
                    stringBuilder.append(",");
                }
            });
            stringBuilder.append(")");
            map.put(sqlParam.trim(), stringBuilder.toString());

        }
    }

    /**
     * @param method   方法this
     * @param sqlParam sql参数
     * @param map      需要替换的sql模版对应的值
     * @param args     method里面所有参数对象的值
     */
    private void createSqlReplaceMap(Method method, String sqlParam, LinkedHashMap<String, Object> map, Object[] args) {
        String matcherStr = getMatcher(sqlParam, 1);

        Annotation[][] pas = method.getParameterAnnotations();
        String[] params = new String[pas.length];
        for (int i = 0; i < pas.length; i++) {
            Param pa = (Param) pas[i][0];
            params[i] = pa.value();
        }

        for (int i = 0; i < args.length; i++) {
            String[] filedByIns = matcherStr.split(":");
            if (filedByIns.length > 1) {
                createSqlReplaceMapByIn(sqlParam, map, filedByIns, params[i], args[i]);
            } else {
                if (args[i].getClass().isArray() || args[i] instanceof Collection<?> || args[i] instanceof Map<?, ?>) {
                    continue;
                }
                createSqlReplaceMap(args[i], sqlParam, matcherStr, params[i], map);
            }
        }
    }

    /**
     * 查询list<?>里面的bean完整包名
     *
     * @param ListBeanName 反射获取的list类型名称
     * @return
     */
    private String findListInBeanName(String ListBeanName) {
        String regex_class_list_bean_name = "\\<(.*?)\\>";
        Pattern p = Pattern.compile(regex_class_list_bean_name);
        Matcher m = p.matcher(ListBeanName);
        while (m.find()) {
            return m.group(1);
        }
        return null;
    }

    /**
     * 根据obj类型判断拼接sql需不需要加''
     *
     * @param obj
     * @return
     */
    private Object replaceSqlByObjType(Object obj) {
        if (obj instanceof Integer) {
            return obj;
        } else if (obj instanceof Double) {
            return obj;
        } else if (obj instanceof Float) {
            return obj;
        } else if (obj instanceof Long) {
            return obj;
        } else if (obj instanceof Boolean) {
            return obj;
        } else if (obj instanceof Date) {
            return obj;
        } else {
            return "'" + obj + "'";
        }
    }

    /**
     * 判断是不是bean
     *
     * @param obj
     * @return
     */
    private boolean checkIsBeanModel(Object obj) {
        if (obj instanceof Integer) {
            return false;
        } else if (obj instanceof String) {
            return false;
        } else if (obj instanceof Double) {
            return false;
        } else if (obj instanceof Float) {
            return false;
        } else if (obj instanceof Long) {
            return false;
        } else if (obj instanceof Boolean) {
            return false;
        } else if (obj instanceof Date) {
            return false;
        } else {
            return true;
        }
    }

    /**
     * 根据类型创建同类型的list
     *
     * @param returnType
     * @return
     * @throws Throwable
     */
    private List createList(Type returnType) throws Throwable {
        //截取list里面的bean对象名称
        String beanName = findListInBeanName(returnType.getTypeName());
        Class classType = Class.forName(beanName);
        //创建bean对象
        Object obj = classType.newInstance();
        //建立对象列表list(List同样反射,越过泛型检查)
        List list = new ArrayList();
        Method m = list.getClass().getMethod("add", Object.class);
        m.invoke(list, obj);
        return list;
    }

    /**
     * 根据类型创建同类型的list里面的bean
     *
     * @param returnType
     * @return
     * @throws Throwable
     */
    private Object createListBean(Type returnType) throws Throwable {
        //截取list里面的bean对象名称
        String beanName = findListInBeanName(returnType.getTypeName());
        Class classType = Class.forName(beanName);
        //创建bean对象
        Object obj = classType.newInstance();

        return obj;
    }

    /**
     * 根据类型创建bean
     *
     * @param beanType
     * @return
     * @throws Throwable
     */
    private Object createBean(Type beanType) throws Throwable {
        Class classType = Class.forName(beanType.getTypeName());
        Object obj = classType.newInstance();
        return obj;
    }

    /**
     * 查找#{}中间的字符
     *
     * @param str
     * @param index 0全取 1查找#{}中间的字符
     * @return
     */
    private String getMatcher(String str, Integer index) {
        String reg = "\\#\\{(.*?)\\}";
        Pattern patten = Pattern.compile(reg);
        Matcher matcher = patten.matcher(str);

        while (matcher.find()) {
            return matcher.group(index).trim();
        }
        return null;
    }

    /**
     * 读取js模版
     *
     * @param str
     * @return
     */
    private String redJsTemp(String str) {
        String regJs = "\\<js>(.*?)\\</js>";
        Pattern patten = Pattern.compile(regJs);
        Matcher matcher = patten.matcher(str);
        while (matcher.find()) {
            String jsOldStr = matcher.group();
            if (matcher.group(1) != null) {
                String jsNewStr = " function add(){  " + matcher.group(1) + " } ";
                str = str.replace(jsOldStr, exeJs(jsNewStr));

            }

        }
        return str;
    }

    /**
     * 执行js脚本
     *
     * @param jsStr
     * @return
     */
    private String exeJs(String jsStr) {
        ScriptEngineManager factory = new ScriptEngineManager();
        ScriptEngine engine = factory.getEngineByName("JavaScript");
        //执行js脚本定义函数
        try {
            engine.eval(jsStr);
            Invocable invocable = (Invocable) engine;
            Object res = null;
            try {
                res = invocable.invokeFunction("add");
            } catch (NoSuchMethodException e) {
                e.printStackTrace();
            }
            System.out.println(res);
            return (String) res;
        } catch (ScriptException e) {
            e.printStackTrace();
        }

        return "";
    }

}

下面就是springboot动态代理，自动注册了

首先我们建一个BaseQueryService，然后后续继承这个的接口类实现动态代理注册

package com.sinoservices.tro.service;

/**
 * @author zhanqi
 * @since 2021/7/3 21:40
 */
public interface BaseQueryService {
    void ok();
}

新建BaseQueryService的实现类

package com.sinoservices.tro.service;

/**
 * @author zhanqi
 * @since 2021/7/3 21:41
 */
public class DefaultQueryServiceImpl implements BaseQueryService {
    @Override
    public void ok() {
        System.out.println("ok");
    }
}

定义代理类实现工厂ServiceProxyFactoryBean<T>

package com.sinoservices.tro.proxy;

import org.springframework.beans.factory.FactoryBean;

import java.lang.reflect.Proxy;

/**
 * 定义代理类实现工厂
 * @author zhanqi
 * @since 2021/7/3 21:55
 */
public class ServiceProxyFactoryBean<T> implements FactoryBean<T> {
    private Class<T> interfaces;

    public ServiceProxyFactoryBean(Class<T> interfaces) {
        this.interfaces = interfaces;
    }

    @Override
    @SuppressWarnings("unchecked")
    public T getObject() throws Exception {
        return (T) Proxy.newProxyInstance(interfaces.getClassLoader(), new Class[]{interfaces},
                new ServiceProxy<>(interfaces));
    }

    @Override
    public Class<?> getObjectType() {
        return interfaces;
    }

    @Override
    public boolean isSingleton() {
        return true;
    }
}

定义接口扫描类ServiceInterfacesScanner

package com.sinoservices.tro.proxy;

import com.sinoservices.tro.service.BaseQueryService;
import org.springframework.beans.factory.annotation.AnnotatedBeanDefinition;
import org.springframework.beans.factory.config.BeanDefinitionHolder;
import org.springframework.beans.factory.support.BeanDefinitionRegistry;
import org.springframework.beans.factory.support.GenericBeanDefinition;
import org.springframework.context.annotation.ClassPathBeanDefinitionScanner;
import org.springframework.core.type.AnnotationMetadata;

import java.util.Arrays;
import java.util.Set;

/**
 * 定义接口扫描类
 * @author zhanqi
 * @since 2021/7/3 22:03
 */
public class ServiceInterfacesScanner extends ClassPathBeanDefinitionScanner {

    ServiceInterfacesScanner(BeanDefinitionRegistry registry) {
        //false表示不使用ClassPathBeanDefinitionScanner默认的TypeFilter
        super(registry, false);
    }

    @Override
    protected Set<BeanDefinitionHolder> doScan(String... basePackages) {
        this.addFilter();
        Set<BeanDefinitionHolder> beanDefinitionHolders = super.doScan(basePackages);
        if (beanDefinitionHolders.isEmpty()) {
            throw new NullPointerException("No interfaces");
        }
        this.createBeanDefinition(beanDefinitionHolders);
        return beanDefinitionHolders;
    }

    /**
     * 只扫描顶级接口
     * @param beanDefinition bean定义
     * @return boolean
     */
    @Override
    protected boolean isCandidateComponent(AnnotatedBeanDefinition beanDefinition) {
        AnnotationMetadata metadata = beanDefinition.getMetadata();
        String[] interfaceNames = metadata.getInterfaceNames();
        return metadata.isInterface() && metadata.isIndependent()&& Arrays.asList(interfaceNames).contains(BaseQueryService.class.getName());
    }

    /**
     * 扫描所有类
     */
    private void addFilter() {
        addIncludeFilter((metadataReader, metadataReaderFactory) -> true);
    }

    /**
     * 为扫描到的接口创建代理对象
     *
     * @param beanDefinitionHolders beanDefinitionHolders
     */
    private void createBeanDefinition(Set<BeanDefinitionHolder> beanDefinitionHolders) {
        for (BeanDefinitionHolder beanDefinitionHolder : beanDefinitionHolders) {
            GenericBeanDefinition beanDefinition = ((GenericBeanDefinition) beanDefinitionHolder.getBeanDefinition());
            //将bean的真实类型改变为FactoryBean
            beanDefinition.getConstructorArgumentValues().addGenericArgumentValue(beanDefinition.getBeanClassName());
            beanDefinition.setBeanClass(ServiceProxyFactoryBean.class);
            beanDefinition.setAutowireMode(GenericBeanDefinition.AUTOWIRE_BY_TYPE);
        }
    }

}

定义注册类

package com.sinoservices.tro.proxy;

import org.springframework.beans.BeansException;
import org.springframework.beans.factory.config.ConfigurableListableBeanFactory;
import org.springframework.beans.factory.support.BeanDefinitionRegistry;
import org.springframework.beans.factory.support.BeanDefinitionRegistryPostProcessor;

/**
 * 定义注册类
 * @author zhanqi
 * @since 2021/7/3 21:44
 */
public class ProxyRegister implements BeanDefinitionRegistryPostProcessor {
    private String basePackage;

    public ProxyRegister(String basePackage) {
        this.basePackage = basePackage;
    }

    @Override
    public void postProcessBeanDefinitionRegistry(BeanDefinitionRegistry registry) throws BeansException {
        if (basePackage.equals("")||basePackage==null) {
            return;
        }
        ServiceInterfacesScanner scanner = new ServiceInterfacesScanner(registry);
        scanner.doScan(basePackage);
    }

    @Override
    public void postProcessBeanFactory(ConfigurableListableBeanFactory configurableListableBeanFactory) throws BeansException {

    }
}

定义一个代理类

package com.sinoservices.tro.proxy;

import com.sinoservices.tro.service.DefaultQueryServiceImpl;

import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;

/**
 * 定义一个代理类
 * @author zhanqi
 * @since 2021/7/3 21:48
 */
public class ServiceProxy<T> implements InvocationHandler {
    private Class<T> interfaces;

    ServiceProxy(Class<T> interfaces) {
        this.interfaces = interfaces;
    }

    @Override
    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
        if (method.getDeclaringClass().equals(interfaces)) {
            System.out.println("执行您的方法：" + method.getName());
            return method.getName();
        } else {
            return method.invoke(new DefaultQueryServiceImpl(), args);
        }
    }
}

配置接口扫描路径

package com.sinoservices.tro.config;

import com.sinoservices.tro.proxy.ProxyRegister;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * 配置接口扫描路径
 * @author zhanqi
 * @since 2021/7/3 22:16
 */
@Configuration
public class QueryConfiguration {
    @Bean
    public ProxyRegister proxyRegister() {
        return new ProxyRegister("com.sinoservices");
    }
}

现在到了展现成功的时候了

FeignClient暴露接口

    @PostMapping("/etOrderService/getList")
    List<EtOrderBean> getList(@RequestBody TestBean testBean);

    @GetMapping("/etOrderService/getModel")
    EtOrderBean getModel(@RequestParam("a") String a,@RequestParam("b") String b,@RequestParam("c") String[] c);

写接口的实现方法(testMapper就是支持注解的)

    @Autowired
    private TestQueryService testMapper;
    
   @Override
    public List<EtOrderBean>  getList(TestBean testBean) {
        return testMapper.getList(testBean);
    }

    @Override
    public EtOrderBean  getModel(String a, String b,String[] c) {
        return testMapper.getModel(a,b,c);
    }

关键的来了哈
变量用#{a}
变量是数组#{:a}根据类型自动转in()
需要判断拼接if(‘aa’!=null)return “AND ETTA_CUSTOM_EBCU_NAME_CN IN#{:c}”
就是一段js代码 注意 return 后面的用 " xxxxx "

package com.sinoservices.common.service;

import com.sinoservices.tro.annotation.Param;
import com.sinoservices.tro.annotation.Query;
import com.sinoservices.tro.service.BaseQueryService;
import com.sinoservices.tro.order.bean.EtOrderBean;
import com.sinoservices.tro.order.bean.TestBean;
import java.util.List;

/**
 * @author zhanqi
 * @since 2021/7/3 20:54
 */
public interface TestQueryService extends BaseQueryService {

    @Query("SELECT * from et_task where ETTA_ORDER_NO=#{a}  AND ETTA_LEVEL=#{b} <js>if('aa'!=null)return \"AND ETTA_CUSTOM_EBCU_NAME_CN IN#{:c}\" </js> limit 3  ")
    List<EtOrderBean> getList(TestBean testBean);

    @Query("SELECT * from et_task where ETTA_DO_NO=#{a}  AND ETTA_LEVEL=#{b}  AND ETTA_CUSTOM_EBCU_NAME_CN IN#{:c}  limit 1 ")
    EtOrderBean getModel(@Param("a") String a,@Param("b") String b, @Param("c")String[] c);
}

postman请求看效果