Grails: source SQL query in 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 looks like this is being performed by a separate transaction. Therefore, it skips all (uncommitted) changes made 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 will create a new connection, and therefore a new transaction. You can use the current Hibernate session (input sessionFactory) to execute raw sql in the current transaction, as shown below.

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