Arthur Oh Arthur Oh - 2 months ago 6
SQL Question

i can't make hibernate sql query

hi i am a noob programmer.

I am learning Hibernate now. And I am frustrated by

SELECT COUNT(*) FROM zipcode a
WHERE (ISNULL( :sido , '') = '' OR a.sido = :sido) AND
(ISNULL( :sigungu , '') = '' OR a.sigungu = :sigungu) AND
(ISNULL( :eupmyun , '') = '' OR a.eupmyun = :eupmyun) AND
(ISNULL( :roadName , '') = '' OR a.road_name = :roadName) AND
(ISNULL( :ri , '') = '' OR a.ri = :ri)


I can't make this sql query to hibernate....

Number num = (Number) session()
.createQuery(
"SELECT COUNT(*) FROM ZipCode a WHERE (ISNULL( :sido , '') = '' OR a.sido = :sido) AND (ISNULL( :sigungu , '') = '' OR a.sigungu = :sigungu) AND (ISNULL( :eupmyun , '') = '' OR a.eupmyun = :eupmyun) AND(ISNULL( :roadName , '') = '' OR a.roadName = :roadName) AND(ISNULL( :ri , '') = '' OR a.ri = :ri) ")
.setParameter("sido", sido).setParameter("sigungu", sigungu).setParameter("eupmyun", eupmyun).setParameter("roadName", roadName).setParameter("ri", ri)
.list();


I try this sql...
I am using Postgresql 9.4 and hibernate 5.0.*

Please help me

Answer

1) Instead of createQuery use createSQLQuery as below:

Query query = session.createSQLQuery("SELECT COUNT(*) FROM zipcode a 
                      WHERE (ISNULL( :sido , '') = '' OR a.sido = :sido) AND
                     (ISNULL( :sigungu , '') = '' OR a.sigungu = :sigungu) AND
                     (ISNULL( :eupmyun , '') = '' OR a.eupmyun = :eupmyun) AND         
                     (ISNULL( :roadName , '') = '' OR a.road_name = :roadName) AND
                    (ISNULL( :ri , '') = '' OR a.ri = :ri) ");

Number number =(Number)query.list().get(0);

createQuery - is a HQL query whereas createSQLQuery is a native db query.

Note: make sure the query is in one line else you need to add ('+') concat operator.