Integrating .NET and Java | Convert System.Guid to java.util.UUID

Recently I’ve got a client request to expose RESTful API (in Grails) that connect’s to Microsoft’s SqlServer Database. Problem occurred when I’ve realized that for same database row column value typed as uniqueidentifier , SqlServer Management Studio gave different string representation of UUID than java code and it’s java.util.UUID class. It appears that UUID is stored with different endian in JVM and .NET CLR. This resulted in empty result set for API consumer (which is built using C# and reading same database), and need to change string representation from MS one to Java one, before passing it to data access layer.

For example, following MS Guid
4E07D932-8D1A-4CE1-9314-7AC7826E8966
would be presented in Java in following format
32D9074E-1A8D-E14C-9314-7AC7826E8966

As you can observer last 8 bytes (in blue) are presented same, while first 8 bytes are reversed in each of the presentation parts. I won’t go too detailed about bytes storage and endians, as intention of the post is to help users solve the problem, rather than lesson in computer architecture.  Below is simple (Groovy) class and it’s converMsUidString method to convert string from MS to Java format.

class DataUtil {

    static def reverseHexBytes = { String bytes ->
        def buff = "",len = bytes.length(),i=len-1;
        while(i >0){
            buff+= (bytes[i-1] + bytes[i]);
            i = i-2;
        }
        return buff;
    }

    static String convertMSUidString(String s){
        def parts = s?.split('-')

        if(s == null || parts == null || parts.size() != 5
                || parts[0].size() != 8 || parts[1].size() !=  4 || parts[2].size() != 4
                || parts[3].size() != 4 || parts[4].size() != 12){
            throw new InvalidArgumentException("${s} does not appear as valid UUID string presentation")
        }

        def bytes0 = reverseHexBytes(parts[0])
        def bytes1 = reverseHexBytes(parts[1])
        def bytes2 = reverseHexBytes(parts[2])
        def bytes3 = parts[3]
        def bytes4 = parts[4]

        def javaId = "${bytes0}-${bytes1}-${bytes2}-${bytes3}-${bytes4}"
        return javaId
    }

}

.. and example of java.util.UUID construction using above class from Grails controller

def sessionIdSMS = request.JSON.sessionId, 
    sessionIdSJava = DataUtil.convertMSUidString(sessionIdSMS), 
    sessionId = java.util.UUID.fromString(sessionIdSJava),
    session = null
...
//request validation
...
    session = sessionDao.getByUUID(sessionId)
...

BASH | Move MySQL database between servers

During development with MySQL moving databases between environments – mostly between some QA / Staging / Production environment to my local workstation in order to troubleshoot issues with real data (or to test new features with real data) is task performed on daily basis. As all developers, I like to automate stuff. As production database can be large, and sometimes messing with real data is not an option, one of the faster way to restore data to local environment is downloading ZIP archive of sql file created with mysqldump. Below is simple Bash script this task. Requirements are SSH access to remote machine, and zip utility installed on both environments. Script can be easily for different flows (e.g. not doing SSH, but rather dumping files directly).

#!/bin/bash

#set variables

DBHOST1=production.db.myapp.com
DBHOST1_USER=myappuser
DBHOST1_PASS=myapppassword

DB_NAME=terefic

DBHOST2=localdevenv
DBHOST2_USER=localuser
DBHOST2_PASS=localpass


#create and copy
echo 'Dumping and compressing remote database';
eval "ssh $DBHOST1 'mysqldump -u$DBHOST1_USER -p$DBHOST1_PASS --lock-tables=false $DB_NAME > $DB_NAME.sql && zip $DB_NAME.sql.zip $DB_NAME.sql'";

echo 'Downloading database archive file';
eval "scp $DBHOST1:~/$DB_NAME.sql.zip /tmp/$DB_NAME.sql.zip";

echo 'Removing remote files';
eval "ssh $DBHOST1 'rm ~/$DB_NAME.sql ; rm ~/$DB_NAME.sql.zip'";

#unzip and restore
echo 'Unpacking archive file';
eval "unzip /tmp/$DB_NAME.sql.zip";

echo 'Restoring database';
eval "cat $DB_NAME.sql | mysql -h$DBHOST2 -u$DBHOST2_USER -p$DBHOST2_PASS $DB_NAME";

echo 'Removing local files';
rm "/tmp/$DB_NAME.sql.zip";
rm "$DB_NAME.sql";

Forcing HTTPS on ASP.NET Application behing SSL offloading Elastic Load Balancer (ELB)

One of the first problems encountered when trying to move ASP.NET MVC2 Application from regular dedicated hosting to cluster of Amazon’s EC2 instances with Elastic load balancer (ELB) distributing traffic between them was endless loop of 303 redirects. Seems like ASP.NET application being moved to new infrastructure had following section in rewrite rules of web.config file forcing every non-HTTPS requests to be redirected to HTTPS-one:

<rules>
        <rule name="Redirect to HTTPS" stopProcessing="true">
          <match url="(.*)" />
          <conditions>
            <add input="{HTTPS}" pattern="^OFF$" />
          </conditions>
          <action type="Redirect" url="https://{HTTP_HOST}/{R:1}" redirectType="SeeOther" />
        </rule>
 </rules>

 

Problem here is that Elastic Load Balancer is configured to offload HTTPS traffic, and forward plain HTTP request to EC2 instance, so instance’s CPU is less utilized. Problem here is that rewrite condition is that incoming request is HTTPS (which is NOT), and thus endless 303 redirects occur. Fortunately, according to non-written standard, Amazons ELB  forwards ‘HTTP_X_FORWARDED_PROTO‘ header with origin request protocol as value, thus above condition could be replaced with
‘do not apply this rule if content of HTTP_X_FORWARDED_PROTO header is ‘https’ or in web.config:

<rules>
        <rule name="Redirect to HTTPS" stopProcessing="true">
        <match url="(.*)" />           
  <conditions>
      <add input="{HTTP_X_FORWARDED_PROTO}" pattern="https" negate="true" />
  </conditions>   
        <action type="Redirect" url="https://{HTTP_HOST}/{R:1}" redirectType="SeeOther" />
        </rule>
 </rules>

Groovy | Connect to SQL Server and execute stored procedure

To connect to Microsoft SQL Server from Groovy script, and execute stored procedure, simply follow steps outlined below

1) Download JDBC driver Jar file from https://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx.
Add jar when running groovy script with -cp parameter:

