Connect to MySQL via hibernate

Question

My db:

CREATE TABLE `ggloor`.`teams` (
  `idteam` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`idteam`));

Settings in applicatin.yml

hibernate:
    cache:
        queries: false
        use_second_level_cache: true
        use_query_cache: false
        region.factory_class: org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory

dataSource:
    pooled: true
    jmxExport: true
    driverClassName: com.mysql.jdbc.Driver
    username: root
    password: 1111

environments:
    development:
        dataSource:
            dbCreate: create-drop
            url: jdbc:mysql://localhost:3306/ggloor?useSSL=false
    test:
        dataSource:
            dbCreate: create-drop
            url: jdbc:mysql://localhost:3306/ggloor?autoreconnect=true;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE
    production:
        dataSource:
            dbCreate: create-drop
            url: jdbc:mysql://localhost:3306/ggloor?autoreconnect=true;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE
            properties:
                jmxEnabled: true
                initialSize: 5
                maxActive: 50
                minIdle: 5
                maxIdle: 25
                maxWait: 10000
                maxAge: 600000
                timeBetweenEvictionRunsMillis: 5000
                minEvictableIdleTimeMillis: 60000
                validationQuery: SELECT 1
                validationQueryTimeout: 3
                validationInterval: 15000
                testOnBorrow: true
                testWhileIdle: true
                testOnReturn: false
                jdbcInterceptors: ConnectionState
                defaultTransactionIsolation: 2 # TRANSACTION_READ_COMMITTED

Created domain

package testgrails12

class Teams {
    Integer idteam
    String name
    static constraints = {
    }
}

Controller

package testgrails12

class Teams {
    Integer idteam
    String name
    static constraints = {
    }
    static mapping = {
        id column: 'idteam', sqlType: 'INT(11)', insertable: false, updateable: false
    }
}

I get an error at the execution stage

Error 500: Internal Server Error URI /bdconnect/index Class com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException Message null Caused by Unknown column 'teams0_.id' in 'field list'

I tried to create hibernate.cfg.xml, Teams.hbm.xml in the conf folder, this did not work.

How to set everything up correctly? Work stopped =(

Error after add mapping

2017-03-29 19:30:34.954 ERROR --- [ost-startStop-1] o.s.b.c.embedded.tomcat.TomcatStarter : Error starting Tomcat context. Exception: org.springframework.beans.factory.BeanCreationException. Message: Error creating bean with name 'grailsCacheFilter': Cannot create inner bean '(inner bean)#4bafa64a' of type [grails.plugin.cache.web.filter.simple.MemoryPageFragmentCachingFilter] while setting bean property 'filter'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name '(inner bean)#4bafa64a': Unsatisfied dependency expressed through method 'setUrlMappingsHandlerMapping' parameter 0; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'urlMappingsHandlerMapping': Unsatisfied dependency expressed through method 'setWebRequestInterceptors' parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'openSessionInViewInterceptor': Cannot resolve reference to bean 'hibernateDatastore' while setting bean property 'hibernateDatastore'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'hibernateDatastore': Bean instantiation via constructor failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.grails.orm.hibernate.HibernateDatastore]: Constructor threw exception; nested exception is org.hibernate.MappingException: Repeated column in mapping for entity: testgrails12.Teams column: idteam (should be mapped with insert="false" update="false") 2017-03-29 19:30:35.003 ERROR --- [ main] o.s.boot.SpringApplication : Application startup failed

I don't know anything about hibernate, but in your class definition the field is called teamid, in your database screenshot its called idteam and your error message names the field id. Looks inconsistent to me. — ventiseis
And for that missing id, why don't you use the @GeneratedValue annotation? — ventiseis

Anton Hlinisty Anton Hlinisty · Accepted Answer · 2017-03-29T14:24:31

The easiest solution is to remove Integer idteam from your entity and let GORM create the DB schema for you.

If you use some legacy database you can align your entity mapping using the static mapping = {} closure as given here, your id field probably will look like that:

static mapping = {
    ....
    id column: 'idteam', sqlType: 'INT(11)', insertable: false, updateable: false
    ....
}

Note: you do not need to define it as a field in your entity. Just use it in mapping.

Connect to MySQL via hibernate

1 Answers