Unable to run native JPA queries in testEntityManager container

Question

Note: I had posted this question on eclipse forums but did not receive a reply though I thought my description of the problem was sufficiently clear.

I am posting it here. It will be great if someone can help.

I have a JPA entity class that does not have a table backing it in the database. We simply created it because we had to run a query that needed to perform a join over many tables. So instead of creating a named query, we went the native query way to improve speed. That is we have a JPA Entity like:

@Entity

@SqlResultSetMapping(name = "userMapping", entities=@EntityResult(entityClass =  
User.class))

@NamednativeQuery(name = "User.query", query = "-- native query here --",   
resultSetMapping = "userMapping")

public class User {

}

Now this runs fine with an entityManager object like: em.createNamedQuery("User.query").setParameter("userParam", param).getResultList();

But while testing this with Testng inside an entitymanager container environment (Created with the EntityManagerFactory), an 0RA-942 table or view does not exist error is thrown. We tried enabling use_native_sql = true in the persistence.properties and in the SessionCustomizer.

We use eclipselink JPA.

can someone help with some feedback on this ?

Thanks

Are you testing against the same database in the container vs JSE environment? — Rick, Jul 30 '14 at 13:05
Yes. I set the persistence properties in the EntityManager(persistence-unit-name, persistence.properties); I customized the database session for this test where i set the schema to use. — bespectacled, Jul 31 '14 at 14:12

score 1 · Answer 1 · answered Jul 28 '14 at 07:41

The clue to solve your problem might be the structure of your native query.

I tested simple use case of using not mapped entity with Named Native query with Eclipselink 2.5.1 and it works great. The code I'm using is like this (Table EMPLOYEE exists in the underlying DB, table USER does not).

@Entity
public class Employee {
    @Id @GeneratedValue int id;
}

@Entity
@SqlResultSetMapping(name="userMapping", entities=@EntityResult(entityClass=User.class))
@NamedNativeQuery(name="User.query", query="SELECT id FROM \"User\"", resultSetMapping =      "userMapping")
public class User {
    @Id
    @GeneratedValue
    int id;
}

With this code named query executes successfully and produces as many results as are in EMPLOYEE table. However if I change the native query to be using USER table somehow I'm getting

SQLSyntaxErrorException: Table/View 'User' does not exist.

You might be updating your question with named query you are using and what table was reported not existing to confirm it is the case.

Thanks for the input. Apparently the solution seemed to be that before running the query, I had to run an "ALTER SESSION SET CURRENT_SCHEMA="schema". Though i set the schema for the entire session using .setTableQualifier("schema"). It din't seemed to be picked up for native queries ? — bespectacled, Jul 31 '14 at 14:19

Unable to run native JPA queries in testEntityManager container

1 Answers1