Example usage for org.hibernate SQLQuery addScalar

List of usage examples for org.hibernate SQLQuery addScalar

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. SQLQuery
  6. addScalar

Introduction

In this page you can find the example usage for org.hibernate SQLQuery addScalar.

Prototype

SQLQuery<T> addScalar(String columnAlias, Type type);

Source Link

Document

Declare a scalar query result.

Usage

From source file:com.thoughtworks.go.server.persistence.MaterialRepository.java

License:Apache License

private Map<PipelineId, Set<Long>> relevantToLookedUpDependencyMap(Session session, List<Long> pipelineIds) {
    final int LOOKED_UP_PIPELINE_ID = 2;
    final int RELEVANT_PIPELINE_ID = 0;
    final int RELEVANT_PIPELINE_NAME = 1;

    String pipelineIdsSql = queryExtensions.queryRelevantToLookedUpDependencyMap(pipelineIds);
    SQLQuery pipelineIdsQuery = session.createSQLQuery(pipelineIdsSql);
    pipelineIdsQuery.addScalar("id", new LongType());
    pipelineIdsQuery.addScalar("name", new StringType());
    pipelineIdsQuery.addScalar("lookedUpId", new LongType());
    final List<Object[]> ids = pipelineIdsQuery.list();

    Map<Long, List<PipelineId>> lookedUpToParentMap = new HashMap<>();
    CollectionUtil.CollectionValueMap<Long, PipelineId> lookedUpToRelevantMap = CollectionUtil
            .collectionValMap(lookedUpToParentMap, new CollectionUtil.ArrayList<>());
    for (Object[] relevantAndLookedUpId : ids) {
        lookedUpToRelevantMap.put((Long) relevantAndLookedUpId[LOOKED_UP_PIPELINE_ID],
                new PipelineId((String) relevantAndLookedUpId[RELEVANT_PIPELINE_NAME],
                        (Long) relevantAndLookedUpId[RELEVANT_PIPELINE_ID]));
    }//from   ww w  . j a v  a 2 s.  c o m
    return CollectionUtil.reverse(lookedUpToParentMap);
}

From source file:com.thoughtworks.go.server.persistence.MaterialRepository.java

License:Apache License

public Long latestModificationRunByPipeline(final CaseInsensitiveString pipelineName, final Material material) {
    final long materialId = findMaterialInstance(material).getId();
    String key = cacheKeyForLatestPmrForPipelineKey(materialId, pipelineName.toLower());
    Long modificationId = (Long) goCache.get(key);
    if (modificationId == null) {
        synchronized (key) {
            modificationId = (Long) goCache.get(key);
            if (modificationId == null) {
                modificationId = (Long) getHibernateTemplate().execute((HibernateCallback) session -> {
                    SQLQuery sqlQuery = session.createSQLQuery("SELECT  MAX(pmr.toRevisionId) toRevisionId "
                            + "FROM (SELECT torevisionid, pipelineid FROM pipelineMaterialRevisions WHERE materialid = :material_id)  AS pmr\n"
                            + "INNER JOIN pipelines p ON ( p.name = :pipeline_name AND p.id = pmr.pipelineId)");

                    sqlQuery.setParameter("material_id", materialId);
                    sqlQuery.setParameter("pipeline_name", pipelineName.toString());
                    sqlQuery.addScalar("toRevisionId", new LongType());
                    return sqlQuery.uniqueResult();
                });/* w  ww.  j a  v a  2  s.com*/
                if (modificationId == null) {
                    modificationId = -1L;
                }
                goCache.put(key, modificationId);
            }
        }
    }
    return modificationId;
}

From source file:com.timesoft.kaitoo.ws.common.CoreContent.java

public static void addAllScalarHb(final SQLQuery sqlQuery, final Map<String, org.hibernate.type.Type> scalarMap)
        throws HibernateException {
    Iterator it = scalarMap.entrySet().iterator();
    Map.Entry pairs;/*from ww  w.  j  a v  a2s . co  m*/
    while (it.hasNext()) {
        pairs = (Map.Entry) it.next();
        sqlQuery.addScalar(pairs.getKey().toString(), (org.hibernate.type.Type) pairs.getValue());
    }
}

From source file:com.userweave.csv.questionnaire.RrtToCsv.java

License:Open Source License

