spring native query调用返回json的函数

pexxcrt2  于 2023-05-16  发布在  Spring
关注(0)|答案(1)|浏览(161)

我是Java新手,我试图调用一个返回JSON对象的函数,但是Java方法返回一个String。
在postrgres中有没有办法将Json转换为VARCHAR,或者在java中有没有办法将JSONObject转换为jsonString

@Repository
public interface CourseRepository extends JpaRepository<Course, Long> {
    @Query(value = "select* from get_enrolled_courses(:id);", nativeQuery = true)
    JSONObject getStudents(@Param("id") int courseId);

}
`    
@Service
public class CourseServices {
public JSONObject getEnrolledStudentsLight(int id){

        return repository.getStudents(id);
    }`
}
@RestController
@RequestMapping("/api/v2/courses")
public class CourseController {
    @GetMapping ("/{courseId}/students/light")
    public String getEnrolledStudentsLight (@PathVariable int courseId ){
        return service.getEnrolledStudentsLight(courseId).toJSONString();
    }

}

我尝试了toJSONString(),但输出是key:valueString,我只想让值像数据库的输出一样是字符串

create or replace  FUNCTION get_enrolled_courses (student_id int)
    RETURNS  varchar
    LANGUAGE plpgsql
As $$
DECLARE s_id INTEGER;
begin
    select student_id into s_id;
    return (select COALESCE(array_to_json(array_agg(row_to_json(t)) ),'[]'::json)
            from (
                     select c.id,c.title,c.credits,c.updated_at,c.created_at
                     from course_student cs join courses c on c.id = cs.course_id
                     where cs.student_id = s_id
                 ) t);
end;
$$;

DB的输出是:[ {“id”:14,“title”:“计算机科学导论”,“学分”:9,“updated_at”:“2023-05-05T20:56:58.160225”,“created_at”:“2023-05-05T20:56:58.160225”},{“id”:18、“标题”:“ db2 ”,“credits”:12,“updated_at”:“2023-05-05T20:57:20.064462”,“created_at”:“2023-05-05T20:57:20.064462”},{“id”:19、“标题”:“信号与系统”,“学分”:9,“updated_at”:“2023-05-05T20:57:20.066494”,“created_at”:[2023-05-05T20:57:20.066494]
有什么建议吗?

c3frrgcw

c3frrgcw1#

1.我会尝试创建实体aka。学生的DBO而不是返回JSONObject。
1.使用返回字符串JSON表示的ObjectMapper。
System. out. println(); String studentsJSON = objectMapper.writeValueAsString(students);

相关问题