JPA标准中使用复合主键的IN子句查询

Question

我在MySQL中有一个名为group_table的表，只有两列user_group_id和group_id（它们都是VARCHAR类型）。这两列一起形成了一个复合主键。

我需要使用IN()子查询执行语句，根据传递给查询的值列表选择行。

@Override
@SuppressWarnings("unchecked")
public List<GroupTable> getList(List<GroupTable> list)
{
    CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
    CriteriaQuery<GroupTable> criteriaQuery=criteriaBuilder.createQuery(GroupTable.class);
    Root<GroupTable> root = criteriaQuery.from(entityManager.getMetamodel().entity(GroupTable.class));
    criteriaQuery.where(root.in(list));
    return entityManager.createQuery(criteriaQuery).getResultList();
}

实现会生成以下查询。

SELECT group_id, 
       user_group_id 
FROM   projectdb.group_table 
WHERE  ((?, ?) IN ((?, ?), (?, ?))) 

/*Binding parameters.*/
bind => [null, null, ROLE_AAA, aaa, ROLE_BBB, aaa]

请注意，关于复合键本身的前两个参数都为null。分别应该是user_group_id和group_id。

为什么在参数列表中它们没有被替换？

---

虽然我对在表中形成复合主键不感兴趣，但在我用于身份验证的JAAS中，这可能是强制性的。 _{在这种情况下，查询返回与从数据库提供的相同的列表，在现实中是不必要的。实际上，我需要此查询以删除多行。}

- Tiny

Answer 1

这是Eclipse Link中缺失的一个功能。我已经为此开发了一个补丁。

/** *****************************************************************************
 * Copyright (c) 1998, 2013 Oracle and/or its affiliates. All rights reserved.
 * This program and the accompanying materials are made available under the
 * terms of the Eclipse Public License v1.0 and Eclipse Distribution License v. 1.0
 * which accompanies this distribution.
 * The Eclipse Public License is available at http://www.eclipse.org/legal/epl-v10.html
 * and the Eclipse Distribution License is available at
 * http://www.eclipse.org/org/documents/edl-v10.php.
 *
 * Contributors:
 * Oracle - initial API and implementation from Oracle TopLink
 * Nicolas Marcotte <[e-mail]> - patch for IN on composite keys comming from expression builder 

 ***************************************************************************** */
package org.eclipse.persistence.internal.expressions;

import java.io.*;
import java.util.*;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.eclipse.persistence.internal.helper.*;
import org.eclipse.persistence.internal.sessions.AbstractSession;
import org.eclipse.persistence.queries.*;
import org.eclipse.persistence.exceptions.*;
import org.eclipse.persistence.expressions.*;
import org.eclipse.persistence.internal.databaseaccess.*;
import org.eclipse.persistence.internal.sessions.AbstractRecord;

/**
 * <p>
 * <b>Purpose</b>: Expression SQL printer.
 * <p>
 * <b>Responsibilities</b>:<ul>
 * <li> Print an expression in SQL format.
 * <li> Replaces FIELD types with field names from the descriptor.
 * <li> Replaces PARAMETER types with row or object values.
 * <li> Calls accessor to print primitive types.
 * </ul>
 * <p>
 * @author Dorin Sandu
 * @since TOPLink/Java 1.0
 */
public class ExpressionSQLPrinter {

    /**
     * Stores the current session. The session accessor
     * is used to print all the primitive types.
     */
    protected AbstractSession session;

    /**
     * Stores the current platform to access platform specific functions.
     */
    protected DatabasePlatform platform;

    /**
     * Stores the call being created.
     */
    protected SQLCall call;

    /**
     * Stores the row. Used to print PARAMETER nodes.
     */
    protected AbstractRecord translationRow;

    /**
     * Indicates whether fully qualified field names
     * (owner + table) should be used or not.
     */
    protected boolean shouldPrintQualifiedNames;

    // What we write on
    protected Writer writer;

    /** Used for distincts in functions. */
    protected boolean requiresDistinct;

    // Used in figuring out when to print a comma in the select line
    protected boolean isFirstElementPrinted;
    private final ExpressionBuilder builder;

    public ExpressionSQLPrinter(AbstractSession session, AbstractRecord translationRow, SQLCall call, boolean printQualifiedNames, ExpressionBuilder builder) {
        this.session = session;
        this.translationRow = translationRow;
        this.call = call;
        this.shouldPrintQualifiedNames = printQualifiedNames;
        // reference session's platform directly if builder or builder's descriptor is null
        if (builder == null || builder.getDescriptor() == null) {
            this.platform = getSession().getPlatform();
        } else {
            this.platform = (DatabasePlatform) getSession().getPlatform(builder.getDescriptor().getJavaClass());
        }
        this.requiresDistinct = false;
        this.builder = builder;
        isFirstElementPrinted = false;
    }

