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.lushapp.common.orm.hibernate.SimpleHibernateDao.java

License:Apache License

/**
 * ?SQL?SQLQuery./*from  ww w . j a v  a 2s.  c  o m*/
 * 
 * @param values ????,?.
 */
public SQLQuery createSQLQuery(final String sql, final Object... values) {
    Assert.hasText(sql, "sql?");
    SQLQuery sqlQuery = getSession().createSQLQuery(sql);
    if (values != null) {
        for (int i = 0; i < values.length; i++) {
            sqlQuery.setParameter(i, values[i]);
        }
    }
    return sqlQuery;
}

From source file:com.lushapp.common.orm.hibernate.SimpleHibernateDao.java

License:Apache License

/**
 * ?SQL?SQLQuery.//ww  w  . j a va  2s.c om
 * 
 * @param sql
 * @param values ???,??.
 * @return
 */
public SQLQuery createSQLQuery(final String sql, final Map<String, ?> values) {
    Assert.hasText(sql, "sql?");
    SQLQuery sqlQuery = getSession().createSQLQuery(sql);
    if (values != null) {
        Set<String> set = values.keySet();
        for (String s : set) {
            sqlQuery.setParameter(s, values.get(s));
        }
    }
    return sqlQuery;
}

From source file:com.lynn.dao.BaseDao.java

/**
 * SQL?List<Map>//w  ww.ja v a2  s  .com
 * select id,name from member.
* @author WKX
* @param sql
* @param values
* @since 2016112 ?3:32:17
*/
public List<Map<String, Object>> getListMapBySQL(String sql, Object... values) {
    SQLQuery query = this.getSession().createSQLQuery(sql);
    if (values != null) {
        for (int i = 0; i < values.length; i++) {
            query.setParameter(i, values[i]);
        }
    }
    query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
    List<Map<String, Object>> result = query.list();
    return result;
}

From source file:com.miraflorescarwash.dao.ClienteDAOImpl.java

@Override
public List<ClienteReporte> reporteComprasPorMesPorCliente(Long idCliente) {
    List lista;/*w  w w .ja v a2  s  . co  m*/
    String consulta;

    consulta = "SELECT * FROM public.ventas_cliente_por_mes(?)";
    SQLQuery query = super.getSession().createSQLQuery(consulta);
    query.setParameter(0, idCliente);
    lista = query.list();
    return this.obtenerListaReportes(lista);
}

From source file:com.miraflorescarwash.dao.ClienteDAOImpl.java

@Override
public List<CreditoDisponibleCliente> verCreditoDisponible(Long idCliente) {
    List lista;//from   w w w  .  j  a v  a2s . c  om
    List<CreditoDisponibleCliente> out;
    String consulta;
    Object a[];

    consulta = "SELECT * FROM public.credito_cliente(?)";
    SQLQuery query = super.getSession().createSQLQuery(consulta);
    query.setParameter(0, idCliente);
    lista = query.list();

    out = new ArrayList<>();
    if (lista != null) {
        for (Object l : lista) {
            a = (Object[]) l;
            out.add(new CreditoDisponibleCliente(Long.valueOf(a[0].toString()), a[1].toString(),
                    a[2].toString(), a[3].toString(), Integer.valueOf(a[4].toString()), a[5].toString()));
        }
    }
    return out;
}

From source file:com.miraflorescarwash.dao.LavadaDAOImpl.java

@Override
public List<Double> obtenerLista(int tipo, int cantidadRegistros) {
    List<Object> lista;
    List<Double> out;
    SQLQuery sql;
    Object[] a;//from  w  w  w . ja v a  2s.  c o  m

    out = new ArrayList<>();
    switch (tipo) {
    case Constantes.REPORTE_LAVADA_DIARIO:
        sql = super.getSession().createSQLQuery("SELECT * FROM public.ventas_pordia (:cantidad);");
        sql.setParameter("cantidad", cantidadRegistros);
        lista = sql.list();
        //                lista = super.execProdecure("", cantidadRegistros);
        break;
    case Constantes.REPORTE_LAVADA_SEMANAL:
        sql = super.getSession().createSQLQuery("SELECT * FROM public.ventas_porsemana (:cantidad);");
        sql.setParameter("cantidad", cantidadRegistros);
        lista = sql.list();
        //                lista = super.execProdecure("ventas_porsemana", cantidadRegistros);
        break;
    case Constantes.REPORTE_LAVADA_MENSUAL:
        sql = super.getSession().createSQLQuery("SELECT * FROM public.ventas_pormes (:cantidad);");
        sql.setParameter("cantidad", cantidadRegistros);
        lista = sql.list();
        //                lista = super.execProdecure("ventas_pormes", cantidadRegistros);
        break;
    default:
        lista = null;
        break;
    }
    if (lista != null) {
        for (Object val : lista) {
            out.add(Double.valueOf(val.toString()));
        }
    }
    return out;
}

From source file:com.mobileman.projecth.persistence.doctor.impl.DoctorDaoImpl.java

License:Apache License

