Hibernate Criteria Query to get projected columns and use unprojected columns in where clause


#1

I am using Criteria Query in my code. It always used to fire select * from ...

Instead I wanted to neglect one column(field) from my query as that field have large number of data stored in bytes. And that was causing slowness in my application.

I added a projection in my query referring example here (Example 6) and it created a query below

select
    this_.TEMPLATE_ID as y0_,
    this_.TEMPLATE_NAME as y1_,
    this_.CREATE_DATE as y2_,
    this_.UPDATE_DATE as y3_,
    this_.STATUS_CODE as y4_,
    this_.USER_ID as y5_,
    this_.UPDATED_BY as y6_,
    this_.CATEGORY_ID as y7_,
    this_.PRACTICE_ID as y8_ 
from
    templates this_ 
inner join
    user user1_ 
        on this_.USER_ID=user1_.USER_ID 
inner join
    template_categories category2_ 
        on this_.CATEGORY_ID=category2_.CATEGORY_ID 
where
    y4_=? 
    and y8_=? 
    and y5_ in (
        ?, ?
    ) 
order by
    y1_ asc limit ?

Then the issue was likeā€¦ Unknown column 'y4_' in 'where clause' and same error for y8_ , y5_ means for all where clause it gave an error.

I modified my SQL to fix it and below is the SQL which I want to get generated through my code where I apply criteria and Projection both.

select
    this_.TEMPLATE_ID as y0_,
    this_.TEMPLATE_NAME as y1_,
    this_.CREATE_DATE as y2_,
    this_.UPDATE_DATE as y3_,
    this_.STATUS_CODE as y4_,
    this_.USER_ID as y5_,
    this_.UPDATED_BY as y6_,
    this_.CATEGORY_ID as y7_,
    this_.PRACTICE_ID as y8_ 
from
    templates this_ 
inner join
    user user1_ 
        on this_.USER_ID=user1_.USER_ID 
inner join
    template_categories category2_ 
        on this_.CATEGORY_ID=category2_.CATEGORY_ID 
where
    this_.STATUS_CODE=1
    and this_.PRACTICE_ID=1 
    and this_.USER_ID in (
        1, 2
    ) 
order by
    y1_ asc limit ?

Can somebody please suggest that what should be the code written to create such query where I want to use columns in the where clause but not projected.