Hibernate - JPA generates duplicate tables with same name in different case

Question

I am relearning Hibernate and JPA. I am using Spring Boot, Gradle and Postgres for my environment. I have a set of domain objects that access a Postgres database. Two objects do not use annotation and use a JDBCTemplate class for database operation. The other set of domain objects use JPA annotations. The JPA set of objects have relationships mapped out using the JPA annotations. When I run my unit tests for checking the database entities for the JDBC Template objects, all is fine. When I check the database, I discovered I have duplicate entities for those domain objects using annotation.

I have reviewed my annotations and setup of the project and have been unsuccessful in locating the my error.

Here is my class definition:

import javax.persistence.*;
import java.io.Serializable;
import java.sql.Timestamp;

@Entity(name="AcceptanceCriteria")
@Table(name="accCriteria_tbl", schema="ia_req_changes")
@SequenceGenerator(name="AcceptCritera_SEQ_GEN", schema="ia_req_changes", sequenceName = "accCriteria_tbl_accCri_id_seq", initialValue = 1, allocationSize = 1)
public class AcceptanceCriteria implements Serializable{

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator="AcceptCritera_SEQ_GEN")
    @Column(name="accCri_id", updatable = false)
    private Long accCri_id;

    @Column(name="accAction")
    private String accAction;
    @Column(name="accControl")
    private String accControl;
    @Column(name="accAccptCriteria")
    private String accAccptCriteria;
    @Column(name="lastModified")
    private Timestamp lastModified;

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "FK_funcReq_id", referencedColumnName = "funcReq_id")
    private FunctionalRequirement functionalReq;


    public AcceptanceCriteria() {
    }

Here is a look at the table from Postgres. The noticeable difference is the table I created with the DDL is the one with the uppercase table name.

Any suggestions or recommendations would be greatly appreciated.

Thanks,

Russ

Answer 1

In Postgres it is possible to create tables (and columns) with case sensitive names by double quotes. For examples, create a table

create table some_case (id bigint);

Try to create it again

create table Some_Case (id bigint);

It results into

ERROR: relation "some_case" already exists

but with double quotes.

create table "Some_Case" (id bigint);

it works and you will end up in a sitution that you now have. I guess that the table having uppercase letters has been deliberately created this way ot it works like this in your JDBC templates? See this related post: Are PostgreSQL column names case-sensitive?

When declaring table name in JPAs @Table annotation it always drops to lowercase. To handle this case there might be tweaks like this post describes Spring boot JPA insert in TABLE with uppercase name with Hibernate

However the best thing to do - if possible - would be to follow the "de facto" convention, all lowercase. In other words to copy data from uppercase table to lowercase table, dropping the upperacase table and perhaps checking the JDBC template classes if they need any modifications because of this.

Searching Stack Overflow provides also other solutions.

Adding escaped double quotest to the "@Table" annotation might also work, like

@Table(name="\"accCriteria_tbl\""

See this answer. Still might not be the best fix in a long term.