14

What is the best way to query for something without using GORM in grails?

I have query that doesn't seem to fit in the GORM model, the query has a subquery and a computed field. I posted on stackoverflow already with no response so I decided to take a different approach. I want to query for something not using GORM within a grails application. Is there an easy way to get the connection and go through the result set?

Community
  • 1
  • 1
Tihom
  • 3,384
  • 6
  • 36
  • 47

2 Answers2

37

In a service or controller, you can add a dependency injection for the dataSource bean and use groovy.sql.Sql or JDBC directly if you're a masochist.

import groovy.sql.Sql

class DataService {

   def dataSource

   void runQuery(...) {
      def sql = new Sql(dataSource)
      sql.eachRow('select * from foo') { row ->
         ...
      }
   }
}
Burt Beckwith
  • 75,342
  • 5
  • 143
  • 156
-1

In the moste cases I use criteria queries.

def c = Account.createCriteria()
def results = c {
    between("balance", 500, 1000)
    eq("branch", "London")
    or {
        like("holderFirstName", "Fred%")
        like("holderFirstName", "Barney%")
    }
    maxResults(10)
    order("holderLastName", "desc")
}
Medrod
  • 986
  • 7
  • 17
  • This doesn't support subqueries from what I Know – Tihom Jan 05 '11 at 20:06
  • @Tihon it supportet subqueries because it is a hibernate-criteria-query http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querycriteria.html#querycriteria-detachedqueries – Medrod Jan 06 '11 at 14:13
  • @Igs GORM is not criteriaquery. Criteria query looks more like GROM than HSQL but it is not the same. GROM is using meta methodes and criteria queries will use hibernate criteria queries. – Medrod Jan 06 '11 at 14:21
  • Interesting. How would you do it in groovy? The answer is more appropriate on my other question: http://stackoverflow.com/questions/4577379/quering-computed-fields-in-gorm If you answer it there, I will give you the correct answer. – Tihom Jan 06 '11 at 16:18