Error when relating two tables with BigInteger datatype in Hibernate

Question

hibernate automatically maps columns on the background. And after working with Grails a year now, I can assume that varying datatypes makes no difference to gorm— it can handle any non-primitive datatypes as its column without any much configuration. But see the given example mapping on Grails 2.4 using Hibernate 4:

class TableA {
    String id

    static hasMany = [joinTable: TableAJoinTableB]

    static mapping = {
        id generator: 'assigned'
    }
}

class TableB {
    BigInteger id

    static hasMany = [joinTable: TableAJoinTableB]

    static mapping = {
        id generator: 'assigned'
    }
}

class TableAJoinTableB {
    String prop1

    static belongsTo = [tablea: TableA, tableb: TableB]

    static mapping = {
        id composite: ["tablea", "tableb"]
    }
}

Assuming that the data at hand respects this relationship, you perform a JOIN query to the three tables using:

// Assuming the query below will *always* retrieve no more than 1 row.
TableAJoinTableB join = TableAJoinTableB.createCriteria().get {
    tablea {
        eq("id", "1KDSURT743")
    }
    eq("prop1", "AT")
}

And simply access the values from TableAand TableB using this:

println "id of TableA via join: " + join?.tablea?.id
println "id of TableB via join: " + join?.tableb?.id

But I can't get to make the second line work, instead it throws an error:

org.hibernate.TypeMismatchException

Provided id of the wrong type for class TableB. Expected: class java.math.BigInteger, got class java.lang.String.

I could always change the id of TableB to String just to end this argument. But there are times when I cannot do that:

e.g. the database primary key is REALLY a number thus will cause an "Invalid number" error when performing a query if I forced its id to String

How can I tell TableAJoinTableB to join with TableB using BigInteger? Isn't Hibernate assume that they should connect using BigInteger since its the datatype of the referencing table's id. The connection to TableA is already working and I can see that the connection to TableB is not supposed to be that way different.

This works with Grails 2.4.4, hibernate 4.3.6.1 and MySql 5.6.27 and MySQL Driver 5.1.29. Do you have a different config? — Sandeep Poonia
Enable Hibernate logs trace 'org.hibernate.type.descriptor.sql.BasicBinder','org.hibernate.SQL' and then check the queries that are being fired when you execute create criteria. — Sandeep Poonia
One solution you can try is by creating overloaded setter methods for id, one that accepts String and another that accepts BigInteger — Sandeep Poonia

Gideon Gideon · Accepted Answer · 2016-01-18T05:30:06

Using an overloaded setter methods for id can be the answer. But it defeats the purpose of auto-mapping of Hibernate foreign keys. The simple solution that I discovered solved the problem:

TableAJoinTableB join = TableAJoinTableB.createCriteria().get {
    tablea {
        eq("id", "1KDSURT743")
    }
    eq("prop1", "AT")
}

println "id of TableA via join: " + join?.tablea?.id

// These won't work and causes the same error to be thrown.
// I'll try to investigate why:
// println "id of TableB via join: " + join?.getTableb()?.id
// println "id of TableB via join: " + join?.getTableb()?.get("id")
// println "id of TableB via join: " + join?.tableb?.id
// println "id of TableB via join: " + join?.tableb?.get("id")

// However, the following line works:
println "id of TableB via join: " + join?.getTableb().getId()

Error when relating two tables with BigInteger datatype in Hibernate

1 Answers