VictorArgentin VictorArgentin - 5 months ago 11x
SQL Question

Grails sql queries

Imagine I have something like this:

def example = {
def temp = ConferenceUser.findAllByUser(User.get(session.user))
[temp: temp]

Explaining my problem:
Although dynamic finders are very easy to use and fast to learn, I must replace dynamic finders of my website for sql queries because it is a requirement. As I don't understand SQL that much, my main questions are:

a) I am using an SQLS database, with the drivers and datasource good configured and my website works as it is right now. If I want to replace the "findAllByUser" for an sql statement, should i do something like this:

def dataSource
def db = new Sql(dataSource)
def temp = db.rows("SELECT ... ")

b) And that will work? I mean, the temp object will be a list as it is if I use "findAllByUser", and do I need to open a connection to the database =?


yes, with grails you can do both plain sql and hql queries. HQL is 'hibernate query language' and allows you to write sql-like statements, but use your domain classes and properties instead of the table names and column names. To do an hql query, do something like

def UserList = ConferenceUser.executeQuery('from ConferenceUser cu where cu.user = ?', [user]),  

what you have here is a parameterized query -- executeQuery sees the ? in the hql string and substitutes the arguments in the array that is the second parameter to the method([user] in this case) for you.


and you can see this on how to do sql queries with Grails

Sql query for insert in grails