Hibernate uses wrong column name despite all anotations

Question

I am trying to configure simple database using Spring JPA, and I encountered, which seems simular to some questions here, but solution to this aren`t working.

I am trying to insert following POJO into PostgreSQL database :

@Entity
@Table(name = "students", schema = "public")
public class Student {

    @Id
    @Column(name = "ID")
    private UUID id = UUID.randomUUID();
    @Column(name = "First_Name")
    private String firstName;
    @Column(name = "Last_Name")
    private String lastName;
    @Column(name = "Age")
    private Integer age;
    @Column(name = "Passport_Number")
    private Integer passNumber;
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "Group_ID", referencedColumnName = "ID")
    private Group group;

    public UUID getId() {
        return id;
    }

    public void setId(@NonNull UUID id) { this.id = id; }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Integer getPassNumber() {
        return passNumber;
    }

    public void setPassNumber(Integer passNumber) {
        this.passNumber = passNumber;
    }

    public Group getGroup() { return group; }

    public void setGroup(Group group) { this.group = group; }

    public void setUpdate(@NonNull Student student) {
        setFirstName(student.getFirstName());
        setLastName(student.getLastName());
        setAge(student.getAge());
        setPassNumber(student.getPassNumber());
    }
}

Table of my database is created using following sql :

CREATE TABLE students (
    "ID" UUID,
    "First_Name" VARCHAR(50),
    "Last_Name" VARCHAR(50),
    "Age" INT,
    "Passport_Number" INT,
    "Group_ID" UUID,
    UNIQUE("First_Name", "Last_Name"),
    UNIQUE("Passport_Number"),
    FOREIGN KEY("Group_ID") REFERENCES groups("ID"),
    PRIMARY KEY ("ID")
);

When trying to insert new Student, Hibernate builds following sql (message in console) :

Hibernate: 
    select
        student0_.ID as id1_0_0_,
        student0_.Age as age2_0_0_,
        student0_.First_Name as first_na3_0_0_,
        student0_.Group_ID as group_id6_0_0_,
        student0_.Last_Name as last_nam4_0_0_,
        student0_.Passport_Number as passport5_0_0_ 
    from
        public.students student0_ 
    where
        student0_.ID=?

And here is the problem. There is no student0_, and so psql off course gives org.postgresql.util.PSQLException: ERROR: column student0_.id does not exist

I tryied also to give name variable to @Entity specifically, but to no effect. Also I tried to change naming stratagies : implicit naming strategy, improved naming strategy, physical naming strategy, implicit legacy naming strategy. And I already tried to change dialect, but also nothing.

My current hibernate properties are :

#Hibernate
hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect
hibernate.show_sql = true
hibernate.format_sql = true
hibernate.hbm2ddl.auto = none
hibernate.ejb.naming_strategy = org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

Also this is Configuration that I use :

@Configuration
@EnableTransactionManagement
@PropertySource({"classpath:dao.properties"})
@EnableJpaRepositories(basePackages = {"com.university.dao"})
public class ApplicationConfig {

    @Autowired
    private Environment environment;

    public ApplicationConfig() {
        super();
    }

    @Bean
    public DataSource dataSource() {
        final BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName(Preconditions.checkNotNull(environment.getProperty("jdbc.driverClassName")));
        dataSource.setUrl(Preconditions.checkNotNull(environment.getProperty("jdbc.url")));
        dataSource.setUsername(Preconditions.checkNotNull(environment.getProperty("jdbc.user")));
        dataSource.setPassword(Preconditions.checkNotNull(environment.getProperty("jdbc.password")));

        return dataSource;
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource dataSource,
                                                                       Environment environment) {
        LocalContainerEntityManagerFactoryBean entityManagerFactory = new LocalContainerEntityManagerFactoryBean();
        entityManagerFactory.setDataSource(dataSource);
        entityManagerFactory.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
        entityManagerFactory.setJpaProperties(getHibernateProperties());
        entityManagerFactory.setPackagesToScan("com.university.domain");

        return entityManagerFactory;
    }

    @Bean
    public PlatformTransactionManager transactionManager() {
        final JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(entityManagerFactory(dataSource(),environment).getObject());

        return transactionManager;
    }

    @Bean
    public PersistenceExceptionTranslationPostProcessor exceptionTranslation() {
        return new PersistenceExceptionTranslationPostProcessor();
    }

    final Properties getHibernateProperties() {
        final Properties hibernateProperties = new Properties();
        hibernateProperties.setProperty("hibernate.hbm2ddl.auto", environment.getProperty("hibernate.hbm2ddl.auto"));
        hibernateProperties.setProperty("hibernate.dialect", environment.getProperty("hibernate.dialect"));
        hibernateProperties.setProperty("hibernate.show_sql", environment.getProperty("hibernate.show_sql"));
        hibernateProperties.setProperty("hibernate.format_sql", environment.getProperty("hibernate.format_sql"));
        hibernateProperties.setProperty("hibernate.ejb.naming_strategy", environment.getProperty("hibernate.ejb.naming_strategy"));

        return hibernateProperties;
    }
}

As for versions, I am using Spring JPA 2.3.1.Release and Hibernate 5+, here is full maven pom file :

<?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>
    <parent>
        <artifactId>spring-boot-starter-parent</artifactId>
        <groupId>org.springframework.boot</groupId>
        <version>2.3.1.RELEASE</version>
        <relativePath/>
    </parent>
    <groupId>com.example</groupId>
    <artifactId>university</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.build.reporting.outputEncoding>UTF-8</project.build.reporting.outputEncoding>
        <java.version>1.8</java.version>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
        <junit-jupiter.version>5.6.1</junit-jupiter.version>
        <junit.version>4.13</junit.version>
        <org.springframework.version>5.2.7.RELEASE</org.springframework.version>
        <org.springframework.boot.version>2.3.1.RELEASE</org.springframework.boot.version>
        <org.hibernate.validator.version>6.1.5.Final</org.hibernate.validator.version>
        <org.apache.tomcat.version>9.0.36</org.apache.tomcat.version>
        <com.vaadin.version>16.0.0</com.vaadin.version>
        <org.junit.jupiter.version>5.6.2</org.junit.jupiter.version>
    </properties>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-surefire-plugin</artifactId>
                <version>3.0.0-M4</version>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-deploy-plugin</artifactId>
                <version>3.0.0-M1</version>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-assembly-plugin</artifactId>
                <version>3.2.0</version>
                <executions>
                    <execution>
                        <phase>package</phase>
                        <goals>
                            <goal>single</goal>
                        </goals>
                    </execution>
                </executions>
                <configuration>
                    <archive>
                        <manifest>
                            <addClasspath>true</addClasspath>
                            <classpathPrefix>lib/</classpathPrefix>
                            <mainClass>com.university.Main</mainClass>
                        </manifest>
                    </archive>
                    <descriptorRefs>
                        <descriptorRef>jar-with-dependencies</descriptorRef>
                    </descriptorRefs>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>${org.springframework.boot.version}</version>
            </plugin>
            <plugin>
                <groupId>com.vaadin</groupId>
                <artifactId>vaadin-maven-plugin</artifactId>
                <version>${com.vaadin.version}</version>
            </plugin>
        </plugins>
    </build>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>com.vaadin</groupId>
                <artifactId>vaadin-bom</artifactId>
                <version>${com.vaadin.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
            <version>${org.springframework.boot.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <version>${org.springframework.boot.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>${org.springframework.boot.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${org.springframework.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
            <version>${org.springframework.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <version>${org.springframework.boot.version}</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-validator</artifactId>
            <version>${org.hibernate.validator.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.tomcat</groupId>
            <artifactId>tomcat-dbcp</artifactId>
            <version>${org.apache.tomcat.version}</version>
        </dependency>
        <dependency>
            <groupId>com.vaadin</groupId>
            <artifactId>vaadin-spring-boot-starter</artifactId>
            <version>${com.vaadin.version}</version>
        </dependency>
        <dependency>
            <groupId>com.vaadin</groupId>
            <artifactId>vaadin</artifactId>
            <version>${com.vaadin.version}</version>
        </dependency>
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>29.0-jre</version>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.2.12.jre7</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.5</version>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>1.4.200</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-api</artifactId>
            <version>${org.junit.jupiter.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-engine</artifactId>
            <version>${org.junit.jupiter.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.junit.vintage</groupId>
            <artifactId>junit-vintage-engine</artifactId>
            <version>${org.junit.jupiter.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.github.stefanbirkner</groupId>
            <artifactId>system-rules</artifactId>
            <version>1.19.0</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.powermock</groupId>
            <artifactId>powermock-reflect</artifactId>
            <version>2.0.7</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
</project>

How may I remove this error without explicitly(manually) rewriting hibernate queries in configuration (so hibernate would use correct column/table names automatically)?

Regardless if your answer/comment helps or not, thank you very much for your effort:)

Try to use naming conventions. Your ID column should be named id. Your columns should be named using lower case and underscores. So id, age, first_name, ... The error is about the id column : ERROR: column student0_.id does not exist — Fabien, Jul 02 '20 at 12:58

Kayaman · Accepted Answer · 2020-07-02T15:57:46.117

2

There is a student0_, it's the table alias declared in from student student0_. It's the column that's not being found since it wasn't escaped properly, so student0_.ID really is treated as student0_.id.

Using case sensitive names isn't recommended in Postgres, whereas it's common with other databases. So instead of "First_name", just use first_name. Otherwise you need to escape the names everywhere e.g.

@Column(name = "\"First_Name\"")

and that's not pretty, and all other Postgres users will frown upon your database schema.

edited Jul 02 '20 at 15:57

answered Jul 02 '20 at 12:59

Kayaman

72,141
5
83
121

If you don't use double quotes identifiers then Postgres is case insensitive (it will translate then to lower case). The effect is you can write queries with mixed case if you believe that is more readable. So writing First_Name is the same as writing first_name. You just need to keep in mind that when Postgres uses the name, in error messages or other output, all will appear in lower case. – Belayer Jul 02 '20 at 21:45

Hibernate uses wrong column name despite all anotations

1 Answers1