Spring Data JPA - entity name invalid identifier

Question

I am implementing a simple CRUD controller but I have got some issues with JPA/Hibernate and Spring Data JPA framework. Repositories seem to work fine unless I use an entity mapped to a table with a name that contains an underscore.

ProfileItem.java

@Entity @Table(name = "PROFILE_ITEM") @SequenceGenerator(name = "SequenceGenerator", sequenceName = "SEQ_PROFILE_ITEM", initialValue = 1, allocationSize = 1) public class ProfileItem implements Serializable { private static final long serialVersionUID = ApplicationConst.DEFAULT_SERIAL_VERSION_UID; @Id @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SequenceGenerator") private Long id; @Column(name = "USER_ID") private String uid; private String context; private String application; @Column(name = "CREATED_ON") @Temporal(value = TemporalType.DATE) private Date createdOn; @Column(name = "MODIFIED_ON") @Temporal(value = TemporalType.DATE) private Date modifiedOn; private Long version; @OneToMany(mappedBy = "profile", fetch = FetchType.EAGER, cascade = CascadeType.ALL) private List<ProfileItemData> profileItemData; // getters and setters ... }

ProfileItemDao.java

public interface ProfileItemDao extends Repository<ProfileItem, Long> { public ProfileItem findById(Long id); }

When I try to invoke the interface method I get the following exception:

Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "PROFILEITE0_"."USER_ID": invalid identifier

Using

@Query("SELECT p FROM ProfileItem p WHERE p.id = :id") public ProfileItem findById(@Param("id") Long id);

did not help either.

I have recreated the scenario for a table with a single word name and everything worked alright. It seems like Spring is trying to force some naming strategy.

Is there any way abandon it? Every solution I have found was tied to Spring Boot which I do not use.

Thank you in advance. I can provide some additional info if necessary.

As far as I know Spring has problems when you name a attribute with underscore but I do not know if the same error happens when you have a column name with underscore. — Victor Viola
– Victor Viola, Commented Feb 17, 2016 at 10:16
"PROFILEITE0_" looks like an alias given by the ORM. I agree with @Jens looks more like the column does not exist, as it is excplicitly mentioned in the error. — Frank
– Frank, Commented Feb 17, 2016 at 10:18
Extend CrudRepository remove your findById method and use findOne. Also is there REALLY a column named USER_ID make sure the casing matches! — M. Deinum
– M. Deinum, Commented Feb 17, 2016 at 10:18
without looking at the SQL invoked you can make whatever supposition you like about the origin of "PROFILEITE0_". Inspecting the SQL invoked for that exception should be step 1 in debugging and then work backwards as to why it created that — Neil Stockton
– Neil Stockton, Commented Feb 17, 2016 at 13:29

tkralik · Accepted Answer · 2016-02-19 13:04:49Z

I have found the answer yesterday. Seems like you guys were right all along. I have added the following line in order to see the actual hibernate SQL queries

persistence.xml

<persistence-unit name="BPM" transaction-type="${persistence-unit.transaction-type}"> <!-- snip --> <properties> <!-- snip --> <property name="hibernate.show_sql" value="true" /> <!-- snip --> </properties> </persistence-unit>

and

repositoryContext.xml (one of the config files on classpath)

<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"> <!-- snip --> <property name="jpaProperties"> <props> <!-- snip --> <prop key="hibernate.show_sql">true</prop> <!-- snip --> </props> </property> </bean>

When I looked into the logs I realised that hibernate used the same alias PROFILEITE0_ for both PROFILE_ITEM and PROFILE_ITEM_DATA queries when it fetched the list of related entities. So I checked the entity and DB schema for PROFILE_ITEM_DATA and realised that the name of one of the columns in the DB and in the entity did not match, just like you guys said.

In the DB, the name of the column used to be USER_ID. Then I renamed it to ID_PROFILE_DATA but forgot to reflect the change in the entity.

Thank you for your time and responses. They have shown me the right way.

Collectives™ on Stack Overflow

Spring Data JPA - entity name invalid identifier

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related