How to implement with hibernate criteria Object the select query with inner join

Question

I'm new with Hibernate and Criteria Query. So I have implemented the query in HQL:

select A.mobilephone
    B.userNick
    C.creditCard
from mobile_table A
inner join user_table B
    on A.codmobile=B.codmobile
inner join Credit C 
    on A.mobileCredit= C.mobileCredit

How can I implement it with Hibernate Criteria Object?

Answer 1

Your example is just a native SQL , not the HQL . Anyway , you can use the following methods from the Criteria API to construct the desired Criteria object :

• Use the setProjection(Projection projection) to define the select clause
• Use the createCriteria(String associationPath,String alias) to define the inner join

For example , if the SQL is :

select
    TableA.columnA ,TableB.columnB ,TableC.columnC
from TableA 
inner join TableB on TableA.tableB_id=TableB.id
inner join TableC on TableA.tableC_id=TableC.id
where TableA.columnAA="AAA"
and TableB.columnBB="BBB"
and TableC.columnCC="CCC"

Then the Criteria object will be:

List<Object[]>resultList= (List<Object[]>)session.createCriteria(TableA.class, "aliasOfTableA") 
                .add(Restrictions.eq("aliasOfTableA.columnAA", "AAA"))  
                .createCriteria("aliasOfTableA.tableBId" , "aliasOfTableB")
                .add(Restrictions.eq("aliasOfTableB.columnBB", "BBB"))
                .createCriteria("aliasOfTableA.tableCId" , "aliasOfTableC")
                .add(Restrictions.eq("aliasOfTableC.columnCC", "CCC"))
                .setProjection( Projections.projectionList()
                        .add( Projections.property("aliasOfTableA.columnA") )
                        .add( Projections.property("aliasOfTableB.columnB"))
                        .add( Projections.property("aliasOfTableC.columnC") )
                ).list();

for (Object[] obj : resultList) {
        System.out.println(obj[0]); //print the value from TableA.columnA
        System.out.println(obj[1]); //print the value from TableB.columnB
        System.out.println(obj[2]); //print the value from TableC.columnC
}   

Please note that all parameters in the Criteria use the property name and class name in the mapped Java entities.

Answer 2

Are you sure that's HQL? Looks like native SQL to me.

In order to use Criteria your entities need to be mapped and associated with each other (since Criteria only allows navigating association paths). I think you could get more help if you give some details about your mappings.