代码之家  ›  专栏  ›  技术社区  ›  Edy Bourne

有人能告诉我们为什么这个JPA条件查询在执行(休眠)时会生成一个无效的SQL语句,以及如何修复它吗?

  •  0
  • Edy Bourne  · 技术社区  · 14 年前

    我很难构造一个条件查询来从id=2的角色实体获取“权限”属性。permission属性是set类型,因此我正在创建一个join并从中进行选择,但查询失败,语法异常报告无效:“where子句中的未知列'2l'”

    生成错误的条件查询是通过以下方式生成的:

    EntityManager entityManager = getEntityManager();
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Object> criteriaQuery = criteriaBuilder.createQuery();
    
    Class<?> queryScopeClass = temp.pack.commons.user.Role.class;
    
    Root<?> from = criteriaQuery.from(queryScopeClass);
    
    Path<?> idAttrPath = from.get("id");
    // also tried criteriaBuilder.equal(attributePath, new Long(2))
    Predicate predicate = criteriaBuilder.equal(attributePath, criteriaBuilder.literal(new Long(2)))
    criteriaQuery.where(predicate);
    
    
    Path<?> attributePath = from.get("permissions");
    PluralAttributePath<?> pluralAttrPath = (PluralAttributePath<?>)attributePath;
    PluralAttribute<?, ?, ?> pluralAttr = pluralAttrPath.getAttribute();
    
    
    Join<?, ?> join = from.join((SetAttribute<Object,?>)pluralAttr);
    
    TypedQuery<Object> typedQuery = entityManager.createQuery(criteriaQuery.select(join));
    return (List<P>)typedQuery.getResultList();
    

    当我执行该行以实际返回结果时,会引发以下异常:

    javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
        at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1235)
        at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1168)
        at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:250)
        at org.hibernate.ejb.criteria.CriteriaQueryCompiler$3.getResultList(CriteriaQueryCompiler.java:260)
        at temp.pack.dao.impl.DefaultDAOService.getProperties(DefaultDAOService.java:628)
    ...
    Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
        at org.hibernate.loader.Loader.doList(Loader.java:2452)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2192)
        at org.hibernate.loader.Loader.list(Loader.java:2187)
        at org.hibernate.hql.classic.QueryTranslatorImpl.list(QueryTranslatorImpl.java:936)
        at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
        at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1258)
        at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
        at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:241)
        ... 20 more
    Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column '2L' in 'where clause'
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
        at java.lang.reflect.Constructor.newInstance(Unknown Source)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
        at com.mysql.jdbc.Util.getInstance(Util.java:384)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3562)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3494)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1960)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2114)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2696)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2105)
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2264)
        at com.jolbox.bonecp.PreparedStatementHandle.executeQuery(PreparedStatementHandle.java:179)
        at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
        at org.hibernate.loader.Loader.getResultSet(Loader.java:1869)
        at org.hibernate.loader.Loader.doQuery(Loader.java:718)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
        at org.hibernate.loader.Loader.doList(Loader.java:2449)
        ... 27 more
    

    角色和权限类已经用JPA和一些Hibernate注释进行了映射,如下所示:

    public abstract class Role implements Serializable {
    
     /**
      * The id of this role. Internal use only.
      * 
      * @since 1.0
      */
     @Id @GeneratedValue
     protected long id;
    
     /**
      * Set of permissions granted to this role.
      * 
      * @since 1.0
      */
     @OneToMany(cascade = { CascadeType.PERSIST, CascadeType.MERGE }, mappedBy="sourceRole")
     protected Set<Permission> permissions = new HashSet<Permission>();
    
    ...
    
    }
    
    public class Permission implements Serializable {
     private static final long serialVersionUID = 1L;
    
     /**
      * The id of this permission. Used internally for persistence.
      * 
      * @since 1.0
      */
     @Id @GeneratedValue
     @Column(name = "PERMISSION_ID")
     protected long id;
    
     /**
      * The group to which the owner of this permission is being granted permission to.
      * 
      * @since 1.0
      */
     @ManyToOne(cascade = { CascadeType.PERSIST, CascadeType.MERGE })
     @JoinColumn(name = "TARGET_ROLE_ID")
     @ForeignKey(name = "FK_TARGET_GROUP_PERMISSION_ID",
       inverseName = "FK_PERMISSION_ID_TARGET_GROUP")
     protected Group targetGroup;
    
     /**
      * The role that has been granted this permission.
      * 
      * @since 1.0
      */
     @ManyToOne(cascade = { CascadeType.PERSIST, CascadeType.MERGE })
     @JoinColumn(name = "SOURCE_ROLE_ID")
     @ForeignKey(name = "FK_SOURCE_GROUP", inverseName = "FK_GROUP_PERMISSIONS")
     private Role sourceRole;
    
    ...
    
    }
    

    这个条件查询有什么问题?

    我期望对typedquery.getResultList()的调用只返回一个元素的集合列表:id=2的角色的权限对象集合。这是试图从id=2的对象角色中选择(并初始化)仅“权限”集合。

    我不熟悉标准查询,很难找到问题所在。

    跟进: 下面是Hibernate试图执行的查询:

    select permission1_.PERMISSION_ID as PERMISSION1_12_,
        permission1_.IS_REQUIRED as IS2_12_,
        permission1_.SOURCE_ROLE_ID as SOURCE3_12_,
        permission1_.TARGET_ROLE_ID as TARGET4_12_
    from (
            select ROLE_ID,
            NAME,
            DESCRIPTION,
            IS_ACTION,
            LABEL,
            null as FIRST_NAME,
            null as LAST_NAME,
            null as PASSWORD_HASH,
            1 as clazz_ from GROUPS
        union
            select ROLE_ID,
                NAME,
                null as DESCRIPTION,
                null as IS_ACTION,
                null as LABEL,
                FIRST_NAME,
                LAST_NAME,
                PASSWORD_HASH,
                2 as clazz_ from USERS
        )
    role0_ inner join PERMISSIONS permission1_ on role0_.ROLE_ID=permission1_.SOURCE_ROLE_ID
        where (role0_.ROLE_ID=2L )
    

    显然,2L的问题在于,从MySQL的角度来看,2L实际上是一个字符串或列,因为它上面有一个L,并且没有被引用。

    这是冬眠中的虫子吗?在我看来是这样的。我不应该出现在生成的SQL查询中…

    有人知道这是怎么回事吗?在我看来,这将是一个主要的错误,不能忽视其他人,所以我假设有一个很好的变化,我正在做一些错误的事情。

    谢谢您!!

    爱德华多

    2 回复  |  直到 14 年前
        1
  •  0
  •   M.J.    14 年前

    您可以尝试对用于创建联接条件的两个表使用别名。这也可能是个问题,因为Hibernate只为hql、s和实体加载创建别名,但条件取决于用户创建别名。

        2
  •  0
  •   Edy Bourne    14 年前

    通过将ID属性数据类型从long替换为long,解决了这个问题。这个简单的更改使得Hibernate开始在查询中放置没有L的2,并且没有发生更多的失败。