/atlassian-hibernate4-extras/src/main/java/com/atlassian/hibernate/extras/hql/JoinTableBasedDeleteHandlerImpl.java

package com.atlassian.hibernate.extras.hql;

import org.hibernate.engine.spi.QueryParameters;
import org.hibernate.engine.spi.SessionFactoryImplementor;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.hql.internal.ast.HqlSqlWalker;
import org.hibernate.hql.internal.ast.tree.DeleteStatement;
import org.hibernate.hql.internal.ast.tree.FromElement;
import org.hibernate.hql.spi.AbstractTableBasedBulkIdHandler;
import org.hibernate.hql.spi.MultiTableBulkIdStrategy;
import org.hibernate.param.ParameterSpecification;
import org.hibernate.persister.collection.AbstractCollectionPersister;
import org.hibernate.persister.entity.Queryable;
import org.hibernate.sql.Alias;
import org.hibernate.sql.JoinFragment;
import org.hibernate.sql.JoinType;
import org.hibernate.type.CollectionType;
import org.hibernate.type.Type;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


/**
 * Custom multi-table bulk delete strategy, derived from Hibernate's own {@code TableBasedDeleteHandlerImpl}.
 * Similar to its prototype, this class relies on a temporary table that is fed with IDs of the entities to remove,
 * based on the original HQL query.
 * <p>
 * The primary difference is that this class performs the deletes using an inner join to the temporary table, rather
 * than a subquery to load all the IDs to delete, as in the original implementation. The inner join approach performs
 * significantly better on certain database engines, primarily MySQL. Note: the performance measurements of the join vs.
 * sub-query approach on MySQL have been performed using the InnoDB storage engine.
 *
 * @since 5.0.0
 * @see JoinTemporaryTableBulkIdStrategy
 */
