Announcement Announcement Module
Collapse
No announcement yet.
Using sum and count with Hibernate Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Using sum and count with Hibernate

    I would *really* appreciate some help on this. I am having a lot of difficulty using aggregate functions with spring and hibernate.

    My in my hibernateDao I have the following method:

    public Integer getCProjectsByYear(String clientId, String targetYear) {
    Integer sum = 0;
    StringBuilder sb = new StringBuilder();
    sb.append(" select count(*) from Project where clientId = ").append(clientId).append(" AND (cpDate >= '").append(targetYear).append("' AND cpDate < DATE_ADD('").append(targetYear).append("',INTERVAL 366 DAY))");
    List result = getHibernateTemplate().find(sb.toString() );
    if (null != result && result.size()>0)
    sum = (Integer)result.get(0);

    return sum;

    }

    This produces and error:
    org.hibernate.hql.ast.QuerySyntaxException: unexpected token: 366 near line 1, column 145 [ select count(*) from com.genevaglobal.model.Project whe
    clientId = 5 AND (cpDate >= '2004-01-01' AND cpDate < DATE_ADD('2004-01-01',INTERVAL 366 DAY))]
    at org.hibernate.hql.ast.QuerySyntaxException.convert (QuerySyntaxException.java:31)
    at org.hibernate.hql.ast.QuerySyntaxException.convert (QuerySyntaxException.java:24)
    at org.hibernate.hql.ast.ErrorCounter.throwQueryExcep tion(ErrorCounter.java:59)
    at org.hibernate.hql.ast.QueryTranslatorImpl.parse(Qu eryTranslatorImpl.java:258)
    at org.hibernate.hql.ast.QueryTranslatorImpl.doCompil e(QueryTranslatorImpl.java:157)
    at org.hibernate.hql.ast.QueryTranslatorImpl.compile( QueryTranslatorImpl.java:111)
    at org.hibernate.engine.query.HQLQueryPlan.<init>(HQL QueryPlan.java:77)
    at org.hibernate.engine.query.HQLQueryPlan.<init>(HQL QueryPlan.java:56)
    at org.hibernate.engine.query.QueryPlanCache.getHQLQu eryPlan(QueryPlanCache.java:72)
    at org.hibernate.impl.AbstractSessionImpl.getHQLQuery Plan(AbstractSessionImpl.java:133)
    at org.hibernate.impl.AbstractSessionImpl.createQuery (AbstractSessionImpl.java:112)
    at org.hibernate.impl.SessionImpl.createQuery(Session Impl.java:1623)
    at org.springframework.orm.hibernate3.HibernateTempla te$29.doInHibernate(HibernateTemplate.java:832)
    at org.springframework.orm.hibernate3.HibernateTempla te.execute(HibernateTemplate.java:362)
    at org.springframework.orm.hibernate3.HibernateTempla te.find(HibernateTemplate.java:830)
    at org.springframework.orm.hibernate3.HibernateTempla te.find(HibernateTemplate.java:822)
    at com.genevaglobal.dao.hibernate.ProjectDaoHibernate .getCProjectsByYear(ProjectDaoHibernate.java:212)


    I get the same error trying sum('cpinvestments').

    What am I doing wrong?

  • #2
    Hibernate provides several query technologies to do complicated querys without all this String concat stuff. You really don't want to do this kind of thing, use the technologies to do the hard work for you.

    Have a look at these.
    http://www.hibernate.org/hib_docs/v3...ngle/#queryhql
    http://www.hibernate.org/hib_docs/v3...#querycriteria

    Comment


    • #3
      Looks like you HQL syntax is incorrect. Refer to the Hibernate HQL document on aggregates.

      http://www.hibernate.org/hib_docs/re.../queryhql.html

      This show how to do sum, count, etc..

      Comment


      • #4
        The other thing you might want to bare it mind is that you can actually run native SQL through Hibernate, this might be useful as I'm not sure the DATE_ADD function will work in HQL.

        http://www.hibernate.org/hib_docs/v3...ngle/#querysql

        Comment


        • #5
          Code:
          StringBuilder sb = new StringBuilder();
          sb.append(" select count(*) from Project where clientId = ").append(clientId).append(" AND (cpDate >= '").append(targetYear).append("' AND cpDate < DATE_ADD('").append(targetYear).append("',INTERVAL 366 DAY))");
          WOW!!!
          Guys, DO NOT do it at home!

          Comment


          • #6
            As Karl pointed out, do not use String concatenation - it's not that it's ugly or error prone but it's subject to SQL injections (http://en.wikipedia.org/wiki/Sql_injection).

            Comment


            • #7
              I would actually say don't use it for all of the reasons. You can prevent SQL injection attacks, but why do you want to do all that boilerplate code.

              Just don't do it, thats what these wonderful tools are there for. Spring with its JdbcTemplate and other support classes, Hibernate with its HQL, QBC and even nativeSQL support. There are so many things to think about otherwise, SQL injection being one of the most serious.

              Comment


              • #8
                Thx for all the responses. I keep forgetting that Hibernate uses hql no sql. I did recently discover hib's filtering options, which seem pretty powerful. I have an impossible deadline so I have to take the path of least learning curve right now, but I will study up on these responses next month.

                Take care.

                Comment


                • #9
                  Originally posted by buzzterrier View Post
                  Thx for all the responses. I keep forgetting that Hibernate uses hql no sql. I did recently discover hib's filtering options, which seem pretty powerful. I have an impossible deadline so I have to take the path of least learning curve right now, but I will study up on these responses next month.
                  Impossible deadlines........ they're always err........ fun? There are lots of options in Hibernate, it can be quite a learning curve. You can always fallback on the fact you can write nativeSQL through Hibernate though .

                  Good luck with it anyway!

                  Comment

                  Working...
                  X