如何使用选择子句中的构造函数为多个表选定的列编写HQL JOIN查询

问题描述

我使用 Constructor（） 在Select Clause 中为多个表的选定列编写HQL JOIN查询。

我有以下实体：

实体1：NotificationObject。 java

  @Entity 
 @Table（name =notification_object）
 public class NotificationObject实现Serializable {
 
 private static final long serialVersionUID = 1L; 
 
 @Id 
 @GeneratedValue（strategy = GenerationType.IDENTITY）
 @Column（columnDefinition =INT（10）UNSIGNED）
 private Integer id; 
 
 @Column（name =entity_type_id，columnDefinition =TINYINT UNSIGNED，nullable = false）
 private short entityTypeId; 
 
 @Column（name =entity_id，columnDefinition =INT（10）UNSIGNED，nullable = false）
 private Integer entityId; 
 $ b @DateTimeFormat（pattern =yyyy-MM-dd）
 @Temporal（TemporalType.TIMESTAMP）
 @CreationTimestamp 
 @Column（name =created_on / *，nullable = false * /）
私人日期createdOn; 
 
 @OneToMany（mappedBy =notificationObject）
 private Set< Notification> notifications = new LinkedHashSet<>（）; 
 $ b $ public NotificationObject（）{} 
 public NotificationObject（Short entityTypeId，Integer entityId）{
 this.entityTypeId = entityTypeId; 
 this.entityId = entityId; 
} 
 
 // Getters and Setters 
} 
  

  @Entity 
 @Table（name = notification_change）
 public class NotificationChange implements Serializable {
 
 private static final long serialVersionUID = 1L; 
 
 @Id 
 @GeneratedValue（strategy = GenerationType.IDENTITY）
 @Column（columnDefinition =INT（10）UNSIGNED）
 private Integer id; 
 
 @ManyToOne（fetch = FetchType.LAZY）
 @JoinColumn（
 name =notification_object_id，nullable = false，
 foreignKey = @ ForeignKey（name = fk_notification_change_notification_object_noti_object_id）
）
 private NotificationObject notificationObject; 
 
 @ManyToOne（fetch = FetchType.LAZY）
 @JoinColumn（
 name =actor_id，columnDefinition =INT（10）UNSIGNED，nullable = false，
 foreignKey = @ ForeignKey（name =fk_notification_change_user_user_id）
）
私人用户actor; 
 
 public NotificationChange（）{} 
 public NotificationChange（User actor）{
 this.actor = actor; 
} 
 
 // Getters and Setters 
} 
  

  @Entity 
 @Table（name = 通知）
 public class Notification实现Serializable {
 
 private static final long serialVersionUID = 1L; 
 
 @Id 
 @GeneratedValue（strategy = GenerationType.IDENTITY）
 @Column（columnDefinition =INT（10）UNSIGNED）
 private Integer id; 
 
 @ManyToOne（fetch = FetchType.LAZY）
 @JoinColumn（
 name =notification_object_id，nullable = false，
 foreignKey = @ ForeignKey（name = fk_notification_notification_object_notification_object_id）
）
 private NotificationObject notificationObject; 
 
 @ManyToOne（fetch = FetchType.LAZY）
 @JoinColumn（
 name =notifier_id，columnDefinition =INT（10）UNSIGNED，nullable = false，
 foreignKey = @ ForeignKey（name =fk_notification_user_user_id）
）
 private用户通知程序; 
 
 @Column（name =is_seen，nullable = false）
 private boolean isSeen; 
 
 @Column（name =is_viewed，nullable = false）
 private boolean isViewed; 
 $ b $ public Notification（）{} 
 public Notification（用户通知程序，布尔isSeen，布尔isViewed）{
 this.notifier = notifier; 
 this.isSeen = isSeen; 
 this.isViewed = isViewed; 
} 
 
 // Getters and Setters 
} 
  

  @Entity 
 @Table（name = user）
 public class User实现Serializable {
 
 private static final long serialVersionUID = 1L; 
 
 @Id 
 @GeneratedValue（strategy = GenerationType.IDENTITY）
 @Column（name =user_id）
 private String user_id; 
 
 //额外字段
 
 @OneToOne（cascade = CascadeType.MERGE）
 @JoinColumn（name =emp_id，columnDefinition =INT（10）UNSIGNED ）
私人雇员雇员; 
 
 @OneToMany（mappedBy =notifier）
 private Set< Notification> notifications = new LinkedHashSet<>（）; 
 $ b $ public User（）{} 
 public User（String user_id）{
 this.user_id = user_id; 
} 
 
 // Getters and Setters 
} 
  

  @Entity 
 @Table（name = employee）
 public class Employee实现Serializable {
 
 private static final long serialVersionUID = 1L; 
 $ b $ public Employee（）{} 
 public Employee（String emp_id）{
 this.emp_id = emp_id; 
 
 $ b @Id 
 @GeneratedValue（strategy = GenerationType.IDENTITY）
 @Column（name =emp_id）
 private String emp_id; 
 
 @Column（name =first_name）
 private String first_name; 
 
 @Column（name =last_name）
 private String last_name; 
 
 //额外字段
 
 @OneToOne（mappedBy =employee）
 @JsonBackReference 
私人用户用户; 
 
 // Getters and Setters 
} 
  

DTO 1：Notify.java

  public class Notify {
 private Integer notificationObjectId，notificationId，notifierId， actorId，entityId; 
 private short entityTypeId; 
 private String notifierName，actorName，message，notificationLink; 
私人日期createdOn; 
 private boolean isSeen，isViewed; 
 
 public Notify（）{} 
 public Notify（Integer notificationObjectId，Integer notificationId，Integer notifierId，Integer actorId，
 Integer entityId，Short entityTypeId，String notifierName，String actorName，String消息，
字符串notificationLink，日期createdOn，布尔isSeen，布尔isViewed）{
 //在这里设置值
} 
 public Notify（Integer notificationObjectId，Integer notificationId，Integer notifierId，String notifierName，
 Integer actorId，String actorName，Integer entityId，Short entityTypeId，
 DateOn，boolean isSeen，boolean isViewed）{
 // Or Here 
} 
 
 // Getters and Setters 
} 
  

JOINs。

我想为实体的选定字段编写HQL JOIN查询以形成 Constructor（） 在Select Clause 通知。


查询1 / strong>

  final String GET_NOTIFICATIONS_FOR_USER = 
select new support.dto.Notify（no.id， id，Integer.parseInt（n.notifier.user_id），
 +concat（n.notifier.employee.first_name，''，n.notifier.employee.last_name），
 +Integer .parseInt（nc.actor.user_id），concat（nc.actor.employee.first_name，''，nc.actor.employee.last_name），
 +no.entityId，no.entityTypeId，no.createdOn ，n.isSeen，n.isViewed）
 +from Notification n，NotificationObject no，NotificationChange nc，User u，Employee e
 +where n.notifier.user_id =：notifierId; 
  

查询2



  final String GET_NOTIFICATIONS_FOR_USER = 
select new support.dto.Notify（no.id，n.id，Integer.parseInt（n.notifier.user_id），
 +concat（n.notifier.employee.first_name，''，n.notifier.employee.first_name），
 +Integer.parseInt（nc.actor.user_id），concat（nc.actor .employee.first_name，''，nc.actor.employee.last_name），
 +no.entityId，no.entityTypeId，no.createdOn，n.isSeen，n.isViewed）
 + from NotificationChange nc inner join nc.notificationObject no
 +inner join no.notifications n
 +where n.notifier.user_id =：notifierId; 
  

我收到以下异常




解决方案

错误告诉你t Hibernate无法找到 Notify 构造函数。

更多，不允许添加 Integer.parseInt 在你的HQL查询中。使用ResultSet中的预期类型，并从传入参数中在构造函数中进行强制转换。

I'm writing HQL JOIN query for multiple table's selected Columns using Constructor() In The Select Clause

I have following Entities:

Entity 1: NotificationObject.java

@Entity
@Table(name="notification_object")
public class NotificationObject implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue( strategy=GenerationType.IDENTITY )
    @Column( columnDefinition="INT(10) UNSIGNED" )
    private Integer id;

    @Column( name="entity_type_id", columnDefinition="TINYINT UNSIGNED", nullable=false )
    private Short entityTypeId;

    @Column( name="entity_id", columnDefinition="INT(10) UNSIGNED", nullable=false )
    private Integer entityId;

    @DateTimeFormat( pattern="yyyy-MM-dd" )
    @Temporal( TemporalType.TIMESTAMP )
    @CreationTimestamp
    @Column( name="created_on"/*, nullable=false*/ )
    private Date createdOn;

    @OneToMany( mappedBy = "notificationObject" )
    private Set<Notification> notifications = new LinkedHashSet<>();

    public NotificationObject() {}
    public NotificationObject(Short entityTypeId, Integer entityId) {
        this.entityTypeId = entityTypeId;
        this.entityId = entityId;
    }

    // Getters and Setters
}

