grails findAll tag

Question

How to use "SELECT id, name, part, description FROM user " in grails findAll tag.

I tried

 User.findAll("SELECT id, name, part, description FROM user") 

instead using

User.findAll("FROM user")

But shows errors . can anyone suggest me the tag

thanks, sri..

Answer 1

finadAll() returns a Collection of domain objects, so enumerating columns to select does not make sense; the queries it understands are not real SQL, and consist basically only of WHERE clauses. Since you don't seem to want to constrain the result set, this is probably all you need:

User.findAll()

It will return a collection of all User objects. If you need constraints, the syntax ist

User.findAll("from User as u where u.id=?", [userId])

Or, even simpler, you can use a dynamic finder:

User.findAllById(userId);

Answer 2

If you want to run report-style queries like this, use the executeQuery method:

def rows = User.executeQuery("SELECT id, name, part, description FROM User")

The return value will be a List of Object[] where each element in the object array is the type of the column, i.e. the 1st element will be a long, 2nd a String, etc.

Note that User has to be capitalized since you're referring to the Hibernate entity - this isn't a SQL query, it's HQL.

Answer 3

If you want to query for only certain fields, you can use a criteria query with a projection.

Example:

def userProperties = User.withCriteria {
    projections {
        property('id')
        property('name')
        property('part')
        property('description')
    }
}

This query will return an array of Strings (or whatever the database column type is mapped to) for each matching row, instead of a domain object.