You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by "seba.wagner@gmail.com" <se...@gmail.com> on 2011/11/14 21:42:24 UTC

Defining Where clause on OneToMany Association, Why is there no @Where Annotation ?

Hi,

could somebody point me to the right direction on this:

I have two Entities:

@Entity
@Table(name = "naviglobal")
public class *Naviglobal* implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
*    *@Column(name="global_id")
    private Long global_id;*
    @OneToMany(fetch = FetchType.EAGER)
    @JoinColumn(name="global_id")
    private List<Navimain> mainnavi;*
}

@Entity
@Table(name = "navimain")
public class *Navimain* implements Serializable {
    @Column(name="deleted")
    private String deleted;
}


What I would like now is to perform a query that gives me all globalnavi
but filter on the mainnavi auto-fetched Entities "Navimain", something like
that:
SELECT c from Naviglobal c
LEFT JOIN c.mainnavi m
WHERE AND m.deleted = false

*Resulting* I will of course get something *completely wrong* as it will
result in having the product of naviglobal multiplicated with navimain
entries....
As Naviglobal AUTO-Fetches the Navimain table I actually do get a lot of
duplicates.
My query simply cannot influence the way openJPA will generate the "Fetch"
for the Navimain table.

I would expect to have sth like a @Where(deleted=false) Annotation ... but
there is none.

There must be some other way or workaround to solve this common issue?

Thanks
Sebastian


-- 
Sebastian Wagner
http://www.openmeetings.de
http://www.webbase-design.de
http://www.wagner-sebastian.com
seba.wagner@gmail.com

AW: Defining Where clause on OneToMany Association, Why is there no @Where Annotation ?

Posted by Michael Spiro <m....@dcs-caesar.de>.
> However, the documentation says that the constant values
> can be strings, numeric or null, but nothing about boolean constants.

Sorry, my fault - it's not in the documentation, but in the source code.



Re: Defining Where clause on OneToMany Association, Why is there no @Where Annotation ?

Posted by Michael Spiro <m....@dcs-caesar.de>.
Hi,

according to the documentation (Part 3, chapter 7, paragraph 6 "Non-Standard
Joins") there should be an "official" option for a join with a constant
value in a specified column. The constant value should be specified in the
attribute "referencedColumnName" of the @JoinColumn annotation (with a
special grammatical trick allowing to tell a constant value from a regular
column name). However, due to a bug it doesn't work properly (see the JIRA
issues OPENJPA-1979 and OPENJPA-2054). Let's hope that this will work in
future releases. However, the documentation says that the constant values
can be strings, numeric or null, but nothing about boolean constants.

By the way, I wonder why this is called "non-standard join". IMHO, this kind
of join is a rather common case.

Kind regards,
Michael Spiro

> -----Ursprüngliche Nachricht-----
> Von: Tobias Trelle [mailto:tobias.trelle@codecentric.de]
> Gesendet: Montag, 5. Dezember 2011 09:06
> An: users@openjpa.apache.org
> Betreff: Re: Defining Where clause on OneToMany Association, Why is
> there no @Where Annotation ?
> 
> Hi Sebastian,
> 
> there is no @Where annotation. You have to use a custom JPQL query like
> this:
> 
> @Entity
> @Table(name = "naviglobal")
> *@NamedQuery(name="getDeleted" query = "SELECT ng FROM Naviglobal ng
> LEFT
> JOIN ng.mainnavi mn WHERE mn.deleted = true")
> *public class *Naviglobal* implements Serializable {
> ...
>     @OneToMany(*fetch = FetchType.LAZY*)
>     @JoinColumn(name="global_id")
>     private List<Navimain> mainnavi;*
> ...
> 
> Somewhere in your service / DAO / or whatever:
> 
> TypedQuery<Naviglobal> q = em.createNamedQuery("getDeleted",
> Naviglobal.class);
> List<Naviglobal> result = q.getResultList();
> 
> HTH,
> Tobias
> 
> --
> View this message in context:
> http://openjpa.208410.n2.nabble.com/Defining-Where-clause-on-OneToMany-
> Association-Why-is-there-no-Where-Annotation-tp6994016p7062094.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.


Re: Defining Where clause on OneToMany Association, Why is there no @Where Annotation ?

Posted by "seba.wagner@gmail.com" <se...@gmail.com>.
Thanks,

I will try with the NamedQuery example. It sounds like exactly what I was
looking for.

I don't know if the constant for a the fetch query is a regular
specification.
I can also remember exactly the problem you describe with a constants of
type Boolean which actually really seems to be impossible (at least in the
past).
In later versions of Hibernate they have implemented some extend to the
official JPA Annotations where you can use something like a
@Where-Annotation targeting my described use-case scenario.

Sebastian

2011/12/5 Michael Spiro <m....@dcs-caesar.de>

> > However, the documentation says that the constant values
> > can be strings, numeric or null, but nothing about boolean constants.
>
> Sorry, my fault - it's not in the documentation, but in the source code.
>
>
>


-- 
Sebastian Wagner
http://www.openmeetings.de
http://www.webbase-design.de
http://www.wagner-sebastian.com
seba.wagner@gmail.com

Re: Defining Where clause on OneToMany Association, Why is there no @Where Annotation ?

Posted by Tobias Trelle <to...@codecentric.de>.
Hi Sebastian,

there is no @Where annotation. You have to use a custom JPQL query like
this:

@Entity
@Table(name = "naviglobal")
*@NamedQuery(name="getDeleted" query = "SELECT ng FROM Naviglobal ng LEFT
JOIN ng.mainnavi mn WHERE mn.deleted = true")
*public class *Naviglobal* implements Serializable {
...
    @OneToMany(*fetch = FetchType.LAZY*)
    @JoinColumn(name="global_id")
    private List<Navimain> mainnavi;* 
...

Somewhere in your service / DAO / or whatever:

TypedQuery<Naviglobal> q = em.createNamedQuery("getDeleted",
Naviglobal.class);
List<Naviglobal> result = q.getResultList();

HTH,
Tobias

--
View this message in context: http://openjpa.208410.n2.nabble.com/Defining-Where-clause-on-OneToMany-Association-Why-is-there-no-Where-Annotation-tp6994016p7062094.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.