Custom query within JPA server-managed session

Question

I'm trying to execute an unrelated query (other_table) within a JTA session (server-managed) in order to do some validations, before persisting the bean EJB into DB with JPA.

Here is what I want to do (aprox):

@PersistenceUnit(unitName="DynamicDatabase")
EntityManagerFactory emf;

@TransactionAttribute(TransactionAttributeType.REQUIRED)
private long nextEntryid() {
    em = emf.createEntityManager();
    Query query = em.createQuery("select t from OTHER_TABLE t");
    // do some validation and checking
    MyTable bean = new MyTable();
    em.persist(bean);
}

However I keep getting a server error, it does not allow to interact with other database items that are not persisted:

org.apache.openjpa.persistence.ArgumentException:
An error occurred while parsing the query filter (query): The name "OTHER_TABLE" is not a recognized entity (...) Perhaps you meant MyTable, which is a close match.

<persistence-unit name="DynamicDatabase" transaction-type="JTA">
    <jta-data-source>jdbc/DB2DynamicConnection</jta-data-source>
    <class>jorge.java.dynamicdatabase.db.MyTable</class>
    </properties>
</persistence-unit>

The question is: what is the proper way to query/alter another table within the same JPA DB connection and JTA transaction?

Pretty newby about this, please be patient with me. Been working on this very long.

Edit: I don't think it's related but FYI I'm using WebSphere Liberty Profile 8.5.5.4, JSDK 8u31, EclipseEE Luna 4.4.2 for Web Dev. Going to add it to tags.

V G · Accepted Answer · 2015-03-23T08:46:01.697

1

The problem is that you have a @NamedQuery that references the entity OTHER_TABLE, and that does not exist or not marked as being an Entity (try in both Java class and persistence.xml).

If the OtherTable Java class is not an entity and if it MUST remain so, then you could use Constructors in JPQL-queries:

SELECT NEW com.domain.reports.MyReport(u.firstName, u.lastName, u.birthday) FROM User u

If you need to write plain SQL queries, then you could do it using native queries, as they are known in the JPA specification.

edited Mar 23 '15 at 08:46

answered Mar 19 '15 at 13:50

V G

18,822
6
51
89

sorry but that's the point -- I want to query something else directly that isn't (or can't be) managed by Java persistence, AND the query must be run inside the same DB transaction. – JCM Mar 19 '15 at 19:34
Your answer gave me an idea, and looking more deeply I guess that the function "javax.persistence.EntityManager.createQuery(String)" obviously requires an Entity, which means, it can't be used for something else different. I'll post my answer as soon as my code works. – JCM Mar 20 '15 at 07:38
If you really understand what an Entity is and if you are sure that `OTHER_TABLE` is not an entity, then you can take a look at [Constructors in JPQL-queries](http://en.wikibooks.org/wiki/Java_Persistence/JPQL#Constructors) – V G Mar 20 '15 at 09:25
That's a very interesting answer my friend, wow, I'll give it +1 because many people will use it for sure. However for the final solution I'd like to be more generic/universal about an "unrelated query". My real example is much more complicated, and I can't handle it from any Java objects, etc. – JCM Mar 20 '15 at 10:17
Since my real query is different (related with sequences, etc), so I have chosen the Native Query solution, and it worked great! – JCM Mar 23 '15 at 11:33

JCM · Answer 2 · 2015-03-20T10:53:35.407

Based on Andrei's suggestion, the JPQL (Java Persistence Query Language) operates on objects, not on tables. This means that em.createQuery() can't be used for universal database interaction.

To execute any SQL statement generally speaking (inside the container-managed transaction JTA), it is necessary to get the DB connection from the entity manager (it will return the persistence context unit JPA).

    // Get container's objects
    em = emf.createEntityManager();
    java.sql.Connection conn = em.unwrap(java.sql.Connection.class);

    // Run the query
    Statement sql = conn.createStatement();
    ResultSet rs = sql.executeQuery("select * from OTHER_TABLE");
    rs.next()
    (...)

    //Container-managed connection shouldn't be closed. 
    rs.close;
    sql.close;

Notice that unwrap call works for JPA but not for Hibernate (other questions already exist on this point). Notice the different language used for the SQL statement select * from than a named query JPQL select t from. Exception handling must be also controlled as usual with a } finally { clause.

That way would allow to execute a complex custom statement within server-managed transaction without using JAVA entities and I can finally sleep.

There is a betted solution then this: [native queries](https://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/Native) — V G, Mar 20 '15 at 13:01
Hello @AndreiI , could you send that comment as an answer so I can mark it as solution (green check)? — JCM, Mar 21 '15 at 11:58

Custom query within JPA server-managed session

2 Answers2