Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
Help coderanch get a
new server
by contributing to the fundraiser
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

JPA named native query

 
Greenhorn
Posts: 2
Netbeans IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hi,

I am using Eclipse Link name native query, i define it in ORM.xml file. I use DB2.

I got a problem, i must specify the schema for the query native SQL. If i remove the schema, it tells me the object has not defined yet.
The Code as below :
ORM.xml
<persistence-unit-metadata>
<persistence-unit-defaults>
<schema>DO3</schema>
</persistence-unit-defaults>
</persistence-unit-metadata>
<named-native-query name="NATIVE_QUERY" result-set-mapping="returnCustomerSetInfo">
<query>
SELECT C76.CUSTOMER_SET_ID, C76.SET_LEVEL_BENEFIT_CD, E20.CUSTOMER_SET_TYPE_LNG_NM
FROM DO3.C76_A999 C76
WHERE C76.CUSTOMER_SET_TYPE_ID = (
SELECT E20.CUSTOMER_SET_TYPE_ID FROM DO3.E20_A999 E20
WHERE E20.CUSTOMER_SET_TYPE_CD = ?typeCode )
</query>
</named-native-query>

java:

Query query = entityManager.createNamedQuery("NATIVE_QUERY");
query.setParameter("typeCode", "BN");
@SuppressWarnings("rawtypes")
List resultList = query.getResultList();

but i check lots of infomation, all of them has not specify schema in native query. Is there any way I can do without specifying a schema ?

Any comment i appreciate .

 
Ranch Hand
Posts: 553
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
When a database user connect to the database they are assigned to a schema. Which schema they are assign to is defined on the database.

If your database user is not connecting to DO3 schema, then it will need to include the schema in referencing the table.

How are you setting your schema for generating SQL in JPA? Are you setting the schema in your <table> elements, or in your defaults?
If you are setting the schema in your orm.xml anyway, what is the issue with using it your named SQL queries in the same file?
 
gregory peck
Greenhorn
Posts: 2
Netbeans IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hi james ,
yes , i am setting the schema in orm.xml, i want to use native SQL query without specifying schema in SQL just like use JPQL. i found when i remove the schema in native SQL , the sql run wrong. because the schema is not the one setting in the orm.xml , but the default .

Because the system may be deployed on different environment, i do not want to specify the schema in the native SQL. I just want to find a way to use the native SQL without specifying SCHEMA.

Now , i give up using the native SQL, continue to use JPQL. thank you.
 
expectation is the root of all heartache - shakespeare. tiny ad:
We need your help - Coderanch server fundraiser
https://coderanch.com/t/782867/Coderanch-server-fundraiser
reply
    Bookmark Topic Watch Topic
  • New Topic