Example usage for org.hibernate SQLQuery setParameter

List of usage examples for org.hibernate SQLQuery setParameter

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

Introduction

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

Prototype

@Override
    NativeQuery<T> setParameter(int position, Object val);

Source Link

Usage

From source file:com.br.uepb.dao.MedicaoBalancaDAO.java

License:Open Source License

/**
 * Metodo para listar a ultima medio do paciente cadastrada
 * @param idPaciente int/*from  www .j a v  a 2  s .  co m*/
 * @return MedicaoBalancaDomain
 */
public MedicaoBalancaDomain obtemUltimaMedicao(int idPaciente) {
    String comando = "select mb.* from medicao_balanca mb " + "where mb.paciente_id = :idPaciente "
            + "order by data_hora desc " + "limit 1";
    SQLQuery query = SessaoAtual().createSQLQuery(comando);
    query.setParameter("idPaciente", idPaciente);
    query.addEntity(MedicaoBalancaDomain.class);

    MedicaoBalancaDomain medicao = (MedicaoBalancaDomain) query.uniqueResult();
    return medicao;
}

From source file:com.br.uepb.dao.MedicaoOximetroDAO.java

License:Open Source License

/**
 * Mtodo para obter a ultima medio cadastrada de acordo com o id do paciente
 * @param idPaciente Id do paciente// ww  w.java  2 s. co m
 * @return MedicaoOximetroDomain
 */
public MedicaoOximetroDomain obtemUltimaMedicao(int idPaciente) {
    String comando = "select mo.* from Medicao_oximetro mo " + "where mo.paciente_id = :idPaciente "
            + "order by data_hora desc " + "limit 1";
    SQLQuery query = SessaoAtual().createSQLQuery(comando);
    query.setParameter("idPaciente", idPaciente);
    query.addEntity(MedicaoOximetroDomain.class);

    MedicaoOximetroDomain medicao = (MedicaoOximetroDomain) query.uniqueResult();
    return medicao;
}

From source file:com.br.uepb.dao.MedicaoPressaoDAO.java

License:Open Source License

/**
 * Mtodo para obter a ultima medio cadastrada
 * @param idPaciente Id do paciente/*from   w  w  w. j a v a 2  s  . c  o  m*/
 * @return MedicaoPressaoDomain
 */
public MedicaoPressaoDomain obtemUltimaMedicao(int idPaciente) {
    String comando = "select mp.* from medicao_pressao mp " + "where mp.paciente_id = :idPaciente "
            + "order by data_hora desc " + "limit 1";
    SQLQuery query = SessaoAtual().createSQLQuery(comando);
    query.setParameter("idPaciente", idPaciente);
    query.addEntity(MedicaoPressaoDomain.class);

    MedicaoPressaoDomain medicao = (MedicaoPressaoDomain) query.uniqueResult();
    return medicao;
}

From source file:com.cloudoa.framework.orm.hibernate.SimpleHibernateDao.java

License:Apache License

public SQLQuery createSQLQuery(final String sqlQueryString, final Object... values) {
    Assert.hasText(sqlQueryString, "queryString?");
    SQLQuery query = getSession().createSQLQuery(sqlQueryString);
    if (values != null) {
        for (int i = 0; i < values.length; i++) {
            query.setParameter(i, values[i]);
        }/*from ww w  .  jav a2  s. c o  m*/
    }
    return query;
}

From source file:com.cms.dao.AppParamsDAO.java

License:Open Source License

