JPA named native query

[gregory peck]

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]

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]

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.