convert SQL to HQL/Criteria

SIau_Tie · **Posted:** Thu Jul 10, 2008 4:00 am

does anybody can help me convert this sql to HQL / Criteria?

Code:

 select count(*) from (
    select 1 as col_0_0_
    from
        settled_txn_log settledtxn0_,
        terminal terminal1_,
        bank bank2_
    where
        settledtxn0_.mid=terminal1_.mid
        and settledtxn0_.tid=terminal1_.tid
        and bank2_.id=terminal1_.bank_id
        and settledtxn0_.txn_dt>='07-01-2008'
        and settledtxn0_.txn_dt<='07-30-2008'
        and bank2_.id=1
    group by
        settledtxn0_.mid ,
        settledtxn0_.tid
   having
       count(settledtxn0_.mid)<=1
) as x

thanks

Cameron McKenzie · **Posted:** Thu Jul 10, 2008 10:31 am

Well, what you've got there is a bunch of table and column names. Hibernate is an ORM tool, and HQL thinks in terms of objects. We really can't do anything unless we know what OBJECTS you are using? Is there one object that maps to those tables, or is there 100? Without knowing, we couldn't even guess about your HQL.

Here's a little tutorial on Hibernate Query Language. Give it a look and see if it can't lead you in the right direction.

http://jpa.ezhibernate.com/Javacode/learn.jsp?tutorial=08masteringhqlandnamedqueries

SIau_Tie · **Posted:** Thu Jul 10, 2008 10:07 pm

this is hbm.xml

Code:

<hibernate-mapping>
  <class table="settled" name="com.sample.db.model.SettledTxnLog">
    <id unsaved-value="null" name="id" type="long" column="id">
      <generator class="native"/>
    </id>
    ........
    <property name="tid" length="20" column="tid"/>
    <property name="mid" length="20" column="mid"/>
    <property name="txnDateTime" type="timestamp"/>
    .....
  </class>
</hibernate-mapping>

<hibernate-mapping>
  <class table="terminal" name="com.sample.db.model.Terminal">
    <id unsaved-value="null" name="id" type="long" column="id">
      <generator class="native"/>
    </id>
    <property name="tid" length="20" column="tid" not-null="true"/>
    <property name="mid" length="20" column="mid" not-null="true"/>
    <many-to-one name="bank"/>
    .......
  </class>
</hibernate-mapping>

<hibernate-mapping>
  <class table="bank" name="com.sample.db.model.Bank">
    <id name="id" type="long" column="id">
      <generator class="native"/>
    </id>
    ....
    <property name="name" length="40" column="name"/>
    .....
  </class>
</hibernate-mapping>

this is my sample HQL :

Code:

select count(*) from 
(
   select settled.mid 
   from com.sample.db.model.SettledTxnLog settled, 
   com.sample.db.model.Terminal terminal, com.sample.db.model.Bank bank 
   where settled.mid = terminal.mid and settled.tid = terminal.tid and bank.id = terminal.bank.id 
   AND settled.txnDateTime >= :startDate AND settled.txnDateTime <= :endDate 
   AND bank = :bank group by settled.mid, settled.tid having count(settled.mid) <= :maxNumberTrans
) 
as table