前端之家

  1. 首页
  2. 问答

Hibernate @Where注释语法问题

• 问答

我的Where注释一直存在问题 我在MySQL 8.0.18上有两个表，即Venues和Concerts，具有此结构和字段

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,DATE        DATETIME,IMAGE       BLOB,FOREIGN KEY (FESTIVAL_ID) REFERENCES FESTIVAL (ID),FOREIGN KEY (VENUE_ID) REFERENCES VENUE (ID),PRIMARY KEY (ID)
);

Java Hibernate和Spring Data JPA配置如下

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);
}

我将使用部署在Wildfly 18.0.0.Final上的Java 11 Spring JPA 5.2.0.RELEASE和Hibernate 5.4.8成功使用以下实体获取以下实体

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 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",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;
    }
}

返回的数据集对应于以下

现在，我的问题是，如果我尝试将@Where批注添加到VenueEntity上的Concerts集合中，就像这样


@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<>();

这两个集合返回空的数据集，尽管在数据库上创建行时，我将DATE字段的值设置为current_date，所以pastConcerts集合至少应显示结果

wuliqunaowuliqunao 回答：Hibernate @Where注释语法问题

伯尔Set是空的，可能是因为current_date是null。在这种情况下，两个谓词都解析为false。发生这种情况是因为休眠无法解析名为current_date的列并将其设置为null。

您可以使用subselect来解决此问题，因为clause只是一个SQL谓词，它会在创建查询时粘贴到where内（取决于您使用的数据库）：

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

或更短一些数据库：

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

P.S .：我认为这不是一个好的设计。测试起来比较困难，并使与这些集合有关的每个查询都无法预测（相同的查询会根据运行时间而导致不同的结果）。

,

我多次映射了相同的关联（实际上是相同的FK列），但是由于Hibernate知道这是相同的FK，因此认为它们是相同的。

在调试Hibernate生成的SQL时发现。

本质上，这在实体上是不可能的

hibernatejavajava-11spring-data-jpasql
本文链接：https://www.f2er.com/3165623.html

大家都在问