Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

extract parameters and count results #674

Open
ymajoros opened this issue Nov 8, 2023 · 4 comments
Open

extract parameters and count results #674

ymajoros opened this issue Nov 8, 2023 · 4 comments

Comments

@ymajoros
Copy link

ymajoros commented Nov 8, 2023

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
Copy link
Owner

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

@ymajoros
Copy link
Author

ymajoros commented Nov 8, 2023

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
Copy link
Owner

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

ymajoros pushed a commit to ymajoros/hypersistence-utils that referenced this issue Nov 8, 2023
@ymajoros
Copy link
Author

ymajoros commented Nov 8, 2023

#675

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants