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.gp.cong.logisoft.hibernate.dao.lcl.LclBookingHotCodeDAO.java

public boolean isHotCodeValidate(Long fileId, String code) throws Exception { // type is not but exists method will affected
    SQLQuery query = getCurrentSession()
            .createSQLQuery("select if(count(*)>0,true,false) as result from lcl_booking_hot_code "
                    + " where SUBSTRING_INDEX(code,'/',1)=:code and file_number_id=:fileId");
    query.setParameter("fileId", fileId);
    query.setParameter("code", code);
    query.addScalar("result", BooleanType.INSTANCE);
    return (Boolean) query.uniqueResult();
}

From source file:com.gp.cong.logisoft.hibernate.dao.lcl.LclBookingHotCodeDAO.java

public Boolean isHazmatHotCodeExist(String fileId) throws Exception {
    StringBuilder queryStr = new StringBuilder();
    queryStr.append("SELECT if((SELECT hazrds FROM ").append(dataBaseName).append(
            ".ldgins WHERE loadcd = SUBSTRING_INDEX(lp.code,'/',1))='y',true,false)  as result  FROM lcl_booking_hot_code lp JOIN genericcode_dup gd ");
    queryStr.append("ON gd.code = SUBSTRING_INDEX(lp.code,'/',1) and ");
    queryStr.append("gd.Codetypeid= (SELECT codetypeid FROM codetype WHERE description='Hot Codes') ");
    queryStr.append("AND gd.Field1='Y' WHERE lp.file_number_id =:fileId limit 1");
    SQLQuery query = getCurrentSession().createSQLQuery(queryStr.toString());
    query.setParameter("fileId", fileId);
    query.addScalar("result", BooleanType.INSTANCE);
    return (Boolean) (query.uniqueResult() != null ? query.uniqueResult() : Boolean.FALSE);
}

From source file:com.gp.cong.logisoft.hibernate.dao.lcl.LclBookingHotCodeDAO.java

public boolean qtHazmatCodeExit(String fileId) throws Exception {
    StringBuilder sb = new StringBuilder();
    sb.append("SELECT ");
    sb.append("  IF(");
    sb.append("    (SELECT ");
    sb.append("      hazrds ");
    sb.append("    FROM ");
    sb.append(dataBaseName).append(".ldgins");
    sb.append("    WHERE loadcd = SUBSTRING_INDEX(lq.code, '/', 1)) = 'y',");
    sb.append("    TRUE,");
    sb.append("    FALSE");
    sb.append("  ) AS hazStatus ");
    sb.append(" FROM");
    sb.append("  lcl_quote_hot_code lq ");
    sb.append("  JOIN genericcode_dup gd ");
    sb.append("    ON gd.code = SUBSTRING_INDEX(lq.code, '/', 1) ");
    sb.append("    AND gd.Codetypeid = ");
    sb.append("    (SELECT ");
    sb.append("      codetypeid ");
    sb.append("    FROM");
    sb.append("      codetype ");
    sb.append("    WHERE description = 'Hot Codes') ");
    sb.append("    AND gd.Field1 = 'Y' ");
    sb.append(" WHERE lq.file_number_id = :fileId");
    SQLQuery queryObject = getCurrentSession().createSQLQuery(sb.toString());
    queryObject.setParameter("fileId", fileId);
    queryObject.addScalar("hazStatus", BooleanType.INSTANCE);
    return (Boolean) (queryObject.uniqueResult() != null ? queryObject.uniqueResult() : Boolean.FALSE);
}

From source file:com.gp.cong.logisoft.hibernate.dao.lcl.LclBookingHotCodeDAO.java

