extract parameters and count results

[ymajoros]

I think it would be useful to be able to extract query parameters (also auto-generated sql query parameters from a JPA criteria query, even if it has no explicit parameters).

I have some example code working, at least in my context. I also use it to count results in pure SQL (the only way IMO to cover all cases without resorting to overcomplicated code), so I also shared this below. That part is maybe more database-specific, and I don't know if it also belongs in this library.

I could make one or more pull requests if it sounds interesting enough. What are your thoughts about this?

` public long countResults(CriteriaQuery<?> query) {
return countResults(query, Object::toString);
}

public long countResults(CriteriaQuery<?> query, Function<Object, Object> parameterConverter) {
    TypedQuery<?> typedQuery = entityManager.createQuery(query);
    return countResults(typedQuery, parameterConverter);
}

/**
 * Counts the number of results returned by the given typed query.
 *
 * @param typedQuery         the typed query to count results from
 * @param parameterConverter the function used to convert query parameters
 * @return the number of results as a long
 */
public long countResults(TypedQuery<?> typedQuery, Function<Object, Object> parameterConverter) {
    String sql = SQLExtractor.from(typedQuery);
    String unpaginatedSql = unpaginate(sql);
    String countSql = "select count(*) from (%s) main".formatted(unpaginatedSql);
    Query countNativeQuery = entityManager.createNativeQuery(countSql);

    List<?> parameterValues = getParameterValues(typedQuery);

    int i = 1;
    for (Object parameterValue : parameterValues) {
        Object convertedParameterValue = parameterConverter.apply(parameterValue);
        countNativeQuery.setParameter(i++, convertedParameterValue);
    }

    return (long) countNativeQuery.getSingleResult();
}

/**
 * Removes the pagination clause from the given paginated SQL statement. Only partly implemented (MySQL, PostgreSQL, Oracle). Only needed for paginated queries, so an easy way of avoiding this is to create an unpaginated version of the same TypedQuery.
 *
 * @param paginatedSql the paginated SQL statement to be unpaginated
 * @return the unpaginated SQL statement
 */
public String unpaginate(String paginatedSql) {
    return paginatedSql
        .replace("limit ?,?", "") // mysql
        .replace("limit ? offset ?", "") // postgresql
        .replace("where r_0_.rn<=?+? and r_0_.rn>? order by r_0_.rn", ""); // oracle
}

public List<?> getParameterValues(TypedQuery<?> typedQuery) {
    Set<JpaCriteriaParameter<?>> parameters = getParameters(typedQuery);
    return parameters.stream()
        .map(JpaCriteriaParameter::getValue)
        .toList();
}

public Set<JpaCriteriaParameter<?>> getParameters(TypedQuery<?> typedQuery) {
    SqmQueryImplementor<?> querySqm = typedQuery.unwrap(SqmQueryImplementor.class);
    SqmStatement<?> sqmStatement = querySqm.getSqmStatement();

    Set<JpaCriteriaParameter<?>> parameters = new LinkedHashSet<>();
    ParameterCollector.collectParameters(
        sqmStatement,
        sqmParameter -> {
            JpaCriteriaParameter<?> jpaCriteriaParameter = ((SqmJpaCriteriaParameterWrapper<?>) sqmParameter).getJpaCriteriaParameter();
            parameters.add(jpaCriteriaParameter);
        },
        sqmStatement.nodeBuilder().getServiceRegistry()
    );
    return parameters;
}`

[vladmihalcea]

You can send the Pull Request if you think it's useful to have it integrated.

[ymajoros]

Well, I'm a little bit mitigated for the count part:

• difficult to test without an actual db (though I had it working on mysql & oracle)
• some parts are db specific (the unpagination part above, though you can avoid it)

But maybe that's inherent to multiple aspects of this library?

What do you think?

[vladmihalcea]

The count part is not needed, but being able to extract the parameters would be useful.

[ymajoros]

#675

It also resolves #668 as I needed it for my pull request (and refactored it further).