Generated save SQL for an association class mapped by GORM has correct column name in SELECT clause, but incorrect one in WHERE clause

Question

I have a many-to-many relationship that has its own association class to represent the join table, with a composite ID of the associations. The join table is as simple as it gets, with just two columns, each of which are a foreign key to each table in the many-to-many relationship:

Table LOCATION_REASON has Columns: LOCATION_ID, REASON_ID

The entities in play are thus the two main entities: Location and Reason, and their join table entity: LocationReason.

Previously, when the project was running Grails 2.2.3 and everything worked just fine, the LocationReason domain class looked like this:

LocationReason.groovy

class LocationReason implements Serializable {
    Location location
    Reason reason

    static belongsTo = [Location, Reason]

    static mapping = {
        id composite: ['location', 'reason']
        version false
        table 'location_reason'
    }

    static constraints = {
        location blank: false
        reason unique: ["location"], blank: false
    }

    def getPK() {
        ["locationId":location.id, "reasonId":reason.id]
    }

    String toString() {
        reason.description + " at " + location.description
    }
}

I have been upgrading to Grails 2.4.5 (and hence had to upgrade the version of Hibernate as well), and am now getting the following error when calling the built-in save method:

java.sql.SQLSyntaxErrorException: ORA-00904: "REASON_1_"."ID": invalid identifier
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
at edu.liberty.swiper.LocationReasonController.save(LocationReasonController.groovy:48)
at grails.plugin.cache.web.filter.PageFragmentCachingFilter.doFilter(PageFragmentCachingFilter.java:198)
at grails.plugin.cache.web.filter.AbstractFilter.doFilter(AbstractFilter.java:63)
at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53)
at grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter.doFilter(MutableLogoutFilter.java:62)
at grails.plugin.springsecurity.web.SecurityRequestHolderFilter.doFilter(SecurityRequestHolderFilter.java:59)
at org.jasig.cas.client.session.SingleSignOutFilter.doFilter(SingleSignOutFilter.java:100)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)

When I turn on SQL logging, this is the query that's being generated:

Hibernate-generated query

select this_.location_id as location1_19_0_, this_.reason_id as reason2_19_0_ 
from location_reason this_ 
where reason_1_.id=? and this_.location_id=?

To preface, ALL other sql operations for this and other entities are successful. I can insert, update, delete on all other entities, and can delete LocationReason entities (update is not applicable), but only inserting LocationReason fails.

If you'll notice, the generated query aliases the location_reason table as this_, and subsequently uses it for each field in the SELECT clause... but then only for the location_id field of the WHERE clause. The reason_1_1 prefix on the other field in the WHERE clause completely baffles me. Hibernate didn't join to the Reason table and alias it as reason_1_1; in fact, it's completely invalid for the query. Additionally, Hibernate knew to use reason_id in the SELECT clause, but then suddenly uses just id in the WHERE clause, which makes no sense. Additionally, I see no difference in my configuration for the Location entity vs the Reason entity, so why is the SQL correct for the location_id, but not for the reason_id?

On a sidenote, I was a little surprised to see a SELECT statement instead of an INSERT, but I guess Hibernate is first checking to see if it already exists before doing so.

I have since looked into various ways to represent the relationship (I relied heavily on the GORM documentaion for Grails 2.4.5 here) and make sure that the columns are specified as being location_id and reason_id, but the result is the same error every single time. For reference, here is the current version of the class, and I have tried virtually every combination of GORM config between what's here and what's commented out:

LocationReason.groovy (Updated)

class LocationReason implements Serializable {

    Location location
    Reason reason

//    static belongsTo = [Location, Reason]

    static mapping = {
        id composite: ['location', 'reason']
        table 'location_reason'
//        location(column: 'location_id')
//        reason(column: 'reason_id')
        columns {
            reason {
                column name: 'reason_id'
            }
            location {
                column name: 'location_id'
            }
        }
//        location insertable: false, updateable: false
//        reason insertable: false, updateable: false
        version false
    }

// Excluding constraints, getPK, and toString as they are the same as above

    @Override
    boolean equals(other) {
        if (!(other instanceof LocationReason)) {
            return false
        }

        other.reason == reason && other.location == location
    }

    @Override
    int hashCode() {
        def builder = new HashCodeBuilder()
        builder.append reason
        builder.append location
        builder.toHashCode()
    }
}

Perhaps the issue is that there's just something Hibernate doesn't like about the Reason class, so I've included some GORM information for both Location and Reason below:

Location.groovy

@EqualsAndHashCode(includes=["description"])
class Location implements Comparable<Location>{
    String description
    Integer capacity = 1000
    String username

    static hasMany = [reasons: Reason]

