...
This solution uses a third-party library to add PostGres Postgres array support to queries. Postgres arrays
Adding the dependency to cps-ri/pom.xml:
...
This solution involves creating two versions of each repository method taking a collection parameter - one that uses an taking a Java array, and one using a Collection:
...
Using Postgres arrays can simplify existing code
The current code for FragmentRepository has code already using Postgres arrays to support "LIKE ANY" logic (using LIKE with a collection). However, it uses query parameters, and is thus limited to 32K parameters.
| Code Block | ||
|---|---|---|
| ||
@Override
public void deleteByAnchorIdAndXpathLikeAny(final int anchorId, final Collection<String> xpathPatterns) {
final String queryString = "DELETE FROM fragment f WHERE f.anchor_id = ? AND xpath LIKE ANY (array[:parameterPlaceholders])";
executeUpdateWithAnchorIdAndCollection(queryString, anchorId, xpathPatterns);
}
// Accept security hotspot as placeholders in SQL query are created internally, not from user input.
@SuppressWarnings("squid:S2077")
private void executeUpdateWithAnchorIdAndCollection(final String sqlTemplate, final int anchorId, final Collection<String> collection) {
if (!collection.isEmpty()) {
final String parameterPlaceholders = String.join(",", Collections.nCopies(collection.size(), "?"));
final String queryStringWithParameterPlaceholders =
sqlTemplate.replaceFirst(":parameterPlaceholders\\b", parameterPlaceholders);
final Query query = entityManager.createNativeQuery(queryStringWithParameterPlaceholders);
query.setParameter(1, anchorId);
int parameterIndex = 2;
for (final String parameterValue : collection) {
query.setParameter(parameterIndex++, parameterValue);
}
query.executeUpdate();
}
} |
Using native Postgres arraysarray support, this code can be written directly in the FragmentRepository interface using Query annotation:
...
- Both solutions have same performance (as determined using Groovy performance tests modified to take 100,000 items)
- Solution #1 is explicit, and easy to understand
- Solution #2 is shorter, but may be not as clear to the reader what it is used for
- Solution #2 is more flexible, adding array support unlocks more Postgres features.
- It's possible to combine both solutions.