DetachedCriteria 動態構建查詢條件：CriteriaBuilder 通用封裝實戰

DetachedCriteria 動態構建查詢條件：CriteriaBuilder 通用封裝實戰

Hibernate DetachedCriteria 支持離線構建查詢，配合 CriteriaBuilder 封裝，可實現「參數非空才加條件」的動態 SQL，避免 HQL 字符串拼接的痛苦。

核心工具類：CriteriaBuilder

public class CriteriaBuilder {
    private final DetachedCriteria dc;
    private final Map<String, ?> params;

    public CriteriaBuilder(DetachedCriteria dc, Map<String, ?> params) {
        this.dc = dc; this.params = params;
    }

    // 等值條件（支持類型轉換）
    public DetachedCriteria addCriterion(String property, String paramName) {
        return addCriterion(property, paramName, Types.VARCHAR, Expression.EQ);
    }
    public DetachedCriteria addCriterion(String property, String paramName, int sqlType) {
        return addCriterion(property, paramName, sqlType, Expression.EQ);
    }
    public DetachedCriteria addCriterion(String property, String paramName, int sqlType, Expression exp) {
        if (params == null) return dc;
        Criterion c = buildCriterion(property, paramName, sqlType, exp);
        if (c != null) dc.add(c);
        return dc;
    }

    private Criterion buildCriterion(String property, String paramName, int sqlType, Expression exp) {
        Object val = params.get(paramName);
        if (val == null || "".equals(val)) return null;
        switch (sqlType) {
            case Types.INTEGER: val = Integer.valueOf(val.toString()); break;
            case Types.VARCHAR: val = val.toString(); break;
            case Types.DATE:    val = DateUtil.stringToDate(val.toString()); break;
            case Types.DECIMAL: val = new BigDecimal(val.toString()); break;
        }
        switch (exp) {
            case EQ: return Restrictions.eq(property, val);
            case NE: return Restrictions.ne(property, val);
            case GT: return Restrictions.gt(property, val);
            case GE: return Restrictions.ge(property, val);
            case LT: return Restrictions.lt(property, val);
            case LE: return Restrictions.le(property, val);
        }
        return null;
    }

    // 模糊查詢
    public DetachedCriteria addLikeCriterion(String property, String paramName) {
        return addLikeCriterion(property, paramName, MatchMode.ANYWHERE);
    }
    public DetachedCriteria addLikeCriterion(String property, String paramName, MatchMode mode) {
        if (params == null) return dc;
        Object val = params.get(paramName);
        if (val != null && !"".equals(val)) {
            dc.add(Restrictions.like(property, val.toString(), mode));
        }
        return dc;
    }
}

// 比較運算符枚舉
public enum Expression { EQ, NE, GT, GE, LT, LE }

Service 層調用示例

@Service("warehouseBS")
public class WarehouseBSImpl implements WarehouseBS {
    @Autowired private WarehouseDao warehouseDao;

    private DetachedCriteria buildCriteria(DetachedCriteria dc, Map<String, Object> params) {
        new CriteriaBuilder(dc, params)
            .addCriterion("warehouse.projectCode", "projectCode");
        return dc;
    }

    @Override
    public List<Map<String, Object>> getWarehouses(String projectCode) {
        Map<String, Object> params = new HashMap<>();
        params.put("projectCode", projectCode);

        DetachedCriteria dc = DetachedCriteria.forClass(Warehouse.class, "warehouse");
        buildCriteria(dc, params);

        // 投影查詢：只取 code/name，轉 Map 返回
        ProjectionList pl = Projections.projectionList();
        pl.add(Projections.property("warehouse.warehouseCode"), "value");
        pl.add(Projections.property("warehouse.warehouseName"), "text");
        dc.setProjection(pl);
        dc.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

        return warehouseDao.findByCriteria(dc);
    }
}

設計優勢

• 類型安全：sqlType 參數自動轉換 Integer/Date/BigDecimal，避免字符串比較錯誤。
• 空值自動忽略：參數為 null/空串不生成 Criterion，實現動態 WHERE。
• 操作符可擴展：Expression 枚舉易於增加 IN、BETWEEN、IS NULL 等。
• 投影+結果轉換：ProjectionList + ALIAS_TO_ENTITY_MAP 直接產出 List<Map>，免去實體映射開銷。

擴展建議

1. 增加 addInCriterion(property, paramName) 支持 IN (?,?,?)。
2. 增加 addBetweenCriterion(property, startParam, endParam) 支持范圍查詢。
3. 整合 Order 排序：dc.addOrder(Order.asc("createTime"))。
4. 封裝分頁：dc.setFirstResult(offset).setMaxResults(pageSize)。

用 CriteriaBuilder 替換 HQL 拼接，代碼更安全、更易讀、更易維護。