two selects queries instead of one before insert

Question

I'm using grails 2.2.2 and I have created a domain class with one unique String attribute:

class Myclass {
    String dscr // my String attibute

    static constraints = {
        dscr unique: true // the code to set it unique
    }
}

and then I run the grails console command to test this simple class with the following code with loggingSql = true to see the resulting queries:

def a = new Myclass([dscr:'dscr1'])
a.save()

The resulting queries are sone below:

Hibernate: select this_.id as id0_0_, this_.version as version0_0_, this_.dscr as dscr0_0_ from myclass this_ where this_.dscr=?
Hibernate: select this_.id as id0_0_, this_.version as version0_0_, this_.dscr as dscr0_0_ from myclass this_ where this_.dscr=?
Hibernate: insert into myclass (version, dscr) values (?, ?)

The mystery here is the two select queries instead of one. The reason for the one query, as I found here, is that the select query is made to to check uniqueness. Why does the second select happen?

Answer 1

After all, I couldn’t find a logical explanation for the two select queries. The best I found is to get rid of these two selects without a manual change of the database, as said here.

So to get rid of these selects, someone should first set the domain class in a different way (without constraints, but with mapping).

class Myclass {
    String dscr

    static mapping = {
        dscr unique: true
    }
}

And then to protect your code from exceptions, because now hibernate doesn’t check for uniqueness, you should insert new elements like this:

try {
    def a = new Myclass([dscr:'dscr1'])
    a.save()
} catch(e) {
    Myclass.withSession { session ->
        session.clear()
    }

    // do whatever you want to handle a possible exception
}

And now the resulting query is only one import query, which may run successfully or not.

Hibernate: insert into myclass (version, dscr) values (?, ?)