Reputation: 575
I've been struggling with a query in hibernate to update the state of an entity. Such entity, called PaymentRequestLink, is in a one to many relation with another entity called extraparameters
PaymentRequestType.java:
@Entity
@Table(name="payment_link")
public class PaymentRequestLink {
private Map<String, ExtraParameter<E>> extraParameters;
@Fetch(FetchMode.SELECT)
@ElementCollection(fetch=FetchType.EAGER)
@CollectionTable(name="extraparameter_payment_link",
schema=BaseEntity.DATABASE_SCHEMA, joinColumns=@JoinColumn(name="extraparameter_payment_link_id"))
@MapKeyColumn(name = "name", length=64, nullable = false)
public Map<String, ExtraParameter<String>> getExtraParameters() {
return extraParameters;
}
ExtraParameter.java:
@Embeddable
public class ExtraParameter<T extends Serializable> implements Serializable {
//...
@Column(name="extra_type", length=64, nullable=false)
@NotNull
public String getType() {
return type;
}
@Column(name="extra_value", length=255, nullable=false)
@NotNull
public T getValue() {
return value;
}
This is a data example of the extraparameter_payment_link table:
extraparameter_payment_link_id extra_type extra_value name
1 java.sql.Date 2019-01-01 EXPIRATION_DATE
The query I want to make is to update the state of the PaymentRequestLink to EXPIRED when the expiration date, which is stored in the extraparameters, is a date before to the current date. This is the query I have now:
String stateSentence = "AND state <> '";
String sqlUpdateLinkDetailed = "UPDATE PaymentRequestLink ld SET state='EXPIRED' " +
"WHERE extraParameters.EXPIRATION_DATE.value <= ? " +
stateSentence + PaymentRequestLinkState.PAID;
Calendar calendar = Calendar.getInstance();
calendar.add(Calendar.MINUTE, 1);
Timestamp now = new Timestamp(calendar.getTimeInMillis());
hibernateTemplate.bulkUpdate(sqlUpdateLinkDetailed, now);
The exception I've got is:
error processing job
org.springframework.orm.hibernate3.HibernateQueryException: could not resolve property: EXPIRATION_DATE of: component[type,value] [UPDATE
com.some.company.PaymentRequestLink ld SET state='EXPIRED' WHERE extraParameters.EXPIRATION_DATE.value <= ? AND state <> 'PAID']; nested exception is org.hibernate.QueryException: could not resolve property: EXPIRATION_DATE of: component[type,value] [UPDATE com.some.company.PaymentRequestLink ld SET state='EXPIRED' WHERE extraParameters.EXPIRATION_DATE.value <= ? AND state <> 'PAID']
Can anyone help me with this?
Upvotes: 3
Views: 1171
Reputation: 6184
Elements of indexed collections (arrays, lists, and maps) can be referred to by index in a where clause only:
String stateSentence = "AND state <> '";
String sqlUpdateLinkDetailed = "UPDATE PaymentRequestLink ld SET state='EXPIRED' " +
"WHERE extraParameters['EXPIRATION_DATE'].value <= ? " +
stateSentence + PaymentRequestLinkState.PAID;
See HQL expressions documentation for details.
Upvotes: 4