CODEWITHSUNDEEP
grailsgrails-orm
demon101
demon101

Reputation: 564

How to get DB connection in grails 3

What is proper way to get DB connection in Grails 3?

For grails 2 following code has works:

((SessionImpl) sessionFactory.getCurrentSession()).connection() // sessionFactory initialized in bootstrap

But after migration to Grails 3 sometimes I see exceptions in the log:

java.sql.SQLException: Operation not allowed after ResultSet closed at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860) at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:743) at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1037) at com.mysql.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:2757) at com.mchange.v2.c3p0.impl.NewProxyResultSet.getLong(NewProxyResultSet.java:424) at java_sql_ResultSet$getLong$3.call(Unknown Source)

It happens for 0,01% of requests

Upvotes: 2

Views: 2368

Answers (3)

demon101
demon101

Reputation: 564

For executing queries inside current hibernate transactions following construction can be used:

sessionFactory.currentSession.doWork {connection -> 
   new Sql(connection).execute(query, params)
}

Upvotes: 0

jgithaiga
jgithaiga

Reputation: 134

From grails docs, you can get the actual dataSource bean. From that you can access the connection or use it to query your db


    import groovy.sql.Sql

    def dataSource

    println "connection: ${dataSource.connection}"

    Sql sql = new Sql(dataSource)
    sql.eachRow("SELECT * FROM note") { row ->
       println "row: ${row}"
    }

Use 'dataSourceUnproxied' to avoid Hibernate transaction and session issues: 


    def dataSourceUnproxied

Upvotes: 0

Daniel
Daniel

Reputation: 3370

I guess it depends on where you need it, but you can inject a DataSource into a service.

javax.sql.DataSource dataSource

Then you can just use

dataSource.getConnection()

Also be aware of the changes to flush mode in GORM 6 (http://gorm.grails.org/6.0.x/hibernate/manual/ section 1.2.1). If an upstream save/commit is failing, your result set could be incidentally closed and trigger an error that looks like this while not really have anything to do with this particular line of code at all. I'd (very temporarily) set back to the old flush mode and see if the problem goes away, before tracking much more down!

Upvotes: 4

Related Questions