Spring boot mybatis 子查询

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