public class JoinTableBasedDeleteHandlerImpl extends AbstractTableBasedBulkIdHandler
        implements MultiTableBulkIdStrategy.DeleteHandler {

    private static final Logger log = LoggerFactory.getLogger(JoinTableBasedDeleteHandlerImpl.class);

    private static final String DELETE_SUFFIX = "_0";

    private final Queryable targetedPersister;

    private final String idInsertSelect;
    private final List<ParameterSpecification> idSelectParameterSpecifications;
    private final List<String> deletes;

    public JoinTableBasedDeleteHandlerImpl(SessionFactoryImplementor factory, HqlSqlWalker walker) {
        this(factory, walker, null, null);
    }

    public JoinTableBasedDeleteHandlerImpl(SessionFactoryImplementor factory, HqlSqlWalker walker, String catalog,
                                           String schema) {
        super(factory, walker, catalog, schema);

        DeleteStatement deleteStatement = (DeleteStatement) walker.getAST();
        FromElement fromElement = deleteStatement.getFromClause().getFromElement();

        targetedPersister = fromElement.getQueryable();
        String bulkTargetAlias = fromElement.getTableAlias();

        ProcessedWhereClause processedWhereClause = processWhereClause(deleteStatement.getWhereClause());
        idSelectParameterSpecifications = processedWhereClause.getIdSelectParameterSpecifications();
        idInsertSelect = generateIdInsertSelect(targetedPersister, bulkTargetAlias, processedWhereClause);
        log.trace("Generated ID-INSERT-SELECT SQL (multi-table delete) : {0}", idInsertSelect);

        deletes = new ArrayList<>();

        for (Type type : targetedPersister.getPropertyTypes()) {
            if (type.isCollectionType()) {
                CollectionType collectionType = (CollectionType) type;
                AbstractCollectionPersister collectionPersister =
                        (AbstractCollectionPersister) factory.getCollectionPersister(collectionType.getRole());
                if (collectionPersister.isManyToMany()) {
                    deletes.add(generateDelete(collectionPersister.getTableName(),
                            collectionPersister.getKeyColumnNames()));
                }
            }
        }

        String[] tableNames = targetedPersister.getConstraintOrderedTableNameClosure();
        String[][] columnNames = targetedPersister.getContraintOrderedTableKeyColumnClosure();
        for (int i = 0; i < tableNames.length; i++) {
            deletes.add(generateDelete(tableNames[i], columnNames[i]));
        }
    }

    @Override
    public Queryable getTargetedQueryable() {
        return targetedPersister;
    }

    @Override
    public String[] getSqlStatements() {
        return deletes.toArray(new String[deletes.size()]);
    }

    @Override
    public int execute(SessionImplementor session, QueryParameters queryParameters) {
        prepareForUse(targetedPersister, session);
        try {
            PreparedStatement ps = null;
            int resultCount = 0;
            try {
                try {
                    ps = session.getTransactionCoordinator().getJdbcCoordinator().getStatementPreparer().prepareStatement(idInsertSelect, false);
                    int pos = 1;
                    pos += handlePrependedParametersOnIdSelection(ps, session, pos);
                    for (ParameterSpecification parameterSpecification : idSelectParameterSpecifications) {
                        pos += parameterSpecification.bind(ps, queryParameters, session, pos);
                    }
                    resultCount = session.getTransactionCoordinator().getJdbcCoordinator().getResultSetReturn().executeUpdate(ps);
                } finally {
                    if (ps != null) {
                        session.getTransactionCoordinator().getJdbcCoordinator().release(ps);
                    }
                }
            } catch (SQLException e) {
                throw convert(e, "could not insert/select ids for bulk delete", idInsertSelect);
            }

            for (String delete : deletes) {
                try {
                    try {
                        ps = session.getTransactionCoordinator()
                                .getJdbcCoordinator()
                                .getStatementPreparer()
                                .prepareStatement(delete, false);
                        handleAddedParametersOnDelete(ps, session);
                        session.getTransactionCoordinator().getJdbcCoordinator().getResultSetReturn().executeUpdate(ps);
                    } finally {
                        if (ps != null) {
                            session.getTransactionCoordinator().getJdbcCoordinator().release(ps);
                        }
                    }
                } catch (SQLException e) {
                    throw convert(e, "error performing bulk delete", delete);
                }
            }

            return resultCount;
        } finally {
            releaseFromUse(targetedPersister, session);
        }
    }

    protected int handlePrependedParametersOnIdSelection(PreparedStatement ps, SessionImplementor session, int pos) throws SQLException {
        return 0;
    }

    protected void handleAddedParametersOnDelete(PreparedStatement ps, SessionImplementor session) throws SQLException {
    }

    private static String[] prefixWithAlias(String[] original, String alias) {
        final String fullPrefix = alias + ".";
        String[] aliased = new String[original.length];

        for (int i = 0; i < original.length; i++) {
            aliased[i] = fullPrefix + original[i];
        }

        return aliased;
    }

    private String generateDelete(String tableName, String[] columnNames) {
        Alias alias = new Alias(DELETE_SUFFIX);
        String deleteTableAlias = alias.toAliasString(tableName);
        String tempIdTable = determineIdTableName(targetedPersister);
        String tempIdTableAlias = alias.toAliasString(tempIdTable);
        String[] aliasedColumnNames = prefixWithAlias(columnNames, deleteTableAlias);

        // join temp table on delete table IDs = temp table IDs (which are the same as the targeted persister IDs)
        // the assumption is that the order of IDs matches
        JoinFragment deleteJoin = factory().getDialect().createOuterJoinFragment();
        deleteJoin.addJoin(tempIdTable, tempIdTableAlias, aliasedColumnNames,
                targetedPersister.getIdentifierColumnNames(),
                JoinType.INNER_JOIN);

        // construct the aliased delete
        return new StringBuilder("delete ")
                .append(deleteTableAlias)
                .append(" from ")
                .append(tableName).append(' ')
                .append(deleteTableAlias).append(' ')
                .append(deleteJoin.toFromFragmentString())
                .toString();
    }
}