Why does Grails not use the provided name of my join table (many-to-many relationship)

Question

Background

I am evaluating Grails and thus have to map the persistence layer for a legacy database. I am starting with three tables:

• Clone
  • among other attributes: primary key id
• CloneSet
  • among other attributes: primary key id
• Clone2CloneSet
  • just two foreign keys cloneID and cloneSetID

The domain classes are coded as follows:

class Clone {
    // among others
    static hasMany = [cloneSets: CloneSet]

    static mapping = {
        id (generator: 'identity')
        cloneSets (
            joinTable: [name: 'Clone2CloneSet', key: 'cloneID', column: 'cloneSetID'],
            cascade: 'none'
        )
    }
}

class CloneSet {
    // among others
    static hasMany = [clones: Clone]
    static belongsTo = Clone
    static mappedBy = [clones: "cloneSets"]

    static mapping = {
        table (name: 'CloneSet') 
        id (generator: 'identity')
        clones (
            joinTable: [name: 'Clone2CloneSet', key: 'cloneSetID', column: 'cloneID'],
            cascade: 'none'
        )
    }
}

Problem

Grails seems to insist that the name of my join table is clone2clone_set:

2013-09-12 10:39:26,459 [localhost-startStop-1] INFO  hbm2ddl.TableMetadata  - table found: mydatabase.dbo.CloneSet
2013-09-12 10:39:26,459 [localhost-startStop-1] INFO  hbm2ddl.TableMetadata  - columns: [id, ...]
2013-09-12 10:39:26,465 [localhost-startStop-1] INFO  hbm2ddl.TableMetadata  - table found: mydatabase.dbo.Clone
2013-09-12 10:39:26,465 [localhost-startStop-1] INFO  hbm2ddl.TableMetadata  - columns: [id, ...]
2013-09-12 10:39:26,469 [localhost-startStop-1] INFO  hbm2ddl.DatabaseMetadata  - table not found: clone2clone_set
| Error 2013-09-12 10:39:26,481 [localhost-startStop-1] ERROR context.GrailsContextLoader  - Error initializing the application: Error creating bean with name 'transactionManagerPostProcessor': Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'transactionManager': Cannot resolve reference to bean 'sessionFactory' while setting bean property 'sessionFactory'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sessionFactory': Invocation of init method failed; nested exception is org.hibernate.HibernateException: Missing table: clone2clone_set
Message: Error creating bean with name 'transactionManagerPostProcessor': Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'transactionManager': Cannot resolve reference to bean 'sessionFactory' while setting bean property 'sessionFactory'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sessionFactory': Invocation of init method failed; nested exception is org.hibernate.HibernateException: Missing table: clone2clone_set
    Line | Method
->>  334 | innerRun  in java.util.concurrent.FutureTask$Sync
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
|    166 | run       in java.util.concurrent.FutureTask
|   1145 | runWorker in java.util.concurrent.ThreadPoolExecutor
|    615 | run       in java.util.concurrent.ThreadPoolExecutor$Worker
^    724 | run . . . in java.lang.Thread
Caused by BeanCreationException: Error creating bean with name 'transactionManager': Cannot resolve reference to bean 'sessionFactory' while setting bean property 'sessionFactory'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sessionFactory': Invocation of init method failed; nested exception is org.hibernate.HibernateException: Missing table: clone2clone_set
->>  334 | innerRun  in java.util.concurrent.FutureTask$Sync
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
|    166 | run       in java.util.concurrent.FutureTask
|   1145 | runWorker in java.util.concurrent.ThreadPoolExecutor
|    615 | run       in java.util.concurrent.ThreadPoolExecutor$Worker
^    724 | run . . . in java.lang.Thread
Caused by BeanCreationException: Error creating bean with name 'sessionFactory': Invocation of init method failed; nested exception is org.hibernate.HibernateException: Missing table: clone2clone_set
->>  334 | innerRun  in java.util.concurrent.FutureTask$Sync
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
|    166 | run       in java.util.concurrent.FutureTask
|   1145 | runWorker in java.util.concurrent.ThreadPoolExecutor
|    615 | run       in java.util.concurrent.ThreadPoolExecutor$Worker
^    724 | run . . . in java.lang.Thread
Caused by HibernateException: Missing table: clone2clone_set
->>  334 | innerRun  in java.util.concurrent.FutureTask$Sync
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
|    166 | run       in java.util.concurrent.FutureTask
|   1145 | runWorker in java.util.concurrent.ThreadPoolExecutor
|    615 | run       in java.util.concurrent.ThreadPoolExecutor$Worker
^    724 | run . . . in java.lang.Thread

Question

How can I persuade Grails to search for the correct join table?

Answer 1

I suppose you do not need mappedBy in CloneSet.

class CloneSet {

    static hasMany = [clones: Clone]
    static belongsTo = Clone

    //Do you really need this?
    //This maps to CloneSet which is incorrect 
    //static mappedBy = [clones: "cloneSets"]

    static mapping = {
        table name: 'CloneSet'
        id generator: 'identity'
        clones joinTable: [name: 'Clone2CloneSet', key: 'cloneSetID', 
                           column: 'cloneID'],
               cascade: 'none'

    }
}

Moreover, if feasible can table names become CLONE_SET, CLONE_CLONE_SET (or CLONE_CLONE_SET_XREF for cross reference). Grails uses CamelCase for domain names and table are named as Camel_Case.

Answer 2

In my case it worked with setting the table name to clone2cloneset, because SQL Server isn't case-sensitive. (This has to be done for all column names as well: no camel-case notation there.)