public boolean isHotCodeExistsForConsolidateFile(String fileId, String hotCode) throws Exception {
    StringBuilder queryBuilder = new StringBuilder();
    queryBuilder.append(" SELECT IF(COUNT(*)>0,'true','false') as result FROM lcl_booking_hot_code hc  ");
    queryBuilder.append(" JOIN lcl_file_number fn ON fn.id=hc.file_number_id WHERE  ");
    queryBuilder.append(//from   w  w w.  j ava 2  s .  co  m
            " fn.id IN (SELECT lcl_file_number_id_a FROM `lcl_consolidation` WHERE lcl_file_number_id_b = ");
    queryBuilder.append(
            " (SELECT lcl_file_number_id_b FROM `lcl_consolidation` WHERE lcl_file_number_id_a =:fileId)) ");
    queryBuilder.append(" AND hc.`code` =:hotCode ");
    SQLQuery query = getCurrentSession().createSQLQuery(queryBuilder.toString());
    query.setParameter("fileId", fileId);
    query.setParameter("hotCode", hotCode);
    return (boolean) query.addScalar("result", BooleanType.INSTANCE).uniqueResult();
}

From source file:com.gp.cong.logisoft.hibernate.dao.lcl.LclBookingSegregationDao.java

public Boolean isCheckedSegregationDr(Long fileId) throws Exception {
    StringBuilder queryStr = new StringBuilder();
    queryStr.append("SELECT IF(COUNT(*)>0,true,false) as result FROM lcl_booking_segregation");
    queryStr.append(" WHERE child_file_number_id=:fileId");
    SQLQuery query = getCurrentSession().createSQLQuery(queryStr.toString());
    query.setParameter("fileId", fileId);
    query.addScalar("result", BooleanType.INSTANCE);
    return (Boolean) query.setMaxResults(1).uniqueResult();
}

From source file:com.gp.cong.logisoft.hibernate.dao.lcl.LclQuoteHotCodeDAO.java

public boolean isHotCodeExist(String Code, String fileId) throws Exception {
    SQLQuery query = getCurrentSession()
            .createSQLQuery("select if(count(*)<1,true,false) as result from lcl_quote_hot_code "
                    + " where code=:code and file_number_id=:fileId");
    query.setParameter("fileId", fileId);
    query.setParameter("code", Code);
    query.addScalar("result", BooleanType.INSTANCE);
    return (Boolean) query.uniqueResult();
}

From source file:com.ibm.ioes.utilities.AjaxHelper.java

public ArrayList getUsersOfRole(String roleId) throws Exception {
    ArrayList employeeList = new ArrayList();
    Session hibernateSession = null;// w w w  . ja  va2 s.com

    try {
        hibernateSession = com.ibm.ioes.npd.hibernate.dao.CommonBaseDao.beginTrans();
        String sql = "SELECT   TM_EMPLOYEE.NPDEMPID,TM_EMPLOYEE.EMPNAME FROM   NPD.TM_ROLEEMPMAPPING TM_ROLEEMPMAPPING "
                + "INNER JOIN NPD.TM_ROLES TM_ROLES ON TM_ROLEEMPMAPPING.ROLEID = TM_ROLES.ROLEID"
                + "   INNER JOIN NPD.TM_EMPLOYEE TM_EMPLOYEE "
                + "ON TM_ROLEEMPMAPPING.NPDEMPID = TM_EMPLOYEE.NPDEMPID WHERE TM_ROLES.ROLEID=:roleId";
        SQLQuery query = hibernateSession.createSQLQuery(sql);

        query.setLong("roleId", Long.valueOf(roleId));

        query.addScalar("npdempid", Hibernate.LONG).addScalar("empname", Hibernate.STRING)
                .setResultTransformer(Transformers.aliasToBean(TmEmployee.class));

        employeeList = (ArrayList) query.list();

    } catch (Exception ex) {
        ex.printStackTrace();
        String msg = ex.getMessage() + " Exception occured in getUsersOfRole method of "
                + this.getClass().getSimpleName() + AppUtility.getStackTrace(ex);
        AppConstants.NPDLOGGER.error(msg);
        throw new NpdException(msg);
    } finally {
        hibernateSession.close();
    }
    return employeeList;
}

From source file:com.liferay.jbpm.db.GraphSession.java

License:Open Source License

