Spring Boot将MySQL查询结果转为JSONArray时失败及EntityManager注入为空问题求助

我有一个查询在MySQL Workbench手动执行完全正常，但在Spring Boot中运行就失败了。手动执行的查询语句如下：

"select labs.collected,biovals.value,biomark.high,biomark.low,biomark.name,biomark.unit from labs\n" + //
"inner join biovals on labs.id = biovals.lab_id\n" + //
"inner join biomark on biovals.biomar_id = biomark.id\n" + //
"where labs.collected='2010-10-29'"

手动查询返回76行数据，示例结果如下：

collected     value   high    low     name          unit
'2010-10-29', '4.4',  '10.8', '3.4',  'WBC',        'x10E3/uL'
'2010-10-29', '5.1',  '6',    '3.77', 'RBC',        'x10E6/uL'
'2010-10-29', '16',   '18',   '11.1', 'Hemoglobin', 'g/dL'
'2010-10-29', '46.9', '55',   '34',   'Hematocrit', '%'

我希望Spring Boot能返回类似结构的JSONArray，期望输出如下：

[
{"collected":"2010-10-29", "value":"4.4", "high":"10.8", "low": "3.4", "name":"WBC", "unit":"x10E3/uL"}
...
]

相关代码文件 #

labController.java #

@GetMapping("/get-biovals-by-date")
public JSONArray getBiovalsByDate() {
    JSONArray biovalsByDate = labService.getBiovalsByDate();
    return biovalsByDate;
}

labService.java #

@Service
public interface LabService {
    JSONArray getBiovalsByDate();
}

LabServiceImpl.java #

@Component
public class LabServiceImpl implements LabService {
    @Autowired
    BiovalRepo biovalRepo;

    public JSONArray getBiovalsByDate() {
        JSONArray jsonArray = new JSONArray();
        try {
            ResultSet rs = biovalRepo.getBiovalsByDate();  //error happens here
            while (rs.next()) {
                int columns = rs.getMetaData().getColumnCount();
                JSONObject obj = new JSONObject();
                for (int i = 0; i < columns; i++)
                    obj.put(rs.getMetaData().getColumnLabel(i + 1).toLowerCase(), rs.getObject(i + 1));
                jsonArray.put(obj);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return jsonArray;
    }
}

BioRepo.java #

@Repository
public interface BiovalRepo extends JpaRepository<Bioval, Integer> {
    
@Query(value = "select labs.collected,biovals.value,biomark.high,biomark.low,biomark.name,biomark.panel,biomark.unit from labs\n" + //
"inner join biovals on labs.id = biovals.lab_id\n" + //
"inner join biomarkers on biovals.biomarker_id = biomarkers.id\n" + //
"where labs.date_collected='2010-10-29'", nativeQuery = true)
ResultSet getBiovalsByDate();
}

运行后出现如下错误：

Query did not return a unique result: 76 results were returned

后续尝试（EDIT） #

我尝试了另一种方式，用EntityManager返回Map列表，这样就不用为每个查询单独写POJO了：

修改后的BiovalRepo.java #

@Repository
public class BiovalRepo {

    @PersistenceContext
    private EntityManager entityManager;
    
    public List<Map<String, Object>> getBiovalsByDate() {
        String sql = "select labs.date_collected,labs.note,biovals.value,biomarkers.high,biomarkers.low,biomarkers.name,biomarkers.panel,biomarkers.unit from labs"+
        "inner join biovals on labs.id = biovals.lab_id" + 
        "inner join biomarkers on biovals.biomarker_id = biomarkers.id" + 
        "where labs.date_collected='2010-10-29'";
        Query query = entityManager.createNativeQuery(sql);
        List<Object[]> results = query.getResultList();

        String[] columnNames = entityManager.getMetamodel().entity(results.get(0).getClass()).getAttributes().stream()
                .map(attribute -> attribute.getName()).toArray(String[]::new);

        return results.stream()
                .map(result -> {
                    Map<String, Object> map = new HashMap<>();
                    for (int i = 0; i < columnNames.length; i++) {
                        map.put(columnNames[i], result[i]);
                    }
                    return map;
                })
                .collect(Collectors.toList());
    }
}

但不幸又出现了新错误：

Cannot invoke "javax.persistence.EntityManager.createNativeQuery(String)" because "this.entityManager" is null

我到底哪里做错了？

备注：内容来源于stack exchange，提问作者user3217883