Spring data + hibernate uses wrong column name

Question

Using Spring Data + hibernate, Hibernate is configured to automatically create the schema. However when querying an exception is thrown (see below).

It looks like the column name used during automatic schema creation is different than the column name for the query.

If I configure LocalSessionFactoryBean to use a naming strategy the problem disappears and the test works.

Is this a bug? or am I misunderstanding something?

The setup is below.

[Edit] As per the log below the message ERROR: column datum0_.datum_id does not exist is present. However the column is actually named datumId, as can be seen from the logged SQL:

create table Datum (
    datumId int4 not null,
    value varchar(255),
    primary key (datumId)
)

Application.java:

package test;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.ImportResource;

@Configuration
@ImportResource("classpath:Application.xml")
@EnableAutoConfiguration
public class Application {
    static Log log = LogFactory.getLog(Application.class);

    public static void main(String[] args) throws Exception {
        ConfigurableApplicationContext context = SpringApplication.run(Application.class, args);
        DatumRepository repository = context.getBean(DatumRepository.class);
        repository.findAll();
    }
}

Datum.java:

package test;

import javax.persistence.*;

@Entity
public class Datum {
    @Id
    @GeneratedValue
    private int datumId;

    @Column
    private String value;

    public int getDatumId() {
        return datumId;
    }

    public void setDatumId(int datumId) {
        this.datumId = datumId;
    }

    public String getValue() {
        return value;
    }

    public void setValue(String value) {
        this.value = value;
    }
}

DatumRepository.java:

package test;

import org.springframework.data.repository.CrudRepository;

public interface DatumRepository extends CrudRepository<Datum, Integer> {
}

Application.xml:

<?xml version="1.0" encoding="UTF-8"?>
<beans
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns="http://www.springframework.org/schema/beans"
        xmlns:context="http://www.springframework.org/schema/context"
        xmlns:jpa="http://www.springframework.org/schema/data/jpa"
        xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
                            http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
                            http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa.xsd">

    <!--Searches for @Configuration classes and stereotypes-->
    <context:component-scan base-package="test" />

    <!--Searches for @Autowired/@Inject-->
    <context:annotation-config />

    <!-- For creation of repositories -->
    <!--<jpa:repositories base-package="test" />-->

    <!--Data source-->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="org.postgresql.Driver"/>
        <property name="url" value="jdbc:postgresql://localhost/nexus"/>
        <property name="username" value="nexus"/>
        <property name="password" value="nexus"/>
    </bean>

    <!-- Hibernate -->
    <bean class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <property name="packagesToScan" value="test" />
        <!--<property name="namingStrategy"><ref bean="naming" /></property>-->

        <property name="hibernateProperties">
            <value>
                hibernate.hbm2ddl.auto=create-drop
                hibernate.show_sql=true
                hibernate.format_sql=true
            </value>
        </property>
    </bean>

    <bean id="naming" class="org.hibernate.cfg.ImprovedNamingStrategy"></bean>
</beans>

pom.xml:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>test</groupId>
    <artifactId>test1</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.2.1.RELEASE</version>
    </parent>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>9.3-1100-jdbc41</version>
        </dependency>

        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.4</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

    <repositories>
        <repository>
            <id>spring-releases</id>
            <url>https://repo.spring.io/libs-release</url>
        </repository>
    </repositories>

    <pluginRepositories>
        <pluginRepository>
            <id>spring-releases</id>
            <url>https://repo.spring.io/libs-release</url>
        </pluginRepository>
    </pluginRepositories>

</project>

(edit) output + exception:

2015-02-03 13:49:14.697  INFO 3515 --- [lication.main()] test.Application                         : Starting Application on desktop with PID 3515 (/home/breamec/src/test1/target/classes started by breamec in /home/breamec/src/test1)
2015-02-03 13:49:14.747  INFO 3515 --- [lication.main()] s.c.a.AnnotationConfigApplicationContext : Refreshing org.springframework.context.annotation.AnnotationConfigApplicationContext@5b525b5f: startup date [Tue Feb 03 13:49:14 GMT 2015]; root of context hierarchy
2015-02-03 13:49:15.232  INFO 3515 --- [lication.main()] o.s.b.f.xml.XmlBeanDefinitionReader      : Loading XML bean definitions from class path resource [Application.xml]
2015-02-03 13:49:16.070  INFO 3515 --- [lication.main()] o.s.j.d.DriverManagerDataSource          : Loaded JDBC driver: org.postgresql.Driver
2015-02-03 13:49:16.308  INFO 3515 --- [lication.main()] j.LocalContainerEntityManagerFactoryBean : Building JPA container EntityManagerFactory for persistence unit 'default'
2015-02-03 13:49:16.340  INFO 3515 --- [lication.main()] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [
    name: default
    ...]