/** 
 * {@inheritDoc}/*  w ww. j a v  a 2  s  .  c  o m*/
 * @see com.mobileman.projecth.persistence.doctor.DoctorDao#findPatientsCountByAgeByDisease(java.lang.Long)
 */
@Override
@SuppressWarnings("unchecked")
public List<Object[]> findPatientsCountByAgeByDisease(Long doctorId) throws IllegalArgumentException {
    if (log.isDebugEnabled()) {
        log.debug("findPatientsCountByAgeByDisease(Long) - start"); //$NON-NLS-1$
    }

    final String sqlQuery = "select count(patient0_.id) as col_0_0_,  " + "case "
            + "   when patient0_.birthday is null then 0 "
            + "   when age(to_date(to_char(patient0_.birthday, '9999'), 'YYYY')) <= interval '19 year' then 1 "
            + "   when age(to_date(to_char(patient0_.birthday, '9999'), 'YYYY')) between interval '20 year' and interval '29 year' then 2 "
            + "   when age(to_date(to_char(patient0_.birthday, '9999'), 'YYYY')) between interval '30 year' and interval '39 year' then 3 "
            + "   when age(to_date(to_char(patient0_.birthday, '9999'), 'YYYY')) between interval '40 year' and interval '49 year' then 4 "
            + "   when age(to_date(to_char(patient0_.birthday, '9999'), 'YYYY')) between interval '50 year' and interval '59 year' then 5 "
            + "   when age(to_date(to_char(patient0_.birthday, '9999'), 'YYYY')) between interval '60 year' and interval '69 year' then 6 "
            + "   else 7 " + "end, "
            + "disease2_.id as col_2_0_, disease2_.code as col_3_0_, disease2_.name as col_4_0_  "
            + "from \"user\" patient0_  "
            + "inner join user_disease diseases1_ on patient0_.id=diseases1_.user_id  "
            + "inner join disease disease2_ on diseases1_.disease_id=disease2_.id  "
            + "where patient0_.user_type='P'  " + "and patient0_.id in ( "
            + "   select (case when userconnec3_.user_id=? then userconnec3_.owner_id else userconnec3_.user_id end)  "
            + "   from user_connection userconnec3_  "
            + "   where userconnec3_.state='A' and (userconnec3_.owner_id=? " + "   or userconnec3_.user_id=?) "
            + ") " + "group by patient0_.birthday, disease2_.id, disease2_.code, disease2_.name "
            + "order by patient0_.birthday, disease2_.id";
    SQLQuery query = getSession().createSQLQuery(sqlQuery);
    query.setParameter(0, doctorId);
    query.setParameter(1, doctorId);
    query.setParameter(2, doctorId);

    List<Object[]> dataList = query.list();

    List<Object[]> result = new ArrayList<Object[]>(dataList.size());
    for (int i = 0; i < dataList.size(); i++) {
        Object[] data = dataList.get(i);
        Disease disease = new Disease(Number.class.cast(data[2]).longValue(), (String) data[3],
                (String) data[4]);
        int group = Number.class.cast(data[1]).intValue();
        result.add(new Object[] { Number.class.cast(data[0]).longValue(), new Integer(group), disease });
    }

    if (log.isDebugEnabled()) {
        log.debug("findPatientsCountByAgeByDisease(Long) - returns"); //$NON-NLS-1$
    }
    return result;
}

From source file:com.mobileman.projecth.persistence.patient.impl.PatientKPIDaoImpl.java

License:Apache License

/**
 * {@inheritDoc}/*from w ww.  j a v  a 2  s . c  o  m*/
 * @see com.mobileman.projecth.persistence.patient.PatientKPIDao#findPatientsKpiAverageScoreTimelineByDisease(java.lang.Long, java.util.Date, java.util.Date)
 */
