These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 3 posts ] 
  Previous topic | Next topic 
Author Message
jbk
 Post subject: QueryParer Fails unexpected token: (
PostPosted: Sat Apr 05, 2008 3:08 pm 
Newbie

Joined: Sat Apr 05, 2008 2:54 pm
Posts: 2
Hibernate version: 3

Code between sessionFactory.openSession() and session.close():
Code:
    --------------------------------------------------------------------------------
                StringBuffer queryString = new StringBuffer();
                queryString.append("select campaign, summed.counts, summed.searchcount " );
                queryString.append("from Campaign campaign ");
                queryString.append("left outer join (");
                queryString.append("   select c, sum(cs.clickCount) counts, sum(cs.searchCount) searchount");
                queryString.append("   from Campaign c, CampaignSummary cs ");
                queryString.append("   where :bmlsId = c.brokerMls.id and c = cs.campaign ");
                queryString.append("   and cs.date_summarized > :from and cs.date_summarized <= :to ");
                queryString.append("   group by c ");
                queryString.append(") summed ");
                queryString.append("on campaign = summed.c ");
                queryString.append("where :bmlsId = campaign.brokerMls.id");

                if ( log.isDebugEnabled() ) {
                    log.debug( "findCampaignsSummarizedWithDataInDateRange() queryString=\n" + queryString.toString());
                }

                Query query = session.createQuery(queryString.toString());
                query.setLong("bmlsId", brokerMls.getId());
                query.setTimestamp("from",start);
                query.setTimestamp("to",end);
   --------------------------------------------------------------------------------


Anootation relationship:
Code:
BrokerMls 1 --- * Campaign 1-----*CampaignSummary

BrokerMls.java ::::::
    @OneToMany(mappedBy="brokerMls",fetch = FetchType.EAGER)
    @Cascade({org.hibernate.annotations.CascadeType.DELETE_ORPHAN})
    @JSON (serialize = false)
    public Set<Campaign> getCampaigns() {
        return campaigns;
    }

Campaign .java ::::::
    @ManyToOne
    @JoinColumn( nullable=false, name="broker_mls_id" )
    @OrderBy( "name" )
    @JSON ( serialize = false )
    public BrokerMls getBrokerMls() {
        return brokerMls;
    }

   @OneToMany(mappedBy="campaign")
    @JSON ( serialize = false )
    public Set<CampaignSummary> getCampaignSummaries() {
        return campaignSummaries;
    }

CampaignSummary.java ::::::
    @ManyToOne
    public Campaign getCampaign() {
        return campaign;
    }



Full stack trace of any exception that occurs:
Code:
2008-04-05 11:26:18,292 [TP-Processor3] DEBUG org.hibernate.engine.query.QueryPlanCache  - unable to locate HQL query plan in cache; generating (select campaign, summed.counts, summed.searchcount from Campaign campaign left outer join (   select c, sum(cs.clickCount), sum(cs.searchCount)    from Campaign c, CampaignSummary cs    where :bmlsId = c.brokerMls.id and c = cs.campaign    and cs.date_summarized > :from and cs.date_summarized <= :to    group by c ) summed on campaign = summed.c where :bmlsId = campaign.brokerMls.id)
2008-04-05 11:26:18,443 [TP-Processor3] DEBUG org.hibernate.hql.ast.QueryTranslatorImpl  - parse() - HQL: select campaign, summed.counts, summed.searchcount from com.roost.model.Campaign campaign left outer join (   select c, sum(cs.clickCount), sum(cs.searchCount)    from com.roost.model.Campaign c, com.roost.model.CampaignSummary cs    where :bmlsId = c.brokerMls.id and c = cs.campaign    and cs.date_summarized > :from and cs.date_summarized <= :to    group by c ) summed on campaign = summed.c where :bmlsId = campaign.brokerMls.id
2008-04-05 11:26:18,452 [TP-Processor3] ERROR org.hibernate.hql.PARSER  - line 1:107: unexpected token: (
2008-04-05 11:26:18,456 [TP-Processor3] DEBUG org.hibernate.hql.ast.ErrorCounter  - line 1:107: unexpected token: (
line 1:107: unexpected token: (
   at org.hibernate.hql.antlr.HqlBaseParser.fromJoin(HqlBaseParser.java:1701)
   at org.hibernate.hql.antlr.HqlBaseParser.fromClause(HqlBaseParser.java:1420)
   at org.hibernate.hql.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java:1130)
   at org.hibernate.hql.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:702)
   at org.hibernate.hql.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:296)
   at org.hibernate.hql.antlr.HqlBaseParser.statement(HqlBaseParser.java:159)
   at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:248)
   at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:157)
   at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
   at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
   at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
   at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
   at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
   at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
   at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)


