Manage @NamedNativeQuery and schema

Question

I have many EntityManager, one per schema that I have (I use entity-mappings file to map EMs with schemas). It works.

When I use @NamedQuery it's working like a charm but when I use @NamedNativeQuery schema is not used. I have to qualify with it SELECT foo FROM schema.table.

Is it the right behaviour ?

I think it's not possible to parameter @NamedNativeQuery to dynamically pass schema (I believe only columns can be dynamics not tables or schemas or anything else) so how can I use @NamedNativeQuery with dynamic schema please ?

Community · Accepted Answer · 2017-05-23 12:33:02Z

8

Prefix your table name with "{h-schema}", e.g.SELECT foo FROM {h-schema}table

(courtesy of getting hibernate default schema name programmatically from session factory?)

edited May 23, 2017 at 12:33

CommunityBot

11 silver badge

answered Oct 28, 2013 at 16:40

James

811 silver badge1 bronze badge

Sign up to request clarification or add additional context in comments.

1 Comment

Agustí Sánchez Over a year ago

That's a nice one. And the schema name already includes the dot "." XDD

Nayan Wadekar · Accepted Answer · 2012-12-20 07:48:54Z

2

Excerpts from documentation :

NamedNativeQuery : Specifies a named native SQL query. Query names are scoped to the persistence unit.
NamedQuery : Specifies a static, named query in the Java Persistence query language. Query names are scoped to the persistence unit.

It isn't specified directly that NamedNativeQuery is static, but both are same scoped & can't be altered afterwards & it's the desired behaviour.

Named queries are mean to be accessed by multiple modules - application wide, identified by unique name, so they are static & constant. You can try building a query string dynamically & can create a native query from it, instead of named native query.

answered Dec 20, 2012 at 7:48

Nayan Wadekar

11.6k4 gold badges52 silver badges74 bronze badges

5 Comments

Olivier J. Over a year ago

I know I can do that. So for you, it is impossible to use named native query with dynamic schema ? Ty

Nayan Wadekar Over a year ago

@OlivierJ. Yes, can't pass table/schema etc. as parameter to named query

Olivier J. Over a year ago

ok I will generate my query with string dynamically. Thank you

Darrell Teague Over a year ago

Generating any query strings dynamically can pose a security risk if the values are taken from unmanaged sources like user-input so this should be done with care. Additionally, dynamic queries cannot take advantage of the connection-pool management performance features for query and execution-plan reuse within the database. Would instead use logic to look up namedQueryX versus namedQueryY based on whatever conditions, with each NamedQuery bound to their respective persistence-units.

Nayan Wadekar Over a year ago

@DarrellTeague Yes, there is security risk if providing inputs directly into query instead of setting parameters/using named queries. It's preferred to have separate persistence unit.

Collectives™ on Stack Overflow

Manage @NamedNativeQuery and schema

2 Answers 2

1 Comment

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related