CODEWITHSUNDEEP
javasqleclipseoracle-databasejpa
Tuomas Toivonen
Tuomas Toivonen

Reputation: 23502

JPA: How to generate entities from another schema?

I have database with tables created for user A. User B is granted CRUD access to those tables. In SQL queries, user B could refer to the tables on the schema A using fully qualified name A.TABLE. The application will use user B to operate on those tables.

However, when I try to generate JPA entity classes for the application using user B connection, the JPA wizard doesn't let me select schema A, where tables resides. I have ensured the grants are correct by using a fully qualified table name A.FOO in the SQL query on user B connection.

enter image description here

I have tried to use synonyms as follows

create or replace synonym B.FOO FOR A.FOO;

But tables still can't be found by the JPA tool. I wouldn't like to generate tables using user A connection either. How to correctly generate entities from different schema?

Upvotes: 0

Views: 722

Answers (1)

Littlefoot
Littlefoot

Reputation: 142993

I don't know whether the following "trick" (basically, a cuckoo in the nest) will work or not, but that's what I used once (not in JPA, though): suppose you start from scratch (i.e. remove that synonym). Then create an empty (where 1 = 2) table (in schema B) for table owned by A, using CTAS:

create table foo as select * from a.foo where 1 = 2;

Now try to do what you're doing - the JPA wizard should see the FOO table as it now belongs to B. Once you're done,

drop table foo;
create synonym foo for a.foo;

and see whether everything works as expected. Good luck!

Upvotes: 1

Related Questions