SlideShare a Scribd company logo
1 of 58
Download to read offline
GRAILS GORM 
Practical basics of how GORM can query for you 
when SQL is all you know. 
Dec 2014 - Ted Vinke
Overview 
• Introduction 
• Querying 
• Basic GORM 
• Dynamic Finders 
• Where Queries 
• Criteria 
• HQL 
• Native SQL 
• How to GORM your existing SQL? 
• Summary
We already know how to 
do SQL in Grails, right?
Just use Groovy! (jeej) 
class AnimalService { 
def dataSource 
def findWithGroovySql(String animalName) { 
def sql = new Sql(dataSource) 
try { 
return sql.rows("select * from animal where name = ?", [animalName]) 
} finally { 
sql.close() 
} 
} 
}
Is SQL your Grails application’s core 
business?
In a nutshell 
GORM stands for Grails Object Relational Mapping 
Grails 2.4.3 uses Hibernate 4 under the hood 
Evolves around domain classes
Domain classes? 
Database likes SQL We – the developers – like 
select * from animals 
where name = “Belle” 
to talk in domain terms 
Animal.findByName(“Belle”) 
select * from animals 
where id = 5 
Animal.get(5)
QUERYING 
What Grails can do for you
Settings 
dataSource { 
driverClassName = "org.h2.Driver" 
url = "jdbc:h2:mem:devDb;..." 
... 
} 
hibernate { 
... 
format_sql = true 
use_sql_comments = true 
}
We have a domain class 
// grails-app/domain/Animal.groovy 
class Animal { 
String name 
int age 
static mapping = { 
id column: "ani_id" 
version false 
} 
}
And some animals 
class BootStrap { 
def init = { servletContext -> 
environments { 
development { 
new Animal(name: "Belle", age: 1).save() 
new Animal(name: "Cinnamon", age: 5).save() 
} 
} 
} 
}
Basic GORM 
def animal = Animal.get(1) 
select animal0_.id as id1_0_0_, animal0_.version as version2_0_0_, 
animal0_.age as age3_0_0_, animal0_.name as name4_0_0_ from animal 
animal0_ where animal0_.id=? 
Hibernate uses unique table and column aliases, e.g. alias(column 
name)_(column unique integer)_(table unique integer)_(some 
suffix) 
int total = Animal.count() 
select count(*) as y0_ from animal this_ 
new Animal(name: "Belle", age: 1).save() 
insert into animal (id, version, age, name) values (null, ?, ?, ?) 
def animal = Animal.first(sort: "age") 
select ... from animal this_ order by this_.age asc limit ? 
• addTo 
• count 
• countBy 
• delete 
• exists 
• first 
• get 
• getAll 
• indent 
• last 
• list 
• listOrderBy 
• …
AnimalController 
// grails-app/controllers/AnimalController.groovy 
class AnimalController { 
def animalService 
def show(String name) { 
def animal = animalService.find...(name) 
log.info "Found " + animal 
... 
} 
} 
http://localhost:8080/query/animal/show?name=Belle
AnimalService 
class AnimalService { 
.... 
}
Dynamic 
finders 
Animal findWithDynamicFinder(String animalName) { 
Animal.findByName(animalName) 
} 
• findBy, countBy 
• findAllBy 
select this_.id as id1_0_0_, this_.version as 
version2_0_0_, this_.age as age3_0_0_, this_.name as 
name4_0_0_ from animal this_ where this_.name=? limit ?
Dynamic 
finders 
Animal findWithDynamicFinder(String animalName) { 
Animal.findByNameAndAgeLessThan(animalName, 3) 
} 
• findBy, countBy 
• findAllBy 
 Combines properties with 
all kinds of operators 
• LessThan 
• LessThanEquals 
• Between 
• Like 
• Not 
• Or 
select this_.id as id1_0_0_, this_.version as 
version2_0_0_, this_.age as age3_0_0_, this_.name as 
name4_0_0_ from animal this_ where this_.name=? and 
this_.age<? limit ?
Dynamic 
finders 
Animal findWithDynamicFinder(String animalName) { 
Animal.findByName(animalName, [sort: "age"]) 
} 
• findBy, countBy 
• findAllBy 
 Combines properties with 
all kinds of operators 
• LessThan 
• LessThanEquals 
• Between 
• Like 
• Not 
• Or 
 Pagination (sort, max, 
etc) and meta params 
(readOnly, timeout, 
etc.) 
select this_.id as id1_0_0_, this_.version as 
version2_0_0_, this_.age as age3_0_0_, this_.name as 
name4_0_0_ from animal this_ where this_.name=? order by 
this_.age asc limit ?
Where 
Animal findWithWhereQuery(String animalName) { 
def query = Animal.where { 
name == animalName 
} 
return query.find() 
} 
Defines a new 
grails.gorm.DetachedCriteria 
select this_.id as id1_0_0_, this_.version as 
version2_0_0_, this_.age as age3_0_0_, this_.name as 
name4_0_0_ from animal this_ where this_.name=?
Where 
Animal findWithWhereQuery(String animalName) { 
def query = Animal.where { 
name == animalName && (age < 3) 
} 
return query.find([sort: "age"]) 
} 
Defines a new 
grails.gorm.DetachedCriteria 
 Enhanced, compile-time 
checked query DSL. 
 More flexible than 
dynamic finders 
select this_.id as id1_0_0_, this_.version as 
version2_0_0_, this_.age as age3_0_0_, this_.name as 
name4_0_0_ from animal this_ where (this_.name=? and 
this_.age<?) order by this_.age asc
Where 
Animal findWithWhereQuery(String animalName) { 
def query = Animal.where { 
name == animalName && (age < 3) 
} 
return query.find() 
} 
The DetachedCriteria 
defined for where can also 
be used for find 
Animal findWithWhereQuery(String animalName) { 
Animal.find { 
name == animalName && (age < 3) 
} 
} 
Tip! 
If your query 
can return 
multiple rows, 
use findAll 
instead!
Criteria 
Animal findWithCriteria(String animalName) { 
// Criteria 
def c = Animal.createCriteria() 
return c.get { 
eq "name", animalName 
} 
} 
 Type-safe Groovy way of 
building criteria queries 
select this_.id as id1_0_0_, this_.version as 
version2_0_0_, this_.age as age3_0_0_, this_.name as 
name4_0_0_ from animal this_ where this_.name=?
Criteria 
Animal findWithCriteria(String animalName) { 
// Criteria 
def c = Animal.createCriteria() 
return c.get { 
eq "name", animalName 
lt "age", 3 
order "age", "desc" 
} 
} 
 Type-safe Groovy way of 
building criteria queries 
• c.list 
• c.get 
• c.scroll 
• c.listDinstinct 
select this_.id as id1_0_0_, this_.version as 
version2_0_0_, this_.age as age3_0_0_, this_.name as 
name4_0_0_ from animal this_ where this_.name=? and 
this_.age<? order by this_.age desc
Criteria and 
projections 
Long calculateTotalAge() { 
// Criteria 
def c = Animal.createCriteria() 
return c.get { 
projections { 
sum("age") 
} 
} 
} 
 Projections change the 