Entity 2: NotificationChange.java

@Entity
@Table(name="notification_change")
public class NotificationChange implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(columnDefinition="INT(10) UNSIGNED")
    private Integer id;

    @ManyToOne( fetch=FetchType.LAZY )
    @JoinColumn(
            name="notification_object_id", nullable=false,
            foreignKey=@ForeignKey(name="fk_notification_change_notification_object_noti_object_id")
    )
    private NotificationObject notificationObject;

    @ManyToOne(fetch=FetchType.LAZY)
    @JoinColumn( 
            name="actor_id", columnDefinition="INT(10) UNSIGNED", nullable=false,
            foreignKey=@ForeignKey(name="fk_notification_change_user_user_id")
    )
    private User actor;

    public NotificationChange() {}
    public NotificationChange( User actor ) {
        this.actor = actor;
    }

    // Getters and Setters
}

Entity 3: Notification.java

@Entity
@Table(name="notification")
public class Notification implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue( strategy=GenerationType.IDENTITY )
    @Column( columnDefinition="INT(10) UNSIGNED" )
    private Integer id;

    @ManyToOne( fetch=FetchType.LAZY )
    @JoinColumn(
            name="notification_object_id", nullable=false,
            foreignKey=@ForeignKey(name="fk_notification_notification_object_notification_object_id")
    )
    private NotificationObject notificationObject;

    @ManyToOne( fetch=FetchType.LAZY )
    @JoinColumn(
            name="notifier_id", columnDefinition="INT(10) UNSIGNED", nullable=false,
            foreignKey=@ForeignKey(name="fk_notification_user_user_id")
    )
    private User notifier;

    @Column( name="is_seen", nullable=false )
    private boolean isSeen;

    @Column( name="is_viewed", nullable=false )
    private boolean isViewed;

    public Notification() {}
    public Notification( User notifier, boolean isSeen, boolean isViewed ) {
        this.notifier = notifier;
        this.isSeen = isSeen;
        this.isViewed = isViewed;
    }

    // Getters and Setters
}

