Grails, Hibernate, SQL Queries

[mdlane]

I'm new to Grails and relatively inexperienced with Java development, so I'm
looking for some hopefully quick advice:

I'm trying to find a way to execute SQL statements within Grails through
Hibernate.

I've already looked at groovy.sql.Sql -- I don't want to need to manually
open a new JDBC instance each time I want to do this. I'd like to try and
go through Hibernate since all of the database information is already set up
and ready to go.

I tried using SQLQuery but I got unusual errors that I couldn't resolve,
probably due to the fact that the way Grails is set up is not like your
average Java app.

Anyone have any specific thoughts/advice? Would be much appreciated, I've
been banging my head on this for days.

[Fernando Takai]

YourDomainClass.executeQuery
http://grails.org/doc/1.0.x/ref/Domain%20Classes/executeQuery.html

Cheers!

Post by mdlane
I'm new to Grails and relatively inexperienced with Java development, so I'm
I'm trying to find a way to execute SQL statements within Grails through
Hibernate.
I've already looked at groovy.sql.Sql -- I don't want to need to manually
open a new JDBC instance each time I want to do this. I'd like to try and
go through Hibernate since all of the database information is already set up
and ready to go.
I tried using SQLQuery but I got unusual errors that I couldn't resolve,
probably due to the fact that the way Grails is set up is not like your
average Java app.
Anyone have any specific thoughts/advice? Would be much appreciated, I've
been banging my head on this for days.
--
http://www.nabble.com/Grails%2C-Hibernate%2C-SQL-Queries-tp18632506p18632506.html
Sent from the grails - user mailing list archive at Nabble.com.
---------------------------------------------------------------------
http://xircles.codehaus.org/manage_email

[Adam]

Hi,

You may want to take a look at HQL:
http://grails.org/doc/1.0.x/guide/5.%20Object%20Relational%20Mapping%20(GORM).html#5.4.3%20Hibernate%20Query%20Language%20(HQL)



On Thu, Jul 24, 2008 at 10:02 AM, Fernando Takai

Post by Fernando Takai
YourDomainClass.executeQuery
http://grails.org/doc/1.0.x/ref/Domain%20Classes/executeQuery.html
Cheers!

Post by mdlane
I'm new to Grails and relatively inexperienced with Java development, so I'm
I'm trying to find a way to execute SQL statements within Grails through
Hibernate.
I've already looked at groovy.sql.Sql -- I don't want to need to manually
open a new JDBC instance each time I want to do this. I'd like to try and
go through Hibernate since all of the database information is already set up
and ready to go.
I tried using SQLQuery but I got unusual errors that I couldn't resolve,
probably due to the fact that the way Grails is set up is not like your
average Java app.
Anyone have any specific thoughts/advice? Would be much appreciated, I've
been banging my head on this for days.
--
http://www.nabble.com/Grails%2C-Hibernate%2C-SQL-Queries-tp18632506p18632506.html
Sent from the grails - user mailing list archive at Nabble.com.
---------------------------------------------------------------------
http://xircles.codehaus.org/manage_email

--
Fernando "Takai"
http://flickr.com/photos/supeertakai
http://fernandotakai.tumblr.com/
http://twitter.com/fernando_takai

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

[Rafael Nami]

YourDomain.executeQuery just executes HQL, it's not a solution when it's
needed to execute plain SQL. It doesn't seem to have a dynamic method to
execute "native queries", that is something that Hibernate have for a long
time...

Just my 2c

Rafael Mauricio Nami

Post by Adam
Hi,
http://grails.org/doc/1.0.x/guide/5.%20Object%20Relational%20Mapping%20(GORM).html#5.4.3%20Hibernate%20Query%20Language%20(HQL)
On Thu, Jul 24, 2008 at 10:02 AM, Fernando Takai

Post by Fernando Takai
YourDomainClass.executeQuery
http://grails.org/doc/1.0.x/ref/Domain%20Classes/executeQuery.html
Cheers!
On Thu, Jul 24, 2008 at 10:59 AM, mdlane <

Post by mdlane
I'm new to Grails and relatively inexperienced with Java development, so I'm
I'm trying to find a way to execute SQL statements within Grails through
Hibernate.
I've already looked at groovy.sql.Sql -- I don't want to need to

manually

Post by Fernando Takai

Post by mdlane
open a new JDBC instance each time I want to do this. I'd like to try

and

Post by Fernando Takai

Post by mdlane
go through Hibernate since all of the database information is already

set

Post by Fernando Takai

Post by mdlane
up
and ready to go.
I tried using SQLQuery but I got unusual errors that I couldn't resolve,
probably due to the fact that the way Grails is set up is not like your
average Java app.
Anyone have any specific thoughts/advice? Would be much appreciated,

I've

Post by Fernando Takai

Post by mdlane
been banging my head on this for days.
--

http://www.nabble.com/Grails%2C-Hibernate%2C-SQL-Queries-tp18632506p18632506.html

Post by Fernando Takai

Post by mdlane
Sent from the grails - user mailing list archive at Nabble.com.
---------------------------------------------------------------------
http://xircles.codehaus.org/manage_email

--
Fernando "Takai"
http://flickr.com/photos/supeertakai
http://fernandotakai.tumblr.com/
http://twitter.com/fernando_takai

