Grails: Raw SQL query in the current transaction

Question

I am trying to execute a raw sql query using something similar to

def dataSource;
Sql sql = new Sql(dataSource);

But, it seems that this runs in a separate transaction of its own. It therefore misses all the (uncommitted) changes done before it in the service method.

What is the best way to run a raw sql query in the current transaction?

Answer 1

The above code would create a new connection, hence a new transaction. You can use the current Hibernate session(injecting sessionFactory) to execute a raw sql in the current transaction as below.

def session = sessionFactory.getCurrentSession()
def results = session.createSQLQuery(yourQueryString)