Tuesday, 13 May 2014

Executing SQL queries with GORM

Yesterday at work somebody asked me how to execute a SQL query through GORM. I didn't remember at the moment how exactly how to do it, but I remembered that Hibernate's Session class had a method called executeSQLQuery.

This is a silly example to show how we have done it. Lets say you have a domain class 'MyDomainClass' and inside a service (MyService) you need to execute a given SQL query. It could be like this:

  class MyDomainClass {
      String name
      Integer age
  }

  class MyService {
      Integer getAgeByExecutingSQL() {
         return MyDomainClass.withSession { session ->
            session.executeSQLQuery("select age from my_domain_class").get()
         }
      }
  }

Accessing Hibernate session through withSession method we can access the method I was mentioning above. Then is just a matter of creating a valid SQL and of course keep in mind that the query doesn't return domain class instances but arrays of data.


2 comments:

  1. Nice blog,explanation is good,thank you for your valuable information.For more details please visit our website.
    Oracle Fusion Training Institute


    ReplyDelete
  2. I think now it is:
    SQLQuery q3 = session.createSQLQuery("select uuid() UNION select uuid()")
    List l3 = q3.list();

    ReplyDelete