i have the following Query i need to convert it into Hibernate:

SELECT A.AGNTID, A.NETWID, A.SUBNET, A.LGLNME, A.AGTNME, A.AADDR1, A.ACITY, A.COUNTY, A.ASTATE, A.ZIPCDE, A.PHONECHAR, A.TINSSN, A.ORGDES, A.PRDGRPDES, A.AGFRABA, A.BNKNME, A.BADDR1, A.BCITY, A.BSTATE, A.BZIPCDE, A.INSTYR, A.STRMDY, A.ENDMDY , IFNULL(B.AUGSNDVOLT,0) AS AUGSNDVOLT, IFNULL(B.SEPSNDVOLT,0) AS SEPSNDVOLT, IFNULL(B.OCTSNDVOLT,0) AS OCTSNDVOLT, IFNULL(B.NOVSNDVOLT,0) AS NOVSNDVOLT, IFNULL(B.DECSNDVOLT,0) AS DECSNDVOLT, IFNULL(B.JANSNDVOLT,0) AS JANSNDVOLT, IFNULL(B.FEBSNDVOLT,0) AS FEBSNDVOLT, IFNULL(B.MARSNDVOLT,0) AS MARSNDVOLT, IFNULL(B.APRSNDVOLT,0) AS APRSNDVOLT, IFNULL(B.MAYSNDVOLT,0) AS MAYSNDVOLT, IFNULL(B.JUNSNDVOLT,0) AS JUNSNDVOLT, IFNULL(B.JULSNDVOLT,0) AS JULSNDVOLT, IFNULL(B.AUGSNDPRNT,0) AS AUGSNDPRNT, IFNULL(B.SEPSNDPRNT,0) AS SEPSNDPRNT, IFNULL(B.OCTSNDPRNT,0) AS OCTSNDPRNT, IFNULL(B.NOVSNDPRNT,0) AS NOVSNDPRNT, IFNULL(B.DECSNDPRNT,0) AS DECSNDPRNT, IFNULL(B.JANSNDPRNT,0) AS JANSNDPRNT, IFNULL(B.FEBSNDPRNT,0) AS FEBSNDPRNT, IFNULL(B.MARSNDPRNT,0) AS MARSNDPRNT, IFNULL(B.APRSNDPRNT,0) AS APRSNDPRNT, IFNULL(B.MAYSNDPRNT,0) AS MAYSNDPRNT, IFNULL(B.JUNSNDPRNT,0) AS JUNSNDPRNT, IFNULL(B.JULSNDPRNT,0) AS JULSNDPRNT, IFNULL(C.AUGPAYVOLT,0) AS AUGPAYVOLT, IFNULL(C.SEPPAYVOLT,0) AS SEPPAYVOLT, IFNULL(C.OCTPAYVOLT,0) AS OCTPAYVOLT, IFNULL(C.NOVPAYVOLT,0) AS NOVPAYVOLT, IFNULL(C.DECPAYVOLT,0) AS DECPAYVOLT, IFNULL(C.JANPAYVOLT,0) AS JANPAYVOLT, IFNULL(C.FEBPAYVOLT,0) AS FEBPAYVOLT, IFNULL(C.MARPAYVOLT,0) AS MARPAYVOLT, IFNULL(C.APRPAYVOLT,0) AS APRPAYVOLT, IFNULL(C.MAYPAYVOLT,0) AS MAYPAYVOLT, IFNULL(C.JUNPAYVOLT,0) AS JUNPAYVOLT, IFNULL(C.JULPAYVOLT,0) AS JULPAYVOLT, IFNULL(C.AUGPAYPRNT,0) AS AUGPAYPRNT, IFNULL(C.SEPPAYPRNT,0) AS SEPPAYPRNT, IFNULL(C.OCTPAYPRNT,0) AS OCTPAYPRNT, IFNULL(C.NOVPAYPRNT,0) AS NOVPAYPRNT, IFNULL(C.DECPAYPRNT,0) AS DECPAYPRNT, IFNULL(C.JANPAYPRNT,0) AS JANPAYPRNT, IFNULL(C.FEBPAYPRNT,0) AS FEBPAYPRNT, IFNULL(C.MARPAYPRNT,0) AS MARPAYPRNT, IFNULL(C.APRPAYPRNT,0) AS APRPAYPRNT, IFNULL(C.MAYPAYPRNT,0) AS MAYPAYPRNT, IFNULL(C.JUNPAYPRNT,0) AS JUNPAYPRNT, IFNULL(C.JULPAYPRNT,0) AS JULPAYPRNT, IFNULL(B.AUGSNDVOLO,0) AS AUGSNDVOLO, IFNULL(B.SEPSNDVOLO,0) AS SEPSNDVOLO, IFNULL(B.OCTSNDVOLO,0) AS OCTSNDVOLO, IFNULL(B.NOVSNDVOLO,0) AS NOVSNDVOLO, IFNULL(B.DECSNDVOLO,0) AS DECSNDVOLO, IFNULL(B.JANSNDVOLO,0) AS JANSNDVOLO, IFNULL(B.FEBSNDVOLO,0) AS FEBSNDVOLO, IFNULL(B.MARSNDVOLO,0) AS MARSNDVOLO, IFNULL(B.APRSNDVOLO,0) AS APRSNDVOLO, IFNULL(B.MAYSNDVOLO,0) AS MAYSNDVOLO, IFNULL(B.JUNSNDVOLO,0) AS JUNSNDVOLO, IFNULL(B.JULSNDVOLO,0) AS JULSNDVOLO, IFNULL(B.AUGSNDPRNO,0) AS AUGSNDPRNO, IFNULL(B.SEPSNDPRNO,0) AS SEPSNDPRNO, IFNULL(B.OCTSNDPRNO,0) AS OCTSNDPRNO, IFNULL(B.NOVSNDPRNO,0) AS NOVSNDPRNO, IFNULL(B.DECSNDPRNO,0) AS DECSNDPRNO, IFNULL(B.JANSNDPRNO,0) AS JANSNDPRNO, IFNULL(B.FEBSNDPRNO,0) AS FEBSNDPRNO, IFNULL(B.MARSNDPRNO,0) AS MARSNDPRNO, IFNULL(B.APRSNDPRNO,0) AS APRSNDPRNO, IFNULL(B.MAYSNDPRNO,0) AS MAYSNDPRNO, IFNULL(B.JUNSNDPRNO,0) AS JUNSNDPRNO, IFNULL(B.JULSNDPRNO,0) AS JULSNDPRNO, IFNULL(C.AUGPAYVOLI,0) AS AUGPAYVOLI, IFNULL(C.SEPPAYVOLI,0) AS SEPPAYVOLI, IFNULL(C.OCTPAYVOLI,0) AS OCTPAYVOLI, IFNULL(C.NOVPAYVOLI,0) AS NOVPAYVOLI, IFNULL(C.DECPAYVOLI,0) AS DECPAYVOLI, IFNULL(C.JANPAYVOLI,0) AS JANPAYVOLI, IFNULL(C.FEBPAYVOLI,0) AS FEBPAYVOLI, IFNULL(C.MARPAYVOLI,0) AS MARPAYVOLI, IFNULL(C.APRPAYVOLI,0) AS APRPAYVOLI, IFNULL(C.MAYPAYVOLI,0) AS MAYPAYVOLI, IFNULL(C.JUNPAYVOLI,0) AS JUNPAYVOLI, IFNULL(C.JULPAYVOLI,0) AS JULPAYVOLI, IFNULL(C.AUGPAYPRNI,0) AS AUGPAYPRNI, IFNULL(C.SEPPAYPRNI,0) AS SEPPAYPRNI, IFNULL(C.OCTPAYPRNI,0) AS OCTPAYPRNI, IFNULL(C.NOVPAYPRNI,0) AS NOVPAYPRNI, IFNULL(C.DECPAYPRNI,0) AS DECPAYPRNI, IFNULL(C.JANPAYPRNI,0) AS JANPAYPRNI, IFNULL(C.FEBPAYPRNI,0) AS FEBPAYPRNI, IFNULL(C.MARPAYPRNI,0) AS MARPAYPRNI, IFNULL(C.APRPAYPRNI,0) AS APRPAYPRNI, IFNULL(C.MAYPAYPRNI,0) AS MAYPAYPRNI, IFNULL(C.JUNPAYPRNI,0) AS JUNPAYPRNI, IFNULL(C.JULPAYPRNI,0) AS JULPAYPRNI from MS1491AGT A left join MS1491SND B on a.agtkey = b.agtkey and SUBSTR(A.PRDGRPDES,22,4) = B.PRDGRP left join MS1491PAY C on a.agtkey = c.agtkey and SUBSTR(A.PRDGRPDES,22,4) = c.PRDGRP order by a.agntid, a.prdgrpdes

for that i have created three hibernate mapping files
1) MS1375AGT.hbm.xml
2) MS1375SND.hbm.xml
3) MS1375PAY.hbm.xml

i have created agtkey and PRDGRPDES as a composit key and created one-to-one mapping between MS1375AGT and MS1375SND and between MS1375AGT and MS1375PAY

my issue is that how to use SUBSTR in join conditions
and i am also not able to use ifnull() function, its give error.

please gide me how to convert above mention query into Hibernate.

Thanks
Amit