[FIXED] How to create a dynamic query using collection-valued named parameters?

Issue

As the title suggests, i'm currently trying to add parts to the JPQL-query using collection-valued named parameters (:queryLst).

Function call:

List<PanelSet> psetLst = setRepository.getMaxZchnrGroupByLeftEight(p.getCustomerNumber(), p.getDrawingNumber(), queryLst);

queryLst:

// Is used to store values from scanned and convert them into parts of a query
ArrayList<String> queryLst = new ArrayList<>();
for (int i = 0; i < size1; i++) {
    scanEdvRev = scanned.get(i).toString();
    queryLst.set(i, "and left(a.drawingnumber, 8) != left('" + scanEdvRev + "', 8)");
}

SetRepository:

public interface SetRepository extends CrudRepository<PanelSet, Integer> {

@Query("select distinct max(a.drawingNumber) from PanelSet a "
        + "where a.customerNumber = :customerNumber "
        + "and a.drawingNumber != :drawingNumber (:queryLst) "
        + "group by left(a.drawingNumber, 8)")
List<PanelSet> getMaxZchnrGroupByLeftEight(@Param("customerNumber") String customerNumber, 
        @Param("drawingNumber") String drawingNumber, 
        @Param("queryLst") ArrayList<String> queryLst);
}

When i run the project i get the following exception:

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 159 [select distinct max(a.drawingNumber) from com.asetronics.qis2.model.PanelSet a where a.customerNumber = :customerNumber and a.drawingNumber != :drawingNumber (:queryLst) group by left(a.drawingNumber, 8)]

I'm unsure whether my approach to this problem is the correct way of doing this and whether this exception is caused by a simple syntax error or by my usage of collection-valued named parameters.

I've followed this guide trying to solve the problem.

EDIT: I'm basically trying to add each String from ArrayList<String> queryLst to the parametrized query inside setRepository.

@Query("select distinct max(a.drawingNumber) from PanelSet a "
    + "where a.customerNumber = :customerNumber "
    + "and a.drawingNumber != :drawingNumber (:queryLst) "
    + "group by left(a.drawingNumber, 8)")

If successful, the query behind the function

List<PanelSet> getMaxZchnrGroupByLeftEight(@Param("customerNumber") String customerNumber, 
            @Param("drawingNumber") String drawingNumber, 
            @Param("queryLst") ArrayList<String> queryLst);

should look like this:

queryStr = "select distinct max(a.drawingNumber) from PanelSet a "
    + "where a.customerNumber = " + customerNumber + ""
    + "and a.drawingNumber != " + drawingNumber + "";
for (String s : queryLst) {
    queryStr = queryStr + s;
}
queryStr = queryStr + " group by left(a.drawingNumber, 8)";

I hope this clarifies what i'm trying to do with queryLst.

Solution

It can't be done using your approach of passing in a list of query chunks.

The closest you'll get is by adding every possible condition to the query and provide values for all those conditions in a way that allows conditions to be ignored, typically by providing a null.

You code might look like this:

@Query("select distinct max(a.drawingNumber) from PanelSet a "
    + "where a.customerNumber = :customerNumber "
    + "and a.drawingNumber != :drawingNumber "
    + "and a.myTextColumn = coalesce(:myTextColumn, a.myTextColumn) "
    + "and a.myIntegerColumn = coalesce(:myIntegerColumn, a.myIntegerColumn) "
    // etc for all possible runtime conditions
    + "group by left(a.drawingNumber, 8)")
List<PanelSet> getMaxZchnrGroupByLeftEight(
    @Param("customerNumber") String customerNumber, 
    @Param("drawingNumber") String drawingNumber, 
    @Param("myTextColumn") String myTextColumn,
    @Param("myIntegerColumn") Integer myIntegerColumn);

Passing null for myTextColumn or myIntegerColumn will allow that column to be any value (except null).

You'll have to find SQL that works for the type of conditions you have and the data type of the columns involved and whether nulls are allowed.

If passing nulls doesn't work, use a special value, perhaps blank for text columns and some "impossible" date like 2999-01-01 fir date columns etc and code the condition like:

and (a.myCol = :myCol or :myCol = '2999-01-01')

Answered By - Bohemian
Answer Checked By - Cary Denson (JavaFixing Admin)