2015-02-03 13:49:16.454  INFO 3515 --- [lication.main()] org.hibernate.Version                    : HHH000412: Hibernate Core {4.3.7.Final}
2015-02-03 13:49:16.457  INFO 3515 --- [lication.main()] org.hibernate.cfg.Environment            : HHH000206: hibernate.properties not found
2015-02-03 13:49:16.458  INFO 3515 --- [lication.main()] org.hibernate.cfg.Environment            : HHH000021: Bytecode provider name : javassist
2015-02-03 13:49:16.683  INFO 3515 --- [lication.main()] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {4.0.5.Final}
2015-02-03 13:49:17.202  INFO 3515 --- [lication.main()] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.PostgreSQL9Dialect
2015-02-03 13:49:17.218  INFO 3515 --- [lication.main()] o.h.e.jdbc.internal.LobCreatorBuilder    : HHH000424: Disabling contextual LOB creation as createClob() method threw error : java.lang.reflect.InvocationTargetException
2015-02-03 13:49:17.355  INFO 3515 --- [lication.main()] o.h.h.i.ast.ASTQueryTranslatorFactory    : HHH000397: Using ASTQueryTranslatorFactory
2015-02-03 13:49:18.189  INFO 3515 --- [lication.main()] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.PostgreSQL9Dialect
2015-02-03 13:49:18.190  INFO 3515 --- [lication.main()] o.h.e.jdbc.internal.LobCreatorBuilder    : HHH000424: Disabling contextual LOB creation as createClob() method threw error : java.lang.reflect.InvocationTargetException
2015-02-03 13:49:18.199  INFO 3515 --- [lication.main()] o.h.e.t.i.TransactionFactoryInitiator    : HHH000399: Using default transaction strategy (direct JDBC transactions)
2015-02-03 13:49:18.199  INFO 3515 --- [lication.main()] o.h.h.i.ast.ASTQueryTranslatorFactory    : HHH000397: Using ASTQueryTranslatorFactory
2015-02-03 13:49:18.211  INFO 3515 --- [lication.main()] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000227: Running hbm2ddl schema export
Hibernate: 
    drop table Datum cascade
2015-02-03 13:49:18.219 ERROR 3515 --- [lication.main()] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000389: Unsuccessful: drop table Datum cascade
2015-02-03 13:49:18.219 ERROR 3515 --- [lication.main()] org.hibernate.tool.hbm2ddl.SchemaExport  : ERROR: table "datum" does not exist
Hibernate: 
    drop sequence hibernate_sequence
2015-02-03 13:49:18.221 ERROR 3515 --- [lication.main()] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000389: Unsuccessful: drop sequence hibernate_sequence
2015-02-03 13:49:18.221 ERROR 3515 --- [lication.main()] org.hibernate.tool.hbm2ddl.SchemaExport  : ERROR: sequence "hibernate_sequence" does not exist
Hibernate: 
    create table Datum (
        datumId int4 not null,
        value varchar(255),
        primary key (datumId)
    )
Hibernate: 
    create sequence hibernate_sequence
2015-02-03 13:49:18.230  INFO 3515 --- [lication.main()] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000230: Schema export complete
2015-02-03 13:49:18.737  INFO 3515 --- [lication.main()] o.s.j.e.a.AnnotationMBeanExporter        : Registering beans for JMX exposure on startup
2015-02-03 13:49:18.745  INFO 3515 --- [lication.main()] test.Application                         : Started Application in 4.32 seconds (JVM running for 8.385)
2015-02-03 13:49:18.961  WARN 3515 --- [lication.main()] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42703
2015-02-03 13:49:18.961 ERROR 3515 --- [lication.main()] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: column datum0_.datum_id does not exist
  Position: 8
[WARNING] 
java.lang.reflect.InvocationTargetException
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.springframework.boot.maven.RunMojo$LaunchRunner.run(RunMojo.java:418)
    at java.lang.Thread.run(Thread.java:745)
