Rewrite plain SQL/JDBC in Blaze Persistence with Hibernate

Hibernate ORM
1

Hi! I want to rewrite the request below initially written in plain SQL/JDBC.

Instead, I want to use Blaze Persistence 1.6.17 and Hibernate 6.6.7 as JPA provider, jdk 21, Jakarta EE JPA (Wildfly 36).

Although it may not be relevant for my question, the transaction type is JTA, container managed, with Wildfly’s EJB container (use of @TransactionAttribute annotation).

The db may be MySQL, Oracle or SQL Server.

The initial method looks like this :

public static long getDocIdForSubjectedToAccessRestrictionsArchivedDocByUnit(long attachmentId, long docId, Connection conn) {
    // If the given attachment ID is corresponding to the source or consult attachment
    // of an archived document that belongs to the unit with the setting field "Restrict access to archived documents" is enabled
    String sqlQuery = "SELECT distinct p_doc.p_iddoc FROM p_doc " +
            "JOIN p_type_unit ON p_doc.p_idtype_unit = p_type_unit.p_idtype_unit " +
            "JOIN p_unit ON p_type_unit.p_idunit = p_unit.p_idunit " +
            "WHERE p_unit.p_archived = 0 " +
            "AND p_unit.p_access_archived_doc_restricted = 1 " +
            "AND p_doc.p_doc_status = " + IDocConstants.DOC_STATUS_ARCHIVED + " ";
    if (attachmentId > 0) {
        sqlQuery += "AND (p_doc.p_source_attachment=? OR p_doc.p_consult_attachment=?)";
    } else if (docId > 0) {
        sqlQuery += "AND p_doc.p_iddoc=?";
    }

    try (PreparedStatement prepareStatement = conn.prepareStatement(sqlQuery)) {
        if (attachmentId > 0) {
            prepareStatement.setLong(1, attachmentId);
            prepareStatement.setLong(2, attachmentId);
        } else if (docId > 0) {
            prepareStatement.setLong(1, docId);
        }
        ResultSet rs = prepareStatement.executeQuery();
        if (rs.next()) {
            return rs.getLong("p_iddoc");
        }
    } catch(SQLException e) {
        logger.error("An error occurred when executing query", e);
    }
    return -1;
}

Now, my request with Blaze Persistence is below, it works BUT it does not replace the above method :

    public static long getDocIdForSubjectedToAccessRestrictionsArchivedDocByUnit(
            long attachmentId, long docId, Connection conn) {

        try (var emHolder = PersistenceContext.getEntityManagerHolder()) {
            EntityManager em = emHolder.getEntityManager();
            CriteriaBuilderFactory cbf = BlazeInitializer.getBlazeContext().getCbf();

            CriteriaBuilder<Long> cb = cbf.create(em, Long.class)
                    .from(DocPO.class, "d")
                    .select("d.id")
                    .distinct()
                    .innerJoin("typeUnit", "tu")
                    .innerJoin("tu.unit", "u");

            cb.where("u.archived").eq(false);
        //    cb.where("u.accessArchivedDocRestricted").eq(true);
            cb.where("d.docStatus").eq(IDocConstants.DOC_STATUS_ARCHIVED);

            if (attachmentId > 0) {
                cb.whereOr()
                        .where("d.mainAttachment.id").eq(attachmentId)
                        .where("d.consultAttachment.id").eq(attachmentId)
                        .endOr();
            } else if (docId > 0) {
                cb.where("d.id").eq(docId);
            }

            List<Long> results = cb.getResultList();
            if (!results.isEmpty()) {
                return results.getFirst();
            }

            return -1;
        }
    }

I have commented the line that causes the bug :

//    cb.where("u.accessArchivedDocRestricted").eq(true);

UPDATE :

Below the final version, that works without bug, but does it replace the initial method above (JDBC/SQL) ?

archived is a property in UnitPO while accessArchivedDocRestricted is a property in DocUnitPO . DocUnitPO extends UnitPO. The inheritance strategy being omitted in the code, I guess it is SINGLE_TABLE.

Thanks!

public static long getDocIdForSubjectedToAccessRestrictionsArchivedDocByUnit(
        long attachmentId, long docId, Connection conn) {

    try (var emHolder = PersistenceContext.getEntityManagerHolder()) {
        EntityManager em = emHolder.getEntityManager();
        CriteriaBuilderFactory cbf = BlazeInitializer.getBlazeContext().getCbf();

        CriteriaBuilder<Long> cb = cbf.create(em, Long.class)
                .from(DocPO.class, "d")
                .select("d.id")
                .distinct()
                .innerJoin("typeUnit", "tu")
                .innerJoin("TREAT(tu.unit AS DocUnitPO)", "u");

        cb.where("u.archived").eq(false);
        cb.where("u.accessArchivedDocRestricted").eq(true);
        cb.where("d.docStatus").eq(IDocConstants.DOC_STATUS_ARCHIVED);

        if (attachmentId > 0) {
            cb.whereOr()
                    .where("d.mainAttachment.id").eq(attachmentId)
                    .where("d.consultAttachment.id").eq(attachmentId)
                    .endOr();
        } else if (docId > 0) {
            cb.where("d.id").eq(docId);
        }

        List<Long> results = cb.getResultList();
        if (!results.isEmpty()) {
            return results.getFirst();
        }

        return -1;
    }
}
2

I’m not sure what the exact question is, but from what I can tell, the translation looks ok to me, though I would suggest you to use eqLiteral() when you pass a literal value, to match the behavior exactly:

public static long getDocIdForSubjectedToAccessRestrictionsArchivedDocByUnit(
        long attachmentId, long docId, Connection conn) {

    try (var emHolder = PersistenceContext.getEntityManagerHolder()) {
        EntityManager em = emHolder.getEntityManager();
        CriteriaBuilderFactory cbf = BlazeInitializer.getBlazeContext().getCbf();

        CriteriaBuilder<Long> cb = cbf.create(em, Long.class)
                .from(DocPO.class, "d")
                .select("d.id")
                .distinct()
                .innerJoin("typeUnit", "tu")
                .innerJoin("TREAT(tu.unit AS DocUnitPO)", "u");

        cb.where("u.archived").eqLiteral(false);
        cb.where("u.accessArchivedDocRestricted").eqLiteral(true);
        cb.where("d.docStatus").eqLiteral(IDocConstants.DOC_STATUS_ARCHIVED);

        if (attachmentId > 0) {
            cb.whereOr()
                    .where("d.mainAttachment.id").eq(attachmentId)
                    .where("d.consultAttachment.id").eq(attachmentId)
                    .endOr();
        } else if (docId > 0) {
            cb.where("d.id").eq(docId);
        }

        List<Long> results = cb.getResultList();
        if (!results.isEmpty()) {
            return results.getFirst();
        }

        return -1;
    }
}
3

Thanks a lot! I just wanted to check if the translation is ok (SQL/JDBC => Blaze Persistence). The method must have exactly the same signature, return type, result set and algorithm. Another important thing : SQL injection risk must be avoided.