Merhaba,

Aşağıdaki gibi oluşturduğum domain yapısında RawMaterial bazında, Stock domaininden toplam miktarları (Sum), Reserved_Stock domaininden toplam miktarları (Sum) ve Order_Detail domaininden toplam miktarları (Sum) sorgulamak istiyorum. RmType, RmGroup, RmProperties, RmProperties_Detail domainlerinden bir veya birkaçına göre süzebilmeliyim (where).

Yapmak istediğim özetle şuydu;

Select RmDefDetail
Join Sum[Stock] on RawMaterial
Join Sum[Order_Detail] on RawMaterial
Join Sum[Reserved_Stock] on RawMaterial
Where RmType, RmGroup, RmProperties, RmPropertiesDetail

Süzme işlemini parametrik yapmak istediğim için Criteria Builder kullanmak istedim ama sum yapılmış bir sorguyu join etmem gerekiyor. Bunu yapamadım. Google'a sordum Criteria Builder desteklemiyor gibisinden sonuçlar aldım. QueryDsl kullanılmasını önerenler vardı.

Criteria Builder ile nasıl yapabilirim ve QueryDsl kullanılmasını önerir misiniz?

Teşekkürler.

alt text

soruldu: 17 Oca '13, 02:59

SaRPaRDa's gravatar image

SaRPaRDa
5.0k213165
cevap kabul oranı: 27%

değiştirildi: 21 Oca '13, 03:56

CemIkta's gravatar image

CemIkta ♦
19.9k29125190

