aspose file tools*
The moose likes Object Relational Mapping and the fly likes JPA named native query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "JPA named native query " Watch "JPA named native query " New topic
Author

JPA named native query

gregory peck
Greenhorn

Joined: Sep 12, 2011
Posts: 2

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 .

James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
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?

TopLink : EclipseLink : Book:Java Persistence : Blog:Java Persistence Performance
gregory peck
Greenhorn

Joined: Sep 12, 2011
Posts: 2

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JPA named native query
 
Similar Threads
@NamedQuery - Where it should be
JPA/Hibernate 3.5.5 in WebSphere Application Server 7 Exception
getting error when i use named-native-query> in orm.xml
EJB deployment error
@NamedQuery : does it have to be declared on an @Entity ?