@SuppressWarnings("unchecked")
private List<Object[]> getRrtResult(RrtConfigurationEntity configuration) {
    QueryObject queryObject = rrtResultDao.createQuery(configuration, null);

    queryObject.setResult("{result.*}, se.id as surveyexec_id");

    SQLQuery q = new QueryTemplate(queryObject).createSqlQuery(getCurrentSession());

    q.addEntity("result", RrtResult.class);
    q.addScalar("surveyexec_id", Hibernate.INTEGER);
    return q.list();
}

From source file:com.userweave.module.methoden.questionnaire.dao.AnswerDaoImpl.java

License:Open Source License

@SuppressWarnings("unchecked")
@Override/*from   w ww.  ja  v  a  2s  .c om*/
public List<Object[]> getValidAnswersForQuestion(MultipleRatingQuestion question, FilterFunctor filterFunctor) {
    QueryObject queryObject = getQueryObject(question, filterFunctor, "singleratinganswer",
            "singleratinganswer");

    queryObject.setResult("{qa.*}, r_qa.ratingterm_id");

    if (queryObject.getHasGroupBy()) {
        queryObject.setGroupBy("qa.id, " + "qa.multipleRatingAnswer_id, " + "qa.rating, " + "qa.ratingTerm_id, "
                + "r_qa.ratingterm_id");
    }

    SQLQuery q = new QueryTemplate(queryObject).createSqlQuery(getCurrentSession());

    q.addEntity("qa", SingleRatingAnswer.class);
    q.addScalar("ratingterm_id", Hibernate.INTEGER);

    return q.list();
}

From source file:com.vega.service.configuration.SQLQueryBuilder.java

public static <T> Object buildQuery(Class<T> classe, String sql) {

    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    session.beginTransaction();// w ww .  j a  va2 s  . c  o  m

    ResultTransformer resultTransformer = new AliasToBeanResultTransformer(classe);

    Field[] fields = classe.getDeclaredFields();
    SQLQuery sqlQuery = session.createSQLQuery(sql);

    for (Field field : fields) {
        if (field.getType().getSimpleName().equals("Integer")) {
            sqlQuery.addScalar(field.getName(), new IntegerType());
        } else if (field.getType().getSimpleName().equals("GregorianCalendar")) {
            sqlQuery.addScalar(field.getName(), new DateType());
        } else if (field.getType().getSimpleName().equals("BigDecimal")) {
            sqlQuery.addScalar(field.getName(), new BigDecimalType());
        } else if (field.getType().getSimpleName().equals("String")) {
            sqlQuery.addScalar(field.getName(), new StringType());
        }
    }

    Object object = sqlQuery.setResultTransformer(resultTransformer).list().get(0);

    session.getTransaction().commit();

    return object;

}

From source file:com.viettel.logistic.wms.dao.GoodsDAO.java

License:Open Source License

public List<GoodsDTO> getListGoods(GoodsDTO goodsDTO) {
    List<GoodsDTO> lstGoodsDTO = new ArrayList();
    List lstParams = new ArrayList();
    //        /*w  w  w  .  ja v  a 2s  .  co  m*/

    StringBuffer sql = new StringBuffer();
    //        
    sql.append("SELECT   g.cust_id custId,");
    sql.append("         g.goods_id goodsId,");
    sql.append("  FROM   goods g, stock_goods_total sg");
    sql.append(" WHERE   s.stock_id = sg.owner_id");
    sql.append("     AND g.goods_id = sg.goods_id");
    sql.append("     AND sg.amount_issue > 0");

    //Tim kiem theo khach hang
    if (!StringUtils.isStringNullOrEmpty(goodsDTO.getCustId())) {
        sql.append("         AND sg.cust_id = ?");
        sql.append("         AND g.cust_id = ?");
        lstParams.add(goodsDTO.getCustId());
        lstParams.add(goodsDTO.getCustId());
    }

    //
    sql.append(" GROUP BY   sg.cust_id");
    sql.append(" ORDER BY   sg.cust_id");
    //Map DTO
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.setResultTransformer(Transformers.aliasToBean(StockGoodsInforDTO.class));
    query.addScalar("custId", new StringType());
    query.addScalar("ownerId", new StringType());
    query.addScalar("ownerType", new StringType());
    query.addScalar("ownerCode", new StringType());
    query.addScalar("ownerName", new StringType());
    query.addScalar("goodsType", new StringType());
    query.addScalar("goodsId", new StringType());
    query.addScalar("goodsCode", new StringType());
    query.addScalar("goodsName", new StringType());
    query.addScalar("goodsState", new StringType());
    query.addScalar("amount", new StringType());
    query.addScalar("amountIssue", new StringType());
    query.addScalar("unitType", new StringType());
    //Set cac gia tri tham so
    for (int i = 0; i < lstParams.size(); i++) {
        query.setParameter(i, lstParams.get(i));
    }

    //
    return query.list();
}

