Spring Data Jpa使用总结——使用@Query返回指定json格式的数据

数据库中有student表：

 

表中有数据： 

现需要获取表中name和address字段的值，并以下列json格式返回：

---

编写代码：

1、student表对应的实体类

package com.example.demo;

import lombok.Data;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

/**
 * @author 肖政宇
 * @date 2019-09-05 20:23
 * 说明：student表对应的实体类
 */
@Data
@Entity
@Table(name = "student")
public class StudentEntity {
    @Id
    private String id;
    @Column
    private String name;
    @Column
    private Integer age;
    @Column
    private String sex;
    @Column
    private String address;
    @Column
    private String phone;

    public StudentEntity() {
    }

    public StudentEntity(String id, String name, Integer age, String sex, String address, String phone) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.sex = sex;
        this.address = address;
        this.phone = phone;
    }
}

2、reposotory接口

 

package com.example.demo;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

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

/**
 * @author 肖政宇
 * @date 2019-09-05 20:27
 * 说明：
 */
public interface StudentRepository extends PagingAndSortingRepository<StudentEntity, String> {

    @Query(value = "SELECT id,address FROM student", nativeQuery = true)
    List<String> findIdAndAddress1();

    @Query(value = "SELECT id,address FROM student", nativeQuery = true)
    List<Map<String, Object>> findIdAndAddress2();

    @Query(value = "SELECT id as 'ID',address as 'ADDRESS' FROM student", nativeQuery = true)
    List<Map<String, Object>> findIdAndAddress3();
}

3、定义3个接口分别调用3个函数

package com.example.demo;

import com.example.util.msg.Message;
import com.example.util.msg.MessageBox;
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.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

/**
 * @author 肖政宇
 * @date 2019-09-05 20:27
 * 说明：
 */
@RestController
@RequestMapping("student/")
public class StudentController {
    @Autowired
    StudentRepository repository;

    @GetMapping("findAll")
    @ResponseBody
    public Message findAll() {
        return new MessageBox<>(repository.findAll());
    }

    @GetMapping("findIdAndAddress2")
    @ResponseBody
    public Message findIdAndAddress2() {
        return new MessageBox<>(repository.findIdAndAddress2());
    }

    @GetMapping("findIdAndAddress3")
    @ResponseBody
    public Message findIdAndAddress3() {
        return new MessageBox<>(repository.findIdAndAddress3());
    }

    @GetMapping("findIdAndAddress1")
    @ResponseBody
    public Message findIdAndAddress1() {
        return new MessageBox<>(repository.findIdAndAddress1());
    }
}

4、分别调用3个接口，接收的数据的格式如下：

函数1：

    @Query(value = "SELECT id,address FROM student", nativeQuery = true)
    List<String> findIdAndAddress1();

数据1:

---

函数2：

    @Query(value = "SELECT id,address FROM student", nativeQuery = true)
    List<Map<String, Object>> findIdAndAddress2();

数据2：

---

函数3：

    @Query(value = "SELECT id as 'ID',address as 'ADDRESS' FROM student", nativeQuery = true)
    List<Map<String, Object>> findIdAndAddress3();

数据3：

其实方法3就只是简单的sql语句的应用，但我在实现这个功能的时候一开始没想到这个方法，走了许多弯路。