15. JPQL — Where Clause — Passing Parameters | Passing Parameters to defined Named Parameters with setParameter() method

Passing Parameters to defined Named Parameters with setParameter() method

→ Now, we have this below Named Query.

   @Entity
-> @NamedQuery(name = Employee.GET_EMPLOYEE_ALLOWANCES_BY_CONDITION, 
query = "select al from Employee e join e.employeeAllowances al 
where al.allowanceAmount > :greaterThanValue")
   public class Employee extends AbstractEntity {

  public static final String GET_EMPLOYEE_ALLOWANCES_BY_CONDITION = 
  "Employee.getAllowancesByACondition";

  @OneToMany(cascade = {CascadeType.PERSIST, CascadeType.REMOVE})
   private Set<Allowance> employeeAllowances = new HashSet<>();

}

→ The expectation here is that this Named Query will return the Allowance instances where the allowanceAmount is greater than the given greaterThanValue.

-> Let’s remember the definition and explanation of Named Parameters in a JPQL.

Explanation of Named Parameters: Named Parameters are placeholders (String values) defined within the **where** clause of JPQL queries to enable dynamic filtering. They allow queries to be flexible and reusable by letting us assign values dynamically at runtime, rather than hardcoding them. This brings in a dynamic behavior to the “filtering” process which we apply by the where clause.

-> What we should infer from this explanation is, Named Parameters are String placeholders that are residing next to where clause of JPQL’s for bring in the “dynamic filtering” behavior to our JPQL’s.

Keep in Mind: We use **Named Parameters, which are residing next to `where **clause in a JPQL query that the “value” of them will be passed by the user dynamically and that “value” will be substitute at runtime by the JPA provider. The benefit of this approach is, the user can be able to pass any “value” to theNamed Parameter` that we define next to where clause and so that, we can be able to “bring in” the dynamic behavior to our certain-specific whereclause in our relevant JPQL query.

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — -

#Topic: In this article, we will explore how to pass parameters when using a Named JPQL Query (Named Query in JPQL) -> in the relevant method of a Repository object.

#Our Task: How to pass this Named Query to the EntityManager(essentially, how to pass this to the relevant and requisite method of the EntityManager) which we will be use in the relevant method of a Repository object?

Implementation of the Task:

public class QueryRepository {

   @Inject
   EntityManager entityManager;

   public Collection<Allowance> getEmployeeAllowancesByACondition
           (BigDecimal greaterThanValue) {

    TypedQuery<Allowance> queryThatHoldsFilteredAllowances = 
           this.entityManager
          .createNamedQuery(Employee.GET_EMPLOYEE_ALLOWANCES_BY_CONDITION, 
                                     Allowance.class)
     ->     .setParameter("greaterThanValue", greaterThanValue);

    List<Allowance> filteredAllowances = 
               queryThatHoldsFilteredAllowances.getResultSet();

    return filteredAllowances;

  }

}

• We pass the parameter from our Repository object method to the **setParameter(String, Object) method of the -> `TypedQuery<X extends Object> interface` which inherits the Query interface.**

-> There are multiple overloads of the setParameter(..) method, but in this case, we are using:

setParameter(String parameterName, Object value)

We can keep “the structure of setParameter(“String word”, Object value) method” in our mind as:

setParameter("NamedParameterItselfDefinedInOurJPQLQuery", 
             methodParameterThatTheValueOfItPassedForFiltering)

// or setParameter("StringWordInQueryForDynamicFiltering",
//                  methodParameterThatTheValueOfItPassedForFiltering)

-> More explicitly;

setParameter("StringWordOrPlaceholderResidingNextToWhereClauseOfOurJPQL",
parameterOfTheRelevantRepositoryObjectMethodThatWePassItsValueToTheNamedParameterInOurJPQL)

Example and Explanation:

setParameter("greaterThanValue", greaterThanValue)

• First Parameter: “greaterThanValue” -> Exact String word (String value) or placeholder that we declared in our Named Query (Named JPQL Query) that put at the top of the relevant Entity Class for dynamic filtering.
• Second Parameter: greaterThanValue -> Object type parameter (in general; it can be and it will be a → Wrapper class exactly like BigDecimal) that is passed as a parameter to the relevant certain-specific CRUD or CRUD-derivative method of the relevant Repository bean and will be passed to the -> “String value” that we define next to where clause in our JPQL query for dynamic filtering purposes.
• Example CRUD-derivative Method of the Relevant Repository Bean -> getEmployeeAllowancesByACondition(BigDecimal greaterThanValue)

An In-Depth Examination of setParameter(“String NamedParameterItself”, Object valueThatWePassToThisNamedParameter) method

-> Essentially, greaterThanValue is an Object type parameter (in general; it can be and it will be a → Wrapper class, exactly like BigDecimal) that is passed as a parameter to the relevant CRUD or CRUD-derivative method of ( getEmployeeAllowancesByACondition(BigDecimal greaterThanValue) ) of the relevant Repository bean and will be passed to the → .setParameter(“greaterThanValue”, greaterThanValue) method of the TypedQuery Interface that we can call next to → createNamedQuery(”nameOfTheRelevantNamedQuery”, queryReturnType.class)method of the EntityManagerInterface instance that we inject into the relevant Repository bean and used in our relevant CRUD-derivative method ( getEmployeeAllowancesByACondition(BigDecimal greaterThanValue) ) of our relevant Repository bean and will be passed to the String value in our JPQL query for dynamic filtering purposes.

• “greaterThanValue” -> Named Parameter in the JPQL query that will be used for dynamic filtering.
• greaterThanValue -> Actual value passed from the Repository object method parameter to be substituted in the JPQL query.

-> This ensures that the value passed in the method (greaterThanValue) replaces the named parameter ("greaterThanValue") in the JPQL query execution.

Deep Dive into setParameter(String namedParameterItself, Object value) in the JPQL Execution Flow

The greaterThanValue parameter in:

public Collection<Allowance> 
      getEmployeeAllowancesByACondition(BigDecimal greaterThanValue)

is passed into the following line:

.setParameter("greaterThanValue", greaterThanValue);

-> This binds the value dynamically to the greaterThanValue placeholder in our Named Query.

-> The JPA runtime will handle the actual substitution, ensuring that the query correctly filters the allowanceAmount attribute in the joined Employee-Allowance table.

— — — — — — — — — — — — — — — — — — — — — — — — — — —

Important Notes about setParameter(String namedParameter,Object value)method

1️⃣ The Return Type After Calling setParameter()

• The setParameter(..) method belongs to the TypedQuery<X> interface, which extends Query.
• This means that even after calling setParameter(..), the return type remains: **TypedQuery<Allowance>**

2️⃣ Using Multiple Named Parameters in JPQL Queries

• If we have multiple filtering conditions, we can call setParameter(..) multiple times:

query.setParameter("firstNamedParameterInJPQL", value1)
     .setParameter("secondNamedParameterInJPQL", value2);

• Example JPQL Query with Multiple Parameters:

query = "select al 
        from Employee e join e.employeeAllowances al
        where al.allowanceAmount > :greaterThanValue 
        and al.allowanceType = :allowanceType"

• Setting multiple Named Parameters in a relevant Repository object method:

query.setParameter("greaterThanValue", greaterThanValue)
     .setParameter("allowanceType", allowanceType);

-> This approach ensures flexibility in dynamic filtering of results using Named Parameters.

Remember the Answer of This Question: How Do We Pass Named Parameters in JPQL Queries?

✔ Use : before Named Parameters in the JPQL query:

query = "select al 
         from Employee e join e.employeeAllowances al
         where al.allowanceAmount > :greaterThanValue"

✔ Pass values dynamically using setParameter() in the relevant Repository object within the data access layer:

.setParameter("greaterThanValue", greaterThanValue)

✔ The JPA runtime automatically substitutes the Named Parameter with the actual value.

— — — — — — — — — — — — — — — — — — — — — — — — — — — -

Summary of Named Parameters

• Named Queries use Named Parameters (:namedParameterName) for dynamic filtering.
• **setParameter("namedParameterName", Object value) binds method parameters to Named Parameters** in the JPQL query.
• The JPA runtime handles the substitution dynamically.
• Multiple Named Parameters can be used for advanced dynamic filtering.

-> Passing parameters in JPQL Named Queries is a clean and structured approach to filtering data dynamically with where clause. Using setParameter() method, we can dynamically bind method parameters to Named Parameters in JPQL queries, making our queries flexible and reusable.