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

小编典典

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

hibernate

我正在使用在选择子句中* 为 多个表的选定列 编写HQL JOIN查询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          
}

我在JOINs上很弱。*
我想为实体的选定字段编写HQL JOIN查询，以形成 DTO的Constructor() “选择子句 ” 。我尝试过的
Notify.java *

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

我正在关注异常

org.org.hibernate.hql.internal.ast.tree.ConstructorNode.resolveConstructor（ConstructorNode.java:174）处的org.hibernate.internal.util.ReflectHelper.getConstructor（ReflectHelper.java:309）处的java.lang.NullPointerException位于org.hibernate.hql.internal的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.selectExprList（HqlSqlBaseWalker.java:2194）的antlr.HqlSqlBaseWalker.selectExpr（HqlSqlBaseWalker.java:2328）的org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query（HqlSqlBaseWalker.java:573）处的org.hibernate.hql.internal.antlr。org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement（HqlSqlBaseWalker.java:249）的HqlSqlBaseWalker.selectStatement（HqlSqlBaseWalker.java:301）org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze（HqlSqlBaseWalker.java:249）
262）在org.hibernate.engine的org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile（QueryTranslatorImpl.java:142）的org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile（QueryTranslatorImpl.java:190）
org.hibernate.engine.query.spi.HQLQueryPlan。（HQLQueryPlan.java:76）上的.query.spi.HQLQueryPlan。（HQLQueryPlan.java:115）org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan（QueryPlanCache
.java：150），位于org.hibernate.internal.AbstractSessionImpl处。getHQLQueryPlan（AbstractSessionImpl.java：298）。在org.hibernate.internal.SessionImpl.createQuery（SessionImpl.java:1821）在support.DAO.MasterDaoImpl.getNotifications（MasterDaoImpl.java:115）上的createQuery（AbstractSessionImpl.java:236）在support.service.MasterServiceImpl.getNotifications（MasterServiceImpls）
.java：158）在support.service.MasterServiceImpl $$ FastClassBySpringCGLIB $$
a355463b.invoke（）在org.springframework.cglib.proxy.MethodProxy.invoke（MethodProxy.java:204）在org.springframework.aop.framework.CglibAopProxy在org.springframework.aop.framework.ReflectiveMethodInvocation.proceed（ReflectiveMethodInvocation.java:157）处的$
CglibMethodInvocation.invokeJoinpoint（CglibAopProxy.java:717）在org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke（ExposeInvocationInterceptor。
org.springframework.aop.framework。org.springframework.aop.framework.CglibAopProxy
$
DynamicAdvisedInterceptor.intercept（CglibAopProxy.java:653）上的ReflectiveMethodInvocation.proceed（ReflectiveMethodInvocation.java:179）在support.service.MasterServiceImpl
$$ EnhancerBySpringCGLIB $
8c2s28e（）。在sun.reflect.NativeMethodAccessorImpl.invoke（本机方法）处的controller.WebSocketController.hello（WebSocketController.java:91）在sun.reflect.DelegatingMethodAccessorImpl.invoke（DelegatingMethodAccessorImpl.invoke（NativeMethodAccessorImpl.java:57）处的sun.reflect.NativeMethodAccessorImpl.invoke（NativeMethod）
org.springframework.messaging.handler的java.lang.reflect.Method.invoke（Method.java:601）的java：43）（org.springframework.messaging.handler的java.lang.reflect.Method.invoke（Method.java:601）
.invocation。org.springframework.messaging.simp.annotation.support.SimpAnnotationMethodMessageHandler.handleMatch（SimpAnnotationMethodMessageHandler.invoke（InvocableHandlerMethod.java:104）在org.springframework.messaging.handler.invocation.AbstractMethodMessageHandler.handleMatch（AbstractMethodMessageHandler.java:447）
java：443）在org.springframework.messaging.handler.invocation.AbstractMethodMessageHandler.handleMessageInternal（AbstractMethodMessageHandler.java:408）在org.springframework.messaging.handler.invocation.AbstractMethodMessageHandler.handleMessageInternal（AbstractMethodMessageHandler.java:408）在org.springframework.messaging.simp.annotation.support.SimpAnnotationMethodMessageHandler.handleMatch
org.springframework.messaging.support的.springframework.messaging.handler.invocation.AbstractMethodMessageHandler.handleMessage（AbstractMethodMessageHandler.java:346）。Java的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）

阅读 194

2020-06-20

共1个答案

小编典典

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

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

2020-06-20