Getting an error 'Multiple ResultSets were returned" while running multiple select queries via JPA EntityManager

Question

To achieve better performance by reducing multiple DB calls, I want to execute multiple select queries in the same DB call. I'm using PostgresSQL database (version 14.9), Java (version 17) and Springboot (version 3.0.6).

Here is my code:

private final EntityManager entityManager;

final List<Object[]> results =
            entityManager.createNativeQuery("select * from roles where role_id <= 200; select * from departments where department_id <= 50")
                .getResultList();

Here is the error that I'm getting:

jakarta.persistence.PersistenceException: Converting `org.hibernate.exception.GenericJDBCException` to JPA `PersistenceException` : JDBC exception executing SQL [select * from roles where role_id <= 200; select * from departments where department_id <= 50]
........
........
........
........
........
Caused by: org.hibernate.exception.GenericJDBCException: JDBC exception executing SQL [select * from roles where role_id <= 200; select * from departments where department_id <= 50]
........
........
........
........
........
Caused by: org.postgresql.util.PSQLException: Multiple ResultSets were returned by the query.
    at org.postgresql.jdbc.PgStatement.getSingleResultSet(PgStatement.java:257)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:138)
    at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:217)   

I tried below code:

private final EntityManager entityManager;

final List<Object[]> results =
            entityManager.createNativeQuery("select * from roles where role_id <= 200; select * from departments where department_id <= 50")
                .getResultList();

I am expecting 2 result sets in "results" List Object.

Answer 1

You're already creating trouble for yourself by doing select * from 2 different tables without a limit parameter.

You can either create a stored procedure and select whatever column you need OR you can do nested select statements and use it to query. You can always get a single resultset and use it. Refer Spring Boot, JPA / Hibernate: How to execute two raw SELECT queries at once?

But the performance hit you're referring is not due to multiple db queries, rather it's due to select * statements without limit parameter.

Answer 2

That is not how hibernate works. Hibernate does not let you pass more than 1 query.

If you really want to do so, you can use UNION ALL. However I don't see/know if the number of columns in both your roles and department table match.

Your approach to the result won't work at all or even if you use UNION ALL, that won't be a good practice given the case that you are trying to fetch results from 2 different tables.

Answer 3

With Hibernate and JPA entityManager.createNativeQuery() typically expects a single query, not multiple ones separated by a semicolon, and in your case UNION might not work as you are querying different entities not referenced to each other but collecting master values.

Two options come to my mind typically when querying distinct entities to collect master entries into one resiult set -

Use Stored Procedure: Sample stored procedure to collect multiple master values (when relational queries are not possible / not required) into one JSON object. This might reduce your roundtrips.

CREATE OR REPLACE FUNCTION get_department_designation()
RETURNS JSON AS $$
DECLARE
    departments_json json;
    desitnation_json json;
BEGIN
    -- Execute the first query and convert the department result set to JSON
    SELECT json_agg(
    json_build_object(
        'department_id', id,
        'department_name', name
    )
) INTO departments_json FROM my_department WHERE is_active = 1;

    -- Execute the second query and convert the designation result set to JSON
    SELECT json_agg(
    json_build_object(
        'designation_id', id,
        'designation_name', name
    )
) INTO desitnation_json FROM my_designation WHERE is_active = 1;

    -- Combine the two JSON objects into a single object
    RETURN json_build_object(
        'departments', departments_json,
        'designations', desitnation_json
    );
END;
$$ LANGUAGE plpgsql;

I have used this approach multiple times in past to query and collect distinct entity sets in a result set.

Or use JDBC Batch:

Statement statement = connection.createStatement();
statement.addBatch("select * from roles where role_id <= 200");
statement.addBatch("select * from departments where department_id <= 50");
List<ResultSet> resultSets = statement.executeBatch();

(I haven't tried this, just an idea if it helps)