java - How to write HQL JOIN query for multiple table's selected Columns using Constructor In The Select Clause

Question

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

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.handleMes

Answer 1

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.