Example usage for org.hibernate SQLQuery setDate

List of usage examples for org.hibernate SQLQuery setDate

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

Introduction

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

Prototype

@Deprecated
@SuppressWarnings("unchecked")
default Query<R> setDate(int position, Date val)

Source Link

Document

Bind a positional Date-valued parameter using just the Date portion.

Usage

From source file:com.neu.web.phmis.dao.ResourceDao.java

public int isDateAvailable(Date date, SurgeryRequestBean surgeryRequestBean) {

    int flag = 0;

    try {/*from  w  w w  .  ja  va2 s  .co  m*/

        session = HibernateUtil.getSessionFactory().openSession();
        SQLQuery query = session.createSQLQuery("CALL isDateAvailable (:1, :2, :3);");
        query.setInteger("1", surgeryRequestBean.getCreatedBy().getEnterpriseBean().getId());
        query.setDate("2", date);
        query.setInteger("3", surgeryRequestBean.getStaffCount());
        List<Integer> list = query.list();
        flag = list.get(0).intValue();

    } catch (HibernateException e) {
        session.getTransaction().rollback();
        surgeryRequestBean = null;
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        session.close();
        return flag;
    }

}

From source file:com.neu.web.phmis.dao.ResourceDao.java

public void scheduleSurgery(SurgeryBean surgeryBean) {

    try {//from   w  w  w.j av  a  2  s  . co m

        session = HibernateUtil.getSessionFactory().openSession();
        SQLQuery query = session.createSQLQuery("CALL addSurgery (:1, :2, :3)");
        session.beginTransaction();
        query.setInteger("1", surgeryBean.getId());
        query.setDate("2", surgeryBean.getScheduleDate());
        query.setInteger("3", surgeryBean.getOperatingRoomBean().getId());

        query.executeUpdate();
        session.getTransaction().commit();

    } catch (HibernateException e) {
        e.printStackTrace();
        session.getTransaction().rollback();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        session.close();
    }

}

From source file:com.sapienter.jbilling.server.metafields.db.MetaFieldDAS.java

License:Open Source License

public List<Integer> getCustomerFieldValues(Integer customerId, MetaFieldType type, Integer groupId,
        Date effectiveDate) {/*from  w ww .ja v a2s.  com*/
    if (null == customerId || null == type || null == groupId) {
        throw new IllegalArgumentException("can have null arguments for customer, type or group");
    }

    SQLQuery query = getSession().createSQLQuery(findCustomerValuesByGroupSQL);
    query.setParameter("type", type.toString());
    query.setParameter("customer", customerId);
    query.setParameter("groupId", groupId);
    query.setDate("startDate", effectiveDate);
    return query.list();
}

From source file:com.sapienter.jbilling.server.metafields.db.MetaFieldDAS.java

License:Open Source License

public List<Integer> getValuesByCustomerAndFields(Integer customerId, List<Integer> fields) {
    if (null == customerId || null == fields || fields.size() == 0) {
        throw new IllegalArgumentException(" customer and fields can not be null");
    }//from   w ww.  ja  v a2s .c o  m

    SQLQuery query = getSession().createSQLQuery(getValuesByCustomerFields);
    query.setParameter("customer", customerId);
    query.setParameterList("fields", fields);

    query.setDate("startDate", new Date());

    return query.list();
}

From source file:es.logongas.fpempresa.dao.empresa.impl.OfertaDAOImplHibernate.java

License:Open Source License