#!/bin/bash
groovy -cp "./lib/sqljdbc41.jar" myScript.groovy

OR if running compiled JVM code (output from groovyc), use java’s cp param

#!/bin/bash
java -cp "./lib/sqljdbc41.jar;myScript.jar"  com.example.MainClas

Note: Examples above assume you have unpacked JDBC driver jar to ‘lib’ directory

2) Create Groovy’s SQL object by using instance method, and passing JDBC connection string to it:

def configuration = [
 'dbInstance' : 'localhost',
 'dbPort' : 1433,
 'dbName' : 'myDatabase',
 'dbUser' : 'sa',
 'dbPass' : 'sa123'
]
 
def sql = Sql.newInstance(
"jdbc:sqlserver://${configuration.dbInstance}:${configuration.dbPort};" + 
"databaseName=" + configuration.dbName,
 configuration.dbUser, configuration.dbPass,
 'com.microsoft.sqlserver.jdbc.SQLServerDriver')

Note : All configuration params – host, port, username and pass are placed in ‘configuration’ map

3) Executing stored procedure is as easy as calling ‘call’ method on Sql object. This method accepts following parameters:

  1. String representing method execution and taking following format
    {? = call SP_NAME(?,?…)}
    Little clarification for question marks:

    1. First question mark represents SP’s return value (0 for OK)
    2. Every following question mark inside brackets presents parameter, either input or output one
  2. List for SP params:
    1. For input parameters use their values
    2. For output parameters specify their type, one of the groovy.sql.OutParameter constants from groovy.sql.Sql class (e.g. groovy.sql.INTEGER)
  3. Closure executed upon SP execution. First parameter passed to closure is return value of stored procedure (usually 0 for OK). Every following parameter matches respecting OUT parameter in same order as declared in procedure itself.

Below is example code for both creating Sql object and executing stored procedure, asuming that stored procedure multiplies two integers (first and second parameter), and assigns result of multiplication to third (output) parameter