From source file:com.viettel.logistic.wms.dao.GoodsDAO.java

License:Open Source License

public List<GoodsDTO> getListGoodsWithCustId(String custId) {
    List<GoodsDTO> lstGoodsDTO = new ArrayList();
    List lstParams = new ArrayList();
    //        /*from w w  w .j a va 2 s .c o  m*/
    if (DataUtil.isStringNullOrEmpty(custId)) {
        return lstGoodsDTO;
    }
    StringBuilder sql = new StringBuilder();
    //        
    sql.append("SELECT   a.goods_id goodsId, a.code code, a.name name, ");
    sql.append("         a.goods_type goodsType, a.unit_type unitType, ");
    sql.append("         a.is_serial isSerial, a.is_serial_strip isSerialStrip  ");
    sql.append("    FROM   goods a ");
    sql.append("    WHERE   a.cust_id = ? AND a.status = '1' ");
    //ChuDV add : 11/12/2015 -- 
    sql.append(
            "    AND EXISTS (SELECT   1 FROM   stock_goods_total WHERE cust_id = ? AND goods_id = a.goods_id AND (amount != 0 OR amount_issue != 0))");

    //Tim kiem theo khach hang
    lstParams.add(custId.trim());
    lstParams.add(custId.trim());
    //Map DTO
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.setResultTransformer(Transformers.aliasToBean(GoodsDTO.class));
    query.addScalar("goodsId", new StringType());
    query.addScalar("code", new StringType());
    query.addScalar("name", new StringType());
    query.addScalar("goodsType", new StringType());
    query.addScalar("unitType", new StringType());
    query.addScalar("isSerial", new StringType());
    query.addScalar("isSerialStrip", new StringType());

    //Set cac gia tri tham so
    for (int i = 0; i < lstParams.size(); i++) {
        query.setParameter(i, lstParams.get(i));
    }

    //Tra ve danh sach hang hoa
    return query.list();
}

From source file:com.viettel.logistic.wms.dao.GoodsDAO.java

License:Open Source License

/**
 * NgocND6 get list goods serial by cust id
 * @param custId/*from w  ww .ja va2 s .c o  m*/
 * @param isSerial
 * @param isSerialStrip
 * @return
 */
public List<GoodsDTO> getListGoodsSerialByCustId(String custId, String isSerial, String isSerialStrip) {
    List<GoodsDTO> lstGoodsDTO = new ArrayList();
    List lstParams = new ArrayList();
    if (DataUtil.isStringNullOrEmpty(custId)) {
        return lstGoodsDTO;
    }
    StringBuilder sql = new StringBuilder();
    //        
    sql.append("SELECT   a.goods_id goodsId, a.code code, a.name name, ");
    sql.append("         a.unit_type unitType, ");
    sql.append("         a.is_serial isSerial, a.is_serial_strip isSerialStrip, a.goods_type goodsType  ");
    sql.append("    FROM   goods a ");
    sql.append("    WHERE   a.cust_id = ? AND a.status = '1' ");
    lstParams.add(custId.trim());
    if (!DataUtil.isStringNullOrEmpty(isSerial)) {
        sql.append("     AND a.is_serial = ? ");
        lstParams.add(isSerial);
    }
    if (!DataUtil.isStringNullOrEmpty(isSerialStrip)) {
        sql.append("     AND a.is_serial_strip = ? ");
        lstParams.add(isSerialStrip);
    }
    //Map DTO
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.setResultTransformer(Transformers.aliasToBean(GoodsDTO.class));
    query.addScalar("goodsId", new StringType());
    query.addScalar("code", new StringType());
    query.addScalar("name", new StringType());
    query.addScalar("goodsType", new StringType());
    query.addScalar("unitType", new StringType());
    query.addScalar("isSerial", new StringType());
    query.addScalar("isSerialStrip", new StringType());

    //Set cac gia tri tham so
    for (int i = 0; i < lstParams.size(); i++) {
        query.setParameter(i, lstParams.get(i));
    }

    //Tra ve danh sach hang hoa
    return query.list();
}

