JPQL JOIN查询 [英] JPQL JOIN Queries

问题描述

我正在尝试使用连接创建一个非常简单的JPQL，但我没有成功。我想获取用户的最近登录日期，并给出用户标识。我使用的是Spring Data JPA + Hibernate。

我有一个包含表Activity的MySQL数据库：

<$ p
@ code> @Table（name =activity）
@SuppressWarnings（serial）
public class Activity实现Serializable {
@Id
@ GeneratedValue
私人长ID;

@Column（name =date，insertable = true，nullable = false，updatable = false）
@Temporal（TemporalType.TIMESTAMP）
private date date;

@Column（insertable = true，name =organization_id，nullable = true，updatable = true，unique = false）
@Basic（可选= true）
private Long organizationId;

@Column（insertable = true，name =operation，nullable = false，updatable = true，unique = false）
@Basic（可选= false）
private String操作;

// getters and setters
}

它包含一个包括LoginAttempt在内的其他表的数量：

  @Table（name =login_attempt）
 @SuppressWarnings（serial ）
 public class LoginAttempt implements Serializable {
 
 @Id 
 @GeneratedValue 
 private Long id; 
 
 @OneToOne（fetch = FetchType.EAGER，可选= false）
 @Basic（可选= false）
 @JoinColumn（name =activity_id，insertable = true，nullable = false，unique = false，updatable = false）
私人活动活动; 
 
 @Column（insertable = true，name =user_id，nullable = true，updatable = true，unique = false）
 @Basic（可选= true）
 private Long用户名; 
 
 @Column（insertable = true，name =user_email，nullable = true，updatable = true，unique = false）
 @Basic（可选= true）
 private String USEREMAIL; 
 
 @Column（insertable = true，name =login_successful，nullable = false，updatable = true，unique = false）
 @Basic（可选= false）
 private boolean登陆成功; 
 
 // getters and setters 
} 
  

所有其他表格参考使用名为activity_id的列的Activity。

这是带有一个方法和@Query注释的DAO。

  public interface MyDAO扩展了JpaRepository< Activity，Long> {
 
 @Query（SELECT a FROM Activity a，LoginAttempt la JOIN a.id la WHERE la.user_email =（：userId）AND a.date =（SELECT MAX（a.date）） ）
 public Activity findMostRecentLoginForUser（@Param（userId）Long userId）; 
 
} 
  

当我在@Query中选择所有语句时它的工作原理，所以我正确地连接到数据库，并能够恢复一切。我开始在查询时遇到了很多验证错误，但现在我得到了以下（部分堆栈跟踪）。

 导致：java.lang.IllegalArgumentException：验证方法的查询失败public abstract com.domain.Activity com.dao.MyDAO.findMostRecentLoginForUser（java.lang.Long）！ 
 at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery（SimpleJpaQuery.java:97）
 at org.springframework.data.jpa.repository.query.SimpleJpaQuery。< init> （SimpleJpaQuery.java:66）
 at org.springframework.data.jpa.repository.query.SimpleJpaQuery.fromQueryAnnotation（SimpleJpaQuery.java:169）
 at org.springframework.data.jpa.repository.query .JpaQueryLookupStrategy $ DeclaredQueryLookupStrategy.resolveQuery（JpaQueryLookupStrategy.java:114）
 at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy $ CreateIfNotFoundQueryLookupStrategy.resolveQuery（JpaQueryLookupStrategy.java:160）
 at org.springframework .data.jpa.repository.query.JpaQueryLookupStrategy $ AbstractQueryLookupStrategy.resolveQuery（JpaQueryLookupStrategy.java:68）
 at org.springframework.data.repository.core.support.RepositoryFactorySupport $ QueryExecutorMethodInterceptor。< init>（RepositoryFactorySupport.java ：290）在org.springframework.data.repository.core.sup上
 port.RepositoryFactorySupport.getRepository（RepositoryFactorySupport.java:158）
