Spring boot mybatis 子查询

爱被打了一巴掌 2022-09-10 11:28 47阅读 0赞

# Spring boot mybatis 子查询 #

#### 需求： ####

一对多的两张表数据，主表的唯一id在副表对应多个数据

主表：acloth\_class  
字段：id, name, status, pid

副表：acloth\_class\_content  
字段：id，class\_id(主表id)，class\_name

#### 流程： ####

1.  表的实体类
2.  mapper类
3.  xml
4.  Service接口和ServiceImpl实现类
5.  接口

步骤一：表的实体类：  
AClothClassContent.java

package com.example.springboot01.dto;
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    public class AClothClassContent { 
        private Integer id;
        private Integer class_id;
        private String class_name;
    }

AClothClass.java：

package com.example.springboot01.dto;
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    import java.util.List;
    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    public class AClothClass { 
        private Integer id;
        private String name;
        private Integer pid;
        private Integer status;
        // 这是子查询结果集
        private List<AClothClassContent> contents;
    }

步骤二：mapper类：AClothClassMapper.java

package com.example.springboot01.mapper;
    import com.example.springboot01.dto.AClothClass;
    import org.apache.ibatis.annotations.Mapper;
    import org.springframework.stereotype.Repository;
    import java.util.List;
    @Mapper
    @Repository
    public interface AClothClassMapper { 
        List<AClothClass> getList();
    }

步骤三：xml：AClothClassMapper.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.example.springboot01.mapper.AClothClassMapper">
        <select id="getList" resultMap="classResultMap">
            SELECT
                *
            FROM acloth_class where status = 1
        </select>
    
        <resultMap id="classResultMap" type="com.example.springboot01.dto.AClothClass">
            <id column="id" property="id" />
            <result column="name" property="name" />
            <result column="pid" property="pid" />
            <result column="status" property="status" />
            <collection column="id" property="contents" javaType="java.util.ArrayList" ofType="com.example.springboot01.dto.AClothClassContent" select="getClassContent">
                <result column="id" property="id"></result>
                <result column="class_id" property="class_id"></result>
                <result column="class_name" property="class_name"></result>
            </collection>
        </resultMap>
        <select id="getClassContent" resultType="com.example.springboot01.dto.AClothClassContent">
            select * from acloth_class_content where
                class_id=#{id}
        </select>
    </mapper>

步骤四：Service和ServiceImpl：AClothClassService.java

package com.example.springboot01.service;
    import com.example.springboot01.dto.AClothClass;
    import java.util.List;
    public interface AClothClassService { 
        List<AClothClass> getList();
    }

AClothClassServiceImpl.java

package com.example.springboot01.service;
    import com.example.springboot01.dto.AClothClass;
    import com.example.springboot01.mapper.AClothClassMapper;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import java.util.List;
    @Service
    public class AClothClassServiceImpl implements AClothClassService{ 
        @Autowired
        AClothClassMapper aClothClassMapper;
        @Override
        public List<AClothClass> getList() { 
            return aClothClassMapper.getList();
        }
    }

步骤六：接口：AClothClassController.java

package com.example.springboot01.controller;
    
    import com.example.springboot01.ret.RetResponse;
    import com.example.springboot01.ret.RetResult;
    import com.example.springboot01.service.AClothClassServiceImpl;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    @RestController
    @RequestMapping("/tree")
    public class AClothClassController { 
        @Autowired
        AClothClassServiceImpl aClothClassServiceImpl;
        @GetMapping(value = "/getList")
        public RetResult<Object> getList(){ 
            return RetResponse.success(aClothClassServiceImpl.getList());
        }
    }

其实实际开发中只需要拿副表的id，然后给前端解析，所以也可以直接写sql：

SELECT *,(SELECT GROUP_CONCAT(id) FROM acloth_class_content b WHERE b.class_id=a.id) as contents FROM acloth_class a;

子查询别名对应实体里新增的字段就可以

或者使用联表分组的方式

SELECT a.id, a.`name`, a.pid, GROUP_CONCAT(b.id) as contents FROM acloth_class a LEFT JOIN acloth_class_content b ON a.id=b.class_id GROUP BY a.id

这里为什么不用\*，而是把主表字段列出来呢，是因为ONLY\_FULL\_GROUP\_BY模式的缘故，分组的字段必须在select中出现

我看网上说可以用这个命令查看模式

select @@global.sql_mode;

然后将里面的ONLY\_FULL\_GROUP\_BY模式去掉，然后用命令设置

set @@global.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

重启数据库就可以了，但是我这么做以后重启数据库还是存在ONLY\_FULL\_GROUP\_BY