SubQuery'i join yapmayı beceremedim. Ama bu şekilde çalışıyor. İlgilenen arkadaşlara teşekkür ederim.

    @Override
    public List<RmStockDto> getStock(MindMapDto selectedRm,
            MindMapDto selectedDepot) {
        CriteriaBuilder cb = em.getCriteriaBuilder();

CriteriaQuery<RmStockDto> qryOrder = cb.createQuery(RmStockDto.class);
        Root<RmOrderDetail> fromOrder = qryOrder.from(RmOrderDetail.class);
        Expression<BigDecimal> qOrder = fromOrder.get("quantity");
        Expression<BigDecimal> iOrder = fromOrder.get("inwardQuantity");
        Expression<BigDecimal> qntOrder = cb.sum(cb.diff(qOrder, iOrder));
        Expression<String> ordRmId = fromOrder.get("rmDefinition").get("rmId");
        qryOrder.select(cb.construct(RmStockDto.class, ordRmId, qntOrder));
        Predicate predOrderStatus = cb.lessThan(fromOrder.<Short> get("status"), (short) 4);
        qryOrder.groupBy(ordRmId);

CriteriaQuery<RmStockDto> qryStock = cb.createQuery(RmStockDto.class);
        Root<RmStock> fromStock = qryStock.from(RmStock.class);
        Expression<BigDecimal> qStock = fromStock.get("quantity");
        Expression<BigDecimal> qntStock = cb.sum(qStock);

Expression<String> depotId = fromStock.get("depot").get("depotId");
        Expression<String> depot = fromStock.get("depot").get("depot");

Join<RmStock, RmDefinition> def = fromStock.join("rmDefinition",
                JoinType.LEFT);
        Join<RmDefinition, RmReservedStock> reserveds = def.join(
                "rmReservedStocks", JoinType.LEFT);
        Expression<BigDecimal> qRes = reserveds.get("quantity");

//      Subquery<RmOrderDetail> subDet = qryStock.subquery(RmOrderDetail.class);
//      Root<RmOrderDetail> fromDet = (Root<RmOrderDetail>) subDet.from(RmOrderDetail.class).alias("det");
//
//      Expression<BigDecimal> qOrder = fromDet.get("quantity");
//      Expression<BigDecimal> iOrder = fromDet.get("inwardQuantity");
//      Expression<BigDecimal> qntOrder = cb.sum(cb.diff(qOrder, iOrder));
//      Expression<String> ordRmId = fromDet.get("rmDefinition").get("rmId");
//      //Expression<RmOrderDetail> expOrd = fromDet;
//      subDet.select(fromDet);
//      Predicate predDetStatus = cb.lessThan(fromDet.<Short> get("status"), (short) 4);
//      subDet.groupBy(fromDet.get("rmDefinition").get("rmId"));

qryStock.select(cb.construct(RmStockDto.class, qntStock, qRes,
                def.get("rmId"), def.get("rawMaterial"),
                def.get("rmGroup").get("groupId"),
                def.get("rmGroup").get("groupName"),
                def.get("rmGroup").get("rmType").get("typeId"),
                def.get("rmGroup").get("rmType").get("typeName"), depotId,
                depot, def.get("minStock"), def.get("rmUnit").get("langParam")));

qryStock.groupBy(qRes, def.get("rmId"), def.get("rawMaterial"), def
                .get("rmGroup").get("groupId"),
                def.get("rmGroup").get("groupName"),
                def.get("rmGroup").get("rmType").get("typeId"),
                def.get("rmGroup").get("rmType").get("typeName"), depotId,
                depot, def.get("minStock"), def.get("rmUnit").get("langParam"));

Predicate prDepot = null;
        if (selectedDepot.getSelectedType().equals("depot")) {
            prDepot = cb.equal(depotId, selectedDepot.getSelectedId());
        } else if (selectedDepot.getSelectedType().equals("stockType")) {
            prDepot = cb.equal(
                    fromStock.get("depot").get("depotType").get("stockType"),
                    selectedDepot.getSelectedId());
        } else {
            prDepot = cb.equal(
                    fromStock.get("depot").get("depotType").get("typeId"),
                    selectedDepot.getSelectedId());
        }

Predicate predRm = null;
        Predicate predRmForOrder = null;

if (selectedRm.getSelectedType().equals("rmType")) {
            predRm = cb.equal(def.get("rmGroup").get("rmType").get("typeId"),
                    selectedRm.getSelectedId());

predRmForOrder = cb.equal(fromOrder.get("rmDefinition").get("rmGroup").get("rmType").get("typeId"),
                    selectedRm.getSelectedId());
        }

if (selectedRm.getSelectedType().equals("rmGroup")) {
            predRm = cb.equal(def.get("rmGroup").get("groupId"),
                    selectedRm.getSelectedId());

predRmForOrder = cb.equal(fromOrder.get("rmDefinition").get("rmGroup").get("groupId"), selectedRm.getSelectedId());
        }

if (selectedRm.getSelectedType().equals("rmProperty")) {
            Subquery<RmDefinition> subProp = qryStock
                    .subquery(RmDefinition.class);
            Root<RmDefinitionDetail> fromProp = subProp
                    .from(RmDefinitionDetail.class);
            subProp.select(fromProp.<RmDefinition> get("rmDefinition"));
            subProp.where(cb.equal(fromProp.get("propertyLst")
                    .get("rmProperty").get("propertyId"),
                    selectedRm.getSelectedId()));
            subProp.distinct(true);
            predRm = cb.in(fromStock.get("rmDefinition").get("rmId")).value(
                    subProp);

predRmForOrder = cb.in(fromOrder.get("rmDefinition").get("rmId")).value(
                    subProp);
        }

if (selectedRm.getSelectedType().equals("rmPropertyList")) {
            Subquery<RmDefinition> subPropL = qryStock
                    .subquery(RmDefinition.class);
            Root<RmDefinitionDetail> fromPropL = subPropL
                    .from(RmDefinitionDetail.class);
            subPropL.select(fromPropL.<RmDefinition> get("rmDefinition"));
            subPropL.where(cb.equal(
                    fromPropL.get("propertyLst").get("propertyListId"),
                    selectedRm.getSelectedId()));
            subPropL.distinct(true);
            predRm = cb.in(fromStock.get("rmDefinition").get("rmId")).value(
                    subPropL);

predRmForOrder = cb.in(fromOrder.get("rmDefinition").get("rmId")).value(
                    subPropL);
        }

if (selectedRm.getSelectedType().equals("rawMaterial")) {
            predRm = cb.equal(def.get("rmId"), selectedRm.getSelectedId());

predRmForOrder = cb.equal(ordRmId, selectedRm.getSelectedId());
        }

Predicate prStock = cb.gt(qStock, BigDecimal.ZERO);
        qryStock.where(cb.and(prDepot, predRm, prStock));
        //subDet.where(cb.and(predDetStatus, predRm));
        qryOrder.where(cb.and(predOrderStatus, predRmForOrder));
        qryStock.orderBy(cb.asc(depot), cb.asc(def.get("rawMaterial")));

List<RmStockDto> orders = em.createQuery(qryOrder).getResultList();
        List<RmStockDto> stocks = em.createQuery(qryStock).getResultList();

for(RmStockDto st : stocks) {
            for(RmStockDto ord : orders) {
                if(st.getRmId().equals(ord.getRmId())) {
                    st.setOrderQuantity(ord.getOrderQuantity());
                }
            }
        }

return stocks;
    }
}
(21 Oca '13, 03:20) SaRPaRDa SaRPaRDa's gravatar image

Merhaba,

cok fazla ayrintiya girmeden kisa bir örnek vermek istiyorum.

    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Double> c = criteriaBuilder.createQuery(Double.class);
    Root<Bill> root = c.from(Bill.class);
    c.select(criteriaBuilder.sum(root.get(Bill_.amount)));
    Path<String> path = root.get(Bill_.customer).get(Customer_.name);
    Predicate equal = criteriaBuilder.equal(path, "hasan");
    c.where(equal);
    TypedQuery<Double> createQuery = entityManager.createQuery(c);
    System.out.println(createQuery.getSingleResult());
permanent link

cevaplandı: 18 Oca '13, 05:31

mahmut_can's gravatar image

mahmut_can ♦
2.9k62552
cevap kabul oranı: 67%

Yapmak istediğim gruplandırılmış veriyi join edebilmek. Sizin yukarıda verdiğiniz parantez içerisindeki sorguyu hazırlıyor. Benim yapamadığım bu sorguyu join edebilmek.

Select a.*, c.qnt From table a

Left Join (select id, sum(qnt) as qnt) c on c.id=a.id

(18 Oca '13, 08:30) SaRPaRDa SaRPaRDa's gravatar image

Join, Bill join Customer: root.get(Bill_.customer).get(Customer_.name)

Senin yapmak istedigin bir "SubQuery". Ilk önce bir Subquery yap, sonra onun üzerine bir Query daha.

(18 Oca '13, 08:32) mahmut_can ♦ mahmut_can's gravatar image

Ben aşağıdaki gibi subquery'i kullanmaya çalıştım ama olmadı. Aşağıda ########## ile işaretlediğim alanda ne yapmam gerektiğini söyleyebilir misin?

CriteriaBuilder cb = em.getCriteriaBuilder();

CriteriaQuery<RmStockDto> qryStock = cb.createQuery(RmStockDto.class);
        Root<RmStock> fromStock = qryStock.from(RmStock.class);
        Expression<BigDecimal> qStock = fromStock.get("quantity");
        Expression<BigDecimal> qntStock = cb.sum(qStock);

Expression<String> depotId = fromStock.get("depot").get("depotId");
        Expression<String> depot = fromStock.get("depot").get("depot");

Join<RmStock, RmDefinition> def = fromStock.join("rmDefinition",
                JoinType.LEFT);
        Join<RmDefinition, RmReservedStock> reserveds = def.join(
                "rmReservedStocks", JoinType.LEFT);
        Expression<BigDecimal> qRes = reserveds.get("quantity");
########################################################################################
        Subquery<BigDecimal> subDet = qryStock.subquery(BigDecimal.class);
        Root<RmOrderDetail> fromDet = subDet.from(RmOrderDetail.class);

Expression<BigDecimal> qOrder = fromDet.get("quantity");
        Expression<BigDecimal> iOrder = fromDet.get("cQuantity");
        Expression<BigDecimal> qntOrder = cb.sum(cb.diff(qOrder, iOrder));
        subDet.select(qntOrder);
        Predicate predDetStatus = cb.lessThan(fromDet.<Short> get("status"), (short) 4);
        subDet.groupBy(fromDet.get("rmDefinition").get("rmId"));

qryStock.select(cb.construct(RmStockDto.class, qntStock, qRes,
                qntOrder, def.get("rmId"), def.get("rawMaterial"),
                def.get("rmGroup").get("groupId"),
                def.get("rmGroup").get("groupName"),
                def.get("rmGroup").get("rmType").get("typeId"),
                def.get("rmGroup").get("rmType").get("typeName"), depotId,
                depot, def.get("minStock"), def.get("rmUnit").get("langParam")));
###############################################################################################
        qryStock.groupBy(qRes, def.get("rmId"), def.get("rawMaterial"), def
                .get("rmGroup").get("groupId"),
                def.get("rmGroup").get("groupName"),
                def.get("rmGroup").get("rmType").get("typeId"),
                def.get("rmGroup").get("rmType").get("typeName"), depotId,
                depot, def.get("minStock"), def.get("rmUnit").get("langParam"));

Predicate prDepot = null;
        if (selectedDepot.getSelectedType().equals("depot")) {
            prDepot = cb.equal(depotId, selectedDepot.getSelectedId());
        } else if (selectedDepot.getSelectedType().equals("stockType")) {
            prDepot = cb.equal(
                    fromStock.get("depot").get("depotType").get("stockType"),
                    selectedDepot.getSelectedId());
        } else {
            prDepot = cb.equal(
                    fromStock.get("depot").get("depotType").get("typeId"),
                    selectedDepot.getSelectedId());
        }

Predicate predRm = null;

if (selectedRm.getSelectedType().equals("rmType")) {
            predRm = cb.equal(def.get("rmGroup").get("rmType").get("typeId"),
                    selectedRm.getSelectedId());

}

if (selectedRm.getSelectedType().equals("rmGroup")) {
            predRm = cb.equal(def.get("rmGroup").get("groupId"),
                    selectedRm.getSelectedId());
        }

if (selectedRm.getSelectedType().equals("rmProperty")) {
            Subquery<RmDefinition> subProp = qryStock
                    .subquery(RmDefinition.class);
            Root<RmDefinitionDetail> fromProp = subProp
                    .from(RmDefinitionDetail.class);
            subProp.select(fromProp.<RmDefinition> get("rmDefinition"));
            subProp.where(cb.equal(fromProp.get("propertyLst")
                    .get("rmProperty").get("propertyId"),
                    selectedRm.getSelectedId()));
            subProp.distinct(true);
            predRm = cb.in(fromStock.get("rmDefinition").get("rmId")).value(
                    subProp);
        }

if (selectedRm.getSelectedType().equals("rmPropertyList")) {
            Subquery<RmDefinition> subPropL = qryStock
                    .subquery(RmDefinition.class);
            Root<RmDefinitionDetail> fromPropL = subPropL
                    .from(RmDefinitionDetail.class);
            subPropL.select(fromPropL.<RmDefinition> get("rmDefinition"));
            subPropL.where(cb.equal(
                    fromPropL.get("propertyLst").get("propertyListId"),
                    selectedRm.getSelectedId()));
            subPropL.distinct(true);
            predRm = cb.in(fromStock.get("rmDefinition").get("rmId")).value(
                    subPropL);
        }

if (selectedRm.getSelectedType().equals("rawMaterial")) {
            predRm = cb.equal(def.get("rmId"), selectedRm.getSelectedId());
        }

Predicate prStock = cb.gt(qStock, BigDecimal.ZERO);
        qryStock.where(cb.and(prDepot, predRm, prStock));
        subDet.where(cb.and(predDetStatus, predRm));
        qryStock.orderBy(cb.asc(depot), cb.asc(def.get("rawMaterial")));

return em.createQuery(qryStock).getResultList();
(18 Oca '13, 08:57) SaRPaRDa SaRPaRDa's gravatar image

Biraz daha basit bir örnek ile baslarsak daha iyi olur.

Bunu deneye bilmem gerekecektir.

(18 Oca '13, 09:04) mahmut_can ♦ mahmut_can's gravatar image

O halde kod üzerinden gitmeyelim. Benim anlamadığım kısmı söyleseniz çözerim diye düşünüyorum;

Subquery'i BigDecimal olarak oluşturursam nasıl join edeceğim. Subquery'i Entity olarak oluşturursam nasıl select edeceğim. Entity olarak oluşturduğumda sadece aynı entity'i select etmeme izin veriyor.

(18 Oca '13, 09:09) SaRPaRDa SaRPaRDa's gravatar image
Cevabınız
toggle preview

powered by BitNami OSQA