0

In spring boot, I create tables of the database using the Code-First approach. then while running the application, the result for one of tables shows this error:

WARN  o.h.t.s.i.ExceptionHandlerLoggedImpl.handleException - GenerationTarget encountered exception accepting command : Error executing DDL "alter table statistic add date timestamp" via JDBC Statement
org.hibernate.tool.schema.spi.CommandAcceptanceExcepti
on: Error executing DDL "alter table statistic add date timestamp" via JDBC Statement]

oracle.jdbc.OracleDatabaseException: ORA-00904: : invalid identifier

The class entity is as follow:

@Entity
@Table(name = "statistic")
public class Statistic {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String title;
    private Long value;
    private Date date;
    private String unit;


    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public Long getValue() {
        return value;
    }

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

    public Date getDate() {
        return date;
    }

    public void setDate(Date date) {
        this.date = date;
    }

    public String getUnit() {
        return unit;
    }

    public void setUnit(String unit) {
        this.unit = unit;
    }
}

Does anyone know where is the problem?

2
  • Add @Temporal(TemporalType.TIMESTAMP) for Date datatype. like @Temporal(TemporalType.TIMESTAMP) private Date date Commented Jun 28, 2021 at 11:04
  • Does this answer your question? Java-Oracle - Unable to create specific table Commented Jun 28, 2021 at 20:41

2 Answers 2

3

This is wrong:

alter table statistic add date timestamp
                          ----
                          this

date is a reserved word for Oracle datatype. Change column name to something else, e.g. datum, c_date, statistic_date, ... and then run

alter table statistic add datum timestamp
Sign up to request clarification or add additional context in comments.

2 Comments

thanks a lot, right, it was a reserved keyword error, I changed the field name and the problem solved!
The most popular reserved words are USER and DATE.
0

If you have this issue and that solutions do not work for you (like me) please try to set up in properties:

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

I will try to give you the big picture. Table in Oracle does have a column in which has been used the keyword and its name is DATE. I've found 3 ways to solve it but none of them does not work for me. Those approaches such as:

  1. Manual escaping by the double quote like ""DATE""
  2. Manual escaping using the Hibernate-specific back-tick character like "'DATE'"
  3. spring.jpa.properties.hibernate.globally_quoted_identifiers=true This settings leads to issues in other tables so it does not work for me.. maybe there is a specific case.

The problem was with the building names of columns with lower case. Hibernate builds query using the pattern like <alias_table>_."date" - in my case - but Oracle has returned an error. Query was sucessufuly executed on the SQLDeveloper when I change "date" to "DATE". So the problem was the case insensitive for hibernate but it did matter for Oracle. Properties of Physical strategy allows hibernate to build the query with column name the same like in annotation - it works. I do not know that this case might occur in other databse than Oracle 12 but Oracle does have it.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.