SpringBoot与MySql实现获取存在一对多列表数据结构小案例

Sql建表语句

CREATE TABLE `person` (
  `pid` varchar(32) NOT NULL,
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `t_user` (
  `id` varchar(32) NOT NULL COMMENT '用户主键ID',
  `username` varchar(200) DEFAULT NULL COMMENT '用户名',
  `password` varchar(50) DEFAULT NULL COMMENT '密码',
  `personid` varchar(32) DEFAULT NULL COMMENT 'personID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表数据

 实体类

package com.sb.entity;
 
import java.util.List;
import lombok.Data;
 
/**
 * @version 1.0.
 * @className :Person
 * @Description: 人实体类
 **/
@Data
public class Person {
  private Integer pid;
  private String name;
  List<User> users;
}


@Data
public class User {
  private String id;
  private String username;
  private String password;
  private String personId;
 
}

定义入参对象:

package com.sb.dto;
 
import lombok.Data;
 
/**
 * @version 1.0.
 * @className :PersonReqDto
 * @Description: 入参DTO
 **/
@Data
public class PersonReqDto {
  private String pid;
  private String name;
}

定义访问数据库接口

package com.sb.mapper;
 
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import com.sb.entity.Person;
import com.sb.entity.User;
import com.sb.vo.GetPersonVo;
 
@Mapper
public interface UserMapper {
  /**
   * 获取用户信息列表
   * 
   * @param personId
   * @return
   */
  List<User> selectUserList(@Param("personId") String personId);
 
}

/**
 * @version 1.0.
 * @className :PersonMapper
 * @Description: personMapper接口
 **/
@Mapper
public interface PersonMapper {
  /**
   * 获取人信息列表
   *
   * @param pid
   * @return
   */
  List<Person> selectPersonList(@Param("pid") String pid);
}

查询数据SQL语句

<select id="selectPersonList" resultType="com.sb.entity.Person" parameterType="java.lang.String">
    SELECT * FROM person  where  pid = #{pid}
</select>
 

<select id="selectUserList" resultType="com.sb.entity.User" parameterType="java.lang.String">
    SELECT * FROM t_user where personid= #{personId}
</select>

业务实现类

package com.sb.service.impl;
 
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.sb.common.exception.DemoException;
import com.sb.constant.PublicConstant;
import com.sb.dto.PersonReqDto;
import com.sb.entity.Person;
import com.sb.entity.User;
import com.sb.mapper.PersonMapper;
import com.sb.mapper.UserMapper;
import com.sb.service.IPersonService;
 
/**
 * @version 1.0.
 * @className :PersonServiceImpl
 * @Description: 实现类
 **/
@Service
public class PersonServiceImpl implements IPersonService {
 
  @Autowired
  private UserMapper userMapper;
 
  @Autowired
  private PersonMapper personMapper;
 
 
  @Override
  public List<Person> getPersonList(PersonReqDto personReqDto) {
    if (StringUtils.isBlank(personReqDto.getPid())) {
      // return Collections.EMPTY_LIST;
      throw new DemoException(PublicConstant.FAIL_CODE, PublicConstant.Person.PERSON_IS_NULL);
    }
    // 返回结果集List
    List<Person> result = new ArrayList<>();
    // 人信息列表
    List<Person> personList = personMapper.selectPersonList(personReqDto.getPid());
    // 用户信息列表
    List<User> userList = userMapper.selectUserList(personReqDto.getPid());
    personList.forEach(item -> {
      Person person = new Person();
      // copy
      BeanUtils.copyProperties(item, person);
      // 赋值
      person.setUsers(userList);
      result.add(person);
    });
    return result;
  }
}

控制层类

 @ApiOperation(value = "查询人列表")
  @PostMapping("/listPeron")
  public ResponseEntity listPeron(@RequestBody PersonReqDto personReqDto) {
    List<Person> list = iHdUserService.getPersonList(personReqDto);
    // 封装到Map
    Map<String, Object> dataMap = new HashMap<>();
    dataMap.put("persons", list);
    return new ResponseEntity(PublicConstant.SUCCESS_CODE, PublicConstant.SUCCESS_MSG, dataMap);
  }

接口返回公共对象

package com.sb.util;
 
/**
 * @version 1.0.
 * @className :ResponseEntity
 * @Description: 响应公共类
 **/
public class ResponseEntity {
  /**
   * 返回编码
   */
  private String msgCode;
 
  /**
   * 返回信息
   */
  private String message;
 
  /**
   * 返回的数据
   */
  private Object data;
 
  public ResponseEntity(String msgCode, String message, Object data) {
    this.msgCode = msgCode;
    this.message = message;
    this.data = data;
  }
 
  public String getMsgCode() {
    return msgCode;
  }
 
  public void setMsgCode(String msgCode) {
    this.msgCode = msgCode;
  }
 
  public String getMessage() {
    return message;
  }
 
  public void setMessage(String message) {
    this.message = message;
  }
 
  public Object getData() {
    return data;
  }
 
  public void setData(Object data) {
    this.data = data;
  }
}

接口请求结果

{
  "msgCode": "1000",
  "message": "操作成功",
  "data": {
    "persons": [
      {
        "pid": 1,
        "name": "hagkegjlarg",
        "users": [
          {
            "id": 1,
            "username": "3333333",
            "password": "222",
            "personId": 1
          },
          {
            "id": 2,
            "username": "6666666666",
            "password": "666",
            "personId": 1
          }
        ]
      }
    ]
  }
}