def configuration = [
  'dbInstance' : 'localhost',
  'dbPort' : 1433,
  'dbName' : 'myDatabase',
  'dbUser' : 'sa',
  'dbPass' : 'sa123'
]

def sql = Sql.newInstance(
  "jdbc:sqlserver://${configuration.dbInstance}:${configuration.dbPort};" +
    "databaseName=" + configuration.dbName,
  configuration.dbUser, configuration.dbPass,
  'com.microsoft.sqlserver.jdbc.SQLServerDriver')

sql.call('{? = call MULTIPLY(?,?,?)}',[
  Sql.INTEGER, 5,5  ,Sql.CHAR],{
 spResult, multiplicationResult ->
  if(spResult == 0){
   assert multiplicationResult == 25
  }else {
   throw new RuntimeException("SP MULTIPLY FAILED WITH ERR CODE:${spResult}")
  }
})

Groovy | Consuming Google Geocoding API

Large number of newly born online businesses have need for some sort of geo location based logic, whatever it’s displaying ‘products near consumer’s location, match-making profiles in visitors neighborhood or simply advertising restaurants, stores and services near user. For each of this operations server side code needs to determine location of user, or location of some other entity (product store, advertised person / business). While people (and post office) work great with addresses, computers don’t, and as such there is need to convert physical address to numbers, that is into latitude / longitude pair.

If you are interested in how geo-coding algorithms work, you can read more details here. However, if you just want a tool (service) that works, there is lots of options, I’ve listed couple of them below, but note that this surely is not final list – intention of this post is to demonstrate consuming Google’s geocoding API from Groovy / Grails. You can find full list of Geocoding services here – http://en.wikipedia.org/wiki/List_of_geocoding_systems
  1. Use Geonames.org database and setup your own database and service. Database can be downloaded from http://www.geonames.org/export/, and implement your own lookup – and geo-code logic. Pros of this approach is price – there is no charges, as everything is hosted by yourself (hosting charges apply, ofcourse). On the other side, implementing such thing is not trivial project, and I would use this approach if you are big as Facebook or any other massive online application,  and have millions of geo-coding request every hour.
  2. Use Geonames free API  – details of API can be found at http://www.geonames.org/export/ws-overview.html. Note that that are service limits that apply, and you’re running commercial business you should consider singing up for premium webservices – more info  here – http://www.geonames.org/commercial-webservices.html
  3. My preferred way is to consume Google’s web services, and it’s Geocoding API. API specification can be found here . For example, geocoding street of Dositejeva, near Belgrade’s national theater, you would parse response for following REST callhttps://maps.googleapis.com/maps/api/geocode/xml?address=Dositejeva,%20Belgrade,%20Serbia

    You’ll see many pieces of data in example above, but what was specifically important for me is extracting:

    1. Latitude  / longitude pair
    2. State for given address
    3. Country for given address

Latitude / longitude can be found at result/geometry/location XML node, that is in result.geometry.location property if you are requesting JSON as response format. In addition, location is split by ‘address components’. To find address state, you should search for address component with type ‘administrative_area_level_1‘, and for country, simple search for address component with type ‘country‘ is enough.

Below is examples in groovy programming language on how to get latitude / longitude pairs, state and country for given physical address

Listing 1 : Geocode physical address in lat/lng pair in groovy

import java.util.logging.Logger

final String GEOCODING_API_HOST = 'https://maps.googleapis.com'

def getGoolgeApiKey = {
    //place your API key kere if you have one
    return ''
}


@Grab(group='org.codehaus.groovy.modules.http-builder', module='http-builder', version='0.7' )
def getLatLng =  { String query ->
        def rval = null
        try{
            new groovyx.net.http.HTTPBuilder(GEOCODING_API_HOST).get([path: '/maps/api/geocode/json', query: [key : getGoolgeApiKey(), address: query, sensor: false]]) { resp, json ->
                rval = json
            }
        }catch(org.apache.http.client.ClientProtocolException ex){
           println ("Protocol error while connecting to  ${GEOCODING_API_HOST}:" + ex.message)
        }catch(IOException ex){
           println ("I/O  error while connecting to  ${GEOCODING_API_HOST}:" + ex.message)
        }catch(URISyntaxException ex){
           println ("URI mallformed: ${GEOCODING_API_HOST}")
        }

        
        return [lat: rval.results.geometry.location[0].lat, lng:rval.results.geometry.location[0].lng]
}
    