处org.springframework.data org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.getObject（RepositoryFactoryBeanSupport.java:162）
。 repository.core.support.RepositoryFactoryBeanSupport.getObject（RepositoryFactoryBeanSupport.java:44）
 at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean（FactoryBeanRegistrySupport.java:142）
 ... 44更多显示java.lang.NullPointerException 
在org.hibernate.hql.internal在org.hibernate.hql.internal.ast.HqlSqlWalker.createFromJoinElement（HqlSqlWalker.java:393）
：
所致。 antlr.HqlSqlBaseWalker.joinElement（HqlSqlBaseWalker.java:3645）
位于org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElement（HqlSqlBaseWalker.java:3431）
位于org.hibernate.hql.internal。 antlr.HqlSqlBaseWalker.fromElementList（HqlSqlBaseWalker.java:3309）
在org.hibernate.hql.internal.antlr.HqlSqlBaseW alker.fromClause（HqlSqlBaseWalker.java:706）
处org.hibernate.hql.internal.antlr org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query（HqlSqlBaseWalker.java:562）
。 HqlSqlBaseWalker.selectStatement（HqlSqlBaseWalker.java:299）
位于org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement（HqlSqlBaseWalker.java:247）
位于org.hibernate.hql.internal.ast。 QueryTranslatorImpl.analyze（QueryTranslatorImpl.java:250）
位于org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile（QueryTranslatorImpl.java:185）
位于org.hibernate.hql.internal.ast。 QueryTranslatorImpl.compile（QueryTranslatorImpl.java:138）
位于org.hibernate.engine.query.spi.HQLQueryPlan。< init>（HQLQueryPlan.java:105）
位于org.hibernate.engine.query .spi.HQLQueryPlan。< init>（HQLQueryPlan.java:80）
 at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan（QueryPlanCache.java:168）
 at org.hibernate。 internal.AbstractSessionImpl.getHQLQueryPlan（AbstractSessionImpl.java:221）
在o rg.hibernate.internal.AbstractSessionImpl.createQuery（AbstractSessionImpl.java:199）
处org.hibernate.ejb.AbstractEntityManagerImpl org.hibernate.internal.SessionImpl.createQuery（SessionImpl.java:1778）
。在sun.reflect.NativeMethodAccessorImpl.invoke的createQuery（AbstractEntityManagerImpl.java:291）在sun.reflect.NativeMethodAccessorImpl.invoke0 
（本机方法）
（NativeMethodAccessorImpl.java:57）
在阳光下。反射.DelegatingMethodAccessorImpl.invoke（DelegatingMethodAccessorImpl.java:43）
 at java.lang.reflect.Method.invoke（Method.java:601）
 at org.springframework.orm.jpa.ExtendedEntityManagerCreator $ ExtendedEntityManagerInvocationHandler。在com.sun.proxy中调用（ExtendedEntityManagerCreator.java:366）
。$ Proxy33.createQuery（Unknown Source）
在org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery（SimpleJpaQuery。 java：91）
 ... 54 more 
  

我的大脑被炸了，不要把我的头绕在任何一个例子上我读过的。任何帮助表示赞赏。您的查询格式不正确 - 它的子查询没有指定它从 选择的内容。 。另外，由于您有一个从 LoginAttempt 到 Activity 的直接引用，因此不需要进行任何显式连接。尝试像这样（未经测试）：

  SELECT la.activity FROM LoginAttempt la 
 WHERE la.user_email =：userId 
 AND la.activity.date =（SELECT MAX（la2.activity.date）FROM LoginAttempt la2 
 WHERE la2.user_email =：userId）
  

子查询选择给定用户的最近日期。外部查询为给定用户选择 Activity ，其日期是子查询的结果。

I'm trying to make a very simple JPQL using a join but I'm having zero success. I want to get the most recent log in date of a user, given a user id. I am using Spring Data JPA + Hibernate.

I have a MySQL database which contains the table Activity:

