数据库中有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语句的应用,但我在实现这个功能的时候一开始没想到这个方法,走了许多弯路。