Hibernate Spring Data Jpa

Question

I have a use case requiring an IN clause to be applied for two tables.

The two tables are not related by a foreign key constraint nor are values same for any of the columns in either so to do the mapping, the query makes use of CONCAT.

The native query therefore, looks something like this:

SELECT A.requiredColumn from A, B WHERE colA LIKE CONCAT(colB, 'FOR_COMPARISON');

To do a join like this in Spring data JPA, I am not sure that there is a way to do explicit mapping since columns are not exactly "equal". These are the threads I have studied:

Selecting from Multiple Tables in Spring Data

Joining two table entities in Spring Data JPA

I applied the Spring data JPA equivalent of IN clause for the below native:

SELECT A.requiredColumn 
from A 
where colA in (
    select CONCAT(colB, 'FOR_COMPARISON') 
    from B);

as:

SELECT a 
from A 
WHERE colA in (
    select concat(
        trim(colB), 'FOR_COMPARISON')
        from  B
    );

However, this resulted in:

QuerySyntaxException: expecting CLOSE, found 'from' near line 1, column 134

Are there any thoughts to approach this problem?

Thanks @jonathan.ihm, that is copy/paste error. Syntax is good. — barsakus, Sep 18 '17 at 18:01
Then please update the question with the actual statements you used. Because the error looks just as what one would expect from the missing parens. — Jens Schauder, Sep 19 '17 at 05:43
@JensSchauder agreed, I have now updated the query to reflect the correct syntax, which was already in place. Apologies for the confusion. — barsakus, Sep 19 '17 at 21:14

score -1 · Answer 1 · edited Sep 19 '17 at 05:45

-1

Another option I explored was usage of NamedQuery. However, seems like I was overcomplicating things.

Fixed the JPQL like this:

select p 
from EntityA p 
WHERE p.colA IN (
    select concat(trim(i.colB), '_APPEND_FOR_EQUALITY') 
    from EntityB i 
    WHERE i.totallyDifferentCol = :inputMe
)

Did not need to "map" the two entities since there doesn't exist one.

edited Sep 19 '17 at 05:45

Jens Schauder

77,657
34
181
348

answered Sep 18 '17 at 19:47

barsakus

77
1
9

Hibernate Spring Data Jpa

1 Answers1