It is well known that Grails sits on top of Spring and Hibernate - two of the most popular Java frameworks. Hibernate is used as the underlying technology for the object-relational mapping of Grails (GORM).
Hibernate is database agnostic. It means that since Grails is based on it, we could write applications that is compatible with most popular databases. We don't need to write different queries for each possible database.
The easiest way to perform database queries is through dynamic finders. It's simple and very intuitive. Check my previous post for a tutorial on this topic. Dynamic finders however are very limited. It may not be suitable for complex requirements and cases where the developer needs a lower level of control. HQL is a very good alternative as it is very similar to SQL.
HQL is fully object oriented and understands inheritance, polymorphism and association. Using it will provide a very powerful and flexible API yet preserving your application to be database agnostic. In Grails, there are two domain methods to use to invoke HQL
package asia.grails.test class Person { String firstName String lastName int age static constraints = { } }
This is the code to retrieve all Person objects from the database:
def listOfAllPerson = Person.executeQuery("from Person")Notice that:
def listOfAllPerson = Person.executeQuery("from asia.grails.test.Person")
Here is a sample code of how we could use the result
listOfAllPerson.each { person -> println "First Name = ${person.firstName}" println "Last Name = ${person.lastName}" println "Age = ${person.age}" }Since listOfAllPerson is a list of Person instances, we could iterate over it and print the details.
When the select clause is explicitly used, HQL will not return a list of domain objects. Instead, it will return a 2 dimensional list. Here is an example assuming that at least 1 record is in the database:
def list = Person.executeQuery("select firstName, lastName from Person") def firstPerson = list[0] def firstName = firstPerson[0] def lastName = firstPerson[1] println "First Name = ${firstName}" println "Last Name = ${lastName}"The variable list will be assigned a list of items. Each item is a list that corresponds to the value as enumerated in the select clause.
The code can also be written like this to help visualize the data structure:
def list = Person.executeQuery("select firstName, lastName from Person") def firstName = list[0][0] def lastName = list[0][1] println "First Name = ${firstName}" println "Last Name = ${lastName}"
People with surname Doe
def peopleWithSurnameDoe = Person.executeQuery("from Person where lastName = 'Doe'")
People who are at least 18 years old
def adults = Person.executeQuery("from Person where age >= 18")
People having first name that contains John
def peopleWithFirstNameLikeJohn = Person.executeQuery("from Person where firstName like '%John%'")
def list = Person.executeQuery("select age, count(*) from Person group by age") list.each { item -> def age = item[0] def count = item[1] println "There are ${count} people with age ${age} years old" }This will print all ages found in the table and how many people have that age.
The having clause is useful to filter out the result of a group by. Here is an example:
def list = Person.executeQuery( "select age, count(*) from Person group by age having count(*) > 1") list.each { item -> def age = item[0] def count = item[1] println "There are ${count} people with age ${age} years old" }This will print all ages found in the table and how many people have that age, provided that there are more than 1 person in the age group.
def listPage1 = Person.executeQuery("from Person order by id", [offset:0, max:10]) def listPage2 = Person.executeQuery("from Person order by id", [offset:10, max:10]) def listPage3 = Person.executeQuery("from Person order by id", [offset:20, max:10])The parameter max informs GORM to fetch a maximum of 10 records only. The offset means how many records to skip before reading the first result.
Note: It is usually better to have an order by clause when paginating results, otherwise most database offers no guarantee on how records are sorted between each query.
def result = Person.executeQuery( "from Person where firstName = ? and lastName = ?", ['John', 'Doe'])The parameters can be passed as a list. The first parameter (John) is used in the first question mark, the second parameter (Doe) is used in the second question mark, and so on.
Results can also be paginated
def result = Person.executeQuery( "from Person where firstName = ? and lastName = ?", ['John', 'Doe'], [offset:0, max:5])
def result = Person.executeQuery( "from Person where firstName = :searchFirstName and lastName = :searchLastName", [searchFirstName:'John', searchLastName:'Doe'])The colon signifies a named parameter variable. Then the values can be passed as a map of values.
Results can also be paginated:
def result = Person.executeQuery( "from Person where firstName = :searchFirstName and lastName = :searchLastName", [searchFirstName:'John', searchLastName:'Doe'], [offset:0, max:5])
Here is a shorter version:
def result = Person.executeQuery( "from Person where firstName = :searchFirstName and lastName = :searchLastName", [searchFirstName:'John', searchLastName:'Doe'], [offset:0, max:5])
package asia.grails.test class Purchase { static hasMany = [items:PurchaseItem] String customer Date dateOfPurchase double price }
package asia.grails.test class PurchaseItem { static belongsTo = Purchase Purchase parentPurchase String product double price int quantity }
Here is a sample code that joins the two tables:
def customerWhoBoughtPencils = Purchase.executeQuery( "select p.customer from Purchase p join p.items i where i.product = 'Pencil' ")This returns all customers who bought pencils
Here are some examples of how to delete records using executeUpdate.
Delete all person records in the database
Purchase.executeUpdate("delete Person")
Here are different ways to delete people with first name John
Person.executeUpdate("delete Person where firstName = 'John'") Person.executeUpdate("delete Person where firstName = ? ", ['John']) Person.executeUpdate("delete Person where firstName = :firstNameToDelete ", [firstNameToDelete:'John'])
Here are some examples of how to delete records using executeUpdate.
Here are different ways on how to make all people have the age 15
Person.executeUpdate("update Person set age = 15") Person.executeUpdate("update Person set age = ?", [15]) Person.executeUpdate("update Person set age = :newAge", [newAge:15])
Here are different ways to set John Doe's age to 15.
Person.executeUpdate( "update Person set age = 15 where firstName = 'John' and lastName = 'Doe'") Person.executeUpdate( "update Person set age = ? where firstName = ? and lastName = ?", [15, 'John', 'Doe']) Person.executeUpdate( "update Person set age = :newAge where firstName = :firstNameToSearch and lastName = :lastNameToSearch", [newAge:15, firstNameToSearch:'John', lastNameToSearch:'Doe'])