@Override
@SuppressWarnings("unchecked")
public List<KeyPerformanceIndicatorStatistics> findPatientsKpiAverageScoreTimelineByDisease(Long doctorId,
        Date startDate, Date endDate) {
    if (log.isDebugEnabled()) {
        log.debug("findPatientsKpiAverageScoreTimelineByDisease(Long, Date, Date) - start"); //$NON-NLS-1$
    }

    final String sqlQuery = "select cast(pkpi.log_date as date), "
            + "kpit.id as kpitid, kpit.code as kpitcode, kpit.description as kpitdesc, "
            + "disease2_.id as did, disease2_.code as dcode, disease2_.name as dname, " + "avg(pkpi.kpi_value) "
            + "from patient_key_performance_indicator pkpi "
            + "inner join key_performance_indicator_type kpit on kpit.id=pkpi.validation_type_id "
            + "inner join disease disease2_ on disease2_.id=kpit.disease_id " + "where pkpi.patient_id in ("
            + "   select (case when userconnec3_.user_id=? then userconnec3_.owner_id else userconnec3_.user_id end) "
            + "   from user_connection userconnec3_ "
            + "   where userconnec3_.state='A' and (userconnec3_.owner_id=? or userconnec3_.user_id=?)" + ") "
            + "and cast(pkpi.log_date as date) between cast(? as date) and cast(? as date) "
            + "group by cast(pkpi.log_date as date), " + "kpit.id, kpit.code, kpit.description,"
            + "disease2_.id, disease2_.code, disease2_.name " + "order by 1, 2";
    SQLQuery query = getSession().createSQLQuery(sqlQuery);
    query.setParameter(0, doctorId);
    query.setParameter(1, doctorId);
    query.setParameter(2, doctorId);
    query.setParameter(3, startDate);
    query.setParameter(4, endDate);

    List<Object[]> dataList = query.list();
    Map<Long, KeyPerformanceIndicatorType> kpiCache = new HashMap<Long, KeyPerformanceIndicatorType>();
    Map<Long, Disease> diseaseCache = new HashMap<Long, Disease>();

    List<KeyPerformanceIndicatorStatistics> result = new ArrayList<KeyPerformanceIndicatorStatistics>(
            dataList.size());
    for (int i = 0; i < dataList.size(); i++) {
        Object[] data = dataList.get(i);

        Disease disease = findDiseasee(data, diseaseCache);
        KeyPerformanceIndicatorType kpit = findKpiType(data, kpiCache);
        if (kpit.getDisease() == null) {
            kpit.setDisease(disease);
        }

        kpit.setDisease(disease);
        double kpiAvg = Number.class.cast(data[7]).doubleValue();
        KeyPerformanceIndicatorStatistics item = new KeyPerformanceIndicatorStatistics(new BigDecimal(kpiAvg),
                (Date) data[0], kpit);
        result.add(item);
    }

    if (log.isDebugEnabled()) {
        log.debug("findPatientsKpiAverageScoreTimelineByDisease(Long, Date, Date) - returns"); //$NON-NLS-1$
    }
    return result;
}

From source file:com.mobileman.projecth.persistence.patient.impl.PatientKPIDaoImpl.java

License:Apache License

/**
 * {@inheritDoc}//ww w. j a  v  a 2  s.c o  m
 * @see com.mobileman.projecth.persistence.patient.PatientKPIDao#findPatientsKpiAverageScoreByDisease(java.lang.Long)
 */
@Override
@SuppressWarnings("unchecked")
public List<Object[]> findPatientsKpiAverageScoreByDisease(Long doctorId) {
    if (log.isDebugEnabled()) {
        log.debug("findPatientsKpiAverageScoreByDisease(Long) - start"); //$NON-NLS-1$
    }

    final String sqlQuery = "select kpit.id as kpitid, kpit.code as kpitcode, kpit.description as kpitdesc, "
            + "disease2_.id as did, disease2_.code as dcode, disease2_.name as dname, " + "avg(pkpi.kpi_value) "
            + "from patient_key_performance_indicator pkpi "
            + "inner join key_performance_indicator_type kpit on kpit.id=pkpi.validation_type_id "
            + "inner join disease disease2_ on disease2_.id=kpit.disease_id " + "where pkpi.patient_id in ("
            + "   select (case when userconnec3_.user_id=? then userconnec3_.owner_id else userconnec3_.user_id end) "
            + "   from user_connection userconnec3_ "
            + "   where userconnec3_.state='A' and (userconnec3_.owner_id=? or userconnec3_.user_id=?)" + ") "
            + "group by kpit.id, kpit.code, kpit.description," + "disease2_.id, disease2_.code, disease2_.name "
            + "order by 1, 2";
    SQLQuery query = getSession().createSQLQuery(sqlQuery);
    query.setParameter(0, doctorId);
    query.setParameter(1, doctorId);
    query.setParameter(2, doctorId);

    List<Object[]> dataList = query.list();

    List<Object[]> result = new ArrayList<Object[]>(dataList.size());
    for (int i = 0; i < dataList.size(); i++) {
        Object[] data = dataList.get(i);
        int index = 0;
        KeyPerformanceIndicatorType kpit = new KeyPerformanceIndicatorType(
                Number.class.cast(data[index++]).longValue(), (String) data[index++], (String) data[index++]);
        Disease disease = new Disease(Number.class.cast(data[index++]).longValue(), (String) data[index++],
                (String) data[index++]);
        kpit.setDisease(disease);
        double kpiAvg = Number.class.cast(data[index++]).doubleValue();
        result.add(new Object[] { data[0], kpit, Double.valueOf(kpiAvg) });
    }

    if (log.isDebugEnabled()) {
        log.debug("findPatientsKpiAverageScoreByDisease(Long) - returns"); //$NON-NLS-1$
    }
    return result;
}

From source file:com.mtech.springsecurity.dao.AccountingPeriodDaoImpl.java

public Integer getMaxBatch(SMEEntity entity) {
    Session iSess = getSession();/* w w  w. j  a  va  2s  .  c  om*/
    SQLQuery query = iSess.createSQLQuery(
            "select max(ap.batchNumber) batchnumber from accounting_periods ap where ap.sme_id = :id");
    query.setParameter("id", entity.getId());
    query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
    List data = query.list();
    logger.warn("accountingperiod::" + data.size());
    Integer number = null;
    for (Object object : data) {
        java.util.Map row = (java.util.Map) object;
        System.out.print("Max number: " + row.get("batchnumber"));
        number = (Integer) row.get("batchnumber");
    }
    return number;
}