public List findProcessInstancesBySearchTerms(String definitionName, String definitionVersion,
        String startDateGT, String startDateLT, String endDateGT, String endDateLT, boolean hideEndedTasks,
        String assignedUserId, boolean andOperator, int start, int end) {

    List list = new ArrayList();

    try {/*from w  w  w. j  av  a 2s .  c  o  m*/
        int definitionVersionInt = 0;

        if (!Validator.isNumber(definitionVersion)) {
            definitionVersion = null;
        } else {
            definitionVersionInt = GetterUtil.getInteger(definitionVersion);
        }

        String assignedUserIdInnerJoin = StringPool.BLANK;
        String assignedUserIdCheck = StringPool.BLANK;

        if (Validator.isNotNull(assignedUserId)) {
            assignedUserIdInnerJoin = "INNER JOIN JBPM_TOKEN ON JBPM_TOKEN.PROCESSINSTANCE_ = "
                    + "JBPM_PROCESSINSTANCE.ID_ INNER JOIN "
                    + "JBPM_TASKINSTANCE ON JBPM_TASKINSTANCE.TOKEN_ = " + "JBPM_TOKEN.ID_ ";
            assignedUserIdCheck = "(JBPM_TASKINSTANCE.ACTORID_ = ?) AND ";
        }

        String endDateCheck = "(JBPM_PROCESSINSTANCE.END_ IS NULL) ";

        if (!hideEndedTasks) {
            endDateCheck = "((JBPM_PROCESSINSTANCE.END_ >= ? [$AND_OR_NULL_CHECK$]) "
                    + "AND (JBPM_PROCESSINSTANCE.END_ <= ? " + "[$AND_OR_NULL_CHECK$])) ";
        }

        String sql = CustomSQLUtil.get(FIND_PROCESS_INSTANCES_BY_SEARCH_TERMS);

        sql = StringUtil.replace(sql, "[$ASSIGNED_USER_ID_INNER_JOIN$]", assignedUserIdInnerJoin);
        sql = StringUtil.replace(sql, "[$ASSIGNED_USER_ID_CHECK$]", assignedUserIdCheck);
        sql = StringUtil.replace(sql, "[$END_DATE_CHECK$]", endDateCheck);
        sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);

        SQLQuery q = _session.createSQLQuery(sql);

        q.addScalar("instanceId", Hibernate.LONG);

        QueryPos qPos = QueryPos.getInstance(q);

        qPos.add(definitionName);
        qPos.add(definitionName);
        qPos.add(definitionVersionInt);
        qPos.add(definitionVersion);
        qPos.add(_getDate(startDateGT, true));
        qPos.add(_getDate(startDateGT, true));
        qPos.add(_getDate(startDateLT, false));
        qPos.add(_getDate(startDateLT, false));

        if (Validator.isNotNull(assignedUserId)) {
            qPos.add(assignedUserId);
        }

        if (!hideEndedTasks) {
            qPos.add(_getDate(endDateGT, true));
            qPos.add(_getDate(endDateGT, true));
            qPos.add(_getDate(endDateLT, false));
            qPos.add(_getDate(endDateLT, false));
        }

        Iterator itr = QueryUtil.iterate(q, _dialect, start, end);

        while (itr.hasNext()) {
            Long instanceId = (Long) itr.next();

            ProcessInstance processInstance = _jbpmContext.loadProcessInstance(instanceId.longValue());

            WorkflowUtil.initInstance(processInstance);

            list.add(processInstance);
        }

    } catch (Exception e) {
        _log.error(e, e);

        throw new RuntimeException(e.getMessage());
    }

    return list;
}

From source file:com.liferay.jbpm.db.GraphSession.java

License:Open Source License

