ElasticSearch集成redis、mysql 基于es索引多条件分页查询

• 前端页面

废话不多说 直接上后端代码

• 启动类

package com.jsy;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan(basePackages = "com.jsy.dao")
public class SpringBootEsRedisApp {
    public static void main(String[] args) {
        SpringApplication.run(SpringBootEsRedisApp.class,args);
    }
}

• 实体类 项目索引project类 和年份投资值projectInput类 一个项目对应多个年份的投资值

package com.jsy.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
import org.springframework.data.annotation.Id;
import org.springframework.data.elasticsearch.annotations.Document;
import org.springframework.data.elasticsearch.annotations.Field;
import org.springframework.data.elasticsearch.annotations.FieldType;

import java.io.Serializable;
import java.util.List;

@Data
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
@Document(indexName = "jhtlproject",type = "project")
public class Project implements Serializable {

    @Id  //主键
    private String projectId;               //项目编码

    @Field(type = FieldType.Keyword)
    private String projectName;             //项目名称

    @Field(type = FieldType.Keyword)
    private String buildNature;             //建设性质

    @Field(type = FieldType.Keyword)
    private String affiliatedUnit;          //所属单位

    //关系属性 每一年的投入金额
    private List<ProjectInput> inputList;

    @Field(type = FieldType.Keyword)
    private String projectTime;             //项目时间

    @Field(type = FieldType.Double)
    private Double projectPrice;               //项目计划值

}

• application.yml配置

server.port=9999
server.servlet.context-path=/EsWeb

spring.datasource.username=root
spring.datasource.password=123
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/jhtlproject?characterEncoding=utf-8
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