    /**
     * Return the call.
     */
    public SQLCall getCall() {
        return call;
    }

    /**
     * INTERNAL:
     * Return the database platform specific information.
     */
    public DatabasePlatform getPlatform() {
        return this.platform;
    }

    protected AbstractSession getSession() {
        return session;
    }

    /**
     * INTERNAL:
     * Return the row for translation
     */
    protected AbstractRecord getTranslationRow() {
        return translationRow;
    }

    public Writer getWriter() {
        return writer;
    }

    /**
     * INTERNAL:
     * Used in figuring out when to print a comma in the select clause
     */
    public boolean isFirstElementPrinted() {
        return isFirstElementPrinted;
    }

    public void printExpression(Expression expression) {
        translateExpression(expression);
    }

    public void printField(DatabaseField field) {
        if (field == null) {
            return;
        }
        //start of patch 1
        //resolve alias if is was not already done 
        if (builder.getTableAliases() != null) {
            DatabaseTable keyAtValue = builder.getTableAliases().keyAtValue(field.getTable());
            if (keyAtValue != null) {
                field.setTableName(keyAtValue.getName());
            }
        }
         //end of patch 1
        try {
            // Print the field using either short or long notation i.e. owner + table name.
            if (shouldPrintQualifiedNames()) {
                getWriter().write(field.getQualifiedNameDelimited(platform));
            } else {
                getWriter().write(field.getNameDelimited(platform));
            }
        } catch (IOException exception) {
            throw ValidationException.fileError(exception);
        }
    }

    public void printParameter(ParameterExpression expression) {
        try {
            final Logger logger = LogManager.getLogger();

            getCall().appendTranslationParameter(getWriter(), expression, getPlatform(), getTranslationRow());

        } catch (IOException exception) {
            throw ValidationException.fileError(exception);            
        }
    }

    public void printParameter(DatabaseField field) {
        getCall().appendTranslation(getWriter(), field);
    }

    public void printPrimitive(Object value) {
        if (value instanceof Collection) {
            printValuelist((Collection) value);
            return;
        }

        session.getPlatform().appendLiteralToCall(getCall(), getWriter(), value);
    }

    public void printNull(ConstantExpression nullValueExpression) {
        if (session.getPlatform().shouldBindLiterals()) {
            DatabaseField field = null;
            Expression localBase = nullValueExpression.getLocalBase();
            if (localBase.isFieldExpression()) {
                field = ((FieldExpression) localBase).getField();
            } else if (localBase.isQueryKeyExpression()) {
                field = ((QueryKeyExpression) localBase).getField();
            }
            session.getPlatform().appendLiteralToCall(getCall(), getWriter(), field);
        } else {
            session.getPlatform().appendLiteralToCall(getCall(), getWriter(), null);
        }
    }

    public void printString(String value) {
        try {
            getWriter().write(value);

        } catch (IOException exception) {
            throw ValidationException.fileError(exception);
        }
    }

    public void printValuelist(Collection values) {
        try {
            getWriter().write("(");
            Iterator valuesEnum = values.iterator();
            while (valuesEnum.hasNext()) {
                Object value = valuesEnum.next();
                // Support nested arrays for IN.
                if (value instanceof Collection) {
                    printValuelist((Collection) value);
                } else if (value instanceof Expression) {
                    ((Expression) value).printSQL(this);
                //start of patch 2
                } else if (value instanceof DatabaseField) {

                    printExpression(builder.getField((DatabaseField) value));            
                //end of patch 2
                } else {
                    session.getPlatform().appendLiteralToCall(getCall(), getWriter(), value);
                }
                if (valuesEnum.hasNext()) {
                    getWriter().write(", ");
                }
            }
            getWriter().write(")");
        } catch (IOException exception) {
            throw ValidationException.fileError(exception);
        }
    }

    /*
     * Same as printValuelist, but allows for collections containing expressions recursively
     */
    public void printList(Collection values) {
        try {
            getWriter().write("(");
            Iterator valuesEnum = values.iterator();
            while (valuesEnum.hasNext()) {
                Object value = valuesEnum.next();
                if (value instanceof Expression) {
                    ((Expression) value).printSQL(this);
                } else {
                    session.getPlatform().appendLiteralToCall(getCall(), getWriter(), value);
                }
                if (valuesEnum.hasNext()) {
                    getWriter().write(", ");
                }                
            }
            getWriter().write(")");
        } catch (IOException exception) {
            throw ValidationException.fileError(exception);
        }
    }

    /**
     * If a distinct has been set the DISTINCT clause will be printed.
     * This is required for batch reading.
     */
    public boolean requiresDistinct() {
        return requiresDistinct;
    }

    protected void setCall(SQLCall call) {
        this.call = call;
    }

    /**
     * INTERNAL:
     * Used in figuring out when to print a comma in the select clause
     */
    public void setIsFirstElementPrinted(boolean isFirstElementPrinted) {
        this.isFirstElementPrinted = isFirstElementPrinted;
    }

    /**
     * If a distinct has been set the DISTINCT clause will be printed.
     * This is required for batch reading.
     */
    public void setRequiresDistinct(boolean requiresDistinct) {
        this.requiresDistinct = requiresDistinct;
    }

    protected void setSession(AbstractSession theSession) {
        session = theSession;
    }

    protected void setShouldPrintQualifiedNames(boolean shouldPrintQualifiedNames) {
        this.shouldPrintQualifiedNames = shouldPrintQualifiedNames;
    }

    /**
     * INTERNAL:
     * Set the row for translation
     */
    protected void setTranslationRow(AbstractRecord theRow) {
        translationRow = theRow;
    }

    public void setWriter(Writer writer) {
        this.writer = writer;
    }

    public boolean shouldPrintParameterValues() {
        return getTranslationRow() != null;
    }

    protected boolean shouldPrintQualifiedNames() {
        return shouldPrintQualifiedNames;
    }

    /**
     * Translate an expression i.e. call the appropriate
     * translation method for the expression based on its
     * type. The translation method is then responsible
     * for translating the subexpressions.
     */
    protected void translateExpression(Expression theExpression) {
        theExpression.printSQL(this);
    }
}

补丁由//start of patch n和//end of patch n分隔。

我会尝试将其提交到上游，但可能需要一些时间。

- Nicolas M

Answer 2

试试这个

        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<GroupTable> cq = cb.createQuery(GroupTable.class);
        Root<GroupTable> r = cq.from(GroupTable.class);
        Expression<EntityPK> exp = r.get("id"); //EntityPK is your primary composite key class and id is the property name of primary key in GroupTable entity
        Predicate predicate = exp.in(list);
        cq.select(r).where(predicate);

        entityManager.createQuery(cq).getResultList();

我有一个如下结构的表格：

create table EntityA (
        col1 integer not null,
        col2 integer not null,
        description varchar(255),
        primary key (col1, col2)
    )

以下是实体和复合键类：

@Entity
public class EntityA implements Serializable {

    @EmbeddedId
    private EntityPK id;
    private String description;

// getters, setteres    
    ...........................
    ............................


    }


@Embeddable
public class EntityPK implements Serializable {

    private int col1;
    private int col2;

// getters, setters, hashcode, equals etc

我的测试代码是

 CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<EntityA> cq = cb.createQuery(EntityA.class);
        Root<EntityA> r = cq.from(EntityA.class);
        Expression<EntityPK> exp = r.get("id");
        Predicate predicate = exp.in(list);
        cq.select(r).where(predicate);
        em.createQuery(cq).getResultList();

生成的SQL语句为：

select
        entitya0_.col1 as col1_0_,
        entitya0_.col2 as col2_0_,
        entitya0_.description as descript3_0_ 
    from
        EntityA entitya0_ 
    where
        entitya0_.col1=? 
        and entitya0_.col2=? 
        or entitya0_.col1=? 
        and entitya0_.col2=? 
        or entitya0_.col1=? 
        and entitya0_.col2=?

- Shailendra

---

能否生成类似于 WHERE ((user_group_id, group_id) IN ((?, ?), (?, ?))) 的查询？MySQL 5.x支持此功能。如果可能的话，这将更好 :) 无论如何，非常感谢。 - Tiny

你在 Hibernate 中测试了查询，但错误却出现在 Eclipselink 中。 - zbig

Answer 3

您可以使用字段拼接方法来解决这个问题。

创建一个返回您DTO/实体中要搜索的两个字段的方法。

  public String getField1Field2Concatenated() {
    return field1+ field2;
  }


List<String> ids = list.stream().map(r -> r.getField1Field2Concatenated()).collect(Collectors.toList());

您可以将两个字段连接起来进行搜索。

Select e from Entity e where concat(e.field1,  c.field2) in (:ids)

如果任何字段不是文本，则可以进行类型转换。

Select e from Entity e where concat(cast(c.field1 as string),  c.field2) in (:ids)

- Fernando Siqueira