@Table(name = "activity")
@SuppressWarnings("serial")
public class Activity implements Serializable {
@Id
@GeneratedValue
private Long id;

@Column(name = "date", insertable = true, nullable = false, updatable = false)
@Temporal(TemporalType.TIMESTAMP)
private Date date;

@Column(insertable = true, name = "organization_id", nullable = true, updatable = true, unique = false)
@Basic(optional = true)
private Long organizationId;

@Column(insertable = true, name = "operation", nullable = false, updatable = true, unique = false)
@Basic(optional = false)
private String operation;

//getters and setters
}

It contains a number of other tables including LoginAttempt:

@Table(name = "login_attempt")
@SuppressWarnings("serial")
public class LoginAttempt implements Serializable{

@Id
@GeneratedValue
private Long id;

@OneToOne(fetch = FetchType.EAGER, optional = false)
@Basic(optional = false)
@JoinColumn(name = "activity_id", insertable = true, nullable = false, unique = false, updatable = false)
private Activity activity;

@Column(insertable = true, name = "user_id", nullable = true, updatable = true, unique = false)
@Basic(optional = true)
private Long userId;

@Column(insertable = true, name = "user_email", nullable = true, updatable = true, unique = false)
@Basic(optional = true)
private String userEmail;

@Column(insertable = true, name = "login_successful", nullable = false, updatable = true, unique = false)
@Basic(optional = false)
private boolean loginSuccessful;

//getters and setters
}

All other tables reference Activity using a column called "activity_id".

This is my DAO with one method and the @Query annotation.

public interface MyDAO extends JpaRepository<Activity, Long> {

@Query("SELECT a FROM Activity a, LoginAttempt la JOIN a.id la WHERE la.user_email = (:userId) AND a.date = (SELECT MAX(a.date))")
public Activity findMostRecentLoginForUser(@Param("userId") Long userId);

}

When I have a simple select all statement in the @Query it works, so I'm connecting to the database correctly and am able to get everything back. I was getting a lot of validation errors on my query at first but now I'm getting the following (partial stacktrace).

Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract com.domain.Activity com.dao.MyDAO.findMostRecentLoginForUser(java.lang.Long)!
at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:97)
at org.springframework.data.jpa.repository.query.SimpleJpaQuery.<init>(SimpleJpaQuery.java:66)
at org.springframework.data.jpa.repository.query.SimpleJpaQuery.fromQueryAnnotation(SimpleJpaQuery.java:169)
at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$DeclaredQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:114)
at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:160)
at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:68)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.<init>(RepositoryFactorySupport.java:290)
at org.springframework.data.repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:158)
at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.getObject(RepositoryFactoryBeanSupport.java:162)
at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.getObject(RepositoryFactoryBeanSupport.java:44)
at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:142)
... 44 more
Caused by: java.lang.NullPointerException
at org.hibernate.hql.internal.ast.HqlSqlWalker.createFromJoinElement(HqlSqlWalker.java:393)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.joinElement(HqlSqlBaseWalker.java:3645)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3431)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:3309)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:706)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:562)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:299)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:247)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:250)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:185)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:138)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:105)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:168)
at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:221)
at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:199)
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1778)
at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:291)
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.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:366)
at com.sun.proxy.$Proxy33.createQuery(Unknown Source)
at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:91)
... 54 more

My brain is fried and I can't get my head round any of the examples I've read. Any help is appreciated. Thanks.

解决方案

Your query is malformed--its subquery doesn't specify what it's selecting from. Also, since you have a direct reference from LoginAttempt to Activity, you don't need to do any explicit joins. Try something like this (untested):

SELECT la.activity FROM LoginAttempt la 
WHERE la.user_email = :userId
AND la.activity.date = (SELECT MAX(la2.activity.date) FROM LoginAttempt la2
                        WHERE la2.user_email = :userId)

The subquery selects the most-recent date for the given user. The outer query selects the Activity for the given user and whose date is the result of the subquery.

这篇关于JPQL JOIN查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！