public List findTaskInstancesBySearchTerms(String taskName, String definitionName, String assignedTo,
        String createDateGT, String createDateLT, String startDateGT, String startDateLT, String endDateGT,
        String endDateLT, boolean hideEndedTasks, boolean andOperator, int start, int end) {

    List list = new ArrayList();

    try {// www  .j  a  v a2s. com
        String sql = "";

        int index = 0;

        if (Validator.isNull(assignedTo) || assignedTo.equals("all")) {
            sql += "(";
            sql += CustomSQLUtil.get(FIND_TASK_INSTANCES_BY_ACTOR);
            sql += ") ";
            sql += "UNION ";
            sql += "(";
            sql += CustomSQLUtil.get(FIND_TASK_INSTANCES_BY_POOL);
            sql += ") ";

            index = 2;
        } else if (assignedTo.equals("me")) {
            sql += CustomSQLUtil.get(FIND_TASK_INSTANCES_BY_ACTOR);

            index = 1;
        } else if (assignedTo.equals("pool")) {
            sql += CustomSQLUtil.get(FIND_TASK_INSTANCES_BY_POOL);

            index = 1;
        }

        sql += "ORDER BY taskActorId DESC, taskCreate ASC";

        String endDateCheck = "(JBPM_TASKINSTANCE.END_ IS NULL) ";

        if (!hideEndedTasks) {
            endDateCheck = "((JBPM_TASKINSTANCE.END_ >= ? " + "[$AND_OR_NULL_CHECK$]) AND "
                    + "(JBPM_TASKINSTANCE.END_ <= ? [$AND_OR_NULL_CHECK$])) ";
        }

        sql = StringUtil.replace(sql, "[$END_DATE_CHECK$]", endDateCheck);
        sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);

        SQLQuery q = _session.createSQLQuery(sql);

        q.addScalar("taskId", Hibernate.LONG);

        QueryPos qPos = QueryPos.getInstance(q);

        for (int i = 0; i < index; i++) {
            qPos.add(taskName);
            qPos.add(taskName);
            qPos.add(definitionName);
            qPos.add(definitionName);
            qPos.add(_getDate(createDateGT, true));
            qPos.add(_getDate(createDateGT, true));
            qPos.add(_getDate(createDateLT, false));
            qPos.add(_getDate(createDateLT, false));
            qPos.add(_getDate(startDateGT, true));
            qPos.add(_getDate(startDateGT, true));
            qPos.add(_getDate(startDateLT, false));
            qPos.add(_getDate(startDateLT, false));

            if (!hideEndedTasks) {
                qPos.add(_getDate(endDateGT, true));
                qPos.add(_getDate(endDateGT, true));
                qPos.add(_getDate(endDateLT, false));
                qPos.add(_getDate(endDateLT, false));
            }

            qPos.add(_userId);
        }

        Iterator itr = QueryUtil.iterate(q, _dialect, start, end);

        while (itr.hasNext()) {
            Long taskId = (Long) itr.next();

            TaskInstance taskInstance = _jbpmContext.loadTaskInstance(taskId.longValue());

            WorkflowUtil.initTask(taskInstance);

            list.add(taskInstance);
        }
    } catch (Exception e) {
        _log.error(e, e);

        throw new RuntimeException(e.getMessage());
    }

    return list;
}

From source file:com.openkm.dao.NodeBaseDAO.java

License:Open Source License

/**
 * Test for category in use/*  w  w  w . jav  a  2 s  .  co m*/
 */
public boolean isCategoryInUse(String catUuid) throws DatabaseException {
    log.debug("isCategoryInUse({}, {})", catUuid);
    final String qs = "from NodeBase nb where :category in elements(nb.categories)";
    final String sql = "select NCT_NODE from OKM_NODE_CATEGORY where NCT_CATEGORY = :catUuid";
    Session session = null;
    Transaction tx = null;
    boolean check;

    try {
        session = HibernateUtil.getSessionFactory().openSession();
        tx = session.beginTransaction();

        if (Config.NATIVE_SQL_OPTIMIZATIONS) {
            SQLQuery q = session.createSQLQuery(sql);
            // q.setCacheable(true);
            q.setString("catUuid", catUuid);
            q.addScalar("NCT_NODE", StandardBasicTypes.STRING);
            check = !q.list().isEmpty();
        } else {
            Query q = session.createQuery(qs).setCacheable(true);
            q.setString("category", catUuid);
            check = !q.list().isEmpty();
        }

        HibernateUtil.commit(tx);
        log.debug("isCategoryInUse: {}", check);
        return check;
    } catch (HibernateException e) {
        HibernateUtil.rollback(tx);
        throw new DatabaseException(e.getMessage(), e);
    } finally {
        HibernateUtil.close(session);
    }
}