---------------------------------------------------------------------
http://xircles.codehaus.org/manage_email

[David Hurt]

Burt Beckwith suggested this way to get a hibernate session and it worked
just fine for me to execute stored procedures through JDBC

"I was going to suggest that you use session.connection() in case there's a
current transaction or other db access, you'd want to use the same
connection. The OpenSessionInView interceptor ensures that there's a current
Session, so setup a dependency injection for the session factory, e.g.

def sessionFactory

then you can call sessionFactory.currentSession.connection() in your service
code."

[Rafael Nami]

Yeah, but there's no way to call hibernate's createSQLQuery from Domain. I
was trying to access the HibernateTemplate, but with no luck. Do I have to
do that on service class ? (It does seems right - JDBC is something without
the scope of a Domain... is something more... Resource-like)...

Do you guys have code examples? on that?

Best Regards

Rafael Nami

Post by David Hurt
Burt Beckwith suggested this way to get a hibernate session and it worked
just fine for me to execute stored procedures through JDBC
"I was going to suggest that you use session.connection() in case there's a
current transaction or other db access, you'd want to use the same
connection. The OpenSessionInView interceptor ensures that there's a current
Session, so setup a dependency injection for the session factory, e.g.
def sessionFactory
then you can call sessionFactory.currentSession.connection() in your
service code."

[grails]

How's this:
import groovy.sql.Sql
class MyController {

def dataSource

def list = {
def qry = "SELECT table.* FROM table"
Sql sql = new Sql(dataSource)

[practiceList: sql.rows(qry)]
}
}

[Rafael Nami]

Yeah, but for that I'll have to bypass the entire service layer, and write
SQL code directly on a controller... Plus I'll have to write spring IoC
configuration (quite easy thou).

I was wondering on using hibernate features to execute SQL, but this one may
answer my problem

Back to grails code...

Best Regards

Rafael Mauricio Nami

Post by grails
import groovy.sql.Sql
class MyController {
def dataSource
def list = {
def qry = "SELECT table.* FROM table"
Sql sql = new Sql(dataSource)
[practiceList: sql.rows(qry)]
}
}
--
http://www.nabble.com/Grails%2C-Hibernate%2C-SQL-Queries-tp18632506p18707241.html
Sent from the grails - user mailing list archive at Nabble.com.
---------------------------------------------------------------------
http://xircles.codehaus.org/manage_email

[Rafael Nami]

David, your suggestion worked like a charm, but there are pieces missing,
because I think that would be much more elegant something like
Domain.createSQLQuery("select ...", [prop1: 'value1'], but it could lead to
other issues as well....

Do you guys know how to access the HibernateTemplate (or JdbcTemplate) from
a Domain (or a Service)?

Thanks in advance, and best regards

Rafael Mauricio Nami

Post by Rafael Nami
Yeah, but for that I'll have to bypass the entire service layer, and write
SQL code directly on a controller... Plus I'll have to write spring IoC
configuration (quite easy thou).
I was wondering on using hibernate features to execute SQL, but this one
may answer my problem
Back to grails code...
Best Regards
Rafael Mauricio Nami

Post by grails
import groovy.sql.Sql
class MyController {
def dataSource
def list = {
def qry = "SELECT table.* FROM table"
Sql sql = new Sql(dataSource)
[practiceList: sql.rows(qry)]
}
}
--
http://www.nabble.com/Grails%2C-Hibernate%2C-SQL-Queries-tp18632506p18707241.html
Sent from the grails - user mailing list archive at Nabble.com.
---------------------------------------------------------------------
http://xircles.codehaus.org/manage_email

[Rafael Nami]

Just for the record -The way that I think it is better (my opinion, not an
absolute truth :D) - adding a dynamic method to my domain objects to execute
sql inside hibernate - I'll have to write a plugin to add this method
(pretty easy considering the examples from the reference documentation).
I'll try to write up this plugin, and return for the community to collect
the feedback.

Thanks for everything, and Best Regards

Rafael Mauricio Nami

Post by Rafael Nami
David, your suggestion worked like a charm, but there are pieces missing,
because I think that would be much more elegant something like
Domain.createSQLQuery("select ...", [prop1: 'value1'], but it could lead to
other issues as well....
Do you guys know how to access the HibernateTemplate (or JdbcTemplate) from
a Domain (or a Service)?
Thanks in advance, and best regards
Rafael Mauricio Nami

Post by Rafael Nami
Yeah, but for that I'll have to bypass the entire service layer, and write
SQL code directly on a controller... Plus I'll have to write spring IoC
configuration (quite easy thou).
I was wondering on using hibernate features to execute SQL, but this one
may answer my problem
Back to grails code...
Best Regards
Rafael Mauricio Nami

Post by grails
import groovy.sql.Sql
class MyController {
def dataSource
def list = {
def qry = "SELECT table.* FROM table"
Sql sql = new Sql(dataSource)
[practiceList: sql.rows(qry)]
}
}
--
http://www.nabble.com/Grails%2C-Hibernate%2C-SQL-Queries-tp18632506p18707241.html
Sent from the grails - user mailing list archive at Nabble.com.
---------------------------------------------------------------------
http://xircles.codehaus.org/manage_email