当前位置: 首页 > 面试题库 >

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

阎星华
2023-03-14
问题内容

我正在使用 在选择子句中*多个表的选定列 编写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)


问题答案:

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

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



 类似资料:
  • 问题内容: 我正在尝试使用“。”写一个列名。没有成功 样本: 或者 任何的想法 ? 问题答案: 只需将其用方括号括起来即可使用 例如

  • 问题内容: 有没有一种方法可以在一个查询中包含多个语句(hibernate)? 这对我有用: 我在中得到了预期的结果。 但是我想要的是与它的构造器有一个新的实例。 例如这样: 或在 我也有兴趣使用DTO对象和DTO对象,但我无法读懂吗?那正确吗? 使用两个示例时,我的Spring Boot应用程序确实从错误开始。 最后我要一张地图 问题答案: 从技术上讲,根据JPQL select子句的定义,它将

  • 我正在尝试基于另一个具有lon的表创建geom表。我试图在表中创建两列,一列使用Spherical Mercator(SRID 4326,地理坐标系),另一列使用投影坐标系(SRID 3857)。以下是我的查询。 我收到以下错误 但是,如果我删除第二个select语句,它工作正常,我的意思是如果sql查询如下所示 如何使用第二个选择语句,如果它正常查询,我们可以使用从语句中选择。但是在这种情况下如

  • 数据库:Sybase Advantage 11 在我对数据进行规范化的过程中,我试图删除从以下语句中得到的结果:

  • 我相信这个sql语句可以工作,但我不知道如何用Java构建这个语句。我的算法技能不是最好的,我试着用一个for循环来处理列表,但我不能拼凑出第二个get引用。太感谢你们了!

  • 来自Teradata,我通常会创建一个包含一些变量的易失性表,我会在代码中使用这些变量。 例如。, 然后我会在SELECT WHERE子句中使用该表: 我试图在色调(Impala editor)中执行类似的操作,但遇到了一个错误: AnalysisException:第5行中的语法错误:未定义:来自表名隐藏^遇到:来自预期的:大小写、强制转换、默认值、存在、FALSE、IF、INTERVAL、NO