nature of the results 
select sum(this_.age) as y0_ from animal this_
How to test?
Dynamic finders, Where and Criteria queries 
can be unit tested! 
create-unit-test AnimalService 
test/unit/AnimalServiceSpec.groovy 
test-app –unit AnimalServiceSpec
AnimalServiceSpec 
• Uses DomainClassUnitTestMixin, simple in-memory ConcurrentHashMap 
@Mock(Animal) 
@TestFor(AnimalService) 
class AnimalServiceSpec extends Specification { 
void "test finding animals with various queries"() { 
given: 
new Animal(name: "Belle", age: 1).save() 
new Animal(name: "Cinnamon", age: 5).save() 
expect: 
"Belle" == service.findWithDynamicFinder("Belle").name 
"Belle" == service.findWithWhereQuery("Belle").name 
"Belle" == service.findWithCriteria("Belle").name 
} 
} 
Just @Mock the domain class 
and insert and verify your test 
data through the GORM API
Unit test with DomainClassUnitTestMixin 
uses 
in-memory ConcurrentHashMap 
which allows mocking of large part of GORM 
• Simple persistence methods like list(), save() 
• Dynamic Finders 
• Named Queries 
• Query By Example 
• GORM Events
HQL 
Animal findWithHQL(String animalName) { 
Animal.find("from Animal as a where a.name = :name", 
["name" : animalName]) 
} 
Hibernate Query Language 
Animal findWithHQL(String animalName) { 
Animal.executeQuery("from Animal a where a.name = 
:name", ["name" : animalName, "max" : 1]).first() 
} 
select animal0_.id as id1_0_, animal0_.version as 
version2_0_, animal0_.age as age3_0_, animal0_.name as 
name4_0_ from animal animal0_ where animal0_.name=? 
limit ?
HQL 
HQL almost looks like SQL… 
Animal.executeQuery("select distinct a.name 
from Animal a order by a.name") 
select distinct animal0_.name as col_0_0_ from 
animal animal0_ order by animal0_.name
class Animal { 
String name 
int age 
static mapping = { 
name column: "ani_name" 
age column: "ani_age" 
} 
} 
select animal0_.id as id1_0_, 
animal0_.version as version2_0_, 
animal0_.ani_age as ani_age3_0_, 
animal0_.ani_name as ani_name4_0_ from 
animal animal0_ where 
animal0_.ani_name=? limit ? 
HQL 
…but uses domain classes and properties instead of tables and columns 
Animal.executeQuery("select distinct a.name from Animal a order by a.name") 
class Animal { 
String name 
int age 
static mapping = {} 
} 
select animal0_.id as id1_0_, 
animal0_.version as version2_0_, 
animal0_.age as age3_0_, animal0_.name 
as name4_0_ from animal animal0_ where 
animal0_.name=? limit ?
How to test?
HQL can be unit tested!
AnimalServiceHibernateSpec 
• Uses HibernateTestMixin, Hibernate 4 and in-memory H2 
@Domain(Animal) 
@TestFor(AnimalService) 
@TestMixin(HibernateTestMixin) 
class AnimalServiceHibernateSpec extends Specification { 
def cleanup() { 
// unit test does not clear db between tests 
Animal.list()*.delete(flush: true) 
} 
void "test finding animal with HQL"() { 
given: 
new Animal(name: "Belle").save() 
new Animal(name: "Cinnamon").save() 
expect: 
"Belle" == service.findWithHQL("Belle").name
Unit test with HibernateTestMixin 
uses 
in-memory H2 database 
which allows testing all of GORM, including 
• String-based HQL queries 
• composite identifiers 
• dirty checking methods 
• other direct interaction with Hibernate 
• Hibernate needs to know 
about all domain classes, 
more than you would like to 
annotate with @Domain, so 
the Hibernate mixin is not 
really useful in practice
HQL can be integration tested! 
create-integration-test AnimalService 
test/integration/AnimalServiceSpec.groovy 
test-app –integration AnimalServiceSpec
AnimalServiceIntegrationSpec 
 Full Grails container is started in test-environment 
 Uses H2 in-memory database. Each test runs in transaction, which is rolled back at end of the test 
// no annotations whatsoever 
class AnimalServiceIntegrationSpec extends Specification { 
def animalService 
void "test finding animal with HQL"() { 
given: 
new Animal(name: "Belle").save() 
new Animal(name: "Cinnamon").save() 
expect: 
"Belle" == animalService.findWithHQL("Belle").name 
}
Integration test 
uses 
in-memory H2 database by default 
which allows testing all of GORM 
• Each test runs in its own transaction, 
which is rolled back at the end of the test
NATIVE SQL 
We have a lot of stored procedures and functions in our 
Oracle database
Groovy SQL 
and Hibernate 
Nastive SQL 
def findWithGroovySql(String animalName) { 
def sql = new Sql(dataSource) 
try { 
// returns rows of resultset 
// e.g. [[ID:1, VERSION:0, AGE:1, NAME:Belle]] 
String query = "select * from animal where name = ? limit 1" 
return sql.rows(query, [animalName]) 
} finally { 
sql.close() 
} 
} 
def findWithHibernateNativeSql(String animalName) { 
def session = sessionFactory.currentSession 
def query = session.createSQLQuery("select * from animal where name = :name limit 1") 
List results = query.with { 
// map columns to keys 
resultTransformer = AliasToEntityMapResultTransformer.INSTANCE 
setString("name", animalName) 
list() 
} 
// results are [[AGE:1, VERSION:0, ID:1, NAME:Belle]] 
results 
}
Just a Groovy call 
A breeze with Groovy SQL - no need to register all kinds of parameters. Perform a direct call with all 
the parameters. The closure is called once. Some more examples here. 
FUNCTION par_id ( 
i_participant_code_type IN participant.participant_code_type%TYPE 
, i_participant_code IN participant.participant_code%TYPE 
) RETURN participant.par_id%TYPE 
IS 
return_waarde participant.par_id%TYPE := NULL; 
Long getParticipantId(String participantCodeType, String participantCode) { 
Sql sql = new groovy.sql.Sql(dataSource) 
Long participantId 
sql.call("{? = call rxpa_general.par_id(?, ?)}", [Sql.BIGINT, participantCodeType, participantCode]) { 
result -> participantId = result 
} 
return participantId 
}
Sometimes trouble 
Has a lot of input parameters, some optional. This might sometimes cause some ORA issues when using the 
direct call method…. 
PROCEDURE set_dry_off_date ( 
o_message_code OUT VARCHAR2 
, i_ani_id IN lactation_period.ani_id_cow%TYPE 
, i_lactation_end_date IN lactation_period.lactation_end_date%TYPE 
, i_jou_id IN oxop_general.t_jou_id%TYPE 
DEFAULT NULL 
, i_par_id_last_change IN lactation_period.par_id_last_change%TYPE 
, i_prc_code_last_change IN lactation_period.prc_code_last_change%TYPE 
) 
IS 
...
Old-fashioned CallableStatement 
String messageCode 
try { 
Connection c = sql.createConnection() 
CallableStatement cs = c.prepareCall("{call axmi_general_mut.set_dry_off_date(?,?,?,?,?,?)}"); 
cs.setLong('i_ani_id', aniId) 
cs.setDate('i_lactation_end_date', new java.sql.Date(lactationEndDate.time)) 
if (journalId) { 
cs.setLong('i_jou_id', journalId) 
} else { 
cs.setNull('i_jou_id', Types.NUMERIC) 
} 
cs.setLong('i_par_id_last_change', parIdLastChange) 
cs.setString('i_prc_code_last_change', prcCodeLastChange) 
cs.registerOutParameter("o_message_code", Types.VARCHAR) 
cs.execute() 
messageCode = cs.getString("o_message_code") 
} catch (java.sql.SQLException e) { 
throw new RuntimeException("axmi_general_mut.set_dry_off_date failed", e) 
} finally { 
sql.close() 
}
How to test? 
But wait! What to test?
From a Grails perspective 
• I’m not interested in the workings of the existing db 
procedures/functions – those should have their own tests 
• I want to test that my service is 
1. calling them properly 
2. return any results properly 
• Unfortunately, there’s currently no easy way to do that 
• Options for CI: 
• Docker container with Oracle XE 
• ?
So, what if you can dream the SQL, 
and want to GORM it?
STEP-BY-STEP STRATEGY 
How to GORMinize Your SQL
Step-by-step 
• Let’s say, 
• you have an existing database with animals 
• in Grails you needs to show their accumulated age 
• you already know the SQL which does the trick: 
select sum(age) from animal
On a high-level 
1. have a method execute your SQL directly by Groovy 
SQL or Hibernate Native SQL 
• have a Grails test verify the logic in that original form 
• in order to do that, create domain class(es) to init test data 
2. refactor the Groovy SQL into GORM 
• your test informs you behaviour is still correct
1. Create an integration test 
• create-integration-test AnimalServiceIntegration 
• create a skeleton test and discover 
class AnimalServiceIntegrationSpec extends Specification { 
def animalService 
void "test calculating the total age"() { 
given: 
new Animal(name: "Belle", age: 1).save() 
new Animal(name: "Cinnamon", age: 5).save() 
expect: 
6 == animalService.calculateTotalAgeWithGroovySql() 
6 == animalService.calculateTotalAge() 
} 
}
1. Create an integration test 
• create-integration-test AnimalServiceIntegration 
• create a skeleton test and discover what you need... 
class AnimalServiceIntegrationSpec extends Specification { 
def animalService 
void "test calculating the total age"() { 
given: 
// some animals with each an age 
expect: 
// call calculation method, verify total age 
} 
} 
Some Animal 
domain classes for 
test data 
The actual business 
method 
2. Create domain class(es) 
• Either create a domain class from scratch, or 
• use the Grails Database Reverse Engineering Plugin 
• use them to initialize your test with 
class AnimalServiceIntegrationSpec extends Specification { 
def animalService 
void "test calculating the total age"() { 
given: 
new Animal(name: "Belle", age: 1).save() 
new Animal(name: "Cinnamon", age: 5).save() 
expect: 
// call calculation method, verify total age 
} 
} 
class Animal { 
String name 
int age 
static mapping = { 
id column: "ani_id" 
version false 
} 
}
2. Implement with Groovy SQL 
• Implement your business method taking the original query. See the various Groovy SQL examples 
Long calculateTotalAgeWithGroovySql() { 
new Sql(dataSource).firstRow("select sum(age) as total from animal").total 
} 
• Invoke it from the test. Verify with enough testcases that it does what it’s supposed to do. 
void "test calculating the total age"() { 
given: 
new Animal(name: "Belle", age: 1).save() 
new Animal(name: "Cinnamon", age: 5).save() 
expect: 
6 == animalService.calculateTotalAgeWithGroovySql() 
} 
}
3. Refactor into GORM 
• Now that you have (enough) test coverage, you can safely refactor into a version which doesn’t use 
Groovy SQL, but GORM or Hibernate features instead 
Long calculateTotalAgeWithGroovySql() { 
new Sql(dataSource).firstRow("select sum(age) as total from animal").total 
} 
• can become e.g. 
Long calculateTotalAge() { 
Animal.executeQuery("select sum(age) as total from animal").total 
} 
• and verify with your tests everything still works as expected
A few tips 
• Use the Grails Build Test Data plugin to refactor your tests to only include the relevant test data, and 
still have valid domain classes 
class AnimalServiceIntegrationSpec extends 
Specification { 
def animalService 
void "test calculating the total age"() { 
given: 
new Animal(name: "Belle", age: 1).save() 
new Animal(name: "Cinnamon", age: 5).save() 
expect: 
6 == animalService.calculateTotalAge() 
} 
} 
@Build(Animal) 
class AnimalServiceIntegrationSpec extends 
Specification { 
def animalService 
void "test calculating the total age"() { 
given: 
Animal.build(age: 1).save() 
Animal.build(age: 5).save() 
expect: 
6 == animalService.calculateTotalAge() 
} 
}
SUMMARY
Where 
Queries 
Less verbose than criteria 
More flexible than Dynamic Finders 
Dynamic 
Finders 
Simple queries with few 
properties 
Criteria 
Hibernate projections & 
restrictions 
Hibernate 
HQL 
Fully object-oriented SQL 
Hibernate 
Native SQL 
Native SQL through Hibernate 
Groovy 
SQL 
Native SQL through Groovy 
My opinionated ranking of 
query options, when 
considering readability, 
writability and testability for 
90% of my use cases.
Summary 
Try simplest possible query option first: easy to write, read & test 
GORM 
• allows you to think in domains rather than SQL 
• is easy to test with unit and integration tests 
• gives you more we haven’t covered yet: caching, named queries, GORM 
events, etc. 
Know the pros and cons of your SQL approach and choose 
accordingly. 
GORM has its strong suits, but native SQL too, e.g. performance tuning or db-specific 
SQL
More info 
• GORM 
• Querying with GORM 
• Dynamic Finders 
• Where Queries 
• Criteria 
• HQL 
• Groovy SQL 
• groovy.sql.Sql 
• database features 
• Hibernate Query Language (HQL) 
• Further reading: 
• How and When to Use Various GORM Querying Options 
• Recipes for using GORM with Grails

More Related Content

What's hot

Groovy presentation
Groovy presentationGroovy presentation
Groovy presentationManav Prasad
 
3.2 javascript regex
3.2 javascript regex3.2 javascript regex
3.2 javascript regexJalpesh Vasa
 
jpa-hibernate-presentation
jpa-hibernate-presentationjpa-hibernate-presentation
jpa-hibernate-presentationJohn Slick
 
Advanced GORM - Performance, Customization and Monitoring
Advanced GORM - Performance, Customization and MonitoringAdvanced GORM - Performance, Customization and Monitoring
Advanced GORM - Performance, Customization and MonitoringBurt Beckwith
 
Spring Framework - AOP
Spring Framework - AOPSpring Framework - AOP
Spring Framework - AOPDzmitry Naskou
 
Advanced task management with Celery
Advanced task management with CeleryAdvanced task management with Celery
Advanced task management with CeleryMahendra M
 
L11 array list
L11 array listL11 array list
L11 array listteach4uin
 
MongoDB Aggregation Framework
MongoDB Aggregation FrameworkMongoDB Aggregation Framework
MongoDB Aggregation FrameworkCaserta
 
MongoDB .local Toronto 2019: Tips and Tricks for Effective Indexing
MongoDB .local Toronto 2019: Tips and Tricks for Effective IndexingMongoDB .local Toronto 2019: Tips and Tricks for Effective Indexing
MongoDB .local Toronto 2019: Tips and Tricks for Effective IndexingMongoDB
 
An Introduction to Celery
An Introduction to CeleryAn Introduction to Celery
An Introduction to CeleryIdan Gazit
 
REST APIs with Spring
REST APIs with SpringREST APIs with Spring
REST APIs with SpringJoshua Long
 
Java Classes | Java Tutorial for Beginners | Java Classes and Objects | Java ...
Java Classes | Java Tutorial for Beginners | Java Classes and Objects | Java ...Java Classes | Java Tutorial for Beginners | Java Classes and Objects | Java ...
Java Classes | Java Tutorial for Beginners | Java Classes and Objects | Java ...Edureka!
 
Java Collections | Collections Framework in Java | Java Tutorial For Beginner...
Java Collections | Collections Framework in Java | Java Tutorial For Beginner...Java Collections | Collections Framework in Java | Java Tutorial For Beginner...
Java Collections | Collections Framework in Java | Java Tutorial For Beginner...Edureka!
 
Spring I/O 2012: Natural Templating in Spring MVC with Thymeleaf
Spring I/O 2012: Natural Templating in Spring MVC with ThymeleafSpring I/O 2012: Natural Templating in Spring MVC with Thymeleaf
Spring I/O 2012: Natural Templating in Spring MVC with ThymeleafThymeleaf
 

What's hot (20)

Java 8 Lambda and Streams
Java 8 Lambda and StreamsJava 8 Lambda and Streams
Java 8 Lambda and Streams
 
Practical Groovy DSL
Practical Groovy DSLPractical Groovy DSL
Practical Groovy DSL
 
Groovy presentation
Groovy presentationGroovy presentation
Groovy presentation
 
3.2 javascript regex
3.2 javascript regex3.2 javascript regex
3.2 javascript regex
 
jpa-hibernate-presentation
jpa-hibernate-presentationjpa-hibernate-presentation
jpa-hibernate-presentation
 
Advanced GORM - Performance, Customization and Monitoring
Advanced GORM - Performance, Customization and MonitoringAdvanced GORM - Performance, Customization and Monitoring
Advanced GORM - Performance, Customization and Monitoring
 
Spring Framework - AOP
Spring Framework - AOPSpring Framework - AOP
Spring Framework - AOP
 
Advanced task management with Celery
Advanced task management with CeleryAdvanced task management with Celery
Advanced task management with Celery
 
L11 array list
L11 array listL11 array list
L11 array list
 
Practical Celery
Practical CeleryPractical Celery
Practical Celery
 
Java8 features
Java8 featuresJava8 features
Java8 features
 
PHP and Mysql
PHP and MysqlPHP and Mysql
PHP and Mysql
 
MongoDB Aggregation Framework
MongoDB Aggregation FrameworkMongoDB Aggregation Framework
MongoDB Aggregation Framework
 
MongoDB .local Toronto 2019: Tips and Tricks for Effective Indexing
MongoDB .local Toronto 2019: Tips and Tricks for Effective IndexingMongoDB .local Toronto 2019: Tips and Tricks for Effective Indexing
MongoDB .local Toronto 2019: Tips and Tricks for Effective Indexing
 
An Introduction to Celery
An Introduction to CeleryAn Introduction to Celery
An Introduction to Celery
 
REST APIs with Spring
REST APIs with SpringREST APIs with Spring
REST APIs with Spring
 
Java Classes | Java Tutorial for Beginners | Java Classes and Objects | Java ...
Java Classes | Java Tutorial for Beginners | Java Classes and Objects | Java ...Java Classes | Java Tutorial for Beginners | Java Classes and Objects | Java ...
Java Classes | Java Tutorial for Beginners | Java Classes and Objects | Java ...
 
Java Collections | Collections Framework in Java | Java Tutorial For Beginner...
Java Collections | Collections Framework in Java | Java Tutorial For Beginner...Java Collections | Collections Framework in Java | Java Tutorial For Beginner...
Java Collections | Collections Framework in Java | Java Tutorial For Beginner...
 
Introduction to java 8 stream api
Introduction to java 8 stream apiIntroduction to java 8 stream api
Introduction to java 8 stream api
 
Spring I/O 2012: Natural Templating in Spring MVC with Thymeleaf
Spring I/O 2012: Natural Templating in Spring MVC with ThymeleafSpring I/O 2012: Natural Templating in Spring MVC with Thymeleaf
Spring I/O 2012: Natural Templating in Spring MVC with Thymeleaf
 

Similar to Grails GORM - You Know SQL. You Know Queries. Here's GORM.

FITC CoffeeScript 101
FITC CoffeeScript 101FITC CoffeeScript 101
FITC CoffeeScript 101Faisal Abid
 
JUnit Kung Fu: Getting More Out of Your Unit Tests
JUnit Kung Fu: Getting More Out of Your Unit TestsJUnit Kung Fu: Getting More Out of Your Unit Tests
JUnit Kung Fu: Getting More Out of Your Unit TestsJohn Ferguson Smart Limited
 
CodeCamp Iasi 10 march 2012 - Practical Groovy
CodeCamp Iasi 10 march 2012 - Practical GroovyCodeCamp Iasi 10 march 2012 - Practical Groovy
CodeCamp Iasi 10 march 2012 - Practical GroovyCodecamp Romania
 
اسلاید جلسه ۹ کلاس پایتون برای هکر های قانونی
اسلاید جلسه ۹ کلاس پایتون برای هکر های قانونیاسلاید جلسه ۹ کلاس پایتون برای هکر های قانونی
اسلاید جلسه ۹ کلاس پایتون برای هکر های قانونیMohammad Reza Kamalifard
 
Please write a java code for this Codes are posted below for.pdf
Please write a java code for this Codes are posted below for.pdfPlease write a java code for this Codes are posted below for.pdf
Please write a java code for this Codes are posted below for.pdfshreedattaagenciees2
 
Querydsl fin jug - june 2012
Querydsl   fin jug - june 2012Querydsl   fin jug - june 2012
Querydsl fin jug - june 2012Timo Westkämper
 
JAVASCRIPT OBJECTS.pdf
JAVASCRIPT OBJECTS.pdfJAVASCRIPT OBJECTS.pdf
JAVASCRIPT OBJECTS.pdfcherop41618145
 
Google Guava for cleaner code
Google Guava for cleaner codeGoogle Guava for cleaner code
Google Guava for cleaner codeMite Mitreski
 
Appengine Java Night #2a
Appengine Java Night #2aAppengine Java Night #2a
Appengine Java Night #2aShinichi Ogawa
 
Most common mistakes of workshops applicants
Most common mistakes of workshops applicantsMost common mistakes of workshops applicants
Most common mistakes of workshops applicantsDominik Wojciechowski
 
Pi j3.4 data-structures
Pi j3.4 data-structuresPi j3.4 data-structures
Pi j3.4 data-structuresmcollison
 
14 Defining classes
14 Defining classes14 Defining classes
14 Defining classesmaznabili
 
Hibernate Query Language
Hibernate Query LanguageHibernate Query Language
Hibernate Query LanguageInnovationM
 

Similar to Grails GORM - You Know SQL. You Know Queries. Here's GORM. (20)

FITC CoffeeScript 101
FITC CoffeeScript 101FITC CoffeeScript 101
FITC CoffeeScript 101
 
Functional programming in java
Functional programming in javaFunctional programming in java
Functional programming in java
 
JUnit Kung Fu: Getting More Out of Your Unit Tests
JUnit Kung Fu: Getting More Out of Your Unit TestsJUnit Kung Fu: Getting More Out of Your Unit Tests
JUnit Kung Fu: Getting More Out of Your Unit Tests
 
CAP615-Unit1.pptx
CAP615-Unit1.pptxCAP615-Unit1.pptx
CAP615-Unit1.pptx
 
CodeCamp Iasi 10 march 2012 - Practical Groovy
CodeCamp Iasi 10 march 2012 - Practical GroovyCodeCamp Iasi 10 march 2012 - Practical Groovy
CodeCamp Iasi 10 march 2012 - Practical Groovy
 
اسلاید جلسه ۹ کلاس پایتون برای هکر های قانونی
اسلاید جلسه ۹ کلاس پایتون برای هکر های قانونیاسلاید جلسه ۹ کلاس پایتون برای هکر های قانونی
اسلاید جلسه ۹ کلاس پایتون برای هکر های قانونی
 
Please write a java code for this Codes are posted below for.pdf
Please write a java code for this Codes are posted below for.pdfPlease write a java code for this Codes are posted below for.pdf
Please write a java code for this Codes are posted below for.pdf
 
Querydsl fin jug - june 2012
Querydsl   fin jug - june 2012Querydsl   fin jug - june 2012
Querydsl fin jug - june 2012
 
classes and objects.pdf
classes and objects.pdfclasses and objects.pdf
classes and objects.pdf
 
JAVASCRIPT OBJECTS.pdf
JAVASCRIPT OBJECTS.pdfJAVASCRIPT OBJECTS.pdf
JAVASCRIPT OBJECTS.pdf
 
Google Guava for cleaner code
Google Guava for cleaner codeGoogle Guava for cleaner code
Google Guava for cleaner code
 
Appengine Java Night #2a
Appengine Java Night #2aAppengine Java Night #2a
Appengine Java Night #2a
 
Most common mistakes of workshops applicants
Most common mistakes of workshops applicantsMost common mistakes of workshops applicants
Most common mistakes of workshops applicants
 
Python oop third class
Python oop   third classPython oop   third class
Python oop third class
 
Core concepts-javascript
Core concepts-javascriptCore concepts-javascript
Core concepts-javascript
 
CSC PPT 13.pptx
CSC PPT 13.pptxCSC PPT 13.pptx
CSC PPT 13.pptx
 
Pi j3.4 data-structures
Pi j3.4 data-structuresPi j3.4 data-structures
Pi j3.4 data-structures
 
Plc (1)
Plc (1)Plc (1)
Plc (1)
 
14 Defining classes
14 Defining classes14 Defining classes
14 Defining classes
 
Hibernate Query Language
Hibernate Query LanguageHibernate Query Language
Hibernate Query Language
 

More from Ted Vinke

Spock the enterprise ready specifiation framework - Ted Vinke
Spock the enterprise ready specifiation framework - Ted VinkeSpock the enterprise ready specifiation framework - Ted Vinke
Spock the enterprise ready specifiation framework - Ted VinkeTed Vinke
 
Upcoming Events 2017 for a Java Software Developer - Ted's Tool Time
Upcoming Events 2017 for a Java Software Developer - Ted's Tool TimeUpcoming Events 2017 for a Java Software Developer - Ted's Tool Time
Upcoming Events 2017 for a Java Software Developer - Ted's Tool TimeTed Vinke
 
Gmail Email Markup - Ted's Tool Time
Gmail Email Markup - Ted's Tool TimeGmail Email Markup - Ted's Tool Time
Gmail Email Markup - Ted's Tool TimeTed Vinke
 
Specifications pattern - Ted's Tool Time
Specifications pattern - Ted's Tool TimeSpecifications pattern - Ted's Tool Time
Specifications pattern - Ted's Tool TimeTed Vinke
 
Code Generation with Groovy, Lombok, AutoValue and Immutables - Ted's Tool Time
Code Generation with Groovy, Lombok, AutoValue and Immutables - Ted's Tool TimeCode Generation with Groovy, Lombok, AutoValue and Immutables - Ted's Tool Time
Code Generation with Groovy, Lombok, AutoValue and Immutables - Ted's Tool TimeTed Vinke
 
JUnit 5 - The Next Generation of JUnit - Ted's Tool Time
JUnit 5 - The Next Generation of JUnit - Ted's Tool TimeJUnit 5 - The Next Generation of JUnit - Ted's Tool Time
JUnit 5 - The Next Generation of JUnit - Ted's Tool TimeTed Vinke
 
JEP 286 Local-Variable Type Inference - Ted's Tool Time
JEP 286 Local-Variable Type Inference - Ted's Tool TimeJEP 286 Local-Variable Type Inference - Ted's Tool Time
JEP 286 Local-Variable Type Inference - Ted's Tool TimeTed Vinke
 
Devoxx 2015 - Web Application Development using Grails and Docker
Devoxx 2015 - Web Application Development using Grails and DockerDevoxx 2015 - Web Application Development using Grails and Docker
Devoxx 2015 - Web Application Development using Grails and DockerTed Vinke
 
Working with Groovy Collections
Working with Groovy CollectionsWorking with Groovy Collections
Working with Groovy CollectionsTed Vinke
 
The Apache Software Foundation - Ted's Tool Time - Sep 2015
The Apache Software Foundation - Ted's Tool Time - Sep 2015The Apache Software Foundation - Ted's Tool Time - Sep 2015
The Apache Software Foundation - Ted's Tool Time - Sep 2015Ted Vinke
 
Devoxx 2014 Report
Devoxx 2014 ReportDevoxx 2014 Report
Devoxx 2014 ReportTed Vinke
 

More from Ted Vinke (11)

Spock the enterprise ready specifiation framework - Ted Vinke
Spock the enterprise ready specifiation framework - Ted VinkeSpock the enterprise ready specifiation framework - Ted Vinke
Spock the enterprise ready specifiation framework - Ted Vinke
 
Upcoming Events 2017 for a Java Software Developer - Ted's Tool Time
Upcoming Events 2017 for a Java Software Developer - Ted's Tool TimeUpcoming Events 2017 for a Java Software Developer - Ted's Tool Time
Upcoming Events 2017 for a Java Software Developer - Ted's Tool Time
 
Gmail Email Markup - Ted's Tool Time
Gmail Email Markup - Ted's Tool TimeGmail Email Markup - Ted's Tool Time
Gmail Email Markup - Ted's Tool Time
 
Specifications pattern - Ted's Tool Time
Specifications pattern - Ted's Tool TimeSpecifications pattern - Ted's Tool Time
Specifications pattern - Ted's Tool Time
 
Code Generation with Groovy, Lombok, AutoValue and Immutables - Ted's Tool Time
Code Generation with Groovy, Lombok, AutoValue and Immutables - Ted's Tool TimeCode Generation with Groovy, Lombok, AutoValue and Immutables - Ted's Tool Time
Code Generation with Groovy, Lombok, AutoValue and Immutables - Ted's Tool Time
 
JUnit 5 - The Next Generation of JUnit - Ted's Tool Time
JUnit 5 - The Next Generation of JUnit - Ted's Tool TimeJUnit 5 - The Next Generation of JUnit - Ted's Tool Time
JUnit 5 - The Next Generation of JUnit - Ted's Tool Time
 
JEP 286 Local-Variable Type Inference - Ted's Tool Time
JEP 286 Local-Variable Type Inference - Ted's Tool TimeJEP 286 Local-Variable Type Inference - Ted's Tool Time
JEP 286 Local-Variable Type Inference - Ted's Tool Time
 
Devoxx 2015 - Web Application Development using Grails and Docker
Devoxx 2015 - Web Application Development using Grails and DockerDevoxx 2015 - Web Application Development using Grails and Docker
Devoxx 2015 - Web Application Development using Grails and Docker
 
Working with Groovy Collections
Working with Groovy CollectionsWorking with Groovy Collections
Working with Groovy Collections
 
The Apache Software Foundation - Ted's Tool Time - Sep 2015
The Apache Software Foundation - Ted's Tool Time - Sep 2015The Apache Software Foundation - Ted's Tool Time - Sep 2015
The Apache Software Foundation - Ted's Tool Time - Sep 2015
 
Devoxx 2014 Report
Devoxx 2014 ReportDevoxx 2014 Report
Devoxx 2014 Report
 

Recently uploaded

Bird eye's view on Camunda open source ecosystem
Bird eye's view on Camunda open source ecosystemBird eye's view on Camunda open source ecosystem
Bird eye's view on Camunda open source ecosystemAsko Soukka
 
Basic Building Blocks of Internet of Things.
Basic Building Blocks of Internet of Things.Basic Building Blocks of Internet of Things.
Basic Building Blocks of Internet of Things.YounusS2
 
Computer 10: Lesson 10 - Online Crimes and Hazards
Computer 10: Lesson 10 - Online Crimes and HazardsComputer 10: Lesson 10 - Online Crimes and Hazards
Computer 10: Lesson 10 - Online Crimes and HazardsSeth Reyes
 
IaC & GitOps in a Nutshell - a FridayInANuthshell Episode.pdf
IaC & GitOps in a Nutshell - a FridayInANuthshell Episode.pdfIaC & GitOps in a Nutshell - a FridayInANuthshell Episode.pdf
IaC & GitOps in a Nutshell - a FridayInANuthshell Episode.pdfDaniel Santiago Silva Capera
 
AI Fame Rush Review – Virtual Influencer Creation In Just Minutes
AI Fame Rush Review – Virtual Influencer Creation In Just MinutesAI Fame Rush Review – Virtual Influencer Creation In Just Minutes
AI Fame Rush Review – Virtual Influencer Creation In Just MinutesMd Hossain Ali
 
Comparing Sidecar-less Service Mesh from Cilium and Istio
Comparing Sidecar-less Service Mesh from Cilium and IstioComparing Sidecar-less Service Mesh from Cilium and Istio
Comparing Sidecar-less Service Mesh from Cilium and IstioChristian Posta
 
NIST Cybersecurity Framework (CSF) 2.0 Workshop
NIST Cybersecurity Framework (CSF) 2.0 WorkshopNIST Cybersecurity Framework (CSF) 2.0 Workshop
NIST Cybersecurity Framework (CSF) 2.0 WorkshopBachir Benyammi
 
Empowering Africa's Next Generation: The AI Leadership Blueprint
Empowering Africa's Next Generation: The AI Leadership BlueprintEmpowering Africa's Next Generation: The AI Leadership Blueprint
Empowering Africa's Next Generation: The AI Leadership BlueprintMahmoud Rabie
 
Machine Learning Model Validation (Aijun Zhang 2024).pdf
Machine Learning Model Validation (Aijun Zhang 2024).pdfMachine Learning Model Validation (Aijun Zhang 2024).pdf
Machine Learning Model Validation (Aijun Zhang 2024).pdfAijun Zhang
 
activity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdf
activity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdf
activity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdfJamie (Taka) Wang
 
OpenShift Commons Paris - Choose Your Own Observability Adventure
OpenShift Commons Paris - Choose Your Own Observability AdventureOpenShift Commons Paris - Choose Your Own Observability Adventure
OpenShift Commons Paris - Choose Your Own Observability AdventureEric D. Schabell
 
Artificial Intelligence & SEO Trends for 2024
Artificial Intelligence & SEO Trends for 2024Artificial Intelligence & SEO Trends for 2024
Artificial Intelligence & SEO Trends for 2024D Cloud Solutions
 
20230202 - Introduction to tis-py
20230202 - Introduction to tis-py20230202 - Introduction to tis-py
20230202 - Introduction to tis-pyJamie (Taka) Wang
 
UiPath Studio Web workshop series - Day 7
UiPath Studio Web workshop series - Day 7UiPath Studio Web workshop series - Day 7
UiPath Studio Web workshop series - Day 7DianaGray10
 
Videogame localization & technology_ how to enhance the power of translation.pdf
Videogame localization & technology_ how to enhance the power of translation.pdfVideogame localization & technology_ how to enhance the power of translation.pdf
Videogame localization & technology_ how to enhance the power of translation.pdfinfogdgmi
 
9 Steps For Building Winning Founding Team
9 Steps For Building Winning Founding Team9 Steps For Building Winning Founding Team
9 Steps For Building Winning Founding TeamAdam Moalla
 
COMPUTER 10: Lesson 7 - File Storage and Online Collaboration
COMPUTER 10: Lesson 7 - File Storage and Online CollaborationCOMPUTER 10: Lesson 7 - File Storage and Online Collaboration
COMPUTER 10: Lesson 7 - File Storage and Online Collaborationbruanjhuli
 

Recently uploaded (20)

Bird eye's view on Camunda open source ecosystem
Bird eye's view on Camunda open source ecosystemBird eye's view on Camunda open source ecosystem
Bird eye's view on Camunda open source ecosystem
 
201610817 - edge part1
201610817 - edge part1201610817 - edge part1
201610817 - edge part1
 
Basic Building Blocks of Internet of Things.
Basic Building Blocks of Internet of Things.Basic Building Blocks of Internet of Things.
Basic Building Blocks of Internet of Things.
 
20230104 - machine vision
20230104 - machine vision20230104 - machine vision
20230104 - machine vision
 
Computer 10: Lesson 10 - Online Crimes and Hazards
Computer 10: Lesson 10 - Online Crimes and HazardsComputer 10: Lesson 10 - Online Crimes and Hazards
Computer 10: Lesson 10 - Online Crimes and Hazards
 
IaC & GitOps in a Nutshell - a FridayInANuthshell Episode.pdf
IaC & GitOps in a Nutshell - a FridayInANuthshell Episode.pdfIaC & GitOps in a Nutshell - a FridayInANuthshell Episode.pdf
IaC & GitOps in a Nutshell - a FridayInANuthshell Episode.pdf
 
AI Fame Rush Review – Virtual Influencer Creation In Just Minutes
AI Fame Rush Review – Virtual Influencer Creation In Just MinutesAI Fame Rush Review – Virtual Influencer Creation In Just Minutes
AI Fame Rush Review – Virtual Influencer Creation In Just Minutes
 
Comparing Sidecar-less Service Mesh from Cilium and Istio
Comparing Sidecar-less Service Mesh from Cilium and IstioComparing Sidecar-less Service Mesh from Cilium and Istio
Comparing Sidecar-less Service Mesh from Cilium and Istio
 
20150722 - AGV
20150722 - AGV20150722 - AGV
20150722 - AGV
 
NIST Cybersecurity Framework (CSF) 2.0 Workshop
NIST Cybersecurity Framework (CSF) 2.0 WorkshopNIST Cybersecurity Framework (CSF) 2.0 Workshop
NIST Cybersecurity Framework (CSF) 2.0 Workshop
 
Empowering Africa's Next Generation: The AI Leadership Blueprint
Empowering Africa's Next Generation: The AI Leadership BlueprintEmpowering Africa's Next Generation: The AI Leadership Blueprint
Empowering Africa's Next Generation: The AI Leadership Blueprint
 
Machine Learning Model Validation (Aijun Zhang 2024).pdf
Machine Learning Model Validation (Aijun Zhang 2024).pdfMachine Learning Model Validation (Aijun Zhang 2024).pdf
Machine Learning Model Validation (Aijun Zhang 2024).pdf
 
activity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdf
activity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdf
activity_diagram_combine_v4_20190827.pdfactivity_diagram_combine_v4_20190827.pdf
 
OpenShift Commons Paris - Choose Your Own Observability Adventure
OpenShift Commons Paris - Choose Your Own Observability AdventureOpenShift Commons Paris - Choose Your Own Observability Adventure
OpenShift Commons Paris - Choose Your Own Observability Adventure
 
Artificial Intelligence & SEO Trends for 2024
Artificial Intelligence & SEO Trends for 2024Artificial Intelligence & SEO Trends for 2024
Artificial Intelligence & SEO Trends for 2024
 
20230202 - Introduction to tis-py
20230202 - Introduction to tis-py20230202 - Introduction to tis-py
20230202 - Introduction to tis-py
 
UiPath Studio Web workshop series - Day 7
UiPath Studio Web workshop series - Day 7UiPath Studio Web workshop series - Day 7
UiPath Studio Web workshop series - Day 7
 
Videogame localization & technology_ how to enhance the power of translation.pdf
Videogame localization & technology_ how to enhance the power of translation.pdfVideogame localization & technology_ how to enhance the power of translation.pdf
Videogame localization & technology_ how to enhance the power of translation.pdf
 
9 Steps For Building Winning Founding Team
9 Steps For Building Winning Founding Team9 Steps For Building Winning Founding Team
9 Steps For Building Winning Founding Team
 
COMPUTER 10: Lesson 7 - File Storage and Online Collaboration
COMPUTER 10: Lesson 7 - File Storage and Online CollaborationCOMPUTER 10: Lesson 7 - File Storage and Online Collaboration
COMPUTER 10: Lesson 7 - File Storage and Online Collaboration
 

Grails GORM - You Know SQL. You Know Queries. Here's GORM.

  • 1. GRAILS GORM Practical basics of how GORM can query for you when SQL is all you know. Dec 2014 - Ted Vinke
  • 2. Overview • Introduction • Querying • Basic GORM • Dynamic Finders • Where Queries • Criteria • HQL • Native SQL • How to GORM your existing SQL? • Summary
  • 3. We already know how to do SQL in Grails, right?
  • 4. Just use Groovy! (jeej) class AnimalService { def dataSource def findWithGroovySql(String animalName) { def sql = new Sql(dataSource) try { return sql.rows("select * from animal where name = ?", [animalName]) } finally { sql.close() } } }
  • 5. Is SQL your Grails application’s core business?
  • 6. In a nutshell GORM stands for Grails Object Relational Mapping Grails 2.4.3 uses Hibernate 4 under the hood Evolves around domain classes
  • 7. Domain classes? Database likes SQL We – the developers – like select * from animals where name = “Belle” to talk in domain terms Animal.findByName(“Belle”) select * from animals where id = 5 Animal.get(5)
  • 8. QUERYING What Grails can do for you
  • 9. Settings dataSource { driverClassName = "org.h2.Driver" url = "jdbc:h2:mem:devDb;..." ... } hibernate { ... format_sql = true use_sql_comments = true }
  • 10. We have a domain class // grails-app/domain/Animal.groovy class Animal { String name int age static mapping = { id column: "ani_id" version false } }
  • 11. And some animals class BootStrap { def init = { servletContext -> environments { development { new Animal(name: "Belle", age: 1).save() new Animal(name: "Cinnamon", age: 5).save() } } } }
  • 12. Basic GORM def animal = Animal.get(1) select animal0_.id as id1_0_0_, animal0_.version as version2_0_0_, animal0_.age as age3_0_0_, animal0_.name as name4_0_0_ from animal animal0_ where animal0_.id=? Hibernate uses unique table and column aliases, e.g. alias(column name)_(column unique integer)_(table unique integer)_(some suffix) int total = Animal.count() select count(*) as y0_ from animal this_ new Animal(name: "Belle", age: 1).save() insert into animal (id, version, age, name) values (null, ?, ?, ?) def animal = Animal.first(sort: "age") select ... from animal this_ order by this_.age asc limit ? • addTo • count • countBy • delete • exists • first • get • getAll • indent • last • list • listOrderBy • …
  • 13. AnimalController // grails-app/controllers/AnimalController.groovy class AnimalController { def animalService def show(String name) { def animal = animalService.find...(name) log.info "Found " + animal ... } } http://localhost:8080/query/animal/show?name=Belle
  • 15. Dynamic finders Animal findWithDynamicFinder(String animalName) { Animal.findByName(animalName) } • findBy, countBy • findAllBy select this_.id as id1_0_0_, this_.version as version2_0_0_, this_.age as age3_0_0_, this_.name as name4_0_0_ from animal this_ where this_.name=? limit ?
  • 16. Dynamic finders Animal findWithDynamicFinder(String animalName) { Animal.findByNameAndAgeLessThan(animalName, 3) } • findBy, countBy • findAllBy  Combines properties with all kinds of operators • LessThan • LessThanEquals • Between • Like • Not • Or select this_.id as id1_0_0_, this_.version as version2_0_0_, this_.age as age3_0_0_, this_.name as name4_0_0_ from animal this_ where this_.name=? and this_.age<? limit ?
  • 17. Dynamic finders Animal findWithDynamicFinder(String animalName) { Animal.findByName(animalName, [sort: "age"]) } • findBy, countBy • findAllBy  Combines properties with all kinds of operators • LessThan • LessThanEquals • Between • Like • Not • Or  Pagination (sort, max, etc) and meta params (readOnly, timeout, etc.) select this_.id as id1_0_0_, this_.version as version2_0_0_, this_.age as age3_0_0_, this_.name as name4_0_0_ from animal this_ where this_.name=? order by this_.age asc limit ?
  • 18. Where Animal findWithWhereQuery(String animalName) { def query = Animal.where { name == animalName } return query.find() } Defines a new grails.gorm.DetachedCriteria select this_.id as id1_0_0_, this_.version as version2_0_0_, this_.age as age3_0_0_, this_.name as name4_0_0_ from animal this_ where this_.name=?
  • 19. Where Animal findWithWhereQuery(String animalName) { def query = Animal.where { name == animalName && (age < 3) } return query.find([sort: "age"]) } Defines a new grails.gorm.DetachedCriteria  Enhanced, compile-time checked query DSL.  More flexible than dynamic finders select this_.id as id1_0_0_, this_.version as version2_0_0_, this_.age as age3_0_0_, this_.name as name4_0_0_ from animal this_ where (this_.name=? and this_.age<?) order by this_.age asc
  • 20. Where Animal findWithWhereQuery(String animalName) { def query = Animal.where { name == animalName && (age < 3) } return query.find() } The DetachedCriteria defined for where can also be used for find Animal findWithWhereQuery(String animalName) { Animal.find { name == animalName && (age < 3) } } Tip! If your query can return multiple rows, use findAll instead!
  • 21. Criteria Animal findWithCriteria(String animalName) { // Criteria def c = Animal.createCriteria() return c.get { eq "name", animalName } }  Type-safe Groovy way of building criteria queries select this_.id as id1_0_0_, this_.version as version2_0_0_, this_.age as age3_0_0_, this_.name as name4_0_0_ from animal this_ where this_.name=?
  • 22. Criteria Animal findWithCriteria(String animalName) { // Criteria def c = Animal.createCriteria() return c.get { eq "name", animalName lt "age", 3 order "age", "desc" } }  Type-safe Groovy way of building criteria queries • c.list • c.get • c.scroll • c.listDinstinct select this_.id as id1_0_0_, this_.version as version2_0_0_, this_.age as age3_0_0_, this_.name as name4_0_0_ from animal this_ where this_.name=? and this_.age<? order by this_.age desc
  • 23. Criteria and projections Long calculateTotalAge() { // Criteria def c = Animal.createCriteria() return c.get { projections { sum("age") } } }  Projections change the nature of the results select sum(this_.age) as y0_ from animal this_
  • 25. Dynamic finders, Where and Criteria queries can be unit tested! create-unit-test AnimalService test/unit/AnimalServiceSpec.groovy test-app –unit AnimalServiceSpec
  • 26. AnimalServiceSpec • Uses DomainClassUnitTestMixin, simple in-memory ConcurrentHashMap @Mock(Animal) @TestFor(AnimalService) class AnimalServiceSpec extends Specification { void "test finding animals with various queries"() { given: new Animal(name: "Belle", age: 1).save() new Animal(name: "Cinnamon", age: 5).save() expect: "Belle" == service.findWithDynamicFinder("Belle").name "Belle" == service.findWithWhereQuery("Belle").name "Belle" == service.findWithCriteria("Belle").name } } Just @Mock the domain class and insert and verify your test data through the GORM API
  • 27. Unit test with DomainClassUnitTestMixin uses in-memory ConcurrentHashMap which allows mocking of large part of GORM • Simple persistence methods like list(), save() • Dynamic Finders • Named Queries • Query By Example • GORM Events
  • 28. HQL Animal findWithHQL(String animalName) { Animal.find("from Animal as a where a.name = :name", ["name" : animalName]) } Hibernate Query Language Animal findWithHQL(String animalName) { Animal.executeQuery("from Animal a where a.name = :name", ["name" : animalName, "max" : 1]).first() } select animal0_.id as id1_0_, animal0_.version as version2_0_, animal0_.age as age3_0_, animal0_.name as name4_0_ from animal animal0_ where animal0_.name=? limit ?
  • 29. HQL HQL almost looks like SQL… Animal.executeQuery("select distinct a.name from Animal a order by a.name") select distinct animal0_.name as col_0_0_ from animal animal0_ order by animal0_.name
  • 30. class Animal { String name int age static mapping = { name column: "ani_name" age column: "ani_age" } } select animal0_.id as id1_0_, animal0_.version as version2_0_, animal0_.ani_age as ani_age3_0_, animal0_.ani_name as ani_name4_0_ from animal animal0_ where animal0_.ani_name=? limit ? HQL …but uses domain classes and properties instead of tables and columns Animal.executeQuery("select distinct a.name from Animal a order by a.name") class Animal { String name int age static mapping = {} } select animal0_.id as id1_0_, animal0_.version as version2_0_, animal0_.age as age3_0_, animal0_.name as name4_0_ from animal animal0_ where animal0_.name=? limit ?
  • 32. HQL can be unit tested!
  • 33. AnimalServiceHibernateSpec • Uses HibernateTestMixin, Hibernate 4 and in-memory H2 @Domain(Animal) @TestFor(AnimalService) @TestMixin(HibernateTestMixin) class AnimalServiceHibernateSpec extends Specification { def cleanup() { // unit test does not clear db between tests Animal.list()*.delete(flush: true) } void "test finding animal with HQL"() { given: new Animal(name: "Belle").save() new Animal(name: "Cinnamon").save() expect: "Belle" == service.findWithHQL("Belle").name
  • 34. Unit test with HibernateTestMixin uses in-memory H2 database which allows testing all of GORM, including • String-based HQL queries • composite identifiers • dirty checking methods • other direct interaction with Hibernate • Hibernate needs to know about all domain classes, more than you would like to annotate with @Domain, so the Hibernate mixin is not really useful in practice
  • 35. HQL can be integration tested! create-integration-test AnimalService test/integration/AnimalServiceSpec.groovy test-app –integration AnimalServiceSpec
  • 36. AnimalServiceIntegrationSpec  Full Grails container is started in test-environment  Uses H2 in-memory database. Each test runs in transaction, which is rolled back at end of the test // no annotations whatsoever class AnimalServiceIntegrationSpec extends Specification { def animalService void "test finding animal with HQL"() { given: new Animal(name: "Belle").save() new Animal(name: "Cinnamon").save() expect: "Belle" == animalService.findWithHQL("Belle").name }
  • 37. Integration test uses in-memory H2 database by default which allows testing all of GORM • Each test runs in its own transaction, which is rolled back at the end of the test
  • 38. NATIVE SQL We have a lot of stored procedures and functions in our Oracle database
  • 39. Groovy SQL and Hibernate Nastive SQL def findWithGroovySql(String animalName) { def sql = new Sql(dataSource) try { // returns rows of resultset // e.g. [[ID:1, VERSION:0, AGE:1, NAME:Belle]] String query = "select * from animal where name = ? limit 1" return sql.rows(query, [animalName]) } finally { sql.close() } } def findWithHibernateNativeSql(String animalName) { def session = sessionFactory.currentSession def query = session.createSQLQuery("select * from animal where name = :name limit 1") List results = query.with { // map columns to keys resultTransformer = AliasToEntityMapResultTransformer.INSTANCE setString("name", animalName) list() } // results are [[AGE:1, VERSION:0, ID:1, NAME:Belle]] results }
  • 40. Just a Groovy call A breeze with Groovy SQL - no need to register all kinds of parameters. Perform a direct call with all the parameters. The closure is called once. Some more examples here. FUNCTION par_id ( i_participant_code_type IN participant.participant_code_type%TYPE , i_participant_code IN participant.participant_code%TYPE ) RETURN participant.par_id%TYPE IS return_waarde participant.par_id%TYPE := NULL; Long getParticipantId(String participantCodeType, String participantCode) { Sql sql = new groovy.sql.Sql(dataSource) Long participantId sql.call("{? = call rxpa_general.par_id(?, ?)}", [Sql.BIGINT, participantCodeType, participantCode]) { result -> participantId = result } return participantId }
  • 41. Sometimes trouble Has a lot of input parameters, some optional. This might sometimes cause some ORA issues when using the direct call method…. PROCEDURE set_dry_off_date ( o_message_code OUT VARCHAR2 , i_ani_id IN lactation_period.ani_id_cow%TYPE , i_lactation_end_date IN lactation_period.lactation_end_date%TYPE , i_jou_id IN oxop_general.t_jou_id%TYPE DEFAULT NULL , i_par_id_last_change IN lactation_period.par_id_last_change%TYPE , i_prc_code_last_change IN lactation_period.prc_code_last_change%TYPE ) IS ...
  • 42. Old-fashioned CallableStatement String messageCode try { Connection c = sql.createConnection() CallableStatement cs = c.prepareCall("{call axmi_general_mut.set_dry_off_date(?,?,?,?,?,?)}"); cs.setLong('i_ani_id', aniId) cs.setDate('i_lactation_end_date', new java.sql.Date(lactationEndDate.time)) if (journalId) { cs.setLong('i_jou_id', journalId) } else { cs.setNull('i_jou_id', Types.NUMERIC) } cs.setLong('i_par_id_last_change', parIdLastChange) cs.setString('i_prc_code_last_change', prcCodeLastChange) cs.registerOutParameter("o_message_code", Types.VARCHAR) cs.execute() messageCode = cs.getString("o_message_code") } catch (java.sql.SQLException e) { throw new RuntimeException("axmi_general_mut.set_dry_off_date failed", e) } finally { sql.close() }
  • 43. How to test? But wait! What to test?
  • 44. From a Grails perspective • I’m not interested in the workings of the existing db procedures/functions – those should have their own tests • I want to test that my service is 1. calling them properly 2. return any results properly • Unfortunately, there’s currently no easy way to do that • Options for CI: • Docker container with Oracle XE • ?
  • 45. So, what if you can dream the SQL, and want to GORM it?
  • 46. STEP-BY-STEP STRATEGY How to GORMinize Your SQL
  • 47. Step-by-step • Let’s say, • you have an existing database with animals • in Grails you needs to show their accumulated age • you already know the SQL which does the trick: select sum(age) from animal
  • 48. On a high-level 1. have a method execute your SQL directly by Groovy SQL or Hibernate Native SQL • have a Grails test verify the logic in that original form • in order to do that, create domain class(es) to init test data 2. refactor the Groovy SQL into GORM • your test informs you behaviour is still correct
  • 49. 1. Create an integration test • create-integration-test AnimalServiceIntegration • create a skeleton test and discover class AnimalServiceIntegrationSpec extends Specification { def animalService void "test calculating the total age"() { given: new Animal(name: "Belle", age: 1).save() new Animal(name: "Cinnamon", age: 5).save() expect: 6 == animalService.calculateTotalAgeWithGroovySql() 6 == animalService.calculateTotalAge() } }
  • 50. 1. Create an integration test • create-integration-test AnimalServiceIntegration • create a skeleton test and discover what you need... class AnimalServiceIntegrationSpec extends Specification { def animalService void "test calculating the total age"() { given: // some animals with each an age expect: // call calculation method, verify total age } } Some Animal domain classes for test data The actual business method 
  • 51. 2. Create domain class(es) • Either create a domain class from scratch, or • use the Grails Database Reverse Engineering Plugin • use them to initialize your test with class AnimalServiceIntegrationSpec extends Specification { def animalService void "test calculating the total age"() { given: new Animal(name: "Belle", age: 1).save() new Animal(name: "Cinnamon", age: 5).save() expect: // call calculation method, verify total age } } class Animal { String name int age static mapping = { id column: "ani_id" version false } }
  • 52. 2. Implement with Groovy SQL • Implement your business method taking the original query. See the various Groovy SQL examples Long calculateTotalAgeWithGroovySql() { new Sql(dataSource).firstRow("select sum(age) as total from animal").total } • Invoke it from the test. Verify with enough testcases that it does what it’s supposed to do. void "test calculating the total age"() { given: new Animal(name: "Belle", age: 1).save() new Animal(name: "Cinnamon", age: 5).save() expect: 6 == animalService.calculateTotalAgeWithGroovySql() } }
  • 53. 3. Refactor into GORM • Now that you have (enough) test coverage, you can safely refactor into a version which doesn’t use Groovy SQL, but GORM or Hibernate features instead Long calculateTotalAgeWithGroovySql() { new Sql(dataSource).firstRow("select sum(age) as total from animal").total } • can become e.g. Long calculateTotalAge() { Animal.executeQuery("select sum(age) as total from animal").total } • and verify with your tests everything still works as expected
  • 54. A few tips • Use the Grails Build Test Data plugin to refactor your tests to only include the relevant test data, and still have valid domain classes class AnimalServiceIntegrationSpec extends Specification { def animalService void "test calculating the total age"() { given: new Animal(name: "Belle", age: 1).save() new Animal(name: "Cinnamon", age: 5).save() expect: 6 == animalService.calculateTotalAge() } } @Build(Animal) class AnimalServiceIntegrationSpec extends Specification { def animalService void "test calculating the total age"() { given: Animal.build(age: 1).save() Animal.build(age: 5).save() expect: 6 == animalService.calculateTotalAge() } }
  • 56. Where Queries Less verbose than criteria More flexible than Dynamic Finders Dynamic Finders Simple queries with few properties Criteria Hibernate projections & restrictions Hibernate HQL Fully object-oriented SQL Hibernate Native SQL Native SQL through Hibernate Groovy SQL Native SQL through Groovy My opinionated ranking of query options, when considering readability, writability and testability for 90% of my use cases.
  • 57. Summary Try simplest possible query option first: easy to write, read & test GORM • allows you to think in domains rather than SQL • is easy to test with unit and integration tests • gives you more we haven’t covered yet: caching, named queries, GORM events, etc. Know the pros and cons of your SQL approach and choose accordingly. GORM has its strong suits, but native SQL too, e.g. performance tuning or db-specific SQL
  • 58. More info • GORM • Querying with GORM • Dynamic Finders • Where Queries • Criteria • HQL • Groovy SQL • groovy.sql.Sql • database features • Hibernate Query Language (HQL) • Further reading: • How and When to Use Various GORM Querying Options • Recipes for using GORM with Grails