java - Hibernate 使用子选择和连接类生成错误的 SQL 查询

Question

目前我正在开发一个具有相当扩展的数据模型的项目。如果我获取单个实体，我会收到 SQLGrammerException 并且生成的查询不是有效的 SQL。也许我正在挑战极限，但也可能我做错了什么。

实体定义如下:

实体:表单

@Entity(name = "Form")
public class Form {
    @OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @MapKey(name = "id")
    @Fetch(FetchMode.SELECT)
    private Map<Integer, FormElement> elements = new HashMap<Integer, FormElement>();
}

实体:FormElement

@Entity(name = "FormElement")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
@Inheritance(strategy = InheritanceType.JOINED)
@DisplayProperty(displayProperty1 = "name")
public abstract class FormElement  extends TranslatableIdentityIdEntity<Integer, FormElementTranslation> {

}

实体:TranslatableIdentityIdEntity

@MappedSuperclass
public abstract class TranslatableIdentityIdEntity<ID extends Serializable, TRANSLATE_ENTITY extends Translation<ID>>
        extends IdentityIdEntity<ID> implements Translatable<ID, TRANSLATE_ENTITY> {

    @org.hibernate.annotations.Cascade(org.hibernate.annotations.CascadeType.DELETE_ORPHAN)
    @OneToMany(mappedBy = "owner", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    @org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
    @MapKey(name = "language")
    @org.hibernate.annotations.Fetch(FetchMode.SUBSELECT)
    private Map<Language, TRANSLATE_ENTITY> translations = MapUtils.lazyMap(new HashMap<Language, TRANSLATE_ENTITY>(),
        new TranslationTransformer());

}

实体:FormElementText

@Entity(name = "FormElementText")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public class FormElementText extends FormElement {
}

生成的 SQL

Hibernate: 
     select
        form0_.id as id71_0_,
        form0_.version as version71_0_,
        form0_.confirmationText as confirma3_71_0_,
        form0_.dateCreated as dateCrea4_71_0_,
            form0_.description as descript5_71_0_,
        form0_.elementClass as elementC6_71_0_,
        form0_.elementIdentifier as elementI7_71_0_,
        form0_.formGrid_id as formGrid11_71_0_,
        form0_.formRendererClassName as formRend8_71_0_,
        form0_.name as name71_0_,
        form0_.showLabelsInField as showLab10_71_0_ 
    from
        showdown.Form form0_ 
    where
        form0_.id=?
Hibernate: 
  select
        context0_.name as name7_1_,
        context0_.version as version7_1_,
        context0_.groupId as groupId7_1_,
        context0_.remark as remark7_1_,
        context0_.value as value7_1_,
        contextgro1_.id as id2_0_,
        contextgro1_.version as version2_0_,
        contextgro1_.name as name2_0_ 
    from
        showdown.Context context0_ 
    left outer join
        showdown.ContextGroup contextgro1_ 
            on context0_.groupId=contextgro1_.id 
    where
        context0_.name=?
Hibernate: 
     select
        elements0_.Form_id as Form1_71_1_,
        elements0_.elements_id as elements2_1_,
        (select
            a5.id 
        from
            FormElement a5 
        where
            a5.id=elements0_.elements_id) as formula0_1_,
        formelemen1_.id as id73_0_,
        formelemen1_.version as version73_0_,
        formelemen1_.elementClass as elementC3_73_0_,
        formelemen1_.elementIdentifier as elementI4_73_0_,
        formelemen1_.elementType as elementT5_73_0_,
        formelemen1_.formElementGridPosition_id as formElem7_73_0_,
        formelemen1_.name as name73_0_,
        formelemen1_1_.fieldSize as fieldSize74_0_,
        formelemen1_1_.readOnly as readOnly74_0_,
        formelemen1_2_.columns as columns75_0_,
        formelemen1_2_.readOnly as readOnly75_0_,
        formelemen1_2_.rows as rows75_0_,
        formelemen1_4_.multipleValues as multiple1_78_0_,
        formelemen1_4_.valueSet_id as valueSet3_78_0_,
        formelemen1_5_.multipleValues as multiple1_79_0_,
        formelemen1_5_.valueSet_id as valueSet3_79_0_,
        formelemen1_6_.multipleValues as multiple1_80_0_,
        formelemen1_6_.valueSet_id as valueSet3_80_0_,
        case 
            when formelemen1_1_.id is not null then 1 
            when formelemen1_2_.id is not null then 2 
            when formelemen1_3_.id is not null then 3 
            when formelemen1_4_.id is not null then 4 
            when formelemen1_5_.id is not null then 5 
            when formelemen1_6_.id is not null then 6 
            when formelemen1_.id is not null then 0 
        end as clazz_0_ 
    from
        showdown.Form_FormElement elements0_ 
    inner join
        showdown.FormElement formelemen1_ 
            on elements0_.elements_id=formelemen1_.id 
    left outer join
        showdown.FormElementText formelemen1_1_ 
            on formelemen1_.id=formelemen1_1_.id 
    left outer join
        showdown.FormElementTextarea formelemen1_2_ 
            on formelemen1_.id=formelemen1_2_.id 
    left outer join
        showdown.FormElementSubmit formelemen1_3_ 
            on formelemen1_.id=formelemen1_3_.id 
    left outer join
        showdown.FormElementRadio formelemen1_4_ 
            on formelemen1_.id=formelemen1_4_.id 
    left outer join
        showdown.FormElementSelect formelemen1_5_ 
            on formelemen1_.id=formelemen1_5_.id 
    left outer join
        showdown.FormElementCheckbox formelemen1_6_ 
            on formelemen1_.id=formelemen1_6_.id 
    where
        elements0_.Form_id=?
Hibernate: 
select
        translatio0_.owner_id as owner6_73_2_,
        translatio0_.id as id2_,
        translatio0_.language_langId as formula9_2_,
        translatio0_.id as id84_1_,
        translatio0_.version as version84_1_,
        translatio0_.language_langId as language5_84_1_,
        translatio0_.label as label84_1_,
        translatio0_.owner_id as owner6_84_1_,
        translatio0_.tooltipMessage as tooltipM4_84_1_,
        language1_.langId as langId12_0_,
        language1_.version as version12_0_,
        language1_.directory as directory12_0_,
        language1_.isoLang as isoLang12_0_,
        language1_.language as language12_0_,
        language1_.latin as latin12_0_,
        language1_.nativeName as nativeName12_0_ 
    from
        showdown.FormElementTranslation translatio0_ 
    inner join
        showdown.Language language1_ 
            on translatio0_.language_langId=language1_.langId 
    where
        translatio0_.owner_id in (
            select
                formelemen1_.id 
            from
                FormElement a5 
            where
                a5.id=elements0_.elements_id
        ) as formula0_1_, formelemen1_.id as id73_0_, formelemen1_.version as version73_0_, formelemen1_.elementClass as elementC3_73_0_, formelemen1_.elementIdentifier as elementI4_73_0_, formelemen1_.elementType as elementT5_73_0_, formelemen1_.formElementGridPosition_id as formElem7_73_0_, formelemen1_.name as name73_0_, formelemen1_1_.fieldSize as fieldSize74_0_, formelemen1_1_.readOnly as readOnly74_0_, formelemen1_2_.columns as columns75_0_, formelemen1_2_.readOnly as readOnly75_0_, formelemen1_2_.rows as rows75_0_, formelemen1_4_.multipleValues as multiple1_78_0_, formelemen1_4_.valueSet_id as valueSet3_78_0_, formelemen1_5_.multipleValues as multiple1_79_0_, formelemen1_5_.valueSet_id as valueSet3_79_0_, formelemen1_6_.multipleValues as multiple1_80_0_, formelemen1_6_.valueSet_id as valueSet3_80_0_, case 
            when formelemen1_1_.id is not null then 1 
            when formelemen1_2_.id is not null then 2 
            when formelemen1_3_.id is not null then 3 
            when formelemen1_4_.id is not null then 4 
            when formelemen1_5_.id is not null then 5 
            when formelemen1_6_.id is not null then 6 
            when formelemen1_.id is not null then 0 
        end as clazz_0_ 
    from
        showdown.Form_FormElement elements0_ 
    inner join
        showdown.FormElement formelemen1_ 
            on elements0_.elements_id=formelemen1_.id 
    left outer join
        showdown.FormElementText formelemen1_1_ 
            on formelemen1_.id=formelemen1_1_.id 
    left outer join
        showdown.FormElementTextarea formelemen1_2_ 
            on formelemen1_.id=formelemen1_2_.id 
    left outer join
        showdown.FormElementSubmit formelemen1_3_ 
            on formelemen1_.id=formelemen1_3_.id 
    left outer join
        showdown.FormElementRadio formelemen1_4_ 
            on formelemen1_.id=formelemen1_4_.id 
    left outer join
        showdown.FormElementSelect formelemen1_5_ 
            on formelemen1_.id=formelemen1_5_.id 
    left outer join
        showdown.FormElementCheckbox formelemen1_6_ 
            on formelemen1_.id=formelemen1_6_.id 
    where
        elements0_.Form_id=?
    )
