5

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 ?

Olivier J.
  • 3,115
  • 11
  • 48
  • 71

2 Answers2

7

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?)

Community
  • 1
  • 1
James
  • 71
  • 1
  • 1
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.

Nayan Wadekar
  • 11,444
  • 4
  • 50
  • 73
  • I know I can do that. So for you, it is impossible to use named native query with dynamic schema ? Ty – Olivier J. Dec 20 '12 at 09:40
  • 1
    @OlivierJ. Yes, can't pass table/schema etc. as parameter to named query – Nayan Wadekar Dec 20 '12 at 10:08
  • ok I will generate my query with string dynamically. Thank you – Olivier J. Dec 20 '12 at 10:56
  • 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. – Darrell Teague Mar 13 '13 at 16:14
  • @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. – Nayan Wadekar Mar 14 '13 at 05:47