Caused by: org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:231)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:214)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:417)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodIntercceptor.invoke(CrudMethodMetadataPostProcessor.java:122)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
    at com.sun.proxy.$Proxy51.findAll(Unknown Source)
    at test.Application.main(Application.java:20)
    ... 6 more
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:91)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2066)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1863)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839)
    at org.hibernate.loader.Loader.doQuery(Loader.java:910)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355)
    at org.hibernate.loader.Loader.doList(Loader.java:2554)
    at org.hibernate.loader.Loader.doList(Loader.java:2540)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370)
    at org.hibernate.loader.Loader.list(Loader.java:2365)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:497)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:387)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:236)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1264)
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:103)
    at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:573)
    at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:449)
    at org.hibernate.jpa.criteria.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:67)
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:289)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.executeMethodOn(RepositoryFactorySupport.java:442)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:427)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:381)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
    ... 14 more
Caused by: org.postgresql.util.PSQLException: ERROR: column datum0_.datum_id does not exist
  Position: 8
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:560)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82)
    ... 45 more

Spring's default naming will map datumId to a column called datum_id. If you wish for your column to use a different name, then just annotate it to say so: @Column(name = "datumId"). — Steve
– Steve, Commented Feb 3, 2015 at 15:21
Scratch that ... you'll probably need to make it @Column(name = "datumid"). If you use mixed case, the SpringNamingStrategy will ignore the name you provide and insert underscores whether you want them or not. — Steve
– Steve, Commented Feb 3, 2015 at 15:36

Predrag Maric · Accepted Answer · 2015-02-03 13:23:11Z

6

By default, Spring Boot uses org.springframework.boot.orm.jpa.SpringNamingStrategy.

Hibernate NamingStrategy that follows Spring recommended naming conventions.

This naming strategy just takes the field name and deduces a column name based only on that, substituting camel case with underscore. You didn't post the exception, but based on this Hibernate probably queried for datum_id column.

Related issue raised on Spring boot project

edited Feb 3, 2015 at 13:23

answered Feb 3, 2015 at 13:07

Predrag Maric

24.5k5 gold badges57 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Axiom255 Over a year ago

Sorry, missed the exception, but have added now.

Predrag Maric Over a year ago

Yes, as I thought datum_id is what Hibernate is looking for. Anyway, given that this is the default behavior, that's the reason you have to specify a naming strategy if the default is not what you need.

Zombies Over a year ago

Springboot is neat in that it brings this in automagically. But kind of annoying in that it takes excessive googling to know what it brought in and why... Not sure if it's saving time or wasting more time.

Bassem Reda Zohdy · Accepted Answer · 2015-03-10 18:37:49Z

6

SpringNamingStrategy extends ImprovedNamingStrategy

so apply the below :

I found the issue it is in ImprovedNamingStrategy if you want to solve it without details use DefaultNamingStrategy instead ImprovedNamingStrategy as below in properties file

spring.jpa.hibernate.naming-strategy=org.hibernate.cfg.DefaultNamingStrategy

Details: There are 2 methods in NamingStrategy interface propertyToColumnName and columnName, first one called if you didn't specify name in @Column and 2nd one called if you specify name in @Column.

in ImprovedNamingStrategy both change the name from camel to _ format while in DefaultNamingStrategy it is returning name as it is in 2nd method which is from my point of view the correct behavior.

edited Mar 10, 2015 at 18:37

answered Mar 10, 2015 at 18:29

Bassem Reda Zohdy

13k3 gold badges37 silver badges40 bronze badges

Comments

postalservice14 · Accepted Answer · 2016-07-06 15:11:31Z

1

Figured it out.

Use DefaultNamingStrategy and backticks in the @Column annotation.

spring.jpa.hibernate.naming-strategy=org.hibernate.cfg.DefaultNamingStrategy

and

@Column(name = "`datumId`")
private int datumId;

answered Jul 6, 2016 at 15:11

postalservice14

2,5444 gold badges26 silver badges33 bronze badges

Comments

jsheeran · Accepted Answer · 2019-12-13 08:29:41Z

1

Hibernate provides two different naming strategy whereas Spring Boot configures the physical naming strategy with SpringPhysicalNamingStrategy where all dots are replaced by underscores and camel casing is replaced by underscores and all table names are generated in lower case. For example, a USERDETAILS entity is mapped to the user_details table.

If you want to use your own custom naming strategy as implemented above, you can make following configuration in application.properties:

spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

edited Dec 13, 2019 at 8:29

jsheeran

3,0372 gold badges19 silver badges33 bronze badges

answered Dec 13, 2019 at 6:52

Imtiyaz m

211 bronze badge

Collectives™ on Stack Overflow

Spring data + hibernate uses wrong column name

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related