@Override
public List<Oferta> getOfertasUsuarioTitulado(DataSession dataSession, Usuario usuario, Provincia provincia,
        Date fechaInicio, Date fechaFin) {
    Session session = (Session) dataSession.getDataBaseSessionImpl();

    StringBuilder sb = new StringBuilder();

    sb.append("SELECT oferta.*\n" + "FROM\n" + "   oferta   \n"
            + "      INNER JOIN  ofertaciclo   ON oferta.idOferta=ofertaciclo.idOferta \n"
            + "      INNER JOIN  municipio as municipiooferta ON oferta.idMunicipio=municipiooferta.idMunicipio\n"
            + "      INNER JOIN  empresa           ON oferta.idEmpresa=empresa.idEmpresa,\n" + "   usuario \n"
            + "      INNER JOIN  formacionacademica ON usuario.idTitulado=formacionacademica.idTitulado \n"
            + "      INNER JOIN  titulado                     ON usuario.idTitulado=titulado.idTitulado  \n"
            + "      INNER JOIN  municipio as municipiotitulado           ON titulado.idMunicipio=municipiotitulado.idMunicipio\n"
            + "WHERE\n" + "   oferta.cerrada <> 1 AND\n" + "   usuario.idIdentity=? AND\n"
            + "   empresa.idCentro IS NULL AND\n" + "   ofertaciclo.idCiclo=formacionacademica.idCiclo AND\n");

    if (provincia != null) {
        sb.append("   municipiooferta.idProvincia=? AND\n");
    }//from   w ww .  java2s  . c  o  m
    if (fechaInicio != null) {
        sb.append("   oferta.fecha>=? AND\n");
    }
    if (fechaFin != null) {
        sb.append("   oferta.fecha<=? AND\n");
    }

    sb.append(
            "   not exists (SELECT * FROM  candidato WHERE candidato.idIdentity=usuario.idIdentity AND candidato.idOferta=oferta.idOferta)\n");

    sb.append(" UNION DISTINCT \n");

    sb.append("SELECT oferta.*\n" + "FROM\n" + "   oferta   \n"
            + "      INNER JOIN  ofertaciclo   ON oferta.idOferta=ofertaciclo.idOferta \n"
            + "      INNER JOIN  municipio as municipiooferta ON oferta.idMunicipio=municipiooferta.idMunicipio\n"
            + "      INNER JOIN  empresa           ON oferta.idEmpresa=empresa.idEmpresa,\n" + "   usuario \n"
            + "      INNER JOIN  formacionacademica ON usuario.idTitulado=formacionacademica.idTitulado   \n"
            + "WHERE\n" + "   oferta.cerrada <> 1 AND\n" + "   usuario.idIdentity=? AND\n"
            + "   empresa.idCentro = formacionacademica.idCentro AND\n"
            + "   ofertaciclo.idCiclo=formacionacademica.idCiclo AND\n");
    if (provincia != null) {
        sb.append("   municipiooferta.idProvincia=? AND\n");
    }
    if (fechaInicio != null) {
        sb.append("   oferta.fecha>=? AND\n");
    }
    if (fechaFin != null) {
        sb.append("   oferta.fecha<=? AND\n");
    }

    sb.append(
            "   not exists (SELECT * FROM candidato WHERE candidato.idIdentity=usuario.idIdentity AND candidato.idOferta=oferta.idOferta)");
    sb.append(" ORDER BY fecha DESC");

    SQLQuery sqlQuery = session.createSQLQuery(sb.toString());
    sqlQuery.addEntity(Oferta.class);

    int paramNumber = 0;

    sqlQuery.setInteger(paramNumber++, usuario.getIdIdentity());
    if (provincia != null) {
        sqlQuery.setInteger(paramNumber++, provincia.getIdProvincia());
    }
    if (fechaInicio != null) {
        sqlQuery.setDate(paramNumber++, fechaInicio);
    }
    if (fechaFin != null) {
        sqlQuery.setDate(paramNumber++, fechaFin);
    }
    sqlQuery.setInteger(paramNumber++, usuario.getIdIdentity());
    if (provincia != null) {
        sqlQuery.setInteger(paramNumber++, provincia.getIdProvincia());
    }
    if (fechaInicio != null) {
        sqlQuery.setDate(paramNumber++, fechaInicio);
    }
    if (fechaFin != null) {
        sqlQuery.setDate(paramNumber++, fechaFin);
    }

    return (List<Oferta>) sqlQuery.list();
}

From source file:fr.gael.dhus.database.dao.ActionRecordReaderDao.java

License:Open Source License

/**
 * Returns the result of a SQL query./*from   w  w w.  j  a va2 s .  c  o m*/
 * 
 * @param sql
 *           The sql string.
 * @param periodicity
 *           A list of two Date or null if not applicable.
 * @return ReturnValue A list of Object[]. Each object tab contains the
 *         result of a row of the SELECT. The list is used for multiple row
 *         results.
 */
@SuppressWarnings("unchecked")
private List<Object[]> getReturnValue(final String sql, final Date start, final Date end) {
    boolean newSession = false;
    Session session;

    try {
        session = getSessionFactory().getCurrentSession();
    } catch (HibernateException e) {
        session = getSessionFactory().openSession();
        newSession = true;
    }
    SQLQuery query = session.createSQLQuery(sql);
    if (start != null)
        query.setDate(0, start);
    if (end != null)
        query.setDate(1, end);
    List<Object[]> result = query.list();

    if (newSession)
        session.disconnect();
    return result;
}

From source file:fr.gael.dhus.database.dao.ActionRecordReaderDao.java

License:Open Source License

/**
 * Returns the result of a SQL query.//from  w  ww.j  a  v  a2s. c om
 * 
 * @param sql
 *           The sql string.
 * @param periodicity
 *           A list of two Date or null if not applicable.
 * @return number of elements returned by the passed query. Is something is
 *    wrong with the query result, 0 is returned
 */
private int getCountValue(final String sql, final Date start, final Date end) {
    boolean newSession = false;
    Session session;
    try {
        session = getSessionFactory().getCurrentSession();
    } catch (HibernateException e) {
        session = getSessionFactory().openSession();
        newSession = true;
    }

    SQLQuery query = session.createSQLQuery(sql);
    if (start != null)
        query.setDate(0, start);
    if (end != null)
        query.setDate(1, end);
    BigInteger result = (BigInteger) query.uniqueResult();

    if (newSession)
        session.disconnect();

    return result.intValue();
}