mybatis.type-aliases-package=com.jsy.entity
mybatis.mapper-locations=classpath:com/jsy/*.xml
mybatis.executor-type=batch

spring.elasticsearch.rest.uris=http://192.168.17.19:9200
spring.elasticsearch.rest.username=es
spring.elasticsearch.rest.password=123456

spring.http.encoding.charset=utf-8
spring.jackson.time-zone=GMT+8

spring.redis.host=Centos
spring.redis.port=6379
spring.redis.timeout=5s
spring.redis.lettuce.pool.max-active=10
spring.redis.lettuce.pool.max-idle=8
spring.redis.lettuce.pool.max-wait=5ms
spring.redis.lettuce.pool.min-idle=1
spring.redis.lettuce.shutdown-timeout=100ms

package com.jsy.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
import java.io.Serializable;
@Data
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
public class ProjectInput implements Serializable {

    //投入年份id 外键对应
    private String inputId;

    //投入时间
    private String projectTime;

    //投入金额
    private Double projectPrice;

    //项目名称
    private String inputName;

}

• 后端controller接口

package com.jsy.controller;
import com.jsy.entity.Project;
import com.jsy.service.ProjectService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.HashSet;
import java.util.List;
@RestController
@RequestMapping("/projectController")
public class ProjectController {

    //依赖注入project业务接口
    @Autowired
    private ProjectService projectService;

    //分页查询 根据不同的字段进行 字段可以为空
    @GetMapping(value = "/queryProjectByPage")
    public List<Project> queryProjectByPage(@RequestParam(value = "page", defaultValue = "1") Integer pageNow,
                                            @RequestParam(value = "rows", defaultValue = "10") Integer pageSize,
                                            @RequestParam(value = "projectId" ) String projectId,
                                            @RequestParam(value = "projectName") String projectName,
                                            @RequestParam(value = "buildNature") String buildNature,
                                            @RequestParam(value = "affiliatedUnit") String affiliatedUnit,
                                            @RequestParam(value = "projectTime") String projectTime){
        //调用接口方法
        List<Project> projectList = projectService.queryProjectByPage(pageNow,pageSize,projectId,projectName,buildNature,affiliatedUnit,projectTime);
        return projectList;
    }

    //获取es中的数据总条数 可以多字段多条件查询总数
    @GetMapping(value = "/queryProjectCount")
    public Integer queryProjectCount(@RequestParam(value = "projectId" ) String projectId,
                                     @RequestParam(value = "projectName") String projectName,
                                     @RequestParam(value = "buildNature") String buildNature,
                                     @RequestParam(value = "affiliatedUnit") String affiliatedUnit,
                                     @RequestParam(value = "projectTime") String projectTime) {
        //调用接口方法 返回中条数
        int queryProjectCount = projectService.queryProjectCount(projectId,projectName,buildNature,affiliatedUnit,projectTime);//project
        return queryProjectCount;
    }

    //总计划值
    @GetMapping(value = "/queryTotalSum")
    public Double queryTotalSum(@RequestParam(value = "projectId" ) String projectId,
                                @RequestParam(value = "projectName") String projectName,
                                @RequestParam(value = "buildNature") String buildNature,
                                @RequestParam(value = "affiliatedUnit") String affiliatedUnit,
                                @RequestParam(value = "projectTime") String projectTime){
        //调用接口方法
        Double aDouble = projectService.queryTotalSum(projectId, projectName, buildNature, affiliatedUnit, projectTime);
        return aDouble;
    }

    //添加项目时用户名实时查询防止重名
    @GetMapping(value = "/existsProject")
    public Boolean queryUserById(Project project) {
        //从es中查询
        return projectService.queryProjectByName(project.getProjectName());
    }

    //添加新的项目
    @PostMapping(value = "/addProject")
    @ResponseBody
    public void addProject(@RequestPart(value = "project") Project project) {
        projectService.addProject(project);
    }

    //修改项目
    @PutMapping(value = "/updateProject")
    @ResponseBody
    public void updateProject(@RequestPart(value = "project") Project project){
        System.out.println("updateProject project = " + project);
        projectService.updateProject(project);
    }

    //修改计划值
    @PutMapping(value = "/updateProjectPrice")
    @ResponseBody
    public void updateProjectPrice(@RequestPart(value = "project") Project project){
        System.out.println("updateProjectPrice project = " + project);
        projectService.updateProjectInput(project);
    }

    //查询所有单位
    @GetMapping(value = "/queryAllProjectAffiliatedUnit")
    @ResponseBody
    public HashSet<String> queryAllProjectAffiliatedUnit(){
        HashSet<String> stringHashSet = projectService.queryAllProjectAffiliatedUnit();
        return stringHashSet;
    }
}

• es的ElasticsearchRepository工具

package com.jsy.es.repsitory;
import com.jsy.entity.Project;
import org.springframework.data.elasticsearch.repository.ElasticsearchRepository;

public interface ProjectRespository extends ElasticsearchRepository<Project,String> {
//    //根据项目id查询
//    public List<Project> findByProjectIdOrProjectNameOrBuildNatureOrAffiliatedUnitOrProjectTimeOrProjectPrice(String projectId, String projectName, String buildNature, String affiliatedUnit, String projectTime, Double projectPrice);
//    //根据项目名查询
    public Project findByProjectName(String projectName);
}

• es工具类TransportClient 用来做java操作

package com.jsy.es.conf;

import org.elasticsearch.client.transport.TransportClient;
import org.elasticsearch.common.settings.Settings;
import org.elasticsearch.common.transport.TransportAddress;
import org.elasticsearch.transport.client.PreBuiltTransportClient;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.net.InetAddress;
import java.net.UnknownHostException;

@Configuration
public class EsConf {
    @Bean
    public TransportClient getTransportClient() throws UnknownHostException {
        //创建TransportClient对象
        TransportClient transportClient = new PreBuiltTransportClient(Settings.EMPTY).addTransportAddress(new TransportAddress(InetAddress.getByName("192.168.17.216"),9300));
        return  transportClient;
    }
}

• es多条件查询自己写的静态类

package com.jsy.es.conf;

import io.netty.util.internal.StringUtil;
import org.elasticsearch.index.query.BoolQueryBuilder;
import org.elasticsearch.index.query.QueryBuilders;

/* 多条件整合类  设置检索条件  方法设置为静态 方便调用
 * projectId            页面项目编码文本框值
 * projectName          页面项目名称文本框值
 * buildNature          页面项目性质下拉列表值
 * affiliatedUnit       页面项目所属单位下拉列表值
 * projectTime          页面项目时间表值
 * */
