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

<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")