println getLatLng('100 John St, New York, NY, US')

Listing 2 : Extract state information from physical address

 
import java.util.logging.Logger

final String GEOCODING_API_HOST = 'https://maps.googleapis.com'

def getGoolgeApiKey = {
    //place your API key kere if you have one
    return ''
}


@Grab(group='org.codehaus.groovy.modules.http-builder', module='http-builder', version='0.7' )
def getCountryAndState =  { String query ->
        def rval = null
        try{
            new groovyx.net.http.HTTPBuilder(GEOCODING_API_HOST).get([path: '/maps/api/geocode/json', query: [key : getGoolgeApiKey(), address: query, sensor: false]]) { resp, json ->
                rval = json
            }
        }catch(org.apache.http.client.ClientProtocolException ex){
           println ("Protocol error while connecting to  ${GEOCODING_API_HOST}:" + ex.message)
        }catch(IOException ex){
           println ("I/O  error while connecting to  ${GEOCODING_API_HOST}:" + ex.message)
        }catch(URISyntaxException ex){
           println ("URI mallformed: ${GEOCODING_API_HOST}")
        }
        
        def result = ['state':'',country : '']
        
        rval.results?.address_components[0].each {
            if (it.types.contains('administrative_area_level_1')) {
                result['state'] = it.long_name
            }
            if (it.types.contains('country')) {
                result['country'] = it.long_name
            }
        }
           
        return result
}
    
println "State / Country for '1018 maddison ave' : " +  getCountryAndState('1018 maddison ave')

Note: Above examples were compiled and tested against Groovy version 2.1.9

You can as well checkout full Geocoding utility class used in Grails application here – http://pastebin.com/hK6veMe1

 

Hibernate | ‘unexpected AST node’ – Suming boolean column in HQL

Today I’ve encountered this error developing Grails application that executes HQL (Hibernate Query Language) code:

unexpected AST node

HQL Query is supposed to pull all patients in  database that are marked as active, but have all of their treatments marked as inactive:

FROM PatientTreatment PT 
JOIN PT.patient P
GROUP BY P.id
HAVING SUM(case when PT.isActive then 1 else 0 end) = 0
AND P.isActive=true

However, after initial run – i got error mentioned above ‘unexpected AST node’ – which  implies there is something wrong with query syntax (unexpected abstract tree node).  It was obvious that problem is within sum() function, and after couple of minutes of research I’ve realized that HQL can’t automatically consider ‘PT.isActive’ as expression, but rather this property’s value MUST be explicitly given. So, HQL that does work is listed below:

FROM PatientTreatment PT 
JOIN PT.patient P
GROUP BY P.id
HAVING SUM(case when PT.isActive = true then 1 else 0 end) = 0
AND P.isActive=true



Hope this saves someone’s time.

Groovy | Generating map of U.S states and cities

I couldn’t find anywhere on the web JSON file for download that has U.S state two-letter abbreviations as keys, and simple array of city names as values. I needed this JSON file to stick into AngularJS application, removing the need to pull data from the server.

What I did is – I’ve primarily found  list of state abbreviations, and secondary U.S. Small business administrations API for listing all counties and cities within one state – which resulted in Groovy script generating wanted JSON file.

State list is taken from  http://adamkinney.com/blog/2012/04/25/list-of-us-states-in-javascript-object-notation/, with some states removed (ones that SBA API did not recognize, like Palau)

Groovy script follows:

// list taken from http://adamkinney.com/blog/2012/04/25/list-of-us-states-in-javascript-object-notation/, and 
// transformed into Groovy notation
def states = [
[ name: 'ALABAMA', abbreviation: 'AL'],
[ name: 'ALASKA', abbreviation: 'AK'],
[ name: 'ARIZONA', abbreviation: 'AZ'],
[ name: 'ARKANSAS', abbreviation: 'AR'],
[ name: 'CALIFORNIA', abbreviation: 'CA'],
[ name: 'COLORADO', abbreviation: 'CO'],
[ name: 'CONNECTICUT', abbreviation: 'CT'],
[ name: 'DELAWARE', abbreviation: 'DE'],
[ name: 'DISTRICT OF COLUMBIA', abbreviation: 'DC'],
[ name: 'FLORIDA', abbreviation: 'FL'],
[ name: 'GEORGIA', abbreviation: 'GA'],
[ name: 'GUAM', abbreviation: 'GU'],
[ name: 'HAWAII', abbreviation: 'HI'],
[ name: 'IDAHO', abbreviation: 'ID'],
[ name: 'ILLINOIS', abbreviation: 'IL'],
[ name: 'INDIANA', abbreviation: 'IN'],
[ name: 'IOWA', abbreviation: 'IA'],
[ name: 'KANSAS', abbreviation: 'KS'],
[ name: 'KENTUCKY', abbreviation: 'KY'],
[ name: 'LOUISIANA', abbreviation: 'LA'],
[ name: 'MAINE', abbreviation: 'ME'],
[ name: 'MARYLAND', abbreviation: 'MD'],
[ name: 'MASSACHUSETTS', abbreviation: 'MA'],
[ name: 'MICHIGAN', abbreviation: 'MI'],
[ name: 'MINNESOTA', abbreviation: 'MN'],
[ name: 'MISSISSIPPI', abbreviation: 'MS'],
[ name: 'MISSOURI', abbreviation: 'MO'],
[ name: 'MONTANA', abbreviation: 'MT'],
[ name: 'NEBRASKA', abbreviation: 'NE'],
[ name: 'NEVADA', abbreviation: 'NV'],
[ name: 'NEW HAMPSHIRE', abbreviation: 'NH'],
[ name: 'NEW JERSEY', abbreviation: 'NJ'],
[ name: 'NEW MEXICO', abbreviation: 'NM'],
[ name: 'NEW YORK', abbreviation: 'NY'],
[ name: 'NORTH CAROLINA', abbreviation: 'NC'],
[ name: 'NORTH DAKOTA', abbreviation: 'ND'],
[ name: 'OHIO', abbreviation: 'OH'],
[ name: 'OKLAHOMA', abbreviation: 'OK'],
[ name: 'OREGON', abbreviation: 'OR'],
[ name: 'PENNSYLVANIA', abbreviation: 'PA'],
[ name: 'PUERTO RICO', abbreviation: 'PR'],
[ name: 'RHODE ISLAND', abbreviation: 'RI'],
[ name: 'SOUTH CAROLINA', abbreviation: 'SC'],
[ name: 'SOUTH DAKOTA', abbreviation: 'SD'],
[ name: 'TENNESSEE', abbreviation: 'TN'],
[ name: 'TEXAS', abbreviation: 'TX'],
[ name: 'UTAH', abbreviation: 'UT'],
[ name: 'VERMONT', abbreviation: 'VT'],
[ name: 'VIRGIN ISLANDS', abbreviation: 'VI'],
[ name: 'VIRGINIA', abbreviation: 'VA'],
[ name: 'WASHINGTON', abbreviation: 'WA'],
[ name: 'WEST VIRGINIA', abbreviation: 'WV'],
[ name: 'WISCONSIN', abbreviation: 'WI'],
[ name: 'WYOMING', abbreviation: 'WY' ]
],

//this our map that will be seriazlied to JSON
allData = [:]

//iterate through all of the states
states.each { it ->
def twoLetterCode = it.abbreviation,
//grab the data from the api
citiesJson = new URL("http://api.sba.gov/geodata/primary_city_links_for_state_of/${twoLetterCode}.json").text,
citiesMap = new groovy.json.JsonSlurper().parseText(citiesJson)

allData[twoLetterCode] = []

//iterate through all cities and add them to result set
citiesMap.each { city ->
allData[twoLetterCode].push(city.name)
}

//sort alphabetically asceding (alphabetical sort is default for String object in Groovy)
allData[twoLetterCode].sort()
}
//serialize Groovy map to JSON
def serializedData = new groovy.json.JsonBuilder(allData).toPrettyString()
//and write to file
new File('/tmp/usCitiesData.json') << serializedData

return true

You can see generated file here – http://pastebin.com/GSpzczfi

Grails Spring Security Plugin – Creating your own custom authorization and authentication

Grails Spring Security plugin works nicely out of the box – creating your User and Role domain classes, and appropriate mapping between them, as well (optionally) request map domain, where you can configure access to specific url through database, rather than hard-coding it, either via annotations or providing simple predefined map to Grails application.