public class BoolQueryBuilderConf {
    public static BoolQueryBuilder getBoolQueryBuilder(String projectId,String projectName,String buildNature,String affiliatedUnit){

        //设置检索条件
        BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery();
        //如果projectId不是空把它设置为必须查询的条件
        if (!StringUtil.isNullOrEmpty(projectId)) {
            boolQueryBuilder.must(QueryBuilders.matchPhraseQuery("projectId",projectId));
        }
        //如果projectName不是空把它设置为必须查询的条件
        if (!StringUtil.isNullOrEmpty(projectName)) {
            boolQueryBuilder.must(QueryBuilders.matchPhraseQuery("projectName",projectName));
        }
        //如果buildNature不是空把它设置为必须查询的条件
        if (!StringUtil.isNullOrEmpty(buildNature)) {
            boolQueryBuilder.must(QueryBuilders.matchPhraseQuery("buildNature",buildNature));
        }
        //如果affiliatedUnit不是空把它设置为必须查询的条件
        if (!StringUtil.isNullOrEmpty(affiliatedUnit)) {
            boolQueryBuilder.must(QueryBuilders.matchPhraseQuery("affiliatedUnit",affiliatedUnit));
        }
        return boolQueryBuilder;
    }
}

• 业务层接口

package com.jsy.service;
import com.jsy.entity.Project;
import com.jsy.entity.ProjectInput;
import org.springframework.data.repository.query.Param;

import java.util.HashSet;
import java.util.List;
public interface ProjectService {
    //分页 并且根据前端传过来的不同字段条件进行分页查询 字段可以为空
    List<Project> queryProjectByPage(
            @Param(value = "pageNow") Integer pageNow,
            @Param(value = "pageSize") Integer pageSize,
            @Param(value = "projectId" ) String projectId,
            @Param(value = "projectName") String projectName,
            @Param(value = "buildNature") String buildNature,
            @Param(value = "affiliatedUnit") String affiliatedUnit,
            @Param(value = "projectTime") String projectTime
            );

    Double queryTotalSum(String projectId, String projectName, String buildNature, String affiliatedUnit, String projectTime);


        //查询项目总数  并且根据多字段条件查询
    int queryProjectCount(@Param(value = "projectId" ) String projectId,
                          @Param(value = "projectName") String projectName,
                          @Param(value = "buildNature") String buildNature,
                          @Param(value = "affiliatedUnit") String affiliatedUnit,
                          @Param(value = "projectTime") String projectTime);


    //根据id查一个
    HashSet<String> queryAllProjectAffiliatedUnit();

    //根据名称查一个
    Boolean queryProjectByName(String projectName);

    //添加一个新的项目
    void addProject(Project project);

    //修改项目
    void updateProject(Project project);

    //修改项目
    void updateProjectInput(Project project);
}

• 业务层实体类

package com.jsy.service.impl;
import com.jsy.dao.InputDao;
import com.jsy.dao.ProjectDao;
import com.jsy.entity.ProjectInput;
import com.jsy.es.conf.BoolQueryBuilderConf;
import com.jsy.entity.Project;
import com.jsy.es.repsitory.ProjectRespository;
import com.jsy.service.ProjectService;
import org.elasticsearch.action.search.SearchResponse;
import org.elasticsearch.client.transport.TransportClient;
import org.elasticsearch.index.query.BoolQueryBuilder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.text.SimpleDateFormat;
import java.util.*;
@Service
@Transactional
public class ProjectServiceImpl implements ProjectService {
    //引入java操作es的服务端口类
    @Resource
    TransportClient transportClient;
    //引入springdata操作es的服务端口类
    @Autowired
    ProjectRespository projectRespository;
    @Autowired
    RedisTemplate<Object, Project> redisTemplate;
    @Resource
    ProjectDao projectDao;
    @Resource
    InputDao inputDao;

