SpringBoot之联表查询数据返回

Spring Boot处理联表查询的数据返回

通过Vo实现

新建Vo实体，存储联表查询返回的数据

Vo实体，UserFeedback.java

package com.clh.literature.model;

public class UserFeedback {

    private String openId;
    private String penName;
    private String headPortrait;
    private String signature;
    private int integral;
    private String title;
    private Boolean freezeWhether;
    private long feedbackId;
    private String feedbackContent;


    public String getOpenId() {
        return openId;
    }

    public void setOpenId(String openId) {
        this.openId = openId;
    }


    public String getPenName() {
        return penName;
    }

    public void setPenName(String penName) {
        this.penName = penName;
    }


    public String getHeadPortrait() {
        return headPortrait;
    }

    public void setHeadPortrait(String headPortrait) {
        this.headPortrait = headPortrait;
    }


    public String getSignature() {
        return signature;
    }

    public void setSignature(String signature) {
        this.signature = signature;
    }


    public int getIntegral() {
        return integral;
    }

    public void setIntegral(int integral) {
        this.integral = integral;
    }


    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }


    public Boolean getFreezeWhether() {
        return freezeWhether;
    }

    public void setFreezeWhether(Boolean freezeWhether) {
        this.freezeWhether = freezeWhether;
    }


    public long getFeedbackId() {
        return feedbackId;
    }

    public void setFeedbackId(long feedbackId) {
        this.feedbackId = feedbackId;
    }


    public String getFeedbackContent() {
        return feedbackContent;
    }

    public void setFeedbackContent(String feedbackContent) {
        this.feedbackContent = feedbackContent;
    }
}

Mapper查询：FeedbackMapper.java

package com.clh.literature.mapper;

import com.clh.literature.model.UserFeedback;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface FeedbackMapper {

    @Select("SELECT * FROM feedback NATURAL JOIN user ORDER BY feedback_id DESC;")
    @Result(column = "feedback_id", property = "feedbackId")
    @Result(column = "feedback_content", property = "feedbackContent")
    @Result(column = "open_id", property = "openId")
    @Result(column = "pen_name", property = "penName")
    @Result(column = "head_portrait", property = "headPortrait")
    @Result(column = "freeze_whether", property = "freezeWhether")
    List<UserFeedback> getAllFeedback();
}

common层：JsonApi.java

com.clh.literature.common;

import com.alibaba.fastjson.JSONObject;
import org.springframework.stereotype.Service;

@Service
public class JsonApi {
    public String toJson(int code, Object data){
        JSONObject jsonObject = new JSONObject();

        jsonObject.put("code", code);
        jsonObject.put("data", data);

        return jsonObject.toJSONString();
    }
}

服务层：FeedbackService.java

package com.clh.literature.service;

import com.alibaba.fastjson.JSON;
import com.clh.literature.common.JsonApi;
import com.clh.literature.mapper.FeedbackMapper;
import com.clh.literature.model.Feedback;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class FeedbackService {

    @Autowired
    private JsonApi jsonApi;

    @Autowired
    private FeedbackMapper feedbackMapper;

    public String getAllFeedback(){
        try{
            Object UserFeedbackObject = JSON.toJSON(feedbackMapper.getAllFeedback());
            return jsonApi.toJson(1, UserFeedbackObject);
        }catch (Exception e){
            return jsonApi.toJson(0, null);
        }
    }
}

控制层：FeedbackController.java

package com.clh.literature.controller;

import com.clh.literature.service.FeedbackService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class FeedbackController {

    @Autowired
    private FeedbackService feedbackService;

    // 获取所有的用户反馈数据+用户数据
    @GetMapping("/feedback")
    public String getAllFeedback(){
        return feedbackService.getAllFeedback();
    }
}

通过Map实现

Map存储联表查询返回的数据
(JsonApi.java同上)

Mapper层示例

package com.clh.literature.mapper;

import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface AttentionMapper {

    @Select("SELECT * FROM attention, user WHERE attention.open_id = user.open_id AND attention.open_id_active = #{openIdActive};")
    @Result(column = "open_id", property = "openId")
    @Result(column = "pen_name", property = "penName")
    @Result(column = "head_portrait", property = "headPortrait")
    @Result(column = "freeze_whether", property = "freezeWhether")
    @Result(column = "attention_id", property = "attentionId")
    @Result(column = "open_id_active", property = "openIdActive")
    List<Map<String, Object>> getAllAttentionUser(String openIdActive);
}

服务层示例

package com.clh.literature.service;

import com.alibaba.fastjson.JSON;
import com.clh.literature.common.JsonApi;
import com.clh.literature.mapper.AttentionMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class AttentionService {

    @Autowired
    private JsonApi jsonApi;

    @Autowired
    private AttentionMapper attentionMapper;

    public String getAllAttentionUser(String openIdActive){
        try{
            Object attentionObject = JSON.toJSON(attentionMapper.getAllAttentionUser(openIdActive));
            return jsonApi.toJson(1, attentionObject);
        }catch (Exception e){
            return jsonApi.toJson(0, null);
        }
    }
}

控制层示例

package com.clh.literature.controller;

import com.clh.literature.service.AttentionService;
import org.apache.ibatis.annotations.Select;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

@RestController
public class AttentionController {

    @Autowired
    private AttentionService attentionService;

    @GetMapping("/attention/{openIdActive}")
    public String getAllAttentionUser(@PathVariable String openIdActive){
        return attentionService.getAllAttentionUser(openIdActive);
    }
}