CODEWITHSUNDEEP
oracle-databaseormspring-data-jpaschema
NathanC
NathanC

Reputation: 115

How do I use Spring Data JPA(Hibernate) ORM mapping to a Oracle Tables from other Schema?

The oracle db is version 12c. I use ojdbc8 connector. I was only granted access to an account, let's call it schema "USR". Login with USR, I can see tables of another schema, let's call it schema "ADM". There is a table "TGT_TABLE" that I want to map it with JPA inside schema ADM. And under USR console, I am able to query "select * from ADM.TGT_TABLE" to get correct result. Now I write up the Entity class as:

@Data
@Entity
@Table(name = "ADM.TGT_TABLE") // or @Table(name = "TGT_TABLE") , Neither worked
public class ApiHeaderLogs {
    @Id
    @Column(name = "id")
    String id;
    ....

and my config:

spring.jpa.hibernate.ddl-auto=none

# Oracle settings
spring.datasource.url=jdbc:oracle:thin:@10.119.125.70:1540:dhtlm4
spring.datasource.username=USR
spring.datasource.password=******
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

and my test:

Optional<ApiHeaderLogsEntity> ahl = apiHeaderLogsService.findById(id);

I got error:

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
....
Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

and the SQL query in console is:

select ... from adm_tgt_table where ... //@Table(name = "ADM.TGT_TABLE")

Or

select ... from tgt_table where ... //@Table(name = "TGT_TABLE")

Clearly it does not address the table ADM.TGT_TABLE. How can I map to ADM.TGT_TABLE? My experience was mainly on MySQL, which has no account related to schema access, and my colleague already proposed a solution using JDBC directly... which I really want to avoid, please let me know if there is a proper way to handle this, thanks

Upvotes: 1

Views: 1683

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21095

You must define explicitely the table_name and the schema in the @Table annotation

Example

 @Table(name="TGT_TABLE", schema="ADM")

See the documentation in javax.persistence.Table

Ommiting the schema assumes the deafult owner of the connecting session, which leads to an error.

Neither can you pass a qualified name (ADM.TGT_TABLE) as a table name.

Upvotes: 2

Related Questions