Query utilizing JPQL does not work as nativeQuery

Question

I have the following sql query which works as expected

Query query = entityManager.createNativeQuery("" + "SELECT d.id FROM document d \n" + "INNER JOIN users_documents ud \n" + "ON ud.document_id = d.id\n" + "WHERE ( d.document_state_id=2 OR d.document_state_id=3 OR d.document_state_id=1)\n" + "AND d.title LIKE \"%abc%\" AND ud.user_id=4 AND ud.sharing_type_id=1\n" + "OR (d.document_state_id=1 AND d.title LIKE \"%abc%\" AND ud.user_id=4 AND ud.sharing_type_id=2);");

And I have this JPQL which DOES not work, it returns nothing always.

 TypedQuery<Long> q1 = entityManager.createQuery("Select d.id FROM Document d " + "INNER JOIN d.documentsForUsers ud" + " WHERE ((d.documentState.id=2 OR d.documentState.id=3 OR d.documentState.id=1)" + " AND (d.title LIKE :value2 AND ud.user.id=:id AND ud.sharingType.id=2))" + " OR (d.documentState.id=1 AND d.title LIKE :value3 AND ud.user.id=:id2 AND ud.sharingType.id=2)" , Long.class); q1.setParameter("value2", "%"+name+"%"); q1.setParameter("id", userId); q1.setParameter("value3", "%"+name+"%"); q1.setParameter("id2", userId);

How can I rewrite that nativeSQL query to JPQL to make it work properly?

If you completely remove the WHERE clause from the JPQL query, do you get any results back? — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 22, 2019 at 10:49

Tim Biegeleisen · Accepted Answer · 2019-08-22 11:03:48Z

This answer mainly corrects some parentheses typos, but appreciate that your WHERE clause in the JPQL query is not the same as in the native. Applying the same balancing of parentheses to the JPQL query gives:

String sql = "SELECT d.id FROM Document d INNER JOIN d.documentsForUsers ud " + "WHERE d.document_state_id IN (1,2,3) AND " + "d.title LIKE :value2 AND ud.user_id = :id AND ud.sharing_type_id = 1 OR " + "(d.document_state_id = 1 AND d.title LIKE :value3 AND ud.user_id = :id2 AND ud.sharing_type_id = 2)";

I don't know exactly what logic you are trying to use in the WHERE clause, but the above at least stays true to what is being used in the native query.

I suspect that you might be intending this version:

String sql = "SELECT d.id FROM Document d INNER JOIN d.documentsForUsers ud " + "WHERE (d.document_state_id IN (1,2,3) AND " + "d.title LIKE :value2 AND ud.user_id = :id AND ud.sharing_type_id = 1) OR " + "(d.document_state_id = 1 AND d.title LIKE :value3 AND ud.user_id = :id2 AND ud.sharing_type_id = 2)";

Collectives™ on Stack Overflow

Query utilizing JPQL does not work as nativeQuery

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related