spring boot 1.封装分页插件pagehelp并做统一做返回值 2.封装SQL 3.redis在mybatis里面使用（大综合）

项目结构

1.实体model模块

2.功能模块sb-mybatis

实际操作
1.引入依赖

         <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.1.10</version>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

1.自定义注解分页PageX

package com.example.annotation;

import java.lang.annotation.*;
//自定义注解
@Documented
@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface PageX {
}
1
2
3
4
5
6
7
8
9

2.自定义封装SQL语句注解
Column列

package com.beiyou.annotion;

import java.lang.annotation.*;

@Target({ ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Column {

    String value() default "";
}
1
2
3
4
5
6
7
8
9
10
11

EQ等于

package com.beiyou.annotion;

import java.lang.annotation.*;

@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Table {
    String value() default "";
}

1
2
3
4
5
6
7
8
9
10
11

NQ 不等于

package com.beiyou.annotion;

import java.lang.annotation.*;

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface NE {
    String value() default "";
}

1
2
3
4
5
6
7
8
9
10
11

IN 条件in

package com.beiyou.annotion;

import java.lang.annotation.*;

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface IN {
    String value() default "";
}

1
2
3
4
5
6
7
8
9
10
11

Like 条件like

package com.beiyou.annotion;

import java.lang.annotation.*;

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Like {
    String value() default "";
}

1
2
3
4
5
6
7
8
9
10
11

PK 主键

package com.beiyou.annotion;

import java.lang.annotation.*;

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface PK {
    String value() default "";

    boolean autoIncrement() default true;
}

1
2
3
4
5
6
7
8
9
10
11
12
13

Table 表名

package com.beiyou.annotion;

import java.lang.annotation.*;

@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Table {
    String value() default "";
}

1
2
3
4
5
6
7
8
9
10
11

Ignore 忽略不封装

package com.beiyou.annotion;

import java.lang.annotation.*;

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Ignore {
    String value() default "";
}
1
2
3
4
5
6
7
8
9
10

3.实体
Range查询范围的类，SQL语句封装

package com.beiyou.common;

import java.io.Serializable;

//范围查询的类
public class Range<T> implements Serializable {
    private T left;
    private boolean leftEQ;
    private T right;
    private boolean rightEQ;

    public Range() {

    }

    public Range(T left, boolean leftEQ, T right, boolean rightEQ) {
        this.left = left;
        this.leftEQ = leftEQ;
        this.right = right;
        this.rightEQ = rightEQ;
    }

    public T getLeft() {
        return left;
    }

    public void setLeft(T left) {
        this.left = left;
    }

    public boolean isLeftEQ() {
        return leftEQ;
    }

    public void setLeftEQ(boolean leftEQ) {
        this.leftEQ = leftEQ;
    }

    public T getRight() {
        return right;
    }

    public void setRight(T right) {
        this.right = right;
    }

    public boolean isRightEQ() {
        return rightEQ;
    }

    public void setRightEQ(boolean rightEQ) {
        this.rightEQ = rightEQ;
    }

    public static <T> Range.RangeBuilder<T> builder() {
        return new Range.RangeBuilder<T>();
    }

    public static class RangeBuilder<T> implements Serializable {
        private T left;
        private boolean leftEQ = true;
        private T right;
        private boolean rightEQ = true;

        RangeBuilder() {
        }

        public Range.RangeBuilder<T> left(T left) {
            this.left = left;
            return this;
        }

        public Range.RangeBuilder<T> leftEQ(boolean leftEQ) {
            this.leftEQ = leftEQ;
            return this;
        }

        public Range.RangeBuilder<T> right(T right) {
            this.right = right;
            return this;
        }

        public Range.RangeBuilder<T> rightEQ(boolean rightEQ) {
            this.rightEQ = rightEQ;
            return this;
        }

        public Range<T> build() {
            return new Range<T>(this.left, this.leftEQ, this.right, this.rightEQ);
        }

    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93

HttpResponse统一返回值

package com.beiyou.model;

import lombok.Data;

import java.io.Serializable;

@Data
//{code:0,message:"成功",data:"ok"}
//{code:502,message:"服务器内部错误",data:null}
//T泛型，可以为多种类型
public class HttpResponse<T> implements Serializable {

    //返回码（公司内部拟定）,
    // 0 代码正常，
    // 非0代表错误 : 1,10001,500 。
    private int code;
    //返回信息，错误信息,
    // 0 : "成功"，
    // 非0 : 的时候就是具体的错误信息.
    private String message;

    private T data;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

测试分页的实体

package com.beiyou.model;

import com.beiyou.annotion.*;
import com.beiyou.common.Range;
import lombok.Builder;
import lombok.Data;

@Data
@Table("orderMaster")
@Builder
public class OrderMasterQuery extends AbstractQuery {

    @EQ
    private Long id;

    @IN
    @Column("id")
    private Long[] ids;

    @Like
    private String receiver;

    @NE
    private String phoneNumber;

    @EQ
    @Column("phoneNumber")
    private String phoneNumberEQ;

    @Column("id")
    private Range<Long> idRange;

    @Ignore
    private String sourceFrom;

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

分页当前页，当前页数量

package com.beiyou.model;


import java.io.Serializable;

public abstract class AbstractQuery implements Serializable {

    private Integer pageIndex;
    private Integer pageSize;
    private String orderByBlock; // " a desc,b asc,c asc"


    public Integer getPageIndex() {
        return pageIndex;
    }

    public void setPageIndex(Integer pageIndex) {
        this.pageIndex = pageIndex;
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }

    public String getOrderByBlock() {
        return orderByBlock;
    }

    public void setOrderByBlock(String orderByBlock) {
        this.orderByBlock = orderByBlock;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

4.配置分页插件

package com.example.config;

import com.github.pagehelper.PageInterceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Configuration
public class MybatisConfig {
    /*
    * 自己注册sqlsessionfactory,目的是增强功能，添加分页插件
    * datasource代表数据源
    * */
    @Bean
    public SqlSessionFactory getSqlSessionFactory(DataSource ds) throws Exception {
        SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
        //设置数据源
        factory.setDataSource(ds);
        //把分页插件设置到SqlSessionFactory插件库
        factory.setPlugins(new PageInterceptor());
        //返回具体实例对象SqlSessionFactory
        // SqlSessionFactoryBean,Bean在后的特殊Bean需要调用getObject()返回Bean
        SqlSessionFactory object = factory.getObject();
        return object;

    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

5.配置redis序列化，解决乱码

package com.example.config;

import com.fasterxml.jackson.annotation.JsonAutoDetect;
import com.fasterxml.jackson.annotation.PropertyAccessor;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.redis.connection.RedisConnectionFactory;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.data.redis.serializer.Jackson2JsonRedisSerializer;
import org.springframework.data.redis.serializer.StringRedisSerializer;

@Configuration
public class RedisConfig {

    @Bean(name = "redisTemplate")
    public RedisTemplate<Object, Object> redisTemplate(RedisConnectionFactory redisConnectionFactory) {
        RedisTemplate<Object, Object> template = new RedisTemplate<>();
        template.setConnectionFactory(redisConnectionFactory);

        //定制化  专为值定制的序列化工具
        Jackson2JsonRedisSerializer jacksonSerializer = new Jackson2JsonRedisSerializer(Object.class);
        ObjectMapper om = new ObjectMapper();
        om.setVisibility(PropertyAccessor.ALL, JsonAutoDetect.Visibility.ANY);
        om.enableDefaultTyping(ObjectMapper.DefaultTyping.NON_FINAL);
        jacksonSerializer.setObjectMapper(om);
        //转为key设置的序列化工具
        StringRedisSerializer stringRedisSerializer = new StringRedisSerializer();

        //设置定制化的序列化工具
        // 在使用注解@Bean返回RedisTemplate的时候，同时配置hashKey与hashValue的序列化方式。
        // key采用String的序列化方式
        template.setKeySerializer(stringRedisSerializer);
        template.setValueSerializer(jacksonSerializer);
        // hash的key也采用String的序列化方式
        template.setHashKeySerializer(stringRedisSerializer);
        template.setHashValueSerializer(jacksonSerializer);
        template.afterPropertiesSet();
        //
        return template;
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

6.配置application.properties

#连接数据库 
spring.datasource.url=jdbc:mysql://localhost:3303/mall?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=@root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
logging.level.com.example=debug
#配置自定义注解
pointcut.property=@annotation(com.example.annotation.PageX)

#redis配置
#redis服务器地址
spring.redis.host=192.168.43.1
#端口号
spring.redis.port=6379
#redis服务器密码默认为空 
#spring.redis.password=
#redis最大连接数
spring.redis.lettuce.pool.max-active=16
#连接池最大阻塞等待时间，（使用负数表示没有限制）
spring.redis.lettuce.pool.max-wait=-1
#连接池的最大空闲时间，默认为8
spring.redis.lettuce.pool.max-idle=8
#连接池的最小空闲时间，默认为8
spring.redis.lettuce.pool.min-idle=0
#连接超时时间
spring.redis.timeout=30000
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

7.编写分页拦截器

package com.example.interceptor;


import cn.hutool.core.util.ObjectUtil;
import cn.hutool.json.JSONUtil;
import com.beiyou.model.AbstractQuery;
import com.github.pagehelper.PageHelper;
import lombok.extern.slf4j.Slf4j;
import org.aopalliance.intercept.MethodInterceptor;
import org.aopalliance.intercept.MethodInvocation;

import java.lang.reflect.Method;

@Slf4j
public class MapperInterceptor implements MethodInterceptor {
    @Override
    public Object invoke(MethodInvocation invocation) throws Throwable {
        //当前拦截的方法
        Method method = invocation.getMethod();
        //当前拦截方法的参数
        Object[] arguments = invocation.getArguments();
        //获取第一个参数，因为我们Mapper对应的方法只有一个参数
        Object arg0 = arguments[0];
        // 判断是否是Query对象
        if (arg0 instanceof AbstractQuery) {
            //强转 AbstractQuery， 目的是获取参数，是否要分页
            AbstractQuery query = (AbstractQuery) arg0;
            Integer pageIndex = query.getPageIndex(); //参数，第几页。
            Integer pageSize = query.getPageSize();// 参数，每页多少条。
            //判断不为空
            if (ObjectUtil.isNotEmpty(pageIndex) && ObjectUtil.isNotEmpty(pageSize)) {
                //启用分页，查询第pageIndex页，每页pageSize条
                PageHelper.startPage(pageIndex, pageSize);
            }
        }
        //执行这个方法
        Object value = invocation.proceed();
        log.info("method:{},args:{},value:{}", method.getName(), JSONUtil.toJsonStr(arguments), value);
        return value;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

8.注册拦截器

package com.example.config;

import com.example.interceptor.MapperInterceptor;
import org.springframework.aop.aspectj.AspectJExpressionPointcutAdvisor;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class AopConfig {

    @Value("${pointcut.property}")
    private String expression;

    @Bean
    public AspectJExpressionPointcutAdvisor aspectJExpressionPointcutAdvisor() {
        AspectJExpressionPointcutAdvisor advisor = new AspectJExpressionPointcutAdvisor();

        advisor.setExpression(expression);
        //注入要拦截的bean
        advisor.setAdvice(new MapperInterceptor());
        return advisor;
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

9.集成分页做统一返回值

package com.example.advice;

import cn.hutool.core.lang.Dict;
import cn.hutool.core.util.StrUtil;

import cn.hutool.json.JSONUtil;
import com.beiyou.model.HttpResponse;
import com.github.pagehelper.Page;
import org.springframework.core.MethodParameter;
import org.springframework.http.MediaType;
import org.springframework.http.converter.StringHttpMessageConverter;
import org.springframework.http.server.ServerHttpRequest;
import org.springframework.http.server.ServerHttpResponse;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.RestControllerAdvice;
import org.springframework.web.servlet.mvc.method.annotation.ResponseBodyAdvice;

import javax.servlet.http.HttpServletRequest;

//basePackages 设置统一拦截基础包名
@RestControllerAdvice(basePackages = {"com.example.controller"})
public class MyResponseBodyAdvice implements ResponseBodyAdvice {
    @Override
    public boolean supports(MethodParameter returnType, Class converterType) {
        return true;//统一做拦截
    }
    //处理返回异常
    @ExceptionHandler(value = Exception.class)
    public Object defaultErrorHandler(HttpServletRequest req, Exception ex) {

        HttpResponse<Object> httpResponse = new HttpResponse<>();
        httpResponse.setCode(1);
        httpResponse.setMessage(ex.getMessage());
        return httpResponse;
    }

    @Override
    public Object beforeBodyWrite(Object body,
                                  MethodParameter returnType,
                                  MediaType selectedContentType,
                                  Class selectedConverterType,
                                  ServerHttpRequest request,
                                  ServerHttpResponse response) {
        HttpResponse<Object> httpResponse = new HttpResponse<>();
        String message = StrUtil.EMPTY;//初始值空值
        httpResponse.setCode(0);
        httpResponse.setMessage(message);
        //body是否是Page类型
        if (body instanceof Page) {
            Page page = (Page) body;
            int pages = page.getPages();//总页数
            long total = page.getTotal();//总条数
            //hutool通过链式构造，创建Dict对象，同时可以按照Map的方式使用。
            Dict dic = Dict.create()
                    .set("total", total)
                    .set("pages", pages)
                    .set("items", page);
            httpResponse.setData(dic);
        } else {
            httpResponse.setData(body);
        }
        //处理返回字符串
        if (selectedConverterType == StringHttpMessageConverter.class) {
            return JSONUtil.toJsonStr(httpResponse);
        } else {
            return httpResponse;
        }

    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

10.封装SQL语句

package com.example.util;

import cn.hutool.core.annotation.AnnotationUtil;
import cn.hutool.core.lang.Assert;
import cn.hutool.core.util.ClassUtil;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import com.beiyou.annotion.*;
import com.beiyou.common.Range;
import com.beiyou.model.AbstractQuery;

import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.HashSet;

public class SqlGen {

    public static final String select = "select";
    public static final String insert = "insert";
    public static final String update = "update";
    public static HashMap<String, String> selectMap = new HashMap<>();

    private static final HashSet<String> pageFileds = new HashSet<String>() {
        {
            add("pageIndex");
            add("pageSize");
            add("orderByBlock");
        }
    };

    //构建update语句
    // update 表名 set a = #{a}，b = #{b} where id = #{id}
    public String update(Object model) {

        Class<?> modelClass = model.getClass();
        StringBuilder sb = new StringBuilder();
        sb.append(" <script>");
        sb.append(" update ");
        String tableName = AnnotationUtil.getAnnotationValue(modelClass, Table.class, "value");
        Assert.notEmpty(tableName, "{} 表名未设置", modelClass.getName());
        sb.append(tableName);
        sb.append(" set ");
        String idString = "";
        Field[] fields = ReflectUtil.getFields(modelClass);
        for (Field field : fields) {
            Ignore ignore = AnnotationUtil.getAnnotation(field, Ignore.class);
            if (ignore != null) {
                continue;
            }
            String fieldName = field.getName();
            String cloumnName = fieldName;
            String cloumnName1 = AnnotationUtil.getAnnotationValue(field, Column.class, "value");
            if (StrUtil.isNotEmpty(cloumnName1)) {
                cloumnName = cloumnName1;
            }
            PK pk = AnnotationUtil.getAnnotation(field, PK.class);
            if (pk != null) {
                idString = StrUtil.format(" {} = #{{}}", cloumnName, fieldName);
                continue;
            }

            sb.append(StrUtil.format(" {} = #{{}},", cloumnName, fieldName));

        }
        sb.deleteCharAt(sb.toString().length() - 1);
        sb.append(" where  ");
        sb.append(idString);
        sb.append("</script>");
        System.out.println(sb.toString());
        return sb.toString().trim();
    }

    //构建insert语句
    public String insert(Object model) {
        //xml :
        // insert into A (a,b,c) values (#{a},#{b},#{c});

        Class<?> modelClass = model.getClass();
        StringBuilder sb = new StringBuilder();
        sb.append(" <script>");
        sb.append(" insert into  ");
        //通过对象头上注解获取表名称
        String tableName = AnnotationUtil.getAnnotationValue(modelClass, Table.class, "value");
        //断言 表名不能为空
        Assert.notEmpty(tableName, "{}没有设置数据库对应的表名", modelClass);

        sb.append(tableName);
        sb.append(" ( ");
        //开始组装 （数据库字段组合： a,b,c,d）
        Field[] fields = ReflectUtil.getFields(modelClass);
        for (Field field : fields) {
            Ignore ignore = AnnotationUtil.getAnnotation(field, Ignore.class);
            if (ignore != null) {
                continue;
            }
            //判断书否是自增主键
            PK pk = AnnotationUtil.getAnnotation(field, PK.class);
            if (pk != null && pk.autoIncrement() == true) {
                continue;
            }
            String fieldName = field.getName(); //获取字段名称
            String columnName = fieldName;     //默认数据表列名为字段名称
            Column column = AnnotationUtil.getAnnotation(field, Column.class);
            if (column != null) {
                columnName = column.value();
            }
            sb.append(columnName);

            sb.append(",");
        }
        sb.deleteCharAt(sb.toString().length() - 1);
        sb.append(")");
        sb.append(" values ");
        sb.append(" ( ");
        //开始组装值 （#{a},#{b},#{c}）
        fields = ReflectUtil.getFields(modelClass);
        for (Field field : fields) {
            Ignore ignore = AnnotationUtil.getAnnotation(field, Ignore.class);
            if (ignore != null) {
                continue;
            }
            //判断书否是自增主键
            PK pk = AnnotationUtil.getAnnotation(field, PK.class);
            if (pk != null && pk.autoIncrement() == true) {
                continue;
            }
            String filedName = field.getName();
            sb.append(StrUtil.format("#{{}}", filedName));
            sb.append(",");
        }

        sb.deleteCharAt(sb.toString().length() - 1);
        sb.append(" ) ");
        sb.append(" </script> ");
        System.out.println(sb.toString());
        return sb.toString().trim();
    }

    //构建查询语句
    public String select(AbstractQuery query) {

        String queryClassName = query.getClass().getName();
        //如果缓存里面有对应的sql语句，直接return;
        if (selectMap.containsKey(queryClassName)) {
            return selectMap.get(queryClassName);
        }

        String modelClassName = StrUtil.sub(queryClassName, 0, queryClassName.length() - 5);

        String tableName = "";
        Class<?> modelClass = ClassUtil.loadClass(modelClassName); // OrderMaster.class
        //获取表名， 可以通过 orderMaster上注解，也可以通过OrderMasterQuery上注解
        Table table = AnnotationUtil.getAnnotation(modelClass, Table.class);
        if (table != null) {
            tableName = table.value();
        } else {
            table = AnnotationUtil.getAnnotation(query.getClass(), Table.class);
            if (table != null) {
                tableName = table.value();
            }
        }
        Assert.notNull(tableName, "{} 没有设置具体表名", modelClassName);

        StringBuilder sb = new StringBuilder();
        sb.append(" <script> ");
        sb.append(" select   ");
        Field[] fields0 = ReflectUtil.getFields(modelClass);
        for (Field field : fields0) {
            Ignore ignore = AnnotationUtil.getAnnotation(field, Ignore.class);
            if (ignore != null) {
                continue;
            }

            String fieldName = field.getName(); //获取字段名称
            String columnName = fieldName;     //默认数据表列名为字段名称
            Column column = AnnotationUtil.getAnnotation(field, Column.class);
            if (column != null) {
                columnName = column.value();
            }
            // 生成的 是  select a, b1 as b from ......
            if (fieldName.equalsIgnoreCase(columnName)) {
                sb.append(StrUtil.format(" {},", columnName));
            } else {
                sb.append(StrUtil.format("{} as {},", columnName, fieldName));
            }

        }

        sb.deleteCharAt(sb.toString().length() - 1);
        sb.append("  from  ");
        sb.append(tableName);
        sb.append(" <where> ");
        sb.append("  1 = 1");

        Field[] fields = ReflectUtil.getFields(query.getClass());
        for (Field field : fields) {
            //过滤掉这两个字段("pageIndex");("pageSize");
            if (pageFileds.contains(field.getName())) {
                continue;
            }
            Ignore ignore = AnnotationUtil.getAnnotation(field, Ignore.class);
            if (ignore != null) {
                continue;
            }
            String filedName = field.getName(); //获取字段名称
            String columnName = filedName;      //默认数据库对应的列名也是字段名称
            Column column = AnnotationUtil.getAnnotation(field, Column.class);
            if (column != null) {
                columnName = column.value(); //如果这个字段有Column注解，以Column注解名称为主
            }
            // 如果不加注解默认使用 =
            Annotation[] anns = AnnotationUtil.getAnnotations(field, true);
            if (anns.length == 0 || (anns.length == 1 && column != null)) {
                if (!ClassUtil.isAssignable(Range.class, field.getType())) {
                    sb.append(String.format("\n<if test=\"%s != null\">" +
                            " \n <![CDATA[  AND %s = #{%s} ]]>\n" +
                            "  \n</if>", filedName, columnName, filedName));

                    continue;
                }
            }
            //处理 =  符号
            EQ eq = AnnotationUtil.getAnnotation(field, com.beiyou.annotion.EQ.class);

            if (eq != null) {
                if (eq != null) {
                    sb.append(String.format("\n<if test=\"%s != null\">" +
                            " \n <![CDATA[  AND %s = #{%s} ]]>\n" +
                            "  \n</if>", filedName, columnName, filedName));
                }
            }
            // 处理 in
            IN in = AnnotationUtil.getAnnotation(field, com.beiyou.annotion.IN.class);
            if (in != null) {
                sb.append(StrUtil.format(" <if test='{} != null and {}.length > 0 '> \n", filedName, filedName));
                sb.append(StrUtil.format("and  {} in \n", columnName));
                sb.append(StrUtil.format(" <foreach item='item' collection='{}' open='('  close=')'  separator=','> \n", filedName));
                sb.append(StrUtil.format("  #{item}\n"));
                sb.append(StrUtil.format(" </foreach> \n"));
                sb.append(StrUtil.format(" </if> \n"));
            }
            //处理 like
            Like like = AnnotationUtil.getAnnotation(field, Like.class);
            if (like != null) {
                sb.append(StrUtil.format("\n<if test=\"{} != null and {} !='' \">" +
                        " \n <![CDATA[  AND {} like concat('%',#{{}},'%') ]]>\n" +
                        "  \n</if>", filedName, filedName, columnName, filedName));
            }

            //处理 !=
            NE ne = AnnotationUtil.getAnnotation(field, NE.class);
            if (ne != null) {
                sb.append(String.format("\n<if test=\"%s != null\">" +
                        " \n <![CDATA[  AND %s != #{%s} ]]>\n" +
                        "  \n</if>", filedName, columnName, filedName));
            }
            //处理范围查询
            if (ClassUtil.isAssignable(Range.class, field.getType())) {
                sb.append(StrUtil.format("  <choose>\n" +
                        "        <when test=\"{} != null and {}.left != null and {}.leftEQ == true\">\n" +
                        "           <![CDATA[  AND {} >= #{{}.left} ]]>\n" +
                        "        </when>\n" +
                        "        <when test=\"{} != null and {}.left != null and {}.leftEQ == false\">\n" +
                        "           <![CDATA[  AND {} > #{{}.left} ]]>\n" +
                        "        </when>\n" +
                        "    </choose>", filedName, filedName, filedName, columnName, filedName, filedName, filedName, filedName, columnName, filedName, filedName, filedName, filedName, columnName, filedName, filedName, filedName, filedName, columnName, filedName));
                sb.append(StrUtil.format("  <choose>\n" +
                        "         <when test=\"{} != null and {}.right != null and {}.rightEQ == true\">\n" +
                        "           <![CDATA[  AND {} <= #{{}.right} ]]>\n" +
                        "        </when>\n" +
                        "        <when test=\"{} != null and {}.right != null and {}.rightEQ == false\">\n" +
                        "           <![CDATA[  AND {} < #{{}.right} ]]>\n" +
                        "        </when>\n" +
                        "    </choose>", filedName, filedName, filedName, columnName, filedName, filedName, filedName, filedName, columnName, filedName, filedName, filedName, filedName, columnName, filedName, filedName, filedName, filedName, columnName, filedName));
            }

        }

        sb.append(" </where> ");

        sb.append(StrUtil.format("\n<if test=\"{} != null and {} !='' \">" +
                " \n <![CDATA[  order by ${{}} ]]>\n" +
                "  \n</if>", "orderByBlock", "orderByBlock", "orderByBlock"));

        sb.append(" </script>");

        String sql = sb.toString().trim();
        //如果缓存没有，这里直接添加
        selectMap.putIfAbsent(queryClassName, sql);
        return sql;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294

11.mapper层数据库操作

package com.example.mapper;

import com.beiyou.model.OrderMaster;
import com.beiyou.model.OrderMasterQuery;
import com.example.annotation.PageX;
import com.example.util.SqlGen;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface OrderMaterMapper {

     //自定义的分页拦截器注解
    @PageX
    @SelectProvider(type = SqlGen.class, method = SqlGen.select)
    List<OrderMaster> queryByOrderMasterQuery(OrderMasterQuery query);

    //插入orderMaster表
    @SelectKey(keyColumn = "id", keyProperty = "id", statement = "select last_insert_id()", before = false, resultType = Long.class)
    // @SelectKey(keyColumn = "id", keyProperty = "id", statement = "select max(id)+1 as id from ordermaster", before = true,
    // resultType = Long.class)
    @InsertProvider(type = SqlGen.class, method = SqlGen.insert)
    Integer insertOrderMaster(OrderMaster orderMaster);

    @UpdateProvider(type = SqlGen.class, method = SqlGen.update)
    Integer updateOrderMaster(OrderMaster orderMaster);

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

12.测试的controller

package com.example.controller;

import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.ObjectUtil;
import com.beiyou.model.OrderMaster;
import com.beiyou.model.OrderMasterQuery;
import com.example.mapper.OrderMaterMapper;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.data.redis.core.ValueOperations;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.TimeUnit;

@Slf4j
@RestController
public class DemoController {

    @Resource
    private OrderMaterMapper orderMaterMapper;


    //测试 update
    @GetMapping("/test/update")
    public Integer testUpdate() {
        OrderMasterQuery query = OrderMasterQuery.builder().id(7L).build();
        List<OrderMaster> orderMasters = orderMaterMapper.queryByOrderMasterQuery(query);
        OrderMaster one = orderMasters.get(0);
        one.setAddress1(one.getAddress1() + "--up");
        Integer ret = orderMaterMapper.updateOrderMaster(one);
        return ret;
    }

   //redis的使用
    @Resource(name = "redisTemplate")
    private ValueOperations<String, OrderMaster> valueOps;
    public final String orderPre = "order#";

    //测试 insert
    @PostMapping("/test/insert")
    public Integer testInser(@RequestBody OrderMaster orderMaster) {

        //数据库添加
        Integer ret = orderMaterMapper.insertOrderMaster(orderMaster);
        //redis添加
        String key = orderPre + orderMaster.getId();
        valueOps.set(key, orderMaster);
        return ret;
    }

    // 前端调用测试
    @PostMapping("/test/sqlgen2")
    public List<OrderMaster> testSqlGen2(@RequestBody OrderMasterQuery query) {

        List<OrderMaster> orderMasters = orderMaterMapper.queryByOrderMasterQuery(query);
        return orderMasters;

    }

    //使用我们的sqlGen生成的xml
    @PostMapping("/test/sqlgen")
    public List<OrderMaster> testSqlGen() {
        // 测试 =
        // OrderMasterQuery query = OrderMasterQuery.builder().id(8L).build();

        // 测试 in
        // Long[] ids = new Long[]{5L,6L,7L};
        //OrderMasterQuery query = OrderMasterQuery.builder().ids(ids).build();

        // 测试范围查询
        // Range<Long> idRange = Range.<Long>builder().right(10L).rightEQ(true).build();
        // OrderMasterQuery query = OrderMasterQuery.builder().idRange(idRange).build();
        // query.setPageIndex(1);
        // query.setPageSize(3);

        // 测试 like
        // OrderMasterQuery query = OrderMasterQuery.builder().receiver("星").build();
        // List<OrderMaster> orderMasters = orderMaterMapper.queryByOrderMasterQuery(query);

        // 测试 不等于
        OrderMasterQuery query = OrderMasterQuery.builder().phoneNumber("18676644557").build();
        List<OrderMaster> orderMasters = orderMaterMapper.queryByOrderMasterQuery(query);

        return orderMasters;

    }

     //redis的使用
    //使用分页插件 前端传参定第几页，几条
    @PostMapping("/test/QueryByOrderMasterQuery")
    public List<OrderMaster> testByOrderMasterQuery(@RequestBody OrderMasterQuery query) {
        //不经过数据库查
        //retList放的是在redis里面的
        List<OrderMaster> retList = new ArrayList<>();

        boolean b = ObjectUtil.isNotEmpty(query.getId());

        //对通过ID单个查询
        if (b == true) {
            //key = order#123
            OrderMaster orderMaster = valueOps.get(orderPre + query.getId());
            if (orderMaster != null) {
                retList.add(orderMaster);
                log.debug("直接从redis获取，没有经过数据库");
                return retList;
            }
        }

        //针对ids 多个查询
        //ids: [58,59,60,61,87]
        //redis: [61,87]
        //[58,59,60]不在
        Long[] ids = query.getIds();
        //notInRedisIds放的是不在redis里面的
        ArrayList<Long> notInRedisIds = new ArrayList<>();
        for (Long id : ids) {
            //组装key
            OrderMaster orderMaster = valueOps.get(orderPre + id);
            if (orderMaster != null) {
                retList.add(orderMaster);
            } else {
                //如过redis没有查到，我们就把ID放进 notInRedisIds；
                notInRedisIds.add(id);
            }
        }

        if (ObjectUtil.isEmpty(notInRedisIds)) { //代表都在redis里面

            return retList;
        }


        query.setIds(ArrayUtil.toArray(notInRedisIds, Long.class));
        log.debug("筛选后的ids：{}", query.getIds());


        //通过数据库查找
        List<OrderMaster> orderMasters = orderMaterMapper.queryByOrderMasterQuery(query);
        log.debug("经过数据库");
        //如过通过数据库查找过来的数据，写到redis
        for (OrderMaster orderMaster : orderMasters) {
            valueOps.set(orderPre + orderMaster.getId(), orderMaster, 7, TimeUnit.DAYS);
            log.debug("通过数据库查得：{}", orderMaster.getId());
        }
        return orderMasters;

    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159