Name and version of the database you are using:
Postgre8

The generated SQL (show_sql=true):
FAILS TO GENERATE ANY



Using hibernate3 using annotations.
I have the following HSQL that I try to run but the QueryParser complains:
Code:
select campaign, summed.counts, summed.searchcount
from Campaign campaign
left outer join (
   select c, sum(cs.clickCount) counts, sum(cs.searchCount) searchcount
   from Campaign c, CampaignSummary cs
   where :bmlsId = c.brokerMls.id and c = cs.campaign
   and cs.date_summarized > :from and cs.date_summarized <= :to
   group by c
) summed
on campaign = summed.c
where :bmlsId = campaign.brokerMls.id

I have tried many variations of this and always complains about unexpected token: (
the nested select by itself runs fine no problem

I get the following exception but I am not sure why.
Code:
2008-04-05 11:26:18,443 [TP-Processor3] DEBUG org.hibernate.hql.ast.QueryTranslatorImpl  - parse() - HQL: select campaign, summed.counts, summed.searchcount from com.roost.model.Campaign campaign left outer join (   select c, sum(cs.clickCount), sum(cs.searchCount)    from com.roost.model.Campaign c, com.roost.model.CampaignSummary cs    where :bmlsId = c.brokerMls.id and c = cs.campaign    and cs.date_summarized > :from and cs.date_summarized <= :to    group by c ) summed on campaign = summed.c where :bmlsId = campaign.brokerMls.id
2008-04-05 11:26:18,452 [TP-Processor3] ERROR org.hibernate.hql.PARSER  - line 1:107: unexpected token: (
2008-04-05 11:26:18,456 [TP-Processor3] DEBUG org.hibernate.hql.ast.ErrorCounter  - line 1:107: unexpected token: (
line 1:107: unexpected token: (
   at org.hibernate.hql.antlr.HqlBaseParser.fromJoin(HqlBaseParser.java:1701)
   at org.hibernate.hql.antlr.HqlBaseParser.fromClause(HqlBaseParser.java:1420)


The normal SQL I am trying to mimic that I have ran and know works is the following::
Code:
                 select campaign.id,campaign.name, counts, searchcount
                     from  campaign
                 LEFT OUTER JOIN
                     (select c.id as id,sum(cs.counts) as counts, sum(cs.searchcount)  as searchcount
                     from broker_mls bmls, campaign c, campaign_summary cs
                     where  c.broker_mls_id in (4652,4653,4650)
                             and c.id = cs.campaign_id
                                 and cs.date_summarized > 'Tue Apr 01 00:00:02 PDT 2008'
                                     and cs.date_summarized <= 'Thu Apr 03 17:40:02 PDT 2008 '
                     group by c.id)
                 as cs
                 on campaign.id = cs.id
                 where campaign.broker_mls_id in (4652,4653,4650);


So not sure if/how my code is wrong.
Top
 Profile  
 
pbk
 Post subject:
PostPosted: Mon Apr 07, 2008 3:24 am 
Newbie

Joined: Fri Jun 10, 2005 9:32 am
Posts: 5
try using session.createSQLQuery() instead of session.createQuery();
Top
 Profile  
 
jbk
 Post subject:
PostPosted: Mon Apr 07, 2008 2:03 pm 
Newbie

Joined: Sat Apr 05, 2008 2:54 pm
Posts: 2
Ok I will try that thanx for the reply.

In this case I must convert everything to regular SQL and that sort of defeats the purpose of of using hibernate.
I mean will get Id's and then will have to make separate call to instantiate the associated campaign objects. That is what I was hoping HQL 'select campaign' would buy me.

Anyway
I am wondering if HQL does not like left outer join on a subquery......?
Since I did not get a response like "your query is bogus" I will assume my query is supported and should work as is but there is some other issue.


Currently I do a get campaigns loop throught them to make the summations.
Long winded but will work.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 3 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.