JPQL: join with unmapped tables

uwe_schaefer · **Joined:** Thu Jan 17, 2008 3:42 am **Posts:** 6

hi

is it possible with jpql (hibernate here) to join to an unmapped table ?
i need to retrieve entities, that match some criteria with an unmapped table (ids, here). yes i know, i can use native queries, but in that case i´d have to create all the other joins myself (which is what the JPA normally does for me).

an example:

jpql query:

Code:

SELECT a FROM Article a WHERE a.author.name='Peter';

what i need would be something liek that:

Code:

SELECT a FROM Article a JOIN someUnmappedTable t on a.id=t.id WHERE a.author.name='Peter';

or even something like

Code:

SELECT a FROM Article a WHERE a.author.name='Peter' AND a.id IN (SELECT id FROM someUnmappedTable)

creating an bogus Entity for that unmapped table works, but isn´t an option (because i would load tons of data from there, which i do not need)

Using latest Hibernate with Entitymanager.

cu uwe

mmerder · **Posted:** Wed Mar 04, 2009 4:32 am

No it's not possible.

Mapping another entity would be the right solution. I don't understand why you say that you would load tons of data:

Code:

SELECT a FROM Article a JOIN someMappedTable t WHERE a.author.name='Peter';

would not cause a load of the other entity unless you have specified fetch-type as eager.

uwe_schaefer · **Joined:** Thu Jan 17, 2008 3:42 am **Posts:** 6

mmerder wrote:

No it's not possible.

Mapping another entity would be the right solution. I don't understand why you say that you would load tons of data

well, what i did was this:

Code:

@Entity
public class News implements Serializable{
    @Entity
    public static class NewsIndex  {
      @Id int id; String query;
       // some col mappings...
    }

    @OneToOne(fetch = FetchType.LAZY)
    @PrimaryKeyJoinColumn(name = "id", referencedColumnName = "id")
    private NewsIndex fullTextIndex = null;
    
    // main entity mappings...

when i now query this:

Code:

SELECT o FROM News o JOIN o.fullTextIndex txt WHERE txt.query='foo'

then hibernate does the proper join query, but afterwards loads the index entities as well:

Code:

Hibernate: select news0_.id as id2_, news0_.version as version2_, news0_.lastModified as lastModi3_2_, news0_.author_id as author8_2_, news0_.created as created2_, news0_.deleted as deleted2_, news0_.headline as headline2_, news0_.plainText as plainText2_ from News news0_ inner join idxNews news_newsi1_ on news0_.id=news_newsi1_.id where news_newsi1_.query='dortmund'
[...]
Hibernate: select news_newsi0_.id as id3_0_, news_newsi0_.group_id as group2_3_0_, news_newsi0_.query as query3_0_, news_newsi0_.weight as weight3_0_ from idxNews news_newsi0_ where news_newsi0_.id=?
Hibernate: select news_newsi0_.id as id3_0_, news_newsi0_.group_id as group2_3_0_, news_newsi0_.query as query3_0_, news_newsi0_.weight as weight3_0_ from idxNews news_newsi0_ where news_newsi0_.id=?
Hibernate: select news_newsi0_.id as id3_0_, news_newsi0_.group_id as group2_3_0_, news_newsi0_.query as query3_0_, news_newsi0_.weight as weight3_0_ from idxNews news_newsi0_ where news_newsi0_.id=?

So how could i prevent that? I did not FETCH join, and the association is defined as LAZY.

thx uwe

mmerder · **Posted:** Wed Mar 04, 2009 8:57 am

Well, thats a special behavior of OneToOne-Associations. If you load a News-Object hibernate needs to know, if there is a NewsIndex to this News, in order to set a proxy or null if there is none. Other ManyToOne-associations would use a join-column, which is null if the association is null. But when you use PrimaryKeyJoinColumn, this join column is never null, even if the association is null, so that hibernate needs to do a select to check if the other end exists. Look hereand herefor more information about that.

Rating is welcome. ;-)

uwe_schaefer · **Joined:** Thu Jan 17, 2008 3:42 am **Posts:** 6

mmerder wrote:

Well, thats a special behavior of OneToOne-Associations. If you load a News-Object hibernate needs to know, if there is a NewsIndex to this News, in order to set a proxy or null if there is none. Other ManyToOne-associations would use a join-column, which is null if the association is null. But when you use PrimaryKeyJoinColumn, this join column is never null, even if the association is null, so that hibernate needs to do a select to check if the other end exists. Look hereand herefor more information about that.

Rating is welcome. ;-)

Still dont get it, but was able to come around that one by (mis)using ManyToOne for that.

In case someone else falls into that: here is the solution that worked for me:

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "id", referencedColumnName = "id", insertable = false, updatable = false)
private NewsIndex fullTextIndex = null;

Where NewsIndex is the Bogus Entity i do not want to load...

thanks for your time, though! Highly appreciated

mmerder · **Posted:** Wed Mar 04, 2009 11:29 am

Well, thats a solution, but be aware, that you will get an exception if you invoke news.getNewsIndex() when a news has no index, because hibernate thinks, there IS a newsIndex.

In your case I would use a OneToOne and make it not optional, or (if it is optional) use another join-column instead of the primary key.

uwe_schaefer · **Joined:** Thu Jan 17, 2008 3:42 am **Posts:** 6

mmerder wrote:

Well, thats a solution, but be aware, that you will get an exception if you invoke news.getNewsIndex() when a news has no index, because hibernate thinks, there IS a newsIndex.

In your case I would use a OneToOne and make it not optional, or (if it is optional) use another join-column instead of the primary key.

i just don´t have that option. see, i don´t have control over the NewsIndex entity´s table, because it comes from a 3rd party software. i will never ever try to get a NewsIndex entity, but just use it to join on for filtering News entities. that´s why i would not even put a getNewsIndex in News.

if i use OneToOne and shared primary key, i did not manage to make that NewsIndex entity not created in java space, so this fake ManyToOne for this fake Entity looks like the only way to actuallay get a

Code:

LEFT JOIN someUnmappedTable a ON myEntity.id=a.id

into a JPQL query, right?

mmerder · **Posted:** Thu Mar 05, 2009 4:30 am

Do you really have to do a join in order to filter?
Why not removing the (not used) toOne-Association and filter with a subquery:

Code:

from News news where news.id in (select id from NewsIndex)

uwe_schaefer · **Joined:** Thu Jan 17, 2008 3:42 am **Posts:** 6

mmerder wrote:

Do you really have to do a join in order to filter?
Why not removing the (not used) toOne-Association and filter with a subquery:

Code:

from News news where news.id in (select id from NewsIndex)

unfortunately, i´m on mysql 5.0, where subselects... well... let´s just say: don´t rock

thx anyway.