Hibernate and Spring DataJPA can not handle Oracle table name with special character?

Question

When I used Spring datajpa and Hibenate to read Oracle, I met an error, I'll desc this:

The Oracle table name includes special character(double quotes), so, I have to use SELECT * FROM "Graph" WHERE "ID"=1 .
So, when I use hibernate to write a entity class, I have to point its special name.

Here is the ddl which is used to create "Graph" table:

CREATE TABLE "ATLASCOPCO_TOOLSNET"."Graph" (
"ID" NUMBER(19) NOT NULL ,
"ResultID" NUMBER(19) NOT NULL ,
"GraphTypeID" NUMBER(19) NOT NULL ,
"SampleTime" BINARY_FLOAT NOT NULL ,
"AngleOffset" BINARY_FLOAT NOT NULL ,
"GraphValues" BLOB NOT NULL ,
"AngleFactor" BINARY_DOUBLE NULL ,
"TorqueFactor" BINARY_DOUBLE NULL ,
"StartTime" BINARY_FLOAT NULL ,
"EndTime" BINARY_FLOAT NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

When I used the normal table name like this:

@Entity
@Table(name = "Graph")
@Getter
@Setter
public class Graph {
    @Id
    @Column(name = "ID\"")
    private Long id;

    @Column(name = "ResultID")
    private Long resultId;

    @Column(name = "GraphTypeID")
    private Long graphTypeID;

    @Column(name = "SampleTime")
    private BINARY_FLOAT sampleTime;

    @Column(name = "AngleOffset")
    private BINARY_FLOAT angleOffset;

    @Column(name = "GraphValue")
    private byte[] graphValue;

    @Column(name = "AngleFactor")
    private BINARY_DOUBLE angleFactor;

    @Column(name = "TorqueFactor")
    private BINARY_DOUBLE torqueFactor;

    @Column(name = "StartTime")
    private BINARY_FLOAT startTime;

    @Column(name = "EndTime")
    private BINARY_FLOAT endTime;
}

I got error:Caused by: java.sql.SQLSyntaxErrorException: ORA-00972: identifier is too long

When I used this:

@Entity
@Table(name = "\"Graph\"")
@Getter
@Setter
public class Graph {
    @Id
    @Column(name = "\"ID\"\"")
    private Long id;

    @Column(name = "\"ResultID\"")
    private Long resultId;

    @Column(name = "\"GraphTypeID\"")
    private Long graphTypeID;

    @Column(name = "\"SampleTime\"")
    private BINARY_FLOAT sampleTime;

    @Column(name = "\"AngleOffset\"")
    private BINARY_FLOAT angleOffset;

    @Column(name = "\"GraphValue\"")
    private byte[] graphValue;

    @Column(name = "\"AngleFactor\"")
    private BINARY_DOUBLE angleFactor;

    @Column(name = "\"TorqueFactor\"")
    private BINARY_DOUBLE torqueFactor;

    @Column(name = "\"StartTime\"")
    private BINARY_FLOAT startTime;

    @Column(name = "\"EndTime\"")
    private BINARY_FLOAT endTime;
}

I got Caused by: java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

Your query does equal to `SELECT * FROM Graph WHERE ID=1` – Anton N Sep 23 '16 at 11:38 — Anton N, Sep 23 '16 at 11:38
See http://stackoverflow.com/a/13568581 – Drunix Oct 08 '16 at 17:48 — Drunix, Oct 08 '16 at 17:48

score 0 · Answer 1 · answered Sep 23 '16 at 11:33

0

Remove the special character \" in column names, it is causing the issue. JPA and Oracle driver will handle all correctly.

You can omit the ", ANSI SQL does work too.

CREATE TABLE NEW_TABLE (ID NUMBER(19) NOT NULL , NAME VARCHAR2(19) NOT NULL);

answered Sep 23 '16 at 11:33

Anton N

2,317
24
28

Rohit Gaikwad · Answer 2 · 2016-09-23T12:20:11.603

Let's Say you have a Column in a table whose name is "id" as shown below:

Then to use the special character (") you need to add an escape sequence as below:

    @Column(name="`\"id\"`")
    private int id;

The result will be a column with name as ("id").
Please find the below updated Graph POJO class:

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table (name="`\"Graph\"`")
@Getter
@Setter
public class Graph {
@Id
@Column (name="`\"ID\"`")
private Long id;

@Column (name="`\"ResultID\"`")
private Long resultId;

@Column(name="`\"GraphTypeID\"`")
private Long graphTypeID;

@Column (name="`\"SampleTime\"`")
private BINARY_FLOAT sampleTime; 

@Column (name="`\"AngleOffset\"`")
private BINARY_FLOAT angleOffset;

@Column (name="`\"GraphValue\"`")
private byte[] graphValue;

@Column (name="`\"AngleFactor\"`")
private BINARY_DOUBLE angleFactor;

@Column (name="`\"TorqueFactor\"`")
private BINARY_DOUBLE torqueFactor;

@Column (name="`\"StartTime\"`")
private BINARY_FLOAT startTime;

@Column (name="`\"EndTime\"`")
private BINARY_FLOAT endTime;
}

To retrieve the data from your Graph table use below code in Hibernate:

Query query=session.createSQLQuery("SELECT * FROM `\" Graph \"` WHERE `\"ID\"`=1 ");

Hope this helps you.

Hibernate and Spring DataJPA can not handle Oracle table name with special character?

2 Answers2