Hibernate @Where annotation syntax issue

Question

I've been having issues with the Where annotation I have two tables on MySQL 8.0.18, Venues and Concerts, with this structure and fields

CREATE TABLE VENUE
(
    ID          INT          NOT NULL AUTO_INCREMENT,
    NAME        VARCHAR(220) NOT NULL,
    IMAGE       VARCHAR(240),
    ACTIVE      BIT          NOT NULL DEFAULT 0,
    COORDINATES VARCHAR(120),
    COUNTRY     VARCHAR(120) NOT NULL DEFAULT 'USA',
    CITY        VARCHAR(220) NOT NULL DEFAULT 'None',
    RATING      INT          NOT NULL DEFAULT 0,
    VERSION     INT          NOT NULL DEFAULT 0,
    PRIMARY KEY (ID)
);

CREATE TABLE CONCERT
(
    ID          INT          NOT NULL AUTO_INCREMENT,
    NAME        VARCHAR(120) NOT NULL,
    DESCRIPTION VARCHAR(120) NOT NULL,
    FESTIVAL_ID INT,
    VENUE_ID    INT,
    RATING      INT          NOT NULL DEFAULT 0,
    DATE        DATETIME,
    IMAGE       BLOB,
    VERSION     INT          NOT NULL DEFAULT 0,
    FOREIGN KEY (FESTIVAL_ID) REFERENCES FESTIVAL (ID),
    FOREIGN KEY (VENUE_ID) REFERENCES VENUE (ID),
    PRIMARY KEY (ID)
);

The Java Hibernate and Spring Data JPA configuration is as following

package com.heatmanofurioso.gigger.service.persistencehibernateimplementation.config;

import com.heatmanofurioso.gigger.service.persistencehibernateimplementation.HibernateServiceMarker;
import lombok.extern.slf4j.Slf4j;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.lookup.JndiDataSourceLookup;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;
import java.util.Properties;

@Configuration
@EnableJpaRepositories(basePackageClasses = {HibernateServiceMarker.class})
@ComponentScan(basePackageClasses = {HibernateServiceMarker.class})
@Slf4j
@EnableTransactionManagement
public class HibernateUtilConfig {

    private static final String MYSQL_DATA_SOURCE = "java:jboss/MysqlDataSource";
    private static final String HIBERNATE_ENTITIES = "com.heatmanofurioso.gigger.service.persistencehibernateimplementation.entity";

    @Bean
    public DataSource dataSource() {
        JndiDataSourceLookup jndiDataSourceLookup = new JndiDataSourceLookup();
        return jndiDataSourceLookup.getDataSource(MYSQL_DATA_SOURCE);
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
        LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();
        entityManagerFactoryBean.setDataSource(dataSource());
        entityManagerFactoryBean.setPackagesToScan(HIBERNATE_ENTITIES);
        log.info("Created entity manager successfully");
        JpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter();

        //Properties to show SQL format of tables on deploy
        Properties jpaProperties = new Properties();
        jpaProperties.put("hibernate.show_sql", true); // Mark as true to log hibernate queries
        jpaProperties.put("hibernate.format_sql", true); // Mark as true to log hibernate queries
        entityManagerFactoryBean.setJpaProperties(jpaProperties);

        entityManagerFactoryBean.setJpaVendorAdapter(jpaVendorAdapter);
        return entityManagerFactoryBean;
    }

    @Bean
    public JpaTransactionManager transactionManager(EntityManagerFactory entityManagerFactory) {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(entityManagerFactory);
        return transactionManager;
    }

    @Bean
    public PersistenceExceptionTranslationPostProcessor exceptionTranslation() {
        return new PersistenceExceptionTranslationPostProcessor();
    }
}

package com.heatmanofurioso.gigger.service.persistencehibernateimplementation.dao;

import com.heatmanofurioso.gigger.service.persistencehibernateimplementation.entity.VenueEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface VenueHibernateServiceImpl extends JpaRepository<VenueEntity, Long> {
    @Query("SELECT t FROM VenueEntity t WHERE t.name like ?1")
    List<VenueEntity> getByName(String name);

    @Query("SELECT t FROM VenueEntity t WHERE t.coordinates = ?1")
    List<VenueEntity> getByCoordinates(String coordinates);
}

package com.heatmanofurioso.gigger.service.persistencehibernateimplementation.dao;

import com.heatmanofurioso.gigger.service.persistencehibernateimplementation.entity.ConcertEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface ConcertHibernateServiceImpl extends JpaRepository<ConcertEntity, Long> {

    @Query("SELECT t FROM ConcertEntity t WHERE t.name like ?1")
    List<ConcertEntity> getByName(String name);
}

And I am successfully obtaining the following entities using Java 11 Spring JPA 5.2.0.RELEASE and Hibernate 5.4.8 deployed on a Wildfly 18.0.0.Final using the following entities

package com.heatmanofurioso.gigger.service.persistencehibernateimplementation.entity;

import org.hibernate.annotations.Where;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.persistence.Version;
import java.util.HashSet;
import java.util.Set;