public List<AppParamsDTO> getProviderFromStaffCode(String staffCode, String mineName) {
    List<AppParamsDTO> lstCategoryList = null;
    StringBuilder sb = new StringBuilder();
    sb.append("   SELECT    PAR_ID parId,   ");
    sb.append("           DESCRIPTION description,   ");
    sb.append("           PAR_CODE parCode,   ");
    sb.append("           PAR_NAME parName,   ");
    sb.append("           PAR_ORDER parOrder,   ");
    sb.append("           PAR_TYPE parType,   ");
    sb.append("           STATUS status   ");
    sb.append("        FROM APP_PARAMS a   ");
    sb.append("    WHERE        a.PAR_TYPE       = 'PROVIDER' ");
    sb.append("         AND a.STATUS = '1' ");
    sb.append("         AND lower(a.PAR_CODE) IN ");
    sb.append("      ( SELECT DISTINCT lower(ti.provider) ");
    sb.append("        FROM TERM_INFORMATION ti ");
    sb.append("        WHERE ti.IS_CONTACT_INFO is NULL AND exists ");
    sb.append("             (   SELECT DISTINCT cs.tax_code ");
    sb.append("                 FROM customer_status cs ");
    sb.append("                 WHERE 1 = 1 ");
    sb.append("                 AND cs.tax_code = ti.tax_code  ");
    if (!StringUtils.isStringNullOrEmpty(staffCode)) {
        sb.append("             AND cs.staff_code = :staffCode ");
    } else { //Add 15/04/2017 Them dieu kien neu la admin
        return getProviderFromMineName(mineName);
    }/*from w w  w  . j  a va2s  .c  o m*/
    if (!StringUtils.isStringNullOrEmpty(mineName)) {
        if (mineName.contains(",")) {
            sb.append("             AND cs.mine_name IN (:mineName) ");
        } else {
            sb.append("             AND cs.mine_name = :mineName ");
        }
    }
    sb.append("             ) ");
    sb.append("      ) ");
    try {
        SQLQuery query = getSession().createSQLQuery(sb.toString());
        //            if (!DataUtil.isStringNullOrEmpty(mineName)) {
        //                
        //                query.setParameter("mineName", mineName);
        //            }
        if (!DataUtil.isStringNullOrEmpty(mineName)) {
            if (mineName.contains(",")) {
                query.setParameterList("mineName", DataUtil.parseInputListString(mineName));
            } else {
                query.setParameter("mineName", mineName);
            }
        }
        if (!StringUtils.isStringNullOrEmpty(staffCode)) {
            query.setParameter("staffCode", staffCode);
        }
        query.setResultTransformer(Transformers.aliasToBean(AppParamsDTO.class));
        query.addScalar("parId", new StringType());
        query.addScalar("description", new StringType());
        query.addScalar("parCode", new StringType());
        query.addScalar("parName", new StringType());
        query.addScalar("parOrder", new StringType());
        query.addScalar("parType", new StringType());
        query.addScalar("status", new StringType());
        lstCategoryList = query.list();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return lstCategoryList;
}

From source file:com.cms.dao.AppParamsDAO.java

License:Open Source License

public List<AppParamsDTO> getProviderFromStaffCodeAndConditions(String staffCode, Map<String, String> map) {

    List<AppParamsDTO> lstCategoryList = null;
    String mineName = map.get("mineName");
    String taxAuthority = map.get("taxAuthority");
    String status = map.get("status");

    if (StringUtils.isStringNullOrEmpty(staffCode)) {
        //Add 17/04/2017 Them dieu kien neu la admin + cac dieu kien status, tinh
        return getProviderFromOtherConditions(mineName, taxAuthority, status);
    }// w w w  .ja v a  2 s.  com
    StringBuilder sb = new StringBuilder();
    sb.append("   SELECT    PAR_ID parId,   ");
    sb.append("           DESCRIPTION description,   ");
    sb.append("           PAR_CODE parCode,   ");
    sb.append("           PAR_NAME parName,   ");
    sb.append("           PAR_ORDER parOrder,   ");
    sb.append("           PAR_TYPE parType,   ");
    sb.append("           STATUS status   ");
    sb.append("        FROM APP_PARAMS a   ");
    sb.append("    WHERE        a.PAR_TYPE       = 'PROVIDER' ");
    sb.append("         AND a.STATUS = '1' ");
    sb.append("         AND lower(a.PAR_CODE) IN ");
    sb.append("      ( SELECT DISTINCT lower(ti.provider) ");
    sb.append("        FROM TERM_INFORMATION ti ");

    if (!StringUtils.isStringNullOrEmpty(taxAuthority)) {
        sb.append("        INNER JOIN CUSTOMER c ON c.TAX_CODE = ti.TAX_CODE ");
    }
    sb.append("        WHERE 1= 1 ");
    if (!StringUtils.isStringNullOrEmpty(taxAuthority)) {
        sb.append("    AND c.TAX_AUTHORITY = ANY(:taxAuthority) ");
    }
    sb.append("        AND  exists ");
    sb.append("             (   SELECT DISTINCT cs.tax_code ");
    sb.append("                 FROM customer_status cs ");
    sb.append("                 WHERE 1 = 1  ");
    sb.append("                 AND cs.tax_code = ti.tax_code  ");
    if (!StringUtils.isStringNullOrEmpty(status)) {
        sb.append("                 AND cs.status = ANY (:status) ");
    }
    if (!StringUtils.isStringNullOrEmpty(staffCode)) {
        sb.append("             AND cs.staff_code = :staffCode ");
    } else { //Add 17/04/2017 Them dieu kien neu la admin + cac dieu kien status, tinh
        return getProviderFromOtherConditions(mineName, taxAuthority, status);
    }
    if (!StringUtils.isStringNullOrEmpty(mineName)) {
        if (mineName.contains(",")) {
            sb.append("             AND cs.mine_name IN (:mineName) ");
        } else {
            sb.append("             AND cs.mine_name = :mineName ");
        }
    }
    sb.append("             ) ");
    sb.append("      ) ");
    try {
        SQLQuery query = getSession().createSQLQuery(sb.toString());
        if (!DataUtil.isStringNullOrEmpty(mineName)) {
            if (mineName.contains(",")) {
                query.setParameterList("mineName", DataUtil.parseInputListString(mineName));
            } else {
                query.setParameter("mineName", mineName);
            }
        }
        if (!StringUtils.isStringNullOrEmpty(staffCode)) {
            query.setParameter("staffCode", staffCode);
        }
        if (!StringUtils.isStringNullOrEmpty(status)) {
            query.setParameterList("status", DataUtil.parseInputListString(status));
        }
        if (!StringUtils.isStringNullOrEmpty(taxAuthority)) {
            query.setParameterList("taxAuthority", DataUtil.parseInputListString(taxAuthority));
        }
        query.setResultTransformer(Transformers.aliasToBean(AppParamsDTO.class));
        query.addScalar("parId", new StringType());
        query.addScalar("description", new StringType());
        query.addScalar("parCode", new StringType());
        query.addScalar("parName", new StringType());
        query.addScalar("parOrder", new StringType());
        query.addScalar("parType", new StringType());
        query.addScalar("status", new StringType());
        lstCategoryList = query.list();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return lstCategoryList;
}

From source file:com.cms.dao.AppParamsDAO.java

License:Open Source License

public List<AppParamsDTO> getProviderFromMineName(String mineName) {
    List<AppParamsDTO> lstCategoryList = null;
    StringBuilder sb = new StringBuilder();
    sb.append("   SELECT    ap.PAR_ID parId,   ");
    sb.append("           ap.DESCRIPTION description,   ");
    sb.append("           ap.PAR_CODE parCode,   ");
    sb.append("           ap.PAR_NAME parName,   ");
    sb.append("           ap.PAR_ORDER parOrder,   ");
    sb.append("           ap.PAR_TYPE parType,   ");
    sb.append("           ap.STATUS status   ");
    sb.append("   FROM APP_PARAMS ap ");
    sb.append("   WHERE PAR_CODE IN ");
    sb.append("     (SELECT DISTINCT provider ");
    sb.append("     FROM term_information ti ");
    sb.append("     WHERE ti.IS_CONTACT_INFO is NULL AND lower(ti.PROVIDER) LIKE (lower(ap.par_code)||'%') ");
    if (!StringUtils.isStringNullOrEmpty(mineName)) {
        if (mineName.contains(",")) {
            sb.append("             AND ti.mine_name IN (:mineName) ");
        } else {//  w w  w . j  ava 2s.c  o m
            sb.append("             AND ti.mine_name = :mineName ");
        }
        //            sb.append("     AND ti.MINE_NAME = :mineName ");
    }
    sb.append("     ) ");
    sb.append("   ORDER BY ap.PAR_ORDER ");

    try {
        SQLQuery query = getSession().createSQLQuery(sb.toString());
        if (!DataUtil.isStringNullOrEmpty(mineName)) {
            if (mineName.contains(",")) {
                query.setParameterList("mineName", DataUtil.parseInputListString(mineName));
            } else {
                query.setParameter("mineName", mineName);
            }
        }

        query.setResultTransformer(Transformers.aliasToBean(AppParamsDTO.class));
        query.addScalar("parId", new StringType());
        query.addScalar("description", new StringType());
        query.addScalar("parCode", new StringType());
        query.addScalar("parName", new StringType());
        query.addScalar("parOrder", new StringType());
        query.addScalar("parType", new StringType());
        query.addScalar("status", new StringType());
        lstCategoryList = query.list();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return lstCategoryList;
}

From source file:com.cms.dao.AppParamsDAO.java

License:Open Source License

public List<AppParamsDTO> getProviderFromOtherConditions(String mineName, String taxAuthority, String status) {
    List<AppParamsDTO> lstCategoryList = null;
    StringBuilder sb = new StringBuilder();
    sb.append("   SELECT    ap.PAR_ID parId,   ");
    sb.append("           ap.DESCRIPTION description,   ");
    sb.append("           ap.PAR_CODE parCode,   ");
    sb.append("           ap.PAR_NAME parName,   ");
    sb.append("           ap.PAR_ORDER parOrder,   ");
    sb.append("           ap.PAR_TYPE parType,   ");
    sb.append("           ap.STATUS status   ");
    sb.append("   FROM APP_PARAMS ap ");
    sb.append("   WHERE lower(PAR_CODE) IN ");
    sb.append("     (SELECT DISTINCT lower(provider) ");
    sb.append("     FROM term_information ti ");
    if (!StringUtils.isStringNullOrEmpty(taxAuthority)) {
        sb.append("     INNER JOIN CUSTOMER c ON c.TAX_CODE = ti.TAX_CODE ");
    }/*from  w  w  w  .  ja v  a 2 s  .c o  m*/
    if (!StringUtils.isStringNullOrEmpty(status)) {
        sb.append("     INNER JOIN CUSTOMER_STATUS cs ON cs.TAX_CODE = ti.TAX_CODE ");
    }
    sb.append("     WHERE ti.IS_CONTACT_INFO is NULL AND lower(ti.PROVIDER) = (lower(ap.par_code))");
    if (!StringUtils.isStringNullOrEmpty(mineName)) {
        if (mineName.contains(",")) {
            sb.append("             AND ti.mine_name IN (:mineName) ");
        } else {
            sb.append("             AND ti.mine_name = :mineName ");
        }
    }
    if (!StringUtils.isStringNullOrEmpty(taxAuthority)) {
        sb.append("     AND c.TAX_AUTHORITY = ANY (:taxAuthority) ");
    }
    if (!StringUtils.isStringNullOrEmpty(status)) {
        sb.append("     AND cs.STATUS = :status ");
    }
    sb.append("     ) ");
    sb.append("   ORDER BY ap.PAR_ORDER ");

    try {
        SQLQuery query = getSession().createSQLQuery(sb.toString());
        if (!DataUtil.isStringNullOrEmpty(mineName)) {
            if (mineName.contains(",")) {
                query.setParameterList("mineName", DataUtil.parseInputListString(mineName));
            } else {
                query.setParameter("mineName", mineName);
            }
        }
        if (!StringUtils.isStringNullOrEmpty(taxAuthority)) {
            query.setParameterList("taxAuthority", DataUtil.parseInputListString(taxAuthority));
        }
        if (!StringUtils.isStringNullOrEmpty(status)) {
            query.setParameter("status", status);
        }
        query.setResultTransformer(Transformers.aliasToBean(AppParamsDTO.class));
        query.addScalar("parId", new StringType());
        query.addScalar("description", new StringType());
        query.addScalar("parCode", new StringType());
        query.addScalar("parName", new StringType());
        query.addScalar("parOrder", new StringType());
        query.addScalar("parType", new StringType());
        query.addScalar("status", new StringType());
        lstCategoryList = query.list();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return lstCategoryList;
}

From source file:com.cms.dao.AppParamsDAO.java

License:Open Source License

public List<AppParamsDTO> getStatusFromStaffCode(String staffCode, String mineName) {
    List<AppParamsDTO> lstCategoryList = null;
    StringBuilder sb = new StringBuilder();
    sb.append("   SELECT    PAR_ID parId,   ");
    sb.append("           DESCRIPTION description,   ");
    sb.append("           PAR_CODE parCode,   ");
    sb.append("           PAR_NAME parName,   ");
    sb.append("           PAR_ORDER parOrder,   ");
    sb.append("           PAR_TYPE parType,   ");
    sb.append("           STATUS status   ");
    sb.append("        FROM APP_PARAMS a   ");
    sb.append("    WHERE        a.PAR_TYPE       = 'CUSTOMER_SERVICE_STATUS' ");
    sb.append("         AND a.STATUS = '1' ");
    sb.append("         AND a.PAR_CODE IN ");
    sb.append("             (   SELECT DISTINCT cs.status ");
    sb.append("                 FROM customer_status cs ");
    sb.append("                 WHERE 1 = 1  ");
    if (!StringUtils.isStringNullOrEmpty(staffCode)) {
        sb.append("             AND cs.staff_code = :staffCode ");
    }//from   w  w w.  ja va 2 s  .  com
    if (!StringUtils.isStringNullOrEmpty(mineName)) {
        if (mineName.contains(",")) {
            sb.append("             AND cs.mine_name IN (:mineName) ");
        } else {
            sb.append("             AND cs.mine_name = :mineName ");
        }
    }
    sb.append("             ) ");
    try {
        SQLQuery query = getSession().createSQLQuery(sb.toString());
        if (!DataUtil.isStringNullOrEmpty(mineName)) {
            if (mineName.contains(",")) {
                query.setParameterList("mineName", DataUtil.parseInputListString(mineName));
            } else {
                query.setParameter("mineName", mineName);
            }

        }
        if (!StringUtils.isStringNullOrEmpty(staffCode)) {
            query.setParameter("staffCode", staffCode);
        }
        query.setResultTransformer(Transformers.aliasToBean(AppParamsDTO.class));
        query.addScalar("parId", new StringType());
        query.addScalar("description", new StringType());
        query.addScalar("parCode", new StringType());
        query.addScalar("parName", new StringType());
        query.addScalar("parOrder", new StringType());
        query.addScalar("parType", new StringType());
        query.addScalar("status", new StringType());
        lstCategoryList = query.list();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return lstCategoryList;
}

From source file:com.cms.dao.CategoryListDAO.java

License:Open Source License

public void updateQuanlityForCategoryList(String categoryId) {
    StringBuilder sb = new StringBuilder();
    sb.append(" UPDATE CATEGORY_LIST a SET a.CUST_QUANTITY = "
            + "(select count(distinct ti.tax_code) from term_information ti "
            + "where ti.MINE_NAME = a.id) WHERE a.id= :idx0 ");
    try {/*  www  . ja v a 2 s. c o m*/
        SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sb.toString());
        query.setParameter("idx0", categoryId);
        query.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    }
}