    /* 分页
     * pageNow              当前页
     * pageSize             每页多少数据
     * projectId            页面项目编码文本框值
     * projectName          页面项目名称文本框值
     * buildNature          页面项目性质下拉列表值
     * affiliatedUnit       页面项目所属单位下拉列表值
     * projectTime          页面项目时间表值
     * */
    @Override
    @Transactional(propagation = Propagation.SUPPORTS, readOnly = true)
    public List<Project> queryProjectByPage(Integer pageNow, Integer pageSize, String projectId, String projectName, String buildNature, String affiliatedUnit, String projectTime) {
        //创建PageRequest 设置起始页  每页条数 es的起始页默认是0  所以－1
        PageRequest pageRequest = PageRequest.of(pageNow - 1, pageSize);
        //调用自己写的多条件整合类 参数projectId projectName buildNature...前端的条件查询数据
        BoolQueryBuilder boolQueryBuilder = BoolQueryBuilderConf.getBoolQueryBuilder(projectId, projectName, buildNature, affiliatedUnit);
        //分页 根据检索条件查询es得到满足条件的全部索引
        List<Project> projectList = projectRespository.search(boolQueryBuilder, pageRequest).getContent();
        //判断集合是否含有数据 如果集合的长度是大于0的 代表从es查到了数据 将集合遍历 获取对应的id
        //创建新的集合 用来存放从数据库或者redis中查到的数据
        ArrayList<Project> ArrayList = new ArrayList<>();
        if (projectList.size() > 0) {
            //遍历es查到的数据
            for (Project project : projectList) {
                //创建一个新的项目对象 用来接收redis中查到的数据或者es查到的数据
                Project addArrayListProject;
                //在redis中查不到这个数据 只能从mysql中查询 然后再讲这条数据存放到redis中去 并且设置存活时间 10分钟
                addArrayListProject = projectDao.queryOneProjectFromMySql(project.getProjectId());
                //我们还需要查出来此项目的所有年份计划值的总和 反馈给前台页面
                ProjectInput queryProjectPriceSumById = new ProjectInput();
                queryProjectPriceSumById.setInputId(project.getProjectId());
                List<ProjectInput> projectInputList = inputDao.queryProjectInputByMySql(queryProjectPriceSumById);
                //如果传过来的时间是空的 我们就传总和
                double sum = 0.00;
                for (ProjectInput projectInput : projectInputList) {
                    if (projectTime==""){
                        sum += projectInput.getProjectPrice();
                    }else if (projectInput.getProjectTime().equals(projectTime)){
                        sum=projectInput.getProjectPrice();
                    }
                }
                addArrayListProject.setProjectPrice(sum);
                ArrayList.add(addArrayListProject);

            }

        }
        return ArrayList;
    }

    //查询所有项目的计划值总和 可以根据前天传过来的五个参数进行过滤 分别是项目编码 名称 建设性质 所属单位 投资时间
    @Override
    @Transactional(propagation = Propagation.SUPPORTS, readOnly = true)
    public Double queryTotalSum(String projectId, String projectName, String buildNature, String affiliatedUnit, String projectTime) {
        //项目编码 名称 建设性质 所属单位需要过滤查询 只有满足条件的才能计算总计划值
        BoolQueryBuilder boolQueryBuilder = BoolQueryBuilderConf.getBoolQueryBuilder(projectId, projectName, buildNature, affiliatedUnit);
        //不分页 查询满足条件的全部索引 将全部id索引进行遍历 去年份计划值表中进行查找筛选
        Iterator<Project> iterator = projectRespository.search(boolQueryBuilder).iterator();
        //设置计划值累计求和初始值为0.00
        double totalSum = 0.00;
        while (iterator.hasNext()) {
            String s = iterator.next().getProjectId();
            //查询满足id的所有本id项目的每一年计划值 如果前天页面传过来的时间不是null，那么也会被当做过滤条件去筛选
            List<ProjectInput> projectInputList = inputDao.queryProjectInputByMySql(new ProjectInput(s, projectTime, null, null));
            for (ProjectInput projectInput : projectInputList) {
                totalSum += projectInput.getProjectPrice();
            }
        }
        return totalSum;
    }

