JPQL中绑定具体参数（一）

Question

在数据库中，我按“状态”列对表进行分区，以获得更好的性能。我的数据库管理员询问我是否直接在 sql 中放入该列的查询值（不通过参数绑定）。

我可以通过将提示 QueryHints.BIND_PARAMETERS 设置为 false 来更改绑定，但所有参数都在 sql 内部。

我可以设置仅在“状态”参数上不绑定吗？

BIND_PARAMETERS = true 时的结果示例

SELECT t0.* FROM S_JOBS_ORG_UNIT_CFG t0 
WHERE ((((t0.ORG_ID = ?) AND (t0.SCHEDULER_NEXT_ACTIVATION < SYSDATE)) AND (t0.ACTIVE = ?)) 
AND NOT EXISTS (SELECT ? FROM S_JOBS t1 WHERE (((t1.ORDER_ID = t0.ORDER_ID) AND (t1.ORG_ID = t0.ORG_ID)) AND NOT ((t1.STATUS = ?)))) )
bind => [472100, Y, 1, E]

和 BIND_PARAMETERS = false 时的结果

SELECT t0.* FROM S_JOBS_ORG_UNIT_CFG t0 
WHERE ((((t0.ORG_ID = 472100) AND (t0.SCHEDULER_NEXT_ACTIVATION < SYSDATE)) AND (t0.ACTIVE = Y)) 
AND NOT EXISTS (SELECT 1 FROM S_JOBS t1 WHERE (((t1.ORDER_ID = t0.ORDER_ID) AND (t1.ORG_ID = t0.ORG_ID)) AND NOT ((t1.STATUS = E)))) )

代码：

        Query jobOrgUnitCfgQuery = entityManager.createQuery(
            "SELECT c FROM JobOrgUnitCfg c WHERE c.orgId = :orgId and c.schedulerNextActivation < current_timestamp and c.active = :active and " +
            " not exists (SELECT j  FROM Job j WHERE j.orderId = c.orderId and j.orgId = c.orgId and j.status <> 'E')");

    jobOrgUnitCfgQuery.setParameter("orgId", orgId);
    jobOrgUnitCfgQuery.setParameter("active", Boolean.TRUE);
    return jobOrgUnitCfgQuery.getResultList();

Answer 1

我认为最好的选择是以编程方式构建查询，就像使用硬编码状态一样，并手动转义其他参数以避免 SQL 注入。