    static mapping = {
        sort description:"asc"
        id generator:'native', params:[sequence:'MY_SCHEMA.ID_SEQ']
        version false
        reasons joinTable: [name: 'LOCATION_REASON', key: 'LOCATION_ID', column: 'REASON_ID'], cascade: 'none', join: 'fetch'
    }

    static constraints = {
        description unique: true, blank: false, maxSize: 64
        capacity nullable: true
        username maxSize: 30
    }

    // Other methods
}

Reason.groovy

class Reason implements DataSourceSpecificRule {
    String description
    Date startDate
    Date endDate
    String username
    ReasonEvaluatorSource reasonEvaluatorSource

    static belongsTo = [Location]

    static hasMany = [locations: Location]

    static mapping = {
        id generator:'native', params:[sequence:'MY_SCHEMA.ID_SEQ']
        startDate column: 'START_DATE'
        endDate column: 'END_DATE'
        reasonEvaluatorSource column: 'REASON_SOURCE_ID'
        locations joinTable: [name: 'LOCATION_REASON', key: 'REASON_ID', column: 'LOCATION_ID'], cascade: 'none'
        version false
    }

    static constraints = {
        description unique: true, maxSize: 128
        startDate nullable: true
        endDate nullable: true
        username maxSize: 30
    }

    // Other methods
}

For reference, DataSourceSpecificRule is an interface that defines some getter and setter methods it expects implementing domain classes to have, such as getId and setId.

For completeness' sake, here are the relevant parts of my BuildConfig that changed with the Grails upgrade:

BuildConfig.groovy (Pre-upgrade, Grails 2.2.3)

grails.servlet.version = "2.5"
grails.project.target.level = 1.6
grails.project.source.level = 1.6

plugins {
    runtime ":hibernate:$grailsVersion"
    runtime ":jquery:1.8.3"
    runtime ":resources:1.2"

    build ":tomcat:$grailsVersion"
    build ':release:2.2.1', ':rest-client-builder:1.0.3'

    compile ':cache:1.0.1'
    compile ":db-reverse-engineer:0.5"
    compile ":rest:0.7"
}

BuildConfig.groovy (Post-upgrade, Grails 2.4.5)

grails.servlet.version = '3.0'
grails.project.target.level = 1.8
grails.project.source.level = 1.8

plugins {
    runtime ':jquery:1.11.1'
    runtime ':resources:1.2.14'
    runtime ':hibernate:3.6.10.18'

    build ':tomcat:8.0.30'
    build ':release:3.1.2', ':rest-client-builder:1.0.3'

    compile ':cache:1.1.6'
    compile ':db-reverse-engineer:0.5'
    compile ':rest:0.8'
    compile 'org.grails.plugins:scaffolding:2.1.2'
}

Thanks in advance for any ideas and/or help!

Answer 1

Still haven't figured out why the built-in save does not work (I'm convinced it's a bug in either GORM or Hibernate, and/or an incompatibility with some other plugin or dependency I'm using), but I did come up with a workaround using JDBCTemplate, which comes pre-loaded with Grails.

So if anyone finds themself in the same pickle, you can utilize the JDBCTemplate by first adding the bean in resources.groovy

/configuration/spring/resources.groovy:

jdbcTemplate(org.springframework.jdbc.core.JdbcTemplate, ref('dataSource'))

where dataSource is the name of the database source bean you're using for this GORM object.

Next you'll need to create a service where you'll use the JDBCTemplate. I chose to make a LocationReasonService

/services/LocationReasonService.groovy:

//package info omitted 

import org.springframework.transaction.annotation.Transactional

class LocationReasonService {
    // Grails will autowire the jdbcTemplate by name into this variable
    def jdbcTemplate

    @Transactional
    def save(LocationReason locationReason) {
        return jdbcTemplate.update("insert into location_reason (location_id, reason_id) values (?, ?)", 
            locationReason?.location?.id, locationReason?.reason?.id)
    }
}

Then configure the controller to use this service:

/controllers/LocationReasonController.groovy:

//package and imports omitted
class LocationReasonController {
    // Grails will autowire the service by name into this variable
    def locationReasonService

    // other fields and methods omitted

    def save() {
        def locationReasonInstance = new LocationReason(params)
        // The built-in GORM save used to work in grails 2.2.3, but there seems to be a bug in 2.4.5 that breaks on saving this particular entity
//        if (!locationReasonInstance.save(flush: true)) { 
        if(!locationReasonService.save(locationReasonInstance)) {
            render(view: 'create', model: [locationReasonInstance: locationReasonInstance])
            return
        }

        flash.message = message(code: 'default.created.message', args: [
            message(code: 'locationReason.label', default: 'LocationReason'),
            locationReasonInstance
        ])
        redirect(action: 'show', params: locationReasonInstance.getPK())
    }
}