mysql - HibernateCriteria 用 OR where 子句左连接

Question

我们的一个查询遇到问题，该查询返回内部联接而不是左联接。该查询旨在检索用户具有显式访问权限的所有项目(用户所属的用户组之一定义为项目“用户”用户组)或隐式操作(项目没有定义“用户”用户组) .

相关代码:

    // HibernateCriteria is a custom class that has junction, conjunction, disjunction objects (the ones from Hibernate)
    ICriteria criteriaInProjectUserGroup = new HibernateCriteria();
    criteriaInProjectUserGroup.addEqualTo(Project.USERGROUP+"." +UserGroup.USERGROUPASSOCIATIONS+"."+UserGroupAssociation.USEROID, user.getOid());
    ICriteria criteriaNoProjectUserGroup = CriteriaFactory.createCriteria();
    criteriaNoProjectUserGroup.addIsNull(Project.USERGROUP); 
    /*The join (criteriaIn...) must be added to the simple is null criteria (criteriaNo...),
     * to result in a right outer join, otherwise an inner join is created by ojb which returns 
     * an empty result set */
    criteriaNoProjectUserGroup.addOrCriteria(criteriaInProjectUserGroup);
    IQuery query = QueryFactory.createQueryByCriteria(ProjectBean.class, criteriaNoProjectUserGroup);        
    Collection projects = broker.getCollectionByQuery(query);

addEqualTo 定义为:

public void addEqualTo(String attribute, Object value) {
    if (value!=null && value instanceof String && containsWildcards((String)value)) {
        addLike(attribute, value);
    }
    else {
        attribute = createAliasIfNecessary(attribute);
        conjunction.add(eq(attribute, value));
    }
}

addIsNull 定义为:

public void addIsNull(String attribute) {
    attribute = createAliasIfNecessary(attribute);
    conjunction.add(isNull(attribute));
}

addOrCriteria 定义为:

public void addOrCriteria(ICriteria criteria) {
    if(criteria instanceof HibernateCriteria) {
        HibernateCriteria subCriteria = (HibernateCriteria) criteria;
        Junction subJunction = subCriteria.resolveJunction();
        if(disjunction == null ) {
            disjunction = disjunction();
        }
        disjunction.add(subJunction);
        aliasNameSet.addAll(subCriteria.aliasNameSet);
    }
}

resolveJunction 定义为:

private Junction resolveJunction() {
    if(junction == null) {
        if(disjunction == null) { // no 'or' subcriteria have been added
            junction = conjunction; // then the main junction is simply the conjunction
        } else {
            // disjunction != null means an 'or' subcriteria has been already added.
            // then the main junction is disjunction with a conjunction as an operand
            junction = disjunction;
            junction.add(conjunction);
        }
    }
    return junction;
}

getCollectionByQuery 将条件提取到 org.hibernate.criterion.DetachedCriteria 对象中:

    try {
        DetachedCriteria detachedCriteria = DetachedCriteria.forClass(clazz);
        // create aliases if necessary
        Set<String> createdAliasSet = new HashSet<String>();
        for(String aliasAssociationPath: aliasNameSet) {
            String[] nestedPropertiesArray = aliasAssociationPath.split("\\.");
            String previousNestedProperty = null;
            for(String nestedProperty: nestedPropertiesArray) {
                String currentAliasName = 
                    (previousNestedProperty == null ? nestedProperty : previousNestedProperty + "_" + nestedProperty);
                if(!createdAliasSet.contains(currentAliasName)) {
                    String currentAssociationPath = 
                        (previousNestedProperty == null ? nestedProperty : previousNestedProperty + "." + nestedProperty);
                    detachedCriteria.createAlias(currentAssociationPath, currentAliasName);
                    createdAliasSet.add(currentAliasName);
                }
                previousNestedProperty = currentAliasName;
            }
        }
        detachedCriteria.add(resolveJunction());
        return detachedCriteria;
    } catch (HibernateException e) {
        throw new PersistenceBrokerException(e);
    } 

然后将其转换为 org.hibernate.Criteria 对象并在该函数上调用 Criteria.list()。

生成的 Mysql 查询是:

select
    this_.oid as oid1_41_2_,
    this_.archived as archived2_41_2_,
    this_.archivedDateTime as archived3_41_2_,
    this_.version as version4_41_2_,
    this_.adminGroupOid as adminGro5_41_2_,
    this_.buildScript as buildScr6_41_2_,
    this_.buildToolType as buildToo7_41_2_,
    this_.deployScript as deploySc8_41_2_,
    this_.deployToolType as deployTo9_41_2_,
    this_.description as descrip10_41_2_,
    this_.issueTrackingSystemOid as issueTr11_41_2_,
    this_.locked as locked12_41_2_,
    this_.name as name13_41_2_,
    this_.projectType as project14_41_2_,
    this_.testScript as testScr15_41_2_,
    this_.testToolType as testToo16_41_2_,
    this_.userGroupOid as userGro17_41_2_,
    this_.vcrOid as vcrOid18_41_2_,
    this_.vcrProjectName as vcrProj19_41_2_,
    usergroup1_.oid as oid1_65_0_,
    usergroup1_.archived as archived2_65_0_,
    usergroup1_.archivedDateTime as archived3_65_0_,
    usergroup1_.version as version4_65_0_,
    usergroup1_.description as descript5_65_0_,
    usergroup1_.name as name6_65_0_,
    usergroup1_.type as type7_65_0_,
    usergroup_2_.oid as oid1_58_1_,
    usergroup_2_.archived as archived2_58_1_,
    usergroup_2_.archivedDateTime as archived3_58_1_,
    usergroup_2_.version as version4_58_1_,
    usergroup_2_.scmUserOid as scmUserO6_58_1_,
    usergroup_2_.userGroupOid as userGrou5_58_1_ 
from
    .PROJECT this_ 
inner join
    .USERGROUP usergroup1_ 
        on this_.userGroupOid=usergroup1_.oid 
inner join
    .SCMUSER_USERGROUP usergroup_2_ 
        on usergroup1_.oid=usergroup_2_.userGroupOid 
where
    (
        (
            usergroup_2_.scmUserOid=3
        ) 
        or (
            this_.userGroupOid is null
        )
    )

问题是，这仅返回具有相关 UserGroup 的项目，因为它是内部联接。这意味着我们无法获取未定义用户组的项目。

我发现解决此问题的方法是对两个连接使用左外连接，但我不知道如何进行此操作。问题是我无法触及 addX 方法，因为这些方法在服务类中绝对无处不在。

如何调整此代码，以便生成的 SQL 使用左外连接而不是内连接？

Answer 1

经过更多研究，我发现解决此问题的正确方法是向 createAlias() 添加 joinType:

 detachedCriteria.createAlias(currentAssociationPath, currentAliasName, JoinType.LEFT_OUTER_JOIN);

但是，这是一个非常复杂的修复，因为当前没有我们使用 joinType 参数的地方，这意味着我需要与更高级的开发人员进一步讨论这个问题。现在，我将使用单独的查询来检索显式和隐式访问权限，并在 Java 中将它们连接在一起。