admin管理员组文章数量:1648284
数据库有三张表：item area location 前者与后者均为一对多的关系，在前端形成一级目录、二级目录、三级目录的形式。因此发往前端的json中需要嵌套表示。
使用springboot框架，maven架构项目，sqlserver数据库。springboot整合sqlserver我的配置以及热部署在其他博客里。
实现思路
- 先查询check表中的item_id，查询共有几组item（一级目录），将结果存入list集合中；
- 根据item_id查询check表中的area_id，查询共有几组area（二级目录），结果存入list集合中；
- 根据item_id,area_id查询check表中的location_id，查询共有几组location（三级目录），结果存入list集合中；
- 上一级目录实体类中创建下一级目录的对象集合属性；
- 遍历第3步中的集合，将查询的数据依次封装到对象中，并将对象推入新建的集合；
- 遍历第2步中的集合，将查询的数据和第5步的集合依次封装到对象中，并将对象推入新建的集合；
- 遍历第1步中的集合，将查询的数据和第6步的集合依次封装到对象中，并将对象推入返回的集合。
实体类
public class Item {
private int itemId;
private String itemName; // 项目uuid
private List<Area> area; // 区域
public Item() {
super();
}
public Item(int itemId, String itemName, List<Area> area) {
super();
this.itemId = itemId;
this.itemName = itemName;
this.area = area;
}
}
public class Area {
private int areaId;
private String areaName; // 名称
private List<Location> location; // 地点
public Area() {
super();
}
public Area(int areaId, String areaName, List<Location> location) {
super();
this.areaId = areaId;
this.areaName = areaName;
this.location = location;
}
}
public class Location {
private int locationId;
private String locationName; // 名称
private Spot spot;
public Location() {
super();
}
public Location(int locationId, String locationName, Spot spot) {
super();
this.locationId = locationId;
this.locationName = locationName;
this.spot = spot;
}
}
public class Spot {
private String picCheck1; // 检查照片1
private String picCheck2; // 检查照片2
private String record; // 记录
private String responsible; // 负责方
private String examiner; // 检查人
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private Date checkTime; // 检查时间
public Spot(String picCheck1, String picCheck2, String record, String responsible, String examiner,
Date checkTime) {
super();
this.picCheck1 = picCheck1;
this.picCheck2 = picCheck2;
this.record = record;
this.responsible = responsible;
this.examiner = examiner;
this.checkTime = checkTime;
}
}
数据层
public interface SpotDao {
/**
* 查询表中有几组item
*
* @return
*/
List<Item> itemIdList();
/**
* 根据item查询几组area
*
* @param itemId
* @return
*/
List<Area> areaIdList(@Param("itemId") int itemId);
/**
* 根据item area查询几组location
*
* @param itemId
* @param areaId
* @return
*/
List<Location> locationList(@Param("itemId") int itemId, @Param("areaId") int areaId);
/**
* 根据item area location 查询数据
* @param itemId
* @param areaId
* @param locationId
* @return
*/
Spot spotList(@Param("itemId") int itemId, @Param("areaId") int areaId,
@Param("locationId") int locationId);
}
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis//DTD Mapper 3.0//EN" "http://mybatis/dtd/mybatis-3-mapper.dtd" >
<mapper
namespace="testmaven05spotcheck.spot.check.dao.SpotDao">
<resultMap id="itemMap"
type="testmaven05spotcheck.spot.check.pojo.Item">
<result property="itemId" column="item_id" />
<result property="itemName" column="item" />
</resultMap>
<resultMap id="areaMap"
type="testmaven05spotcheck.spot.check.pojo.Area">
<result property="areaId" column="area_id" />
<result property="areaName" column="area" />
</resultMap>
<resultMap id="locationMap"
type="testmaven05spotcheck.spot.check.pojo.Location">
<result property="locationId" column="location_id" />
<result property="locationName" column="location" />
</resultMap>
<resultMap id="spotMap"
type="testmaven05spotcheck.spot.check.pojo.Spot">
<result property="picCheck1" column="pic_check1" />
<result property="picCheck2" column="pic_check2" />
<result property="record" column="record" />
<result property="responsible" column="responsible" />
<result property="examiner" column="examiner" />
<result property="checkTime" column="check_time" />
</resultMap>
<select id="itemIdList" resultMap="itemMap">
select c.item_id,i.item
from
t_check c inner join t_item i on c.item_id=i.id
inner join t_area a on
c.area_id=a.id
inner join t_location l on c.location_id=l.id
group by
c.item_id,i.item
</select>
<select id="areaIdList" resultMap="areaMap">
select c.area_id,a.area
from
t_check c inner join t_item i on c.item_id=i.id
inner join t_area a on
c.area_id=a.id
inner join t_location l on c.location_id=l.id
where
c.item_id=#{itemId}
group by c.area_id,a.area
</select>
<select id="locationList" resultMap="locationMap">
select
c.location_id,l.location
from t_check c inner join t_item i on
c.item_id=i.id
inner join t_area a on c.area_id=a.id
inner join
t_location l on c.location_id=l.id
where c.item_id=#{itemId} and c.area_id=#{areaId}
group by c.location_id,l.location
</select>
<select id="spotList" resultMap="spotMap">
select pic_check1,pic_check2,record,responsible,examiner,check_time
from t_check c inner join t_item i on c.item_id=i.id
inner join t_area
a on c.area_id=a.id
inner join t_location l on c.location_id=l.id
<where>
<if test="itemId!=0">
c.item_id=#{itemId}
<if test="areaId!=0">
and c.area_id=#{areaId}
<if test="locationId!=0">
and c.location_id=#{locationId}
</if>
</if>
</if>
</where>
</select>
</mapper>
服务层
在这里完成对查询出的数据的json嵌套格式操作
@Service
public class SpotServiceImpl implements SpotService {
@Autowired
private SpotDao spotDao;
@Override
public List<Item> getAllSpot() {
Item itemResult = null;
Area areaResult = null;
Location locationResult = null;
List<Item> itemResultList = new ArrayList<Item>();
// 搜索表中共有多少item id
List<Item> itemList = spotDao.itemIdList();
for (Item item : itemList) {
// 根据每个item id搜索area
List<Area> areaList = spotDao.areaIdList(item.getItemId());
List<Area> areaResultList = new ArrayList<Area>();
for (Area area : areaList) {
// 根据每个area id搜索location
List<Location> locationList = spotDao.locationList(item.getItemId(), area.getAreaId());
List<Location> locationResultList = new ArrayList<Location>();
for (Location location : locationList) {
// 根据每个location搜索对应的spot
Spot spot = spotDao.spotList(item.getItemId(), area.getAreaId(), location.getLocationId());
// 将spot、location的id、name放入一个对象locationResult中
locationResult = new Location(location.getLocationId(), location.getLocationName(), spot);
// 将对象放入集合中
locationResultList.add(locationResult);
}
// 将location集合、area的id、name放入对象areaResult中
areaResult = new Area(area.getAreaId(), area.getAreaName(),locationResultList);
// 将对象放入集合中
areaResultList.add(areaResult);
}
// 将area集合、item id、name放入对象itemResult中
itemResult = new Item(item.getItemId(), item.getItemName(), areaResultList);
// 将对象放入集合中
itemResultList.add(itemResult);
}
return itemResultList;
}
}
控制层
规定访问路径，调用接口
@RestController
@RequestMapping("/spot")
public class SpotController {
@Autowired
private SpotService spotService;
/**
* 查询所有数据，并在json中嵌套显示
* @return
*/
@RequestMapping("/spotall")
public Object getAllSpot() {
List<Item> spot = spotService.getAllSpot();
return new SpotMessage(true, "数据", spot);
}
}
json数据
{
"status": true,
"message": "数据",
"data": [
{
"itemId": 1,
"itemName": "4a6494f1-d0f4-476a-9809-d3b09dcfc379",
"area": [
{
"areaId": 1,
"areaName": "锌锅至出口",
"location": [
{
"locationId": 1,
"locationName": "锌锅气刀",
"spot": {
"picCheck1": "pic1-1",
"picCheck2": "pic1-2",
"record": "气刀刀架锌渣、锌粉积聚",
"responsible": "生产清扫",
"examiner": "朱炜毛飞俊潘宏清",
"checkTime": "2020-12-25 14:41:59"
}
},
{
"locationId": 2,
"locationName": "锌锅炉鼻子",
"spot": {
"picCheck1": "pic2-1",
"picCheck2": "",
"record": "炉鼻子锌渣、锌粉积聚",
"responsible": "生产清扫",
"examiner": "朱炜毛飞俊潘宏清",
"checkTime": "2020-12-25 14:42:19"
}
},
{
"locationId": 3,
"locationName": "镀后冷却风箱喷嘴口（1＃风机）",
"spot": {
"picCheck1": "pic3-1",
"picCheck2": "",
"record": "锌片",
"responsible": "生产清扫",
"examiner": "朱炜毛飞俊潘宏清",
"checkTime": "2020-12-25 14:42:40"
}
},
{
"locationId": 10,
"locationName": "8＃CPC",
"spot": {
"picCheck1": "pic10-1",
"picCheck2": "",
"record": "辊子支架端面锌泥积聚",
"responsible": "生产清扫",
"examiner": "朱炜毛飞俊潘宏清",
"checkTime": "2020-12-25 14:44:41"
}
},
{
"locationId": 11,
"locationName": "8＃CPC感应框",
"spot": {
"picCheck1": "pic11-1",
"picCheck2": "",
"record": "锌渣、锌粉积聚",
"responsible": "生产清扫",
"examiner": "朱炜毛飞俊潘宏清",
"checkTime": "2020-12-25 14:44:45"
}
}
]
},
{
"areaId": 2,
"areaName": "铜锅到大门",
"location": [
{
"locationId": 4,
"locationName": "镀后冷却风箱喷嘴口（2＃风机）",
"spot": {
"picCheck1": "pic4-1",
"picCheck2": "",
"record": "锌片",
"responsible": "生产清扫",
"examiner": "朱炜毛飞俊潘宏清",
"checkTime": "2020-12-25 14:42:50"
}
},
{
"locationId": 5,
"locationName": "1＃顶辊抛光器",
"spot": {
"picCheck1": "pic5-1",
"picCheck2": "pic5-2",
"record": "导向丝杆变形弯曲、轴瓦开裂、限位变形",
"responsible": "设备处理",
"examiner": "朱炜毛飞俊潘宏清",
"checkTime": "2020-12-25 14:43:00"
}
},
{
"locationId": 8,
"locationName": "8＃CPC顶棚阳光板",
"spot": {
"picCheck1": "pic8-1",
"picCheck2": "",
"record": "顶棚阳光板积灰",
"responsible": "生产清扫",
"examiner": "朱炜毛飞俊潘宏清",
"checkTime": "2020-12-25 14:44:29"
}
}
]
},
{
"areaId": 3,
"areaName": "铁锅到大鹅",
"location": [
{
"locationId": 6,
"locationName": "1＃顶辊",
"spot": {
"picCheck1": "pic6-1",
"picCheck2": "pic6-2",
"record": "两侧轴承座干油脂节油盒漫溢",
"responsible": "生产清扫",
"examiner": "朱炜毛飞俊潘宏清",
"checkTime": "2020-12-25 14:43:06"
}
},
{
"locationId": 7,
"locationName": "2＃顶辊",
"spot": {
"picCheck1": "pic7-1",
"picCheck2": "",
"record": "下方通板口无止挡块，垃圾易掉入通道线",
"responsible": "设备处理",
"examiner": "朱炜毛飞俊潘宏清",
"checkTime": "2020-12-25 14:44:23"
}
},
{
"locationId": 9,
"locationName": "8＃CPC压辊",
"spot": {
"picCheck1": "pic9-1",
"picCheck2": "",
"record": "轴承座油脂积聚",
"responsible": "生产清扫",
"examiner": "朱炜毛飞俊潘宏清",
"checkTime": "2020-12-25 14:44:36"
}
}
]
}
]
},
{
"itemId": 3,
"itemName": "1a256668-bd75-4b8f-8bdc-b897ac8ac3ce",
"area": [
{
"areaId": 1,
"areaName": "锌锅至出口",
"location": [
{
"locationId": 1,
"locationName": "锌锅气刀",
"spot": {
"picCheck1": "pic1-1",
"picCheck2": "pic1-2",
"record": "气刀刀架锌渣、锌粉积聚",
"responsible": "生产清扫",
"examiner": "朱炜毛飞俊潘宏清",
"checkTime": "2020-12-25 14:48:09"
}
},
{
"locationId": 2,
"locationName": "锌锅炉鼻子",
"spot": {
"picCheck1": "pic2-1",
"picCheck2": "",
"record": "炉鼻子锌渣、锌粉积聚",
"responsible": "生产清扫",
"examiner": "朱炜毛飞俊潘宏清",
"checkTime": "2020-12-25 14:48:15"
}
},
{
"locationId": 3,
"locationName": "镀后冷却风箱喷嘴口（1＃风机）",
"spot": {
"picCheck1": "pic3-1",
"picCheck2": "",
"record": "锌片",
"responsible": "生产清扫",
"examiner": "朱炜毛飞俊潘宏清",
"checkTime": "2020-12-25 14:48:21"
}
}
]
},
{
"areaId": 2,
"areaName": "铜锅到大门",
"location": [
{
"locationId": 4,
"locationName": "镀后冷却风箱喷嘴口（2＃风机）",
"spot": {
"picCheck1": "pic4-1",
"picCheck2": "",
"record": "锌片",
"responsible": "生产清扫",
"examiner": "朱炜毛飞俊潘宏清",
"checkTime": "2020-12-25 14:48:27"
}
},
{
"locationId": 5,
"locationName": "1＃顶辊抛光器",
"spot": {
"picCheck1": "pic5-1",
"picCheck2": "pic5-2",
"record": "导向丝杆变形弯曲、轴瓦开裂、限位变形",
"responsible": "设备处理",
"examiner": "朱炜毛飞俊潘宏清",
"checkTime": "2020-12-25 14:48:32"
}
}
]
}
]
}
]
}
小结
需要注意的是，服务层中需要注意存放结果的集合的初始化位置。应在每次循环开始时初始化集合，而不是在方法开始时，否则会出现将每级目录的所有数据分别推入相应集合的现象。
版权声明：本文标题：在一对多的情况下发送嵌套json 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：https://www.elefans.com/xitong/1729494623a1202788.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论