Entity 4: User.java

@Entity
@Table(name="user")
public class User implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="user_id")
    private String user_id;

    // Extra fields

    @OneToOne(cascade=CascadeType.MERGE)
    @JoinColumn(name="emp_id", columnDefinition="INT(10) UNSIGNED")
    private Employee employee;

    @OneToMany( mappedBy="notifier" )
    private Set<Notification> notifications = new LinkedHashSet<>();

    public User() {}
    public User(String user_id) {
        this.user_id = user_id;
    }

    // Getters and Setters
}

Entity 5: Employee.java

@Entity
@Table(name="employee")
public class Employee implements Serializable {

    private static final long serialVersionUID = 1L;

    public Employee() { }
    public Employee( String emp_id ) {
        this.emp_id = emp_id;
    }

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="emp_id")
    private String emp_id;

    @Column(name="first_name")
    private String first_name;

    @Column(name="last_name")
    private String last_name;

    // Extra fields

    @OneToOne(mappedBy="employee")
    @JsonBackReference
    private User user;

    // Getters and Setters
}

DTO 1: Notify.java

public class Notify {
    private Integer notificationObjectId, notificationId, notifierId, actorId, entityId;
    private Short entityTypeId;
    private String notifierName, actorName, message, notificationLink;
    private Date createdOn;
    private boolean isSeen, isViewed;

    public Notify() {}
    public Notify ( Integer notificationObjectId, Integer notificationId, Integer notifierId, Integer actorId,
            Integer entityId, Short entityTypeId, String notifierName, String actorName, String message,
            String notificationLink, Date createdOn, boolean isSeen, boolean isViewed ) {
        // Set Values Here
    }
    public Notify (Integer notificationObjectId, Integer notificationId, Integer notifierId, String notifierName, 
            Integer actorId, String actorName, Integer entityId, Short entityTypeId, 
            Date createdOn, boolean isSeen, boolean isViewed ) {
        // Or Here
    }

    // Getters and Setters          
}

I'm weak at JOINs.
I want to write HQL JOIN query for entity's selected field to form Constructor() In The Select Clause of Notify.java DTO.
What I have tried:

Query 1

final String GET_NOTIFICATIONS_FOR_USER =
"select new support.dto.Notify ( no.id, n.id, Integer.parseInt( n.notifier.user_id ), "
+ "concat ( n.notifier.employee.first_name, ' ', n.notifier.employee.last_name ), "
+ "Integer.parseInt( nc.actor.user_id ), concat( nc.actor.employee.first_name, ' ', nc.actor.employee.last_name ), "
+ "no.entityId, no.entityTypeId, no.createdOn, n.isSeen, n.isViewed ) "
+ "from Notification n, NotificationObject no, NotificationChange nc, User u, Employee e "
+ "where n.notifier.user_id = :notifierId";

Query 2

final String GET_NOTIFICATIONS_FOR_USER =
"select new support.dto.Notify ( no.id, n.id, Integer.parseInt( n.notifier.user_id ), "
+ "concat ( n.notifier.employee.first_name, ' ', n.notifier.employee.first_name ), "
+ "Integer.parseInt( nc.actor.user_id ), concat( nc.actor.employee.first_name, ' ', nc.actor.employee.last_name ), "
+ "no.entityId, no.entityTypeId, no.createdOn, n.isSeen, n.isViewed ) "
+ "from NotificationChange nc inner join nc.notificationObject no "
+ "inner join no.notifications n "
+ "where n.notifier.user_id = :notifierId";

I'm getting following Exception

解决方案

The error tells you that Hibernate couldn't locate the Notify constructor.

More, you are not allowed to add Integer.parseInt in your HQL query. Use the expected type from the ResultSet and do the casting inside the constructor from the incoming parameter.

这篇关于如何使用选择子句中的构造函数为多个表选定的列编写HQL JOIN查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！