Hmm..there are two peices of advice that you will find in the documentation
for JPA as you go along...first use JPQL rather than SQL because the JPA
implementations are very good at generating high-performance queries. Only
use SQL when you need to optimize the query in a way that is not supported
by JPQL. Second, use named queries because they are precompiled and thus
avoids parsing the query every time it is needed, thus improving the
performance of the application. They also enforce best practice of using
parameters etc etc etc...to a sum total of better performance.
I think the advantages of using named queries outway the *trouble* of
defining the query in the entity and accessing it from the Dao. For large
applications this starts to make more sense.
However, if you need to you do have options to do it your way. You can
access the Session using getHibernateTemplate().getSession(). Once you have
the Session you can use the createSQLQuery(String sql) method passing your
SQL String as a parameter and then executing it. This is one way that I can
think of on the fly (but I feel uncomfortable suggesting it). Maybe others
could suggest other ways.
You could also try the Hibernate forums, where you might get more
comprehensive ideas, since the problem is more of a hibernate problem than
an AppFuse problem as such.
Hope this helps.
Vincent
On Fri, Sep 12, 2008 at 6:20 PM, Joey122 <[EMAIL PROTECTED]> wrote:
>
> Thanks very much for your input below - Its much appreciated
>
> My question is simply why go the trouble of putting the sql in the entity
> class and then backing it out in the DAO?
>
> Is there any way I can construct the sql string in the DAO and add all the
> neccessary filters that I need then in the SQL?
>
> I know exactly how to remove duplicates if I can get full control over the
> sql group bys
>
>
>
> Vincent Ramdhanie wrote:
> >
> > Yes it is possible. There are a number of options. For instance, you may
> > use
> > named queries. In this case you write the query in your Entity class then
> > access the query from your DAO when needed. Lets see if I could construct
> > an
> > example:
> >
> > Suppose you have an Entity class called Landlord, then you might do this:
> >
> > @NamedQueries({
> > @NamedQuery(name="landlord.details",
> > query = "Select a from Landlord a where a.someProperty = :var")
> > })
> > @Entity
> > @Table(name="landlord")
> > public class Landlord {
> >
> > ...
> > }//end of Landlord class
> >
> > You may have many queries defined like this. Notice the :var parameter
> > that
> > I included.
> >
> > Now the LandlordDaoHibernate implementation may access this query like
> > this:
> >
> > public List<Landord> findAllLandlords(String param) {
> > String args[] = {"var"};
> > Object vals[] = {param};
> > return
> > getHibernateTemplate().findByNamedQueryAndNamedParam("landlord.details",
> > args, vals);
> > }
> >
> > This would be an example of a method in your Dao. The HibernateTemplate
> > class has many other methods for executing queries so if this is not
> > suitable you can look up some others.
> >
> > If you prefer a Native Query (using SQL) rather than JPSQL then you can
> > use
> > @NamedNativeQuery instead. It looks like this:
> >
> > @NamedNativeQuery(
> > name="nameofyourquery",
> > query="actual sql query here",
> > resultClass=YourClass.class
> > )
> >
> > Though I am sure this would not be necessary.
> >
> > Now the trick is to write the query itself in such a way that it
> > eliminates
> > the duplicates for you.
> >
> >
> >
> > On Fri, Sep 12, 2008 at 5:28 PM, Joey122 <[EMAIL PROTECTED]> wrote:
> >
> >> Thanks for the reply -
> >>
> >> I guess the problem is that in the DAO we do something like:
> >> getHibernateTemplates.findByCriteria......
> >> This doesnt work for me :( - I d much rather write the sql statement
> from
> >> scratch like select colA from myTable.... Is this possible?
> >>
> >> Vincent Ramdhanie wrote:
> >> I don't think that a group by is designed to remove duplicates, you
> >> probably want to select distinct. However, in Hibernate if you are using
> >> eager fetching you might still end up with duplicates so you may have to
> >> remove them yourself. This topic might provide some clues:
> >> http://www.javalobby.org/java/forums/t68781.html Vincent On Fri, Sep
> 12,
> >> 2008 at 6:42 AM, Joey122 wrote: > I ve been blown away by how neat
> >> Appfuse
> >> is and its been a great > kickstart. > > I m trying to ensure that
> >> duplicates are removed from the data returned by > hibernate by forcing
> a
> >> group by - So far I ve tried to use the Projection > group property on
> >> the
> >> criteria as follows: > >
> >> criteria.setProjection(Projections.projectionList().add( >
> >> Projections.groupProperty("landlord_details").as(
> >> "landlord_details")).add(
> >> > Projections.groupProperty("landlord_agent_details").as( >
> >> "landlord_agent_details"))); > > However this no longer returns all the
> >> data
> >> I need and only returns the > columns which I have grouped by which is
> >> wrong
> >> :( > > Can anyone give a pointer on how to force a group by on certain
> >> columns > whilst still keeping all the other columns that I need? >
> >> ------------------------------ > View this message in context: Forcing A
> >> Group By In Hibernate > Sent from the AppFuse - User mailing list
> >> archiveat
> >> Nabble.com. >
> >>
> >>
> >> ------------------------------
> >> View this message in context: Re: Forcing A Group By In
> >> Hibernate<
> http://www.nabble.com/Forcing-A-Group-By-In-Hibernate-tp19453450s2369p19464442.html
> >
> >> Sent from the AppFuse - User mailing list
> >> archive<http://www.nabble.com/AppFuse---User-f2370.html>at Nabble.com.
> >>
> >
> >
> >
> > --
> > Vincent Ramdhanie
> > Vinette Technologies
> >
> >
>
> --
> View this message in context:
> http://www.nabble.com/Forcing-A-Group-By-In-Hibernate-tp19453450s2369p19465116.html
> Sent from the AppFuse - User mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
--
Vincent Ramdhanie
Vinette Technologies