From source file:com.viettel.logistic.wms.dao.InventoryResultDAO.java

License:Open Source License

public List<InventoryResultDTO> getLstCheckedResult(String inventoryActionId) {
    List<InventoryResultDTO> lstInvenResult = new ArrayList<>();
    StringBuffer sql = new StringBuffer();
    //create sql/*from   w w  w.jav a 2 s . c  o  m*/
    sql.append(" SELECT  ");
    sql.append(" NVL(a.inventory_result_id, b.inventory_result_id) inventoryResultId ");
    sql.append(" ,NVL(a.inventory_action_id, b.inventory_action_id) inventoryActionId ");
    sql.append(" ,NVL(a.goods_code, b.goods_code) goodsCode ");
    sql.append(" ,NVL(a.goods_name, b.goods_name) goodsName ");
    sql.append(" ,NVL(a.goods_id, b.goods_id) goodsId ");
    sql.append(" ,NVL(a.unit_id, b.unit_id) unitId  ");
    sql.append(" ,NVL(a.unit_name, b.unit_name) unitName ");
    sql.append(" ,NVL(a.cell_id, b.cell_id) cellId ");
    sql.append(" ,NVL(a.cell_code, b.cell_code) cellCode ");
    sql.append(" ,NVL(a.barcode, b.barcode) barcode ");
    sql.append(" ,NVL(a.from_serial, b.from_serial) fromSerial ");
    sql.append("  ,NVL(a.to_serial, b.to_serial) toSerial ");
    sql.append(" ,NVL(b.amount_inventory,0) amountInventory  ");
    sql.append(" ,NVL(a.amount,0) amount ");
    sql.append(" ,NVL(a.note, b.note) note  ");
    sql.append(" ,NVL(b.amount_inventory,0) - NVL(a.amount,0) as amountFalse  ");
    sql.append(" ,NVL(a.type, b.type) type  ");
    sql.append(" FROM (select * from inventory_result where inventory_action_id = ? and type = '1') a  ");
    sql.append(" full join (select * from inventory_result where inventory_action_id = ? and type = '2') b ");
    sql.append(" on NVL(a.goods_code, 'ABC') = NVL(b.goods_code , 'ABC') ");
    sql.append(" and   NVL(a.cell_code, 'ABC')= NVL(b.cell_code, 'ABC') ");
    sql.append(" and   NVL(a.barcode, 'ABC')= NVL(b.barcode , 'ABC') ");
    sql.append(" and   NVL(a.from_serial, 'ABC')= NVL(b.from_serial , 'ABC') ");
    sql.append(" and   NVL(a.to_serial, 'ABC')= NVL(b.to_serial, 'ABC')  ");

    //SET PARAMETTER
    SQLQuery query = getSession().createSQLQuery(sql.toString());

    query.setResultTransformer(Transformers.aliasToBean(InventoryResultDTO.class));
    query.addScalar("inventoryResultId", new StringType());
    query.addScalar("inventoryActionId", new StringType());
    query.addScalar("goodsCode", new StringType());
    query.addScalar("goodsName", new StringType());
    query.addScalar("goodsId", new StringType());
    query.addScalar("unitId", new StringType());
    query.addScalar("unitName", new StringType());
    query.addScalar("cellId", new StringType());
    query.addScalar("cellCode", new StringType());
    query.addScalar("barcode", new StringType());
    query.addScalar("fromSerial", new StringType());
    query.addScalar("toSerial", new StringType());
    query.addScalar("amountInventory", new StringType());
    query.addScalar("amount", new StringType());
    query.addScalar("note", new StringType());
    query.addScalar("amountFalse", new StringType());
    query.addScalar("type", new StringType());

    int inventoryId = 0;
    try {
        inventoryId = Integer.parseInt(inventoryActionId);
    } catch (Exception e) {
    }
    //SET PARAMETER
    query.setParameter(0, inventoryId);
    query.setParameter(1, inventoryId);
    //
    lstInvenResult = query.list();
    //List list;
    //        if(lstInvenResult == null || lstInvenResult.size()==0)
    //        {
    //            return lstInvenResult;
    //        }
    return lstInvenResult;
}