如何使用选择子句中的构造函数为多个表的选定列编写 HQL JOIN 查询 [英] How to write HQL JOIN query for multiple table's selected Columns using Constructor In The Select Clause

问题描述

我正在使用 Constructor() 在选择子句中

我有以下实体:

实体 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
}

实体 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
}

实体 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
}

实体 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
}

实体 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          
}

我不擅长 JOIN.
我想为实体的选定字段编写 HQL JOIN 查询以形成 Constructor() In The Select Clause of Notify.java DTO.
我尝试过的:

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:

查询 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";

查询 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";

我正在关注异常

java.lang.NullPointerException在 org.hibernate.internal.util.ReflectHelper.getConstructor(ReflectHelper.java:309)在 org.hibernate.hql.internal.ast.tree.ConstructorNode.resolveConstructor(ConstructorNode.java:174)在 org.hibernate.hql.internal.ast.tree.ConstructorNode.prepare(ConstructorNode.java:144)在 org.hibernate.hql.internal.ast.HqlSqlWalker.processConstructor(HqlSqlWalker.java:1091)在 org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectExpr(HqlSqlBaseWalker.java:2328)在 org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectExprList(HqlSqlBaseWalker.java:2194)在 org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectClause(HqlSqlBaseWalker.java:1476)在 org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:573)在 org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:301)在 org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:249)在 org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:262)在 org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:190)在 org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:142)在 org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:115)在 org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:76)在 org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:150)在 org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:298)在 org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:236)在 org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1821)在 support.DAO.MasterDaoImpl.getNotifications(MasterDaoImpl.java:115)在 support.service.MasterServiceImpl.getNotifications(MasterServiceImpl.java:158)在 support.service.MasterServiceImpl$$FastClassBySpringCGLIB$$a355463b.invoke()在 org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)在 org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:717)在 org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)在 org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)在 org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)在 org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:653)在 support.service.MasterServiceImpl$$EnhancerBySpringCGLIB$$8c2728e2.getNotifications()在 support.controller.WebSocketController.hello(WebSocketController.java:91)在 sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)在 sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)在 sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)在 java.lang.reflect.Method.invoke(Method.java:601)在 org.springframework.messaging.handler.invocation.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:185)在 org.springframework.messaging.handler.invocation.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:104)在 org.springframework.messaging.handler.invocation.AbstractMethodMessageHandler.handleMatch(AbstractMethodMessageHandler.java:447)在 org.springframework.messaging.simp.annotation.support.SimpAnnotationMethodMessageHandler.handleMatch(SimpAnnotationMethodMessageHandler.java:443)在 org.springframework.messaging.simp.annotation.support.SimpAnnotationMethodMessageHandler.handleMatch(SimpAnnotationMethodMessageHandler.java:82)在 org.springframework.messaging.handler.invocation.AbstractMethodMessageHandler.handleMessageInternal(AbstractMethodMessageHandler.java:408)在 org.springframework.messaging.handler.invocation.AbstractMethodMessageHandler.handleMessage(AbstractMethodMessageHandler.java:346)在 org.springframework.messaging.support.ExecutorSubscribableChannel$SendTask.run(ExecutorSubscribableChannel.java:135)在 java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)在 java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)在 java.lang.Thread.run(Thread.java:722)

java.lang.NullPointerException at org.hibernate.internal.util.ReflectHelper.getConstructor(ReflectHelper.java:309) at org.hibernate.hql.internal.ast.tree.ConstructorNode.resolveConstructor(ConstructorNode.java:174) at org.hibernate.hql.internal.ast.tree.ConstructorNode.prepare(ConstructorNode.java:144) at org.hibernate.hql.internal.ast.HqlSqlWalker.processConstructor(HqlSqlWalker.java:1091) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectExpr(HqlSqlBaseWalker.java:2328) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectExprList(HqlSqlBaseWalker.java:2194) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectClause(HqlSqlBaseWalker.java:1476) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:573) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:301) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:249) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:262) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:190) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:142) at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:115) at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:76) at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:150) at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:298) at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:236) at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1821) at support.DAO.MasterDaoImpl.getNotifications(MasterDaoImpl.java:115) at support.service.MasterServiceImpl.getNotifications(MasterServiceImpl.java:158) at support.service.MasterServiceImpl$$FastClassBySpringCGLIB$$a355463b.invoke() at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:717) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:653) at support.service.MasterServiceImpl$$EnhancerBySpringCGLIB$$8c2728e2.getNotifications() at support.controller.WebSocketController.hello(WebSocketController.java:91) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:601) at org.springframework.messaging.handler.invocation.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:185) at org.springframework.messaging.handler.invocation.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:104) at org.springframework.messaging.handler.invocation.AbstractMethodMessageHandler.handleMatch(AbstractMethodMessageHandler.java:447) at org.springframework.messaging.simp.annotation.support.SimpAnnotationMethodMessageHandler.handleMatch(SimpAnnotationMethodMessageHandler.java:443) at org.springframework.messaging.simp.annotation.support.SimpAnnotationMethodMessageHandler.handleMatch(SimpAnnotationMethodMessageHandler.java:82) at org.springframework.messaging.handler.invocation.AbstractMethodMessageHandler.handleMessageInternal(AbstractMethodMessageHandler.java:408) at org.springframework.messaging.handler.invocation.AbstractMethodMessageHandler.handleMessage(AbstractMethodMessageHandler.java:346) at org.springframework.messaging.support.ExecutorSubscribableChannel$SendTask.run(ExecutorSubscribableChannel.java:135) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603) at java.lang.Thread.run(Thread.java:722)

推荐答案

该错误告诉您 Hibernate 无法找到 Notify 构造函数.

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

此外，您不能在 HQL 查询中添加 Integer.parseInt.使用 ResultSet 中的预期类型，并在构造函数内部从传入参数进行转换.

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 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！