2011-10-06 07:45:25,525 [http-8080-Processor20] WARN  org.hibernate.util.JDBCExceptionReporter - SQL Error: 102, SQLState: 42W04
2011-10-06 07:45:25,525 [http-8080-Processor20] ERROR org.hibernate.util.JDBCExceptionReporter - ASA Error -131: Syntax error near 'as' on line 1
Hibernate: 

select
        translatio0_.owner_id as owner6_73_2_,
        translatio0_.id as id2_,
        translatio0_.language_langId as formula9_2_,
        translatio0_.id as id84_1_,
        translatio0_.version as version84_1_,
        translatio0_.language_langId as language5_84_1_,
        translatio0_.label as label84_1_,
        translatio0_.owner_id as owner6_84_1_,
        translatio0_.tooltipMessage as tooltipM4_84_1_,
        language1_.langId as langId12_0_,
        language1_.version as version12_0_,
        language1_.directory as directory12_0_,
        language1_.isoLang as isoLang12_0_,
        language1_.language as language12_0_,
        language1_.latin as latin12_0_,
        language1_.nativeName as nativeName12_0_ 
    from
        showdown.FormElementTranslation translatio0_ 
    inner join
        showdown.Language language1_ 
            on translatio0_.language_langId=language1_.langId 
    where
        translatio0_.owner_id in (
            select
                formelemen1_.id 
            from
                FormElement a5 
            where
                a5.id=elements0_.elements_id
        ) as formula0_1_, formelemen1_.id as id73_0_, formelemen1_.version as version73_0_, formelemen1_.elementClass as elementC3_73_0_, formelemen1_.elementIdentifier as elementI4_73_0_, formelemen1_.elementType as elementT5_73_0_, formelemen1_.formElementGridPosition_id as formElem7_73_0_, formelemen1_.name as name73_0_, formelemen1_1_.fieldSize as fieldSize74_0_, formelemen1_1_.readOnly as readOnly74_0_, formelemen1_2_.columns as columns75_0_, formelemen1_2_.readOnly as readOnly75_0_, formelemen1_2_.rows as rows75_0_, formelemen1_4_.multipleValues as multiple1_78_0_, formelemen1_4_.valueSet_id as valueSet3_78_0_, formelemen1_5_.multipleValues as multiple1_79_0_, formelemen1_5_.valueSet_id as valueSet3_79_0_, formelemen1_6_.multipleValues as multiple1_80_0_, formelemen1_6_.valueSet_id as valueSet3_80_0_, case 
            when formelemen1_1_.id is not null then 1 
            when formelemen1_2_.id is not null then 2 
            when formelemen1_3_.id is not null then 3 
            when formelemen1_4_.id is not null then 4 
            when formelemen1_5_.id is not null then 5 
            when formelemen1_6_.id is not null then 6 
            when formelemen1_.id is not null then 0 
        end as clazz_0_ 
    from
        showdown.Form_FormElement elements0_ 
    inner join
        showdown.FormElement formelemen1_ 
            on elements0_.elements_id=formelemen1_.id 
    left outer join
        showdown.FormElementText formelemen1_1_ 
            on formelemen1_.id=formelemen1_1_.id 
    left outer join
        showdown.FormElementTextarea formelemen1_2_ 
            on formelemen1_.id=formelemen1_2_.id 
    left outer join
        showdown.FormElementSubmit formelemen1_3_ 
            on formelemen1_.id=formelemen1_3_.id 
    left outer join
        showdown.FormElementRadio formelemen1_4_ 
            on formelemen1_.id=formelemen1_4_.id 
    left outer join
        showdown.FormElementSelect formelemen1_5_ 
            on formelemen1_.id=formelemen1_5_.id 
    left outer join
        showdown.FormElementCheckbox formelemen1_6_ 
            on formelemen1_.id=formelemen1_6_.id 
    where
        elements0_.Form_id=?
    )

希望有人能引导我走向正确的方向。提前致谢。

Answer 1

通过为每个用例创建特定查询来修复，以便我们可以控制所有关系的加载。