But if you want to customize authorization and authentication process, like for in instance working with legacy systems, or, for an example, if you want to store salt for password on completely other server or in has file (or any way you want), your Grails application needs bit more configuration.

Below are 3 simple steps to achieve this behaviour

1. Create your own Authentication Provider – implementing AuthenticationProvider interface

class MyAuthenticationProvider implements AuthenticationProvider {
 
@Override
public Authentication authenticate(Authentication arg0)
throws AuthenticationException {
def userName = (arg0 as UsernamePasswordAuthenticationToken).getPrincipal(),
password = (arg0 as UsernamePasswordAuthenticationToken).getCredentials(),
user = getUserByUserName(userName),
providedPassword = encodePassword(password, user),
realPassword = getUserPassword(user)
if(!providedPassword.equals(realPassword)){
throw new BadCredentialsException("Bad login credentials!")
}
def authorities = getUserAuthorities(user)
return new UsernamePasswordAuthenticationToken(user, arg0.credentials, authorities)
}
 
private def getUserByUserName = { username ->
//TODO your application code here
//TODO retrieve user by username, and return
}
 
private def encodePassword = { password ->
//TODO your application code here
//TODO encode password
}
 
private def getUserPassword = { user ->
//TODO your application code here
//TODO return user password
//e.g. return user.getPassword()
}
 
private def getUserAuthorities = { user ->
//TODO your application code here
//TODO return user password
//e.g. return user.getUserRoles()
}
 
@Override
public boolean supports(Class<?> arg0) {
return true;
}
}

2. Create a bean named ‘myApplicationAuthenticationProvider‘, in resources.groovy

beans = { 
myAuthenticationProvider(com.myproductname.MyAuthenticationProvider)
}

3. Reference created bean in Config.groovy

grails.plugin.springsecurity.providerNames = ['myAuthenticationProvider']

Note that above example is tested on Grails version 2.3.5. Just stick your logic in authenticate() method of your authentication provider, above example has TODO comments, but you can organize your code any way you wish (advanced example would be reading password salt from storage other than relational database, e.g. hashfile)

Backup mongo database from remote server – bash script

MongoDB comes with hand command – line utitliy mongodump, that comes in handy when you want to create backup of your mongo database. You can later restore this dump by using another handy command-line utility – mongorestore.

Below is simple bash script that makes backup on a remote server via SSH, copies over backup to local machine, and compresses it using tar utility. Backup filename contains timestamp.

#!/bin/bash

#create backup on remote machine
ssh remotehost.com 'cd /tmp && mongodump -o yourdatabasename'

#copy to local machine in backup directory
scp -r remotehost.com:/tmp/yourdatabasename /backup/yourdatabasename

#rename file to contain current date and time
mv /backup/yourdatabasename /backup/yourdatabasename.`date "+%Y-%m-%d-%H%M"`

#create archive with database backup
tar -zcvf yourdatabasename.`date "+%Y-%m-%d-%H%M"`.tar.gz yourdatabasename.`date "+%Y-%m-%d-%H%M"`

#remove backup directory, since we created archieve
rm -rf /backup/yourdatabasename.`date "+%Y-%m-%d-%H%M"`

Additionally, if you set this script to run as cron job, you may want to delete old backups, let’s day older than 2 weeks. You can achieve this by adding following line to above script

#find all files alder than 14 days , and remove
find /backup/yourdatabasename*.tar.gz -mtime +14 -exec rm {} ;

Grails many to many associations and fetch with join

Lately I have been working on a custom written web solution using Grails MVC framework, that’s stacked on top of Java / Groovy, and is parallel to RoR.  Since I’ve started working with Grails  and it’s object-relational mapper GORM that’s stacked on top of Hibernate, as someone that comes with no in-depth Hibernate knowledge I came up with following problem:

I had to create many-to-many mapping between entities A and B, let’s called them Developer and Project, and wanted to lazy load developers when needed  but, using sql JOIN statement, rather than reading one-by-one which was database-intensive. Examine following definitions:

class Project {
String title

static hasMany = [ developers : Developer ]
}

class Developer {

String title

static hasMany = [projects : Project]
static belongsTo = Project
}