    /* 查询总条数           可以根据多个字段条件查询
     * projectId            页面项目编码文本框值
     * projectName          页面项目名称文本框值
     * buildNature          页面项目性质下拉列表值
     * affiliatedUnit       页面项目所属单位下拉列表值
     * projectTime          页面项目时间表值
     *
     * */
    @Override
    @Transactional(propagation = Propagation.SUPPORTS, readOnly = true)
    public int queryProjectCount(String projectId, String projectName, String buildNature, String affiliatedUnit, String projectTime) {
        //调用自己写的类 参数projectId projectName buildNature...前端条件查询等等
        BoolQueryBuilder boolQueryBuilder = BoolQueryBuilderConf.getBoolQueryBuilder(projectId, projectName, buildNature, affiliatedUnit);
        //MatchAllQueryBuilder matchAllQueryBuilder = QueryBuilders.matchAllQuery();//查询所有数据没有条件时候用的
        //连接索引 设置类型  绑定查询条件
        SearchResponse searchResponse = transportClient.prepareSearch("jhtlproject")
                .setTypes("project")
                .setQuery(boolQueryBuilder)
                .get();
        Long totalHits = searchResponse.getHits().getTotalHits();//总条数
        int intValue = totalHits.intValue();
        return intValue;
    }

    /*
     * 实时监测添加新项目时候的醒目名称是否存在
     * */
    @Override
    @Transactional(propagation = Propagation.SUPPORTS, readOnly = true)
    public Boolean queryProjectByName(String projectName) {
        Project byProjectName = projectRespository.findByProjectName(projectName);
        if (byProjectName == null) {
            return false;
        } else {
            return true;
        }
    }

    /*
     * 添加新的项目
     * */
    @Override
    public void addProject(Project project) {
        String yyyyMMddhhmmss = new SimpleDateFormat("yyyyMMddhhmmss").format(new Date()).substring(2);
        //设置新加项目的id  我们拿时间年月日时分秒做  切割后12位 作为id
        project.setProjectId(yyyyMMddhhmmss);
        //创建对应的年份计划值实体类 持久化到数据库
        ProjectInput projectInput = new ProjectInput(yyyyMMddhhmmss, project.getProjectTime(), project.getProjectPrice(), project.getProjectName());
        //保存到mysql中
        projectDao.insertProjectToMySql(project);
        inputDao.insertProjectInputToMySql(projectInput);
        //将项目数据存到es索引库中
        projectRespository.save(project);
        redisTemplate.opsForSet().add(project.getProjectTime(), project);
    }

    /*
     * 修改项目信息
     * project前端传过来的修改项目内容
     * */
    @Override
    public void updateProject(Project project) {
        System.out.println("imp  updateProject---+++project = " + project);
        //修改mysql数据库
        projectDao.updateProjectToMySql(project);

        projectRespository.save(project);
        //修改redis中的数据
    }

    @Override
    public void updateProjectInput(Project project) {
        System.out.println("imp  updateProjectInput---+++project = " + project);
        ProjectInput projectInput = new ProjectInput(project.getProjectId(), project.getProjectTime(), project.getProjectPrice(), project.getProjectName());
        List<ProjectInput> projectInputList = inputDao.queryProjectInputByMySql(projectInput);
        Boolean flog = true;
        for (ProjectInput input : projectInputList) {
            if (project.getProjectTime()!=""&&input.getProjectTime().equals(project.getProjectTime())) {
                inputDao.updateProjectInputToMySql(projectInput);
                flog = false;
            }
        }
        if (flog) {
            inputDao.insertProjectInputToMySql(projectInput);
        }
    }

    /* 根据id查询
     * projectId 查询的id
     * */
    @Override
    @Transactional(propagation = Propagation.SUPPORTS, readOnly = true)
    public HashSet<String> queryAllProjectAffiliatedUnit() {
        Iterable<Project> all = projectRespository.findAll();
        Iterator<Project> iterator = all.iterator();
        HashSet<String> queryAllProjectAffiliatedUnitSet = new HashSet<>();
        while (iterator.hasNext()) {
            queryAllProjectAffiliatedUnitSet.add(iterator.next().getAffiliatedUnit());
        }
        return queryAllProjectAffiliatedUnitSet;
    }
}