From source file:it.mdps.gestguide.database.dao.hibernate.IstruttoreDaoImpl.java

@SuppressWarnings("unchecked")
@Override/*  ww  w. j  av  a2s . co  m*/
@Transactional(readOnly = true)
public List<Istruttore> getAvailableInstructors(int schoolId, int licenseId, Date fromDate, Date toDate) {
    Session session = super.sessionFactory.getCurrentSession();
    String sql = "select * FROM Istruttore WHERE id_autoscuola = :schoolId " + " AND id_istruttore in ( "
            + "      SELECT a.id_istruttore FROM Abilitazione a, Istruttore i " // seleziona istruttori abilitati alla patente richiesta e appartenenti alla scuola
            + "      WHERE i.id_autoscuola = :schoolId AND i.id_istruttore=a.id_istruttore "
            + "      AND id_patente = :licenseId)" + "   AND id_istruttore NOT IN ("
            + "      SELECT id_istruttore FROM Prenotazione " // seleziona istruttori impegnati in quell'arco temporale
            + "      WHERE id_autoscuola = :schoolId " + "      AND ("
            + "         (:fromDate BETWEEN data_inizio AND data_fine) " + "         OR"
            + "         (:toDate BETWEEN data_inizio AND data_fine)" + "         OR"
            + "         (:fromDate <= data_inizio AND :toDate >= data_fine)" + "      )" + ")";
    SQLQuery query = session.createSQLQuery(sql);
    query.addEntity(Istruttore.class);

    query.setInteger("schoolId", schoolId);
    query.setInteger("licenseId", licenseId);
    query.setDate("fromDate", fromDate);
    query.setDate("toDate", toDate);

    return query.list();
}

From source file:it.mdps.gestguide.database.dao.hibernate.MezzoDaoImpl.java

@SuppressWarnings("unchecked")
@Override//from  ww w.  j a v  a2s.  c  om
@Transactional(readOnly = true)
public List<Mezzo> getAvailableVehicles(int schoolId, int licenseId, Date fromDate, Date toDate) {
    Session session = super.sessionFactory.getCurrentSession();

    String sql = "select * FROM Mezzo WHERE id_autoscuola = :schoolId " + " AND id_mezzo in ( "
            + "      SELECT a.id_mezzo FROM Abilitazione_Mezzo a, Mezzo m " // seleziona mezzi abilitati alla patente richiesta e appartenenti alla scuola
            + "      WHERE m.id_autoscuola = :schoolId AND m.id_mezzo=a.id_mezzo "
            + "      AND id_patente = :licenseId)" + "   AND id_mezzo NOT IN ("
            + "      SELECT id_mezzo FROM Prenotazione " // seleziona mezzi impegnati in quell'arco temporale
            + "      WHERE id_autoscuola = :schoolId " + "      AND ("
            + "         (:fromDate BETWEEN data_inizio AND data_fine) " + "         OR"
            + "         (:toDate BETWEEN data_inizio AND data_fine)" + "         OR"
            + "         (:fromDate <= data_inizio AND :toDate >= data_fine)" + "      )" + ")";
    SQLQuery query = session.createSQLQuery(sql);
    query.addEntity(Mezzo.class);

    query.setInteger("schoolId", schoolId);
    query.setInteger("licenseId", licenseId);
    query.setDate("fromDate", fromDate);
    query.setDate("toDate", toDate);

    return query.list();
}

From source file:lt.emasina.resthub.server.handler.Handler.java

License:Open Source License

public void applyParameters(SQLQuery query) throws SQLException {
    for (Map.Entry<QueryParameter, Object> e : parameters.entrySet()) {

        QueryParameter p = e.getKey();/* w  w  w .  jav  a 2 s . c om*/
        Object value = e.getValue();
        String name = p.getSqlName();

        if (value != null && p.getArray()) {

            switch (p.getType()) {
            case DATE:
                query.setParameterList(name, (Object[]) value, new DateType());
                break;
            case NUMBER:
                query.setParameterList(name, (Object[]) value, new BigDecimalType());
                break;
            case STRING:
                query.setParameterList(name, (Object[]) value, new StringType());
                break;
            case CLOB:
            case BLOB:
                throw new ClientErrorException(Status.CLIENT_ERROR_BAD_REQUEST,
                        String.format("LOBs are not supported as parameters: %s", name));
            }

        } else {

            switch (p.getType()) {
            case DATE:
                query.setDate(name, (Date) value);
                break;
            case NUMBER:
                query.setBigDecimal(name, (BigDecimal) value);
                break;
            case STRING:
                query.setString(name, (String) value);
                break;
            case CLOB:
            case BLOB:
                throw new ClientErrorException(Status.CLIENT_ERROR_BAD_REQUEST,
                        String.format("LOBs are not supported as parameters: %s", name));
            }
        }
    }
}