Hibernate SQLGrammarException in a Spring Data DSL Query

Question

I should make a query that select all the transaction made by all the terminals that a partner has: the partner is selected with the criteria that in his terminal has one specific terminal.

I receive this Exception from a Hibernate query:

nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet

This is the query:

@Query(value = "SELECT T FROM Transaction as T WHERE T.terminal IN (select P.terminals FROM Partner P WHERE :terminal IN (P.terminals))  AND T.transactionShopCloseAt IS NULL")
public List <Transaction> getAllTransactionsWithoutClosureForTerminal(@Param("terminal")Terminal terminal);

These are the involved classes:

TRANSACTION:

@Entity
@Table(name = "TRANSACTION")
@GenericGenerator(name = "pk_id_generator", strategy = "uuid2")
public class Transaction extends Identifiable<String, Long> {

@Column(name = "SHOP_CLOSURE_AT")
@Temporal(TemporalType.TIMESTAMP)
private Date transactionShopCloseAt;

@ManyToOne
@JoinColumn(name = "TERMINAL_ID", referencedColumnName = "ID")
private Terminal terminal;
}

PARTNER

@Entity
@Table(name = "PARTNER")
@GenericGenerator(name = "pk_id_generator", strategy = "uuid2")
public class Partner extends Identifiable<String, Long> {

@NotEmpty
@Column(name = "NAME", nullable = false)
private String name;

@OneToMany(mappedBy = "owner")
private Set<Terminal> terminals = new HashSet<>();

TERMINAL

@Entity
@Table(name = "TERMINAL")
@GenericGenerator(name = "pk_id_generator", strategy = "uuid2")
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)    
public class Terminal extends Identifiable<String, Long> {

@NotNull
@Column(name = "TERMINAL_ID", nullable = false, length = 16)
private String terminalID;

@NotNull
@Column(name = "TERMINAL_HW_ID", nullable = false, length = 64)
private String terminalHWID;

@ManyToOne //(cascade= CascadeType.ALL)
@JoinColumn(name = "PARTNER_ID", referencedColumnName = "ID")
private Partner owner;

A MAPPED SUPERCLASS IDENTIFIABLE:

@MappedSuperclass
public abstract class Identifiable<I extends Serializable, V>  {

@Id
@GeneratedValue(generator = "pk_id_generator")
@Column(name = "ID", nullable = false, length = 36)
private I ID;

public I getID() {
    return ID;
}

Do you have any idea what can be and how should be done the HQuery? Thank you in advnce!

Exception:

org.apache.cxf.interceptor.Fault: org.hibernate.exception.SQLGrammarException: could not extract ResultSet; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Caused by: org.springframework.orm.jpa.JpaSystemException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123) 
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
Position: 683
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
at sun.reflect.GeneratedMethodAccessor213.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at     org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$StatementHandler.invoke(AbstractJdbc23PooledConnection.java:453)
at com.sun.proxy.$Proxy18.executeQuery(Unknown Source)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:80)
... 128 more

Answer 1

WHERE :terminal IN (P.terminals)

that is invalid. Replace it with

WHERE :terminal member of P.terminals

AFAIK,

T.terminal IN (select P.terminals FROM Partner P

is also invalid. Replace it with

T.terminal.id in (select terminal.id from Partner P inner join P.terminals terminal