I have a query that works fine when input manually in MySql Workbench, but fails when input from Spring Boot. The manual query is:

"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'"

The manual query returns 76 rows like this

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', '%'

I'd like Spring Boot to return similar in a JSONArray. Expected output is:

[
{"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();
}

When that gets run, an error shows

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

What am I doing wrong?

EDIT

Tried another way with EntityManager to return a list of maps

BiovalRep.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());
    }
}

That would be cool if it worked since you would never have write a separate pogo for every db query. Unfortunately, it produces error:

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

I have a query that works fine when input manually in MySql Workbench, but fails when input from Spring Boot. The manual query is:

"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'"

The manual query returns 76 rows like this

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', '%'

I'd like Spring Boot to return similar in a JSONArray. Expected output is:

[
{"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();
}

When that gets run, an error shows

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

What am I doing wrong?

EDIT

Tried another way with EntityManager to return a list of maps

BiovalRep.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());
    }
}

That would be cool if it worked since you would never have write a separate pogo for every db query. Unfortunately, it produces error:

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

• java
• spring-boot

• I suspect that when you specify the return value is ResultSet, it expects 1 result to return, but your query has 76 rows to return. I think you may want to use a JdbcTemplate instead of using jpa repository abstraction for finer control over the resultset – pebble unit Commented Feb 11 at 0:46
• Though, would it be more straighforward for you to get the JPA query as a List of POJO, then convert it into a JsonArray? – pebble unit Commented Feb 11 at 0:48
• in your repository, you use jpa, which does not return ResultSet, but in this case List<T>. You can create a DTO object and your jpa return List<BiovalDTO>, and in your service just loop over the list. – Hendra Commented Feb 11 at 0:51
• Mysql query does not seem to fail as you do not get a mysql error message. You need to fix your java code. – Shadow Commented Feb 11 at 0:52
• 1 Maybe you are searching for stackoverflow/a/79064817 ? – Thomas Kläger Commented Feb 11 at 7:45

1 Answer 1

You're using an JpaRepository and when you're specifying a native query, it means you're specifying things in terms of SQL (rather than JPQL) and this doesn't mean that JDBC is now exposed (ResultSet is a JDBC class). I suspect that if you did get it to return a ResultSet, it would already be closed by then and hence unusable.

You could retrieve just the data, but this wouldn't give you the metadata you seem to want (but your current approach would struggle with deeply nested data anyway):

List<Object[]> getBiovalsByDate();