Conditional part of a hibernate query

Question

this is a simplification of a complex query.

I want to retrieve the details of a concrete event-new (with a known passed idNew).

The problem is that I don't know how to tell to the hibernate sql engine that I want to retrieve the fields of that record, independently on how that new has or not an associated file.

I don't know how to write the conexion with the last clause (and newsWfLocalFiles.idNewsWfLocal = newsWfLocal.idNewsWfLocal)

Query query = this.sessionFactory.getCurrentSession().createQuery(
    "select news.idNews, newsWfLocal.title, newsWf.date, newsWfLocalFiles.url" +
    " from News news, NewsWf newsWf, NewsWfLocal newsWfLocal, NewsWfLocalFiles newsWfLocalFiles" +
    " where news.idNews = :idNews" +
    " and newsWf.news.idNews = news.idNews" +
    " and newsWf.idNewsWf = newsWfLocal.idNewsWf" +
    " and newsWfLocal.idLocal = 1" +
    " and newsWfLocalFiles.idNewsWfLocal = newsWfLocal.idNewsWfLocal");

I suppose that the key is to build the form sentence properly, but I cant figure out how to do that.

These are the relationships between tables:

News one-to-many NewsWf

NewsWf one-to-many NewsWfLocal

NewsWfLocal one-to-one NewsWfLocalFiles

With the next form clause I feel I am near the solution, but the retrieved 'localFile' is still null, although in the cases that field is not empty:

from News news join news.newsWfs newsWfs join newsWfs.newsWfsLocal newsWfsLocal left outer join newsWfsLocal.newsWfLocalFiles newsWfLocalFiles

Here the details of the one-to-one mappings:

in NewsWfLocal.xml:

<one-to-one name="newsWfLocalFiles" class="com.sample.mdl.NewsWfLocalFiles" fetch="select" lazy="false" ></one-to-one>

in NewsWfLocalFiles.xml:

<one-to-one name="newsWfLocal" class="com.sample.mdl.NewsWfLocal" fetch="select" lazy="false"></one-to-one>

GeertPt · Accepted Answer · 2013-01-15 17:05:17Z

1

You don't need to completely write out your SQL, hibernate will generate it for you.

I'm taking some assumptions about your entities, but it could be something simple as:

Query query = session.createQuery("select news from News news "
           +"join news.wf wf "
           +"where news.id=:id and wf.local=:local")
query.setParameter("id",id);
query.setParameter("local", local);
List list = query.list();
for(News news: list)
{
    if(!news.getWf().getLocal().getFiles().isEmpty())
    {

    }
}

In hibernate, joins are only possible using many-to-one/one-to-one/one-to-many associations defined in your entities, e.g.

Query query = session.createQuery("select news from News news "
           +"join news.wf wf "
           +"join wf.local local "
           +"join local.files file ")

For one-to-one and many-to-one, joins are even made implicitly.

Query query = session.createQuery("select news from News news "
           +"where news.id=:id and news.wf.local.id=:idLocal ")

This will implicitly join three tables. That doesn't work for one-to-many, though, like news.wf.local.files.name=..., that will give errors (since hibernate 3.3), since local.files is one-to-many.

See http://docs.jboss.org/hibernate/orm/3.5/reference/en/html/queryhql.html#queryhql-joins

edited Jan 15, 2013 at 17:05

answered Jan 15, 2013 at 15:58

GeertPt

18k3 gold badges44 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JLLMNCHR Over a year ago

Thanks. Is it possible to mix explicit joins and implicit joins in the same query?: <code>from News news, NewsWf newsWf, NewsWfLocal newsWfLocal left outer join newsWfLocal. newsWfLocalFiles newsWfLocalFiles</code>

GeertPt Over a year ago

Added an example for what hibernate calls implicit joins. It's not what you show. Don't you have an entity association News->NewsWf?

JLLMNCHR Over a year ago

I edited the post to show associations between tables I have mapped, and the last try. Thanks

JLLMNCHR · Accepted Answer · 2013-01-17 09:30:12Z

0

I have discovered that is a problem concerned the way hibernate handle the one-to-one/zero mappings.

Some tricky techniques found in Google doesn't work for me.

Changing that one-to-one/zero (between NewsWfLocal and NewsWfLocalFiles) mapping to a one-to-many relationship solves the problem.

The left outer join is still necessary for newsWfsLocal instances that doesn't have files associated:

from News news join news.newsWfs newsWfs join newsWfs.newsWfsLocal newsWfsLocal left outer join newsWfsLocal.newsWfLocalFiles newsWfLocalFiles

answered Jan 17, 2013 at 9:30

JLLMNCHR

1,6196 gold badges31 silver badges58 bronze badges

1 Comment

GeertPt Over a year ago

You need a bi-directional one-to-one mapping. You had two one-to-one relations that hibernate didn't know were related, so it probably generated foreign keys in both tables. For a bidirectional one-to-one relation, you need to disguise one as a many-to-one with a property-ref or inverse="true". See docs.jboss.org/hibernate/orm/3.6/reference/en-US/html/…

Collectives™ on Stack Overflow

Conditional part of a hibernate query

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related