Obviously, there is many to many relationship defined between Developer and Project domain definition, and by Grails official documentation, many to many associations need to have owning end of definition, Project in this case. Owning end means that all saves, and updates to Project will cascade to developer.

Database schema  is created using following statements (taken directly from mysql log)

create table developer (id bigint not null auto_increment, version bigint not null, title varchar(255) not null, primary key (id)) ENGINE=InnoDB

create table project (id bigint not null auto_increment, version bigint not 
null, title varchar(255) not null, primary key (id)) ENGINE=InnoDB

create table project_developers (project_id bigint not null, developer_id 
bigint not null, primary key (project_id, developer_id)) ENGINE=InnoDB

alter table project_developers add index FK700662CFD8DF43FA (project_id), add constraint FK700662CFD8DF43FA foreign key (project_id) references project (id)

alter table project_developers add index FK700662CF76D8825A (developer_id), 
add constraint FK700662CF76D8825A foreign key (developer_id) 
references developer (id)

Consider following code and log output when inserting values, if Grails is configured to log sql using dataSource.logSql  setting:

 def project = new Project(title:"Awesome web solution")

project.addToDevelopers(new Developer(title:"John Doe"))
project.addToDevelopers(new Developer(title:"Mark Smith"))
project.addToDevelopers(new Developer(title:"Joanne Doe"))

project.save()

Hibernate: insert into project (version, title) values (?, ?)
Hibernate: insert into developer (version, title) values (?, ?)
Hibernate: insert into project_developers (project_id, developer_id) values (?, ?)

It simply inserts values into mapped domain object tables, and mapper table. Since repeated rows are ignored in log, there isn’t 3 statements for 2nd and 3rd row – there actually present in real mysql log:

 73 Query insert into project (version, title) 
                                      values (0, 'Awesome web solution')
73 Query insert into developer (version, title) values (0, 'Joanne Doe')
73 Query insert into developer (version, title) values (0, 'Mark Smith')
73 Query insert into developer (version, title) values (0, 'John Doe')
73 Query insert into project_developers 
                                    (project_id, developer_id) values (1, 1)
73 Query insert into project_developers 
                                    (project_id, developer_id) values (1, 2)
73 Query insert into project_developers 
                                    (project_id, developer_id) values (1, 3)

And now, what happens with GORM mapping by default if we try to pull all developers that worked on a project. See piece of code below

EXAMPLE 0

def project = Project.findByTitle("Awesome web solution")

project.developers.each { developer ->
println "${developer.title} works on project '${project.title}'"
}

– and sql log that follows it

// ...
//pulling out developer with given title before

//pulls out all developer IDs that work on a project
Hibernate: select developers0_.project_id as project1_0_0_, developers0_.developer_id as developer2_0_
from project_developers developers0_
where developers0_.project_id=?

//pulls out developer 1
Hibernate: select developer0_.id as id2_0_, developer0_.version as version2_0_, developer0_.title as title2_0_
from developer developer0_
where developer0_.id=?

//pulls out developer 2
Hibernate: select developer0_.id as id2_0_, developer0_.version as version2_0_, developer0_.title as title2_0_
from developer developer0_
where developer0_.id=?

//pulls out developer 3
Hibernate: select developer0_.id as id2_0_, developer0_.version as version2_0_, developer0_.title as title2_0_
from developer developer0_
where developer0_.id=?

Obviously, there is n+1 query executed for project with n developers, which is database-intensive even for projects with small number of developers, since main thread will be constantly put in I/O wait, waiting for database reply, and database will process larger number of queries, so MySQL server CPU is unnecessary utilized. Simple idea for anyone with some knowledge of SQL would be to perform a JOIN instead of row-by-row querying.

Techniques for loading more than one row at a time in GORM, by Grails documentation are

  1. Setting lazy loading to false (pulling all date at once), since GORM mapping is lazy loading associations by default
  2. Specifying ‘join’ fetch 

 Trying to set lazy loading to false on Project domain, simple does not work, and gives us same sql log:

EXAMPLE 1

