CODEWITHSUNDEEP
spring-bootjpaormspring-data-jpa
Sree
Sree

Reputation: 971

How to write a custom where clause in JPA orm.xml file

My Springboot application has orm.xml file in my WEB-INF folder. I have few named native queries in the orm.xml file.

I want to add a where condition in the query based on the values in the parameter to the query.

I am looking for some thing like this:

 <named-native-query name="MyReport.countInventory">
        <query>
        SELECT COUNT(*) AS COUNT
        FROM
          Inventory
        WHERE 
         <if :date>
             add_date = :date
         </if>
        </query>
    </named-native-query>

Upvotes: 0

Views: 686

Answers (1)

Jens Schauder
Jens Schauder

Reputation: 81998

This is not possible with JPA. It just allows straight-forward SQL statements without any conditional processing.

In many cases, one can actually implement the required conditional logic in SQL itself as Turo described in his/her comment.

Since you are seeming to be using Spring Data JPA you can also use Specifications to dynamically create conditions.

Another alternative would be to use MyBatis which allows constructing SQL statements in a way similar to what you outline in your question. The following example is taken from http://www.mybatis.org/mybatis-3/dynamic-sql.html

<select id="findActiveBlogWithTitleLike"
     resultType="Blog">
  SELECT * FROM BLOG
  WHERE state = ‘ACTIVE’
  <if test="title != null">
    AND title like #{title}
  </if>
</select>

Upvotes: 1

Related Questions