CODEWITHSUNDEEP
javamysqlhibernatejpaspring-data-jpa
carlj
carlj

Reputation: 956

Spring boot JPA insert in TABLE with uppercase name with Hibernate

i have a table entity mapped as : 

@Entity
public class ItemsToRegister implements Serializable{

@Id
@Column(name = "ID_ITEM_TO_REGISTER")
@GeneratedValue(strategy = GenerationType.AUTO)
private int id;
.....

When i try to insert new record in database, the table name was translated in lowercase as : items_to_register , but my table name is ITEMS_TO_REGISTER How can i fix my problem without change MySql configuration? (my.cnf)

I have in my application.properties file : 

spring.jpa.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect
spring.jpa.hibernate.naming_strategy = org.hibernate.cfg.ImprovedNamingStrategy

Upvotes: 76

Views: 125134

Answers (9)

DDan
DDan

Reputation: 8276

If you are using yml configuration, add this:

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

Upvotes: 0

Vadim Zin4uk
Vadim Zin4uk

Reputation: 1796

Try this property:

spring:
  jpa:
    properties:
      hibernate:
        globally_quoted_identifiers: true

Upvotes: 3

Oleh Tatsiun
Oleh Tatsiun

Reputation: 919

I use H2 database in the example.

Making table name "user" with backticks will give you lowercase table name in your database.

Entity class:

enter image description here

Table name in the database:

enter image description here

P.S. You can name entity as "User"(uppercase) it will give you "user"(lowercase) anyway

Source: http://coddingbuddy.com/article/56566857/jpa-uppercase-table-names

Upvotes: 3

carlj
carlj

Reputation: 956

The solution is to add: 

spring.jpa.hibernate.naming_strategy=org.hibernate.cfg.EJB3NamingStrategy

to application.properties

Upvotes: 11

mjassani
mjassani

Reputation: 129

You can implement your own strategy and invoke it from application.properties:

spring.jpa.hibernate.naming.physical-strategy=com.proto.CustomPhysicalNamingStrategy

Bellow an example that always capitalize the first letter 

import java.io.Serializable;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.boot.model.naming.Identifier;
import org.hibernate.boot.model.naming.PhysicalNamingStrategy;
import org.hibernate.engine.jdbc.env.spi.JdbcEnvironment;

public class CustomPhysicalNamingStrategy implements PhysicalNamingStrategy, Serializable {
    /**
     * Singleton access
     */
    public static final CustomPhysicalNamingStrategy INSTANCE = new CustomPhysicalNamingStrategy();

    @Override
    public Identifier toPhysicalCatalogName(Identifier name, JdbcEnvironment context) {
        return capitalize(name);
    }

    @Override
    public Identifier toPhysicalSchemaName(Identifier name, JdbcEnvironment context) {
        return capitalize(name);
    }

    @Override
    public Identifier toPhysicalTableName(Identifier name, JdbcEnvironment context) {
        return capitalize(name);
    }

    @Override
    public Identifier toPhysicalSequenceName(Identifier name, JdbcEnvironment context) {
        return capitalize(name);
    }

    @Override
    public Identifier toPhysicalColumnName(Identifier name, JdbcEnvironment context) {
        return capitalize(name);
    }

    private Identifier capitalize(Identifier name) {
        if (name == null)
            return null;
        if (name.isQuoted())
            return name;
        String text = StringUtils.capitalize(name.getText());
        return Identifier.toIdentifier(text);
    }
}

Upvotes: 6

SekharKari
SekharKari

Reputation: 521

As @jasonleakey suggested we can consider using naming-strategy as below.

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

this tells Hibernate to generate SQL's as exactly as specified in the @Table (name=" ") or @Column(name=" "). All well.

But keep in mind - while using PhysicalNamingStrategy without @Table, @Column annotations in the entity class, hibernate generates SQL using class name and variable names. Consider the below java class

Class Employee {
   private String firstName;
   private String lastName; 
}

then the generated sql would be,

select employee0_.firstName,employee0_lastName from Employee employee0_;

Unfortunately this is not a great choice as typically we would have defined the columns in DB as FIRST_NAME and LAST_NAME and table name as EMPLOYEE. Had you not used PhysicalNamingStrategy the SQL would have been

select employee0_.first_name,employee0_last_name from employee employee0_;

so it's really a choice between the below two options.

  • Use PhysicalStrategy and explicitly define all tables names/column names in java code with @Table and @Column annotations.
    or
  • Define lowercase table name in db and let hibernate automatically generate table names/column names for us.

Upvotes: 21

jasonleakey
jasonleakey

Reputation: 2016

On hibernate 5, it would be 

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

in your application.properties file.

Upvotes: 179

John Thompson
John Thompson

Reputation: 524

You'll need to escape the table name with tics(`) to make it case sensitive. 

@Table(name = "`ITEMS_TO_REGISTER`")

Upvotes: 16

Mikk
Mikk

Reputation: 2229

You can try:

@Entity
@Table(name = "ITEMS_TO_REGISTER")
public class ItemsToRegister implements Serializable {
   ...

Upvotes: 1

Related Questions