SpringBoot结合mybatis实现多表关联查询

背景
好久不用java,最近项目中用到了java开发后台，选用框架为SpringBoot,操作数据库使用JPA，确实方便，连我这种很久不用java的菜鸟都能用，看来确实入门比较简单，但是现实中一个项目不可能一直是单表查询或者简单的表关联就可以完成的，为了满足各种关联查询需求，有必要引入mybatis进行助攻，完成开发，下面我就以实际项目需求进行介绍
一、需求
一个主表（案件表）、还有来源、类型、区划、案由等大概五个左右的表需要和主表进行关联，查询每个表中的字段，当然可以使用JPA的查询方式，就是通过引入每个表对应的实体进行关联，也可以完成该项功能，但是总感觉 这种操作缺点意思，通过咨询前辈，他们也建议使用mybatis进行多表处理，果断使用。
二、环境搭建
1、引入依赖

  <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>

2、设置驼峰命名，因为我不想写results，直接用驼峰实现数据库字段与实体属性进行匹配。

###################  开启mybatis驼峰命名  ###################
mybatis.configuration.map-underscore-to-camel-case=true

3、启动程序中添加=扫描Mapper

package cn.enilu.flash.api;

import cn.enilu.flash.dao.BaseRepositoryFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.domain.EntityScan;
import org.springframework.boot.builder.SpringApplicationBuilder;
import org.springframework.boot.web.servlet.support.SpringBootServletInitializer;
import org.springframework.cache.annotation.EnableCaching;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

/**
 * ApiApplication
 *
 * @author enilu
 * @version 2018/9/11 0011
 */
@EnableCaching
@SpringBootApplication
@ComponentScan(basePackages = "cn.enilu.flash")
@EntityScan(basePackages = "cn.enilu.flash.bean.entity")
@EnableJpaRepositories(basePackages = "cn.enilu.flash.dao", repositoryFactoryBeanClass = BaseRepositoryFactoryBean.class)
@EnableJpaAuditing
@MapperScan(basePackages = "cn.enilu.flash.dao")
public class ApiApplication extends SpringBootServletInitializer {

    @Override
    protected SpringApplicationBuilder configure(SpringApplicationBuilder application) {
        return application.sources(ApiApplication.class);
    }

    public static void main(String[] args) {
        SpringApplication.run(ApiApplication.class);
    }
}

三、编码
创建实体对象，用于返回关联后的实体属性。

package cn.enilu.flash.bean.vo;

import cn.enilu.flash.bean.entity.BaseEntity;
import lombok.Data;
import org.hibernate.annotations.Table;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

import javax.persistence.Entity;
import javax.persistence.EntityListeners;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

import java.util.Date;

/**
 * @ClassName EventInfosVo
 * @Description TODO
 * @Author ZhouZhiqiang
 * @Date 2021/1/16 23:34
 * @Version 1.0
 **/
@Entity(name = "view_event_infos")
@Table(appliesTo = "view_event_infos")
@Data
@EntityListeners(AuditingEntityListener.class)
public class ViewEventInfosVo extends  BaseEntity{

    private String taskNumber;
    private String eventSourceId;
    private String eventStateId;

    private Date eventTime;

    private String sponsor;

    private String coPerson;

    private String lawProcedure;
    private String litigantType;
    private String litigantId;
    private String legalPerson;
    private String litigantName;

    private String litigantAge;

    private Integer litigantSex;

    private String litigantTelephone;

    private String litigantAddress;

    private String eventAddress;
    private String districtId;
    private String eventType;
    private String eventDescription;
    private String causeAction;
    private String illegalBasis;
    private String illegalContent;
    private String punishBasis;
    private String punishContent;
    private Integer lawId;

    private Integer handler;

    private String x;

    private String y;

    private String img;

    private String existingDocuments;

    private String eventSourceName;
    private String eventTypeName;
    private String districtName;
    private String sponsorName;
    private String sponsorNumber;
    private String coPersonName;

    private String coPersonNumber;
    private String causeActionTitle;
    private String illegalBasisText;
    private String illegalContentText;
    private String punishBasisText;
    private String punishContentText;

}

2、编写Provider,该类的主要功能是实现多表查询语句，各种查询条件直接进行sql拼接即可，需要注意的是多个参数需要用Map，因此传递参数的时候用@param注解

package cn.enilu.flash.provider;

import java.util.Map;

/**
 * @ClassName EventProvider
 * @Description TODO
 * @Author ZhouZhiqiang
 * @Date 2021/1/17 19:29
 * @Version 1.0
 **/
public class EventProvider {
    public String getEventVoSql(Map<String, Object> para){
        String sql=" select * from view_event_infos where 1=1";
        if(para.get("eventStateId")!=null){
           sql+=" and event_state_id="+para.get("eventStateId");
        }

        if(para.get("handler")!=null){
            sql+=" and handler="+para.get("handler");
        }

        if(para.get("id")!=null){
            sql+=" and id="+para.get("id");
        }
        return sql;

    }

}

3、dao层中添加Mapper,该类是实现调用Provider提供的查询语句，本案例中EventProvider就是要调用的对象，method是指sql返回的方法。

package cn.enilu.flash.dao;

import cn.enilu.flash.bean.vo.ViewEventInfosVo;
import cn.enilu.flash.provider.EventProvider;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.SelectProvider;

import java.util.List;

public interface EventMapper {

    @SelectProvider(type = EventProvider.class, method = "getEventVoSql")
    public List<ViewEventInfosVo> getEventViewList(@Param("id") long id, @Param("handler") long handler, @Param("eventStateId") String eventStateId);
}

4、service层进行调用Mapper

package cn.enilu.flash.service.event;


import cn.enilu.flash.bean.entity.event.EventInfos;
import cn.enilu.flash.bean.entity.event.ViewEventInfos;
import cn.enilu.flash.bean.vo.ViewEventInfosVo;
import cn.enilu.flash.dao.EventMapper;
import cn.enilu.flash.dao.event.EventInfosRepository;

import cn.enilu.flash.service.BaseService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class EventInfosService extends BaseService<EventInfos,Long,EventInfosRepository>  {
    private Logger logger = LoggerFactory.getLogger(getClass());
    @Autowired
    private EventInfosRepository eventInfosRepository;
    @Autowired
    private EventMapper eventMapper;
    //获取视图返回的数据
    public List<ViewEventInfosVo> getEventInfosList(long id,long handler,String eventStateId){
        List<ViewEventInfosVo> list=eventMapper.getEventViewList(id,handler,eventStateId);
        return list;
    }
}

5、controller调用，返回给前端

@RequestMapping(value = "/list/vo",method = RequestMethod.GET)
	@ApiOperation("案件视图信息列表")
	//@RequiresPermissions(value = "/eventInfos")
	public Object listVo(@RequestParam(required = false) Long id,@RequestParam(required = false) String stateId,@RequestParam(required = false) Long handler) {
		Page<EventInfos> page = new PageFactory<EventInfos>().defaultPage();
//		page.addFilter("id",id);
//		page.addFilter("handler",handler);	//过滤转办对象
//		page.addFilter("eventStateId",stateId);	//过滤案件状态
//		page = eventInfosService.queryPage(page);
		List list = eventInfosService.getEventInfosList(id,handler,stateId);
		page.setRecords(list);
		return Rets.success(page);
	}

6、易错点总结
（1）注意开启mybatis的驼峰命名命令，否则返回的对象可能都为null.
（2）注意启动程序中增加扫描Mapper注解

  @MapperScan(basePackages = "cn.enilu.flash.dao")