• dao层

package com.jsy.dao;
import com.jsy.entity.Project;
public interface ProjectDao {
    //根据id查一个项目
    Project queryOneProjectFromMySql(String projectId);

    //添加project索引到数据库
    void insertProjectToMySql(Project project);

    void updateProjectToMySql(Project project);
}

package com.jsy.dao;
import com.jsy.entity.ProjectInput;
import java.util.List;
public interface InputDao {
    //添加项目年份计划值
    void insertProjectInputToMySql(ProjectInput projectInput);
    void updateProjectInputToMySql(ProjectInput projectInput);
    List<ProjectInput> queryProjectInputByMySql(ProjectInput projectInput);
}

• mapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jsy.dao.ProjectDao">
    <cache type="com.jsy.cache.ProjectDefineRedisCache">
        <property name="timeout" value="60"/>
    </cache>

    <resultMap type="com.jsy.entity.Project" id="ProjectMap">
        <id property="projectId" column="projectId" jdbcType="VARCHAR"/>
        <result property="projectName" column="projectName" jdbcType="VARCHAR"/>
        <result property="buildNature" column="buildNature" jdbcType="VARCHAR"/>
        <result property="affiliatedUnit" column="affiliatedUnit" jdbcType="VARCHAR"/>
        <collection property="inputList" ofType="com.jsy.entity.ProjectInput">
            <result property="inputId" column="inputId"/>
            <result property="projectTime" column="projectTime"/>
            <result property="projectPrice" column="projectPrice"/>
            <result property="inputName" column="inputName"/>
        </collection>
    </resultMap>

    <!--查询项目-->
    <select id="queryOneProjectFromMySql" resultMap="ProjectMap" resultType="com.jsy.entity.Project">
        select
            projectId, projectName, buildNature, affiliatedUnit,inputId,projectTime,projectPrice,inputName
        from project p join input i
        on p.projectId=i.inputId
        where projectId=#{projectId}
    </select>

    <!--添加项目主要索引-->
    <insert id="insertProjectToMySql" parameterType="com.jsy.entity.Project">
        insert into project(projectId,projectName,buildNature,affiliatedUnit)
        values (#{projectId},#{projectName},#{buildNature},#{affiliatedUnit})
    </insert>

    <!--修改项目主要索引-->
    <update id="updateProjectToMySql" parameterType="com.jsy.entity.Project">
        update project
            set projectName=#{projectName},buildNature=#{buildNature},affiliatedUnit=#{affiliatedUnit}
        where projectId = #{projectId}
    </update>
</mapper>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.jsy.dao.InputDao">

    <resultMap type="com.jsy.entity.ProjectInput" id="pinput">
        <result property="inputId" column="inputId" jdbcType="VARCHAR"/>
        <result property="projectTime" column="projectTime" jdbcType="VARCHAR"/>
        <result property="projectPrice" column="projectPrice" jdbcType="NUMERIC"/>
        <result property="inputName" column="inputName" jdbcType="VARCHAR"/>
    </resultMap>

    <!--查询项目年份计划值-->
    <select id="queryProjectInputByMySql" resultMap="pinput">
        select
            inputId,projectTime,projectPrice,inputName
        from input
        <where>
            <if test="inputId != null and inputId != ''">
                and inputId = #{inputId}
            </if>
            <if test="projectTime != null and projectTime != ''">
                and projectTime = #{projectTime}
            </if>
        </where>
    </select>

    <!--添加项目年份计划值-->
    <insert id="insertProjectInputToMySql" parameterType="com.jsy.entity.ProjectInput">
        insert into input(inputId,projectTime,projectPrice,inputName)
        values (#{inputId},#{projectTime},#{projectPrice},#{inputName})
    </insert>


    <!--修改项目-->
    <update id="updateProjectInputToMySql" parameterType="com.jsy.entity.ProjectInput">
        update input
            set projectPrice=#{projectPrice}
        <where>
            <if test="inputId != null and inputId != ''">
                and inputId = #{inputId}
            </if>
            <if test="projectTime != null and projectTime != ''">
                and projectTime = #{projectTime}
            </if>
        </where>
    </update>
</mapper>

• 集成redis二级缓存

package com.jsy;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;

@Component
public class ApplicationContextHolder implements ApplicationContextAware {
    private static ApplicationContext applicationContext=null;
    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        this.applicationContext=applicationContext;
    }
    public static Object getBean(String beanName){
        return applicationContext.getBean(beanName);
    }
}

• redis将对象序列化

package com.jsy.cache;
import com.jsy.entity.Project;
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;
//定义自己的ProjectRedisTemplate实现自定义对象的序列化
@Configuration
public class RedisConf {
    @Bean
    public RedisTemplate<Object, Project> getProjectRedisTemplate(RedisConnectionFactory redisConnectionFactory) {
        RedisTemplate<Object, Project> redisTemplate = new RedisTemplate<>();
        redisTemplate.setConnectionFactory(redisConnectionFactory);
        Jackson2JsonRedisSerializer<Project> serializer = new Jackson2JsonRedisSerializer<Project>(Project.class);
        redisTemplate.setDefaultSerializer(serializer);
        return redisTemplate;
    }
}

• redis对mysql的二级缓存

package com.jsy.cache;
import com.jsy.ApplicationContextHolder;
import org.apache.ibatis.cache.Cache;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.data.redis.core.RedisCallback;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.data.redis.core.ValueOperations;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.locks.ReadWriteLock;
import java.util.concurrent.locks.ReentrantReadWriteLock;
//springboot集成redis和mybatis实现二级缓存
public class ProjectDefineRedisCache implements Cache {
    private static final Logger logger = LoggerFactory.getLogger(ProjectDefineRedisCache.class);
    //记录的是Mppper的Namespace
    private String id;
    private long timeout=300;
    private final ReadWriteLock readWriteLock = new ReentrantReadWriteLock();
    private RedisTemplate redisTemplate;  //
    public ProjectDefineRedisCache(String id){
        this.id=id;
    }
    private RedisTemplate getRedisTemplate(){
        return (RedisTemplate) ApplicationContextHolder.getBean("redisTemplate");
    }
    @Override
    public String getId() {
        return id;
    }
    @Override
    public void putObject(Object key, Object value) {
        if(redisTemplate==null){
            redisTemplate=getRedisTemplate();
        }
        logger.debug("将查询结果缓存到Redis--cache.key"+key+"\tvalue"+value);
        ValueOperations opsForValue = redisTemplate.opsForValue();
        opsForValue.set(key, value, 10, TimeUnit.MINUTES);
    }
    @Override
    public Object getObject(Object key) {
        if(redisTemplate==null){
            redisTemplate=getRedisTemplate();
        }
        logger.debug("获取缓存结果"+key);
        ValueOperations opsForValue = redisTemplate.opsForValue();
        return opsForValue.get(key);
    }
    @Override
    public Object removeObject(Object key) {
        if(redisTemplate==null){
            redisTemplate=getRedisTemplate();
        }
        logger.debug("删除Redis中的Key:"+key);
        ValueOperations opsForValue = redisTemplate.opsForValue();
        Object value = opsForValue.get(key);
        redisTemplate.delete(key);
        return value;
    }
    @Override
    public void clear() {
        if(redisTemplate==null){
            redisTemplate=getRedisTemplate();
        }
        logger.debug("删除所有Redis中的缓存");
        redisTemplate.execute((RedisCallback) connection -> {
            connection.flushDb();
            return null;
        });
    }
    @Override
    public int getSize() {
        return 0;
    }
    @Override
    public ReadWriteLock getReadWriteLock() {
        return readWriteLock;
    }
    public void setTimeout(long timeout) {
        this.timeout = timeout;
    }
}