@Entity
@Table(name = "VENUE")
public class VenueEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID", nullable = false)
    private Long id;

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

    @Column(name = "IMAGE", nullable = false)
    private String image;

    @Column(name = "ACTIVE", nullable = false)
    private Boolean active;

    @Column(name = "COORDINATES")
    private String coordinates;

    @Column(name = "RATING")
    private Long rating;

    @Column(name = "COUNTRY")
    private String country;

    @Column(name = "CITY")
    private String city;

    @OneToMany(mappedBy = "venue", fetch = FetchType.EAGER)
    private Set<ConcertEntity> concerts = new HashSet<>();

    @Column(name = "VERSION")
    @Version
    private Long version;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getImage() {
        return this.image;
    }

    public void setImage(String image) {
        this.image = image;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Boolean getActive() {
        return active;
    }

    public void setActive(Boolean active) {
        this.active = active;
    }

    public String getCoordinates() {
        return coordinates;
    }

    public void setCoordinates(String coordinates) {
        this.coordinates = coordinates;
    }

    public Long getRating() {
        return rating;
    }

    public void setRating(Long rating) {
        this.rating = rating;
    }

    public String getCountry() {
        return country;
    }

    public void setCountry(String country) {
        this.country = country;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

    public Set<ConcertEntity> getConcerts() {
        return concerts;
    }

    public void setConcerts(Set<ConcertEntity> concerts) {
        this.concerts = concerts;
    }

    public Long getVersion() {
        return version;
    }

    public void setVersion(Long version) {
        this.version = version;
    }
}

package com.heatmanofurioso.gigger.service.persistencehibernateimplementation.entity;

import javax.persistence.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.HashSet;
import java.util.Set;

@Entity
@Table(name = "CONCERT")
public class ConcertEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID", nullable = false)
    private Long id;

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

    @Column(name = "DESCRIPTION")
    private String description;

    @Column(name = "RATING")
    private Long rating;

    @Column(name = "DATE", nullable = false)
    private String date;

    @Column(name = "IMAGE")
    private String image;

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(
        name = "USER_CONCERT",
        joinColumns = {@JoinColumn(name = "CONCERT_ID")},
        inverseJoinColumns = {@JoinColumn(name = "USER_ID")}
    )
    private Set<UserEntity> userConcert = new HashSet<>();

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(
        name = "CONCERT_ARTIST",
        joinColumns = {@JoinColumn(name = "CONCERT_ID")},
        inverseJoinColumns = {@JoinColumn(name = "ARTIST_ID")}
    )
    private Set<ArtistEntity> concertArtist = new HashSet<>();

    @OneToOne
    @JoinColumn(name = "FESTIVAL_ID", nullable = false)
    private FestivalEntity festival;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "VENUE_ID")
    private VenueEntity venue;

    @Column(name = "VERSION")
    @Version
    private Long version;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public Long getRating() {
        return rating;
    }

    public void setRating(Long rating) {
        this.rating = rating;
    }

    public LocalDateTime getDate() {
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        return LocalDateTime.parse(date, formatter);
    }

    public void setDate(LocalDateTime date) {
        this.date = date.toString();
    }

    public String getDateString() {
        return date;
    }

    public void setDate(String date) {
        this.date = date;
    }

    public String getImage() {
        return image;
    }

    public void setImage(String image) {
        this.image = image;
    }

    public Set<UserEntity> getUserConcert() {
        return userConcert;
    }

    public void setUserConcert(Set<UserEntity> userConcert) {
        this.userConcert = userConcert;
    }

    public Set<ArtistEntity> getConcertArtist() {
        return concertArtist;
    }

    public void setConcertArtist(Set<ArtistEntity> concertArtist) {
        this.concertArtist = concertArtist;
    }

    public FestivalEntity getFestival() {
        return festival;
    }

    public void setFestival(FestivalEntity festival) {
        this.festival = festival;
    }

    public VenueEntity getVenue() {
        return venue;
    }

    public void setVenue(VenueEntity venue) {
        this.venue = venue;
    }

    public Long getVersion() {
        return version;
    }

    public void setVersion(Long version) {
        this.version = version;
    }
}

The returned dataset corresponds to the following

Now, my issue is that if I try to add the @Where annotation to my Concerts collection on my VenueEntity, like so

@OneToMany(mappedBy = "venue", fetch = FetchType.EAGER)
    private Set<ConcertEntity> concerts = new HashSet<>();

    @OneToMany(mappedBy = "venue", fetch = FetchType.EAGER)
    @Where(clause = "date < current_date")
    private Set<ConcertEntity> pastConcerts = new HashSet<>();

    @OneToMany(mappedBy = "venue", fetch = FetchType.EAGER)
    @Where(clause = "date => current_date")
    private Set<ConcertEntity> futureConcerts = new HashSet<>();

those two collections return empty datasets, even though, at the creation of the rows on the database, I am setting the value of the DATE field as current_date, so the pastConcerts collection should show results at least

Answer 1

I have the same association (literally the same FK column) mapped multiple times - but because Hibernate knows this is the same FK it considers them as the same.

Found out while debugging the SQL that Hibernate generated.

Essentially, this is not possible on the entity

Answer 2

Borh Sets are empty probably because current_date is null. Both predicates resolve to false in that case. It happens because hibernate cannot resolve a column named current_date and sets it to null.

You might work it around by using subselect, because clause is simply an sql predicate that gets pasted inside where on query creation (it depends on the database you're using):

@Where(clause = "date < (select * from current_date())")

Or shorter in some databases:

@Where(clause = "date < (select current_date())")

P.S.: In my opinion, this is not a good design. It's harder to test and makes every query concerning those sets unpredictible (same query can result in different results depending on time run).