class Project {
String title

static hasMany = [ developers : Developer ]

static mapping = {
developers lazy:false
}
}

        select project0_.id as id0_0_, project0_.version as version0_0_, 
               project0_.title as title0_0_ 
        from project project0_ where project0_.id=1
        select developers0_.project_id as project1_0_0_,
             developers0_.developer_id as developer2_0_ 
        from project_developers developers0_ 
        where developers0_.project_id=1

        select developer0_.id as id2_0_, developer0_.version as version2_0_,
               developer0_.title as title2_0_ 
        from developer developer0_ where developer0_.id=2
 select developer0_.id as id2_0_, developer0_.version as version2_0_,
               developer0_.title as title2_0_ 
        from developer developer0_ where developer0_.id=1

        select developer0_.id as id2_0_, developer0_.version as version2_0_,
               developer0_.title as title2_0_ 
        from developer developer0_ where developer0_.id=3

If setting fetching associations using JOIN statement, only one developer is pulled out, since reading project statement is merged with reading developers statement.

EXAMPLE 2

class Project {
String title

static hasMany = [ developers : Developer ]

static mapping = {
developers fetch:'join'
}
}

sql log (directly from MySQL log file):

               select this_.id as id2_0_, 
                       this_.version as version2_0_, 
                       this_.title as title2_0_,
                       developers2_.project_id as project2_2_2_, 
                      developers2_.developer_id as developer1_2_
               from project this_ 
               left outer join project_developers developers2_ 
                     on this_.id=developers2_.project_id 
               where this_.title='Awesome web solution' limit 1
        select developer0_.id as id0_0_, 
                     developer0_.version as version0_0_,
                     developer0_.title as title0_0_ 
               from developer developer0_ 
               where developer0_.id=1

Obviously, when reading entity by findBy, it’s translated to ‘limit 1’ sql statement, and thus limits reading of project developers to only one. Even if reading statement changed to reading project directly, so there’s no merging of two Hibernate statements, this approach won’t make things better, since every developer is pulled out of database in it’s own query, though it will give correct (read ALL) results

EXAMPLE 3:

 def project = Project.get(1)

project.developers.each { developer ->
println "${developer.title} works on project '${project.title}'"
}

MySQL  LOG file:

select developer0_.id as id0_0_,
developer0_.version as version0_0_,
developer0_.title as title0_0_
from developer developer0_
where developer0_.id=1

select project0_.id as id2_0_, project0_.version as version2_0_,
project0_.title as title2_0_,
developers1_.project_id as project2_2_2_,
developers1_.developer_id as developer1_2_
from project project0_
left outer join project_developers developers1_
on project0_.id=developers1_.project_id
where project0_.id=1

select developer0_.id as id0_0_, developer0_.version as version0_0_,
developer0_.title as title0_0_
from developer developer0_
where developer0_.id=2

select developer0_.id as id0_0_, developer0_.version as version0_0_,
developer0_.title as title0_0_
from developer developer0_
where developer0_.id=1

select developer0_.id as id0_0_, developer0_.version as version0_0_,
developer0_.title as title0_0_
from developer developer0_
where developer0_.id=3

Still, large number of queries, for simple result. Analyzing all of above examples implies that mapper table ‘projects_developers’ is actually what’s treated as ‘developers’ association in Project domain, in the key to achieving the goal is to set join behavior on  OWNED end of an association, that is on Developer domain class:

EXAMPLE 4:

class Project {
String title
static hasMany = [ developers : Developer ]
}

class Developer {

String title

static hasMany = [projects : Project]
static belongsTo = Project

static mapping = {
projects fetch:'join'
}
}

And following SQL log

select project0_.id as id0_0_, project0_.version as version0_0_,
project0_.title as title0_0_
from project project0_
where project0_.id=1

select developers0_.project_id as project1_0_1_,
developers0_.developer_id as developer2_1_,
developer1_.id as id2_0_, developer1_.version as version2_0_,
developer1_.title as title2_0_
from project_developers developers0_
inner join developer developer1_ on developers0_.developer_id=developer1_.id
where developers0_.project_id=1

As you can see only 2 sql queries are executed – one to pull out project, and second to pull out all of the project’s developers, and no limit is implied.

It is lengthy post, but hope that this will save someone’s time, as I spent quite some time understanding GORM and it’s gotchas. For any furhter tweaking like CASCADE constraints on MySQL, pagination parameters, etc, please refer to official GORM documentation.