What is the equivalent mysql statement of criteria code shown below

Question

Im tryingto understand the code shown below

Criteria criteria = session.createCriteria(Payables.class);
            criteria.add(Restrictions.eq("companyId", companyId));

        criteria.createAlias("makePayment", "makePayment");

        if (creditorId != null) {
            criteria.createAlias("makePayment.creditor", "creditor");
            criteria.add(Restrictions.eq("creditor.id", creditorId));
        }

            criteria.add(Restrictions.eq("journalEntryId", journalEntryId));

I do know what createCriteria do, but adding createAlias makes it really confusing for me. I've already read the documentations but everything's still a blur.

Could you tell me how does the code above looks like using mysql statement?

enable sql logging when you run this query and you will see in log the full sql query — Frederic Henri, Sep 14 '15 at 08:13
@FredericHenri unfortunately, I cannot do that but Its to long of a story to tell. Based here https://docs.jboss.org/hibernate/orm/3.2/api/org/hibernate/Criteria.html it is making an innerjoin, if thats the case, then, where can I see this part "FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name" , the columns that they used to join the table? — user3714598, Sep 14 '15 at 08:25

score 1 · Accepted Answer · answered Sep 14 '15 at 08:32

    Criteria criteria = session.createCriteria(Payables.class);
    // select * from Payables p;

    criteria.add(Restrictions.eq("companyId", companyId));
    // where p.companyId = :companyId

    criteria.createAlias("makePayment", "makePayment");
    // inner join Payement makePayment on makePayement.payables_id = p.id

    if (creditorId != null) {
        criteria.createAlias("makePayment.creditor", "creditor");
        // inner join Creditor c on c.payement_id = makePayement.id
        criteria.add(Restrictions.eq("creditor.id", creditorId));
        // where c.id = :creditorId
    }

    criteria.add(Restrictions.eq("journalEntryId", journalEntryId));
    // where p.journalEntryId = :journalEntryId

So the result is :

    select * from Payables p
    inner join Payement makePayment on makePayement.payables_id = p.id
    inner join Creditor c on c.payement_id = makePayement.id
    where p.companyId = :companyId
    and c.id = :creditorId
    and p.journal_entry_id = :journalEntryId

For the table name, use the value in @Table annotation of the entity, for the column name, use the @Column name value located on field or getter, for the column name used in join, use the value in the @JoinColumn annotation located near the @OneToMany or @ManyToOne annotation

Hi @Thierry May I ask where you get this "Payment" ? Thank you :) — user3714598, Sep 14 '15 at 09:32
you're creating an alias on the criteria "criteria.createAlias("makePayment", "makePayment");". In the entity Payables you should have a Collection field named makePayement. I just guessed a name. Without your entities mappings, it is not possible to write the exact sql table/column names ;) — Thierry, Sep 14 '15 at 11:16

score 0 · Answer 2 · answered Sep 14 '15 at 08:27

0

createAlias adds join

something like

...
FROM Payables p1
     JOIN Payment as p2 ON p2.paybles_id=p1.id
...

Then you can use the joined table to add conditions in WHERE section

answered Sep 14 '15 at 08:27

StanislavL

56,971
9
68
98

from the code that I posted. Its unclear to me the column that it used to join. Could you tell where that is? Thank you – user3714598 Sep 16 '15 at 00:42

What is the equivalent mysql statement of criteria code shown below

2 Answers2