GRAILS: Execute Raw/Native SQL Queries

Home / Blog / Java / Grails / GRAILS: Execute Raw/Native SQL Queries

Although Grails provides a great abstraction on top of hibernate (GORM), it is sometimes necessary to execute raw SQL against the configured data source. An example of when you might want to do this is when you’d like to execute a query with computed values for reporting purposes.

For example, say you have an “events” table, and you’d like to chart how many events were created grouped by the entry dates. You could have SQL like such:


select substring(event.data_created, 1, 10) as dateCreated, count(event.id) from event where event.date_created > ? group by dateCreated order by event.date_created;

With the above query, you’ll retrieve the amount of events grouped by the day they occurred. Since you’re not binding this return data to any entity, its easier (and in my opinion cleaner), to execute this query apart from any hibernate approach (such as a named query).

In your service, define the following:


def sessionFactory;

This line will ensure grails will inject the configured hibernate session factory into your service. Now, within your service method, you could write code as such:


Session session = sessionFactory.openSession();
session.doWork new Work() {
@Override
public void execute(Connection conn) throws SQLException {
// execute your statement against the connection
}
};

Let me know if you have any better ideas…

Comments
  • Jim

    I define the data source for injection:

    javax.sql.DataSource dataSource

    Then access it with Groovy sql:

    def sql = new Sql(datasource).
    ….
    sql.close()

    Don’t know if this is a better method tho!

Leave a Comment