Example usage for org.hibernate Query setParameterList

List of usage examples for org.hibernate Query setParameterList

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. Query
  6. setParameterList

Introduction

In this page you can find the example usage for org.hibernate Query setParameterList.

Prototype

Query<R> setParameterList(int position, Object[] values);

Source Link

Usage

From source file:es.sm2.openppm.core.dao.TimesheetDAO.java

License:Open Source License

/**
 * Hours by resource in dates in status APP3
 * /*ww w  . j a v  a  2  s.  co m*/
 * @param since
 * @param until
 * @param activities
 * @param employee
 * @return
 */
@SuppressWarnings("unchecked")
public List<Object[]> getHoursResourceInDates(Date since, Date until, List<Projectactivity> activities,
        Employee employee) {

    // Declare response
    List<Object[]> result = null;

    if (ValidateUtil.isNotNull(activities)) {

        Query query = getSession().createQuery("select project.idProject, activity.idActivity,  " + "SUM("
                + "(case when (timesheet.hoursDay1 is not null and TO_DAYS(timesheet.initDate) >= TO_DAYS(:since) and TO_DAYS(timesheet.initDate) <= TO_DAYS(:until)) THEN timesheet.hoursDay1 ELSE 0 end ) + "
                + "(case when (timesheet.hoursDay2 is not null and TO_DAYS(timesheet.initDate) + 1 >= TO_DAYS(:since) and TO_DAYS(timesheet.initDate) + 1 <= TO_DAYS(:until)) THEN timesheet.hoursDay2 ELSE 0 end ) + "
                + "(case when (timesheet.hoursDay3 is not null and TO_DAYS(timesheet.initDate) + 2 >= TO_DAYS(:since) and TO_DAYS(timesheet.initDate) + 2 <= TO_DAYS(:until)) THEN timesheet.hoursDay3 ELSE 0 end ) + "
                + "(case when (timesheet.hoursDay4 is not null and TO_DAYS(timesheet.initDate) + 3 >= TO_DAYS(:since) and TO_DAYS(timesheet.initDate) + 3 <= TO_DAYS(:until)) THEN timesheet.hoursDay4 ELSE 0 end ) + "
                + "(case when (timesheet.hoursDay5 is not null and TO_DAYS(timesheet.initDate) + 4 >= TO_DAYS(:since) and TO_DAYS(timesheet.initDate) + 4 <= TO_DAYS(:until)) THEN timesheet.hoursDay5 ELSE 0 end ) + "
                + "(case when (timesheet.hoursDay6 is not null and TO_DAYS(timesheet.initDate) + 5 >= TO_DAYS(:since) and TO_DAYS(timesheet.initDate) + 5 <= TO_DAYS(:until)) THEN timesheet.hoursDay6 ELSE 0 end ) + "
                + "(case when (timesheet.hoursDay7 is not null and TO_DAYS(timesheet.initDate) + 6 >= TO_DAYS(:since) and TO_DAYS(timesheet.initDate) + 6 <= TO_DAYS(:until)) THEN timesheet.hoursDay7 ELSE 0 end )"
                + ") " + "from Projectactivity activity " + "join activity.project as project "
                + "join activity.wbsnode as wbsnode " + "left join activity.timesheets as timesheet "
                + "left join timesheet.employee as employee " + "left join employee.contact as contact "
                + "where activity in(:activities) " + "and employee.idEmployee = :idEmployee "
                + "and timesheet.status = :app3 " + "group by project.idProject, activity.idActivity "
                + "order by project.accountingCode asc, activity.idActivity asc");

        query.setParameterList("activities", activities);
        query.setInteger("idEmployee", employee.getIdEmployee());
        query.setDate("since", since);
        query.setDate("until", until);
        query.setString("app3", Constants.TIMESTATUS_APP3);

        result = query.list();
    } else {

        // Initialize list
        result = new ArrayList<Object[]>();
    }

    return result;
}

From source file:es.sm2.openppm.core.dao.TimesheetDAO.java

License:Open Source License

/**
 * Hours by resource in dates in status APP2 or APP3
 * /*from   www  . jav  a 2s.c  o m*/
 * @param since
 * @param until
 * @param operations
 * @param employee
 * @return
 */
@SuppressWarnings("unchecked")
public List<Object[]> getHoursResourceInDatesOperation(Date since, Date until, List<Operation> operations,
        Employee employee) {

    // Declare response
    List<Object[]> result = null;

    if (ValidateUtil.isNotNull(operations)) {

        Query query = getSession().createQuery("select operation.idOperation,  " + "SUM("
                + "(case when (timesheet.hoursDay1 is not null and TO_DAYS(timesheet.initDate) >= TO_DAYS(:since) and TO_DAYS(timesheet.initDate) <= TO_DAYS(:until)) THEN timesheet.hoursDay1 ELSE 0 end ) + "
                + "(case when (timesheet.hoursDay2 is not null and TO_DAYS(timesheet.initDate) + 1 >= TO_DAYS(:since) and TO_DAYS(timesheet.initDate) + 1 <= TO_DAYS(:until)) THEN timesheet.hoursDay2 ELSE 0 end ) + "
                + "(case when (timesheet.hoursDay3 is not null and TO_DAYS(timesheet.initDate) + 2 >= TO_DAYS(:since) and TO_DAYS(timesheet.initDate) + 2 <= TO_DAYS(:until)) THEN timesheet.hoursDay3 ELSE 0 end ) + "
                + "(case when (timesheet.hoursDay4 is not null and TO_DAYS(timesheet.initDate) + 3 >= TO_DAYS(:since) and TO_DAYS(timesheet.initDate) + 3 <= TO_DAYS(:until)) THEN timesheet.hoursDay4 ELSE 0 end ) + "
                + "(case when (timesheet.hoursDay5 is not null and TO_DAYS(timesheet.initDate) + 4 >= TO_DAYS(:since) and TO_DAYS(timesheet.initDate) + 4 <= TO_DAYS(:until)) THEN timesheet.hoursDay5 ELSE 0 end ) + "
                + "(case when (timesheet.hoursDay6 is not null and TO_DAYS(timesheet.initDate) + 5 >= TO_DAYS(:since) and TO_DAYS(timesheet.initDate) + 5 <= TO_DAYS(:until)) THEN timesheet.hoursDay6 ELSE 0 end ) + "
                + "(case when (timesheet.hoursDay7 is not null and TO_DAYS(timesheet.initDate) + 6 >= TO_DAYS(:since) and TO_DAYS(timesheet.initDate) + 6 <= TO_DAYS(:until)) THEN timesheet.hoursDay7 ELSE 0 end )"
                + ") " + "from Timesheet timesheet " + "join timesheet.operation as operation "
                + "join timesheet.employee as employee " + "join employee.contact as contact "
                + "where operation in(:operations) " + "and employee.idEmployee = :idEmployee "
                + "and (timesheet.status = :app2 or timesheet.status = :app3) "
                + "group by operation.idOperation "
                + "order by operation.operationName asc, operation.idOperation asc");

        query.setParameterList("operations", operations);
        query.setInteger("idEmployee", employee.getIdEmployee());
        query.setDate("since", since);
        query.setDate("until", until);
        query.setString("app2", Constants.TIMESTATUS_APP2);
        query.setString("app3", Constants.TIMESTATUS_APP3);

        result = query.list();
    } else {

        // Initialize list
        result = new ArrayList<Object[]>();
    }

    return result;
}

From source file:es.sm2.openppm.core.dao.TimesheetDAO.java

License:Open Source License

/**
 * Get timesheets in app3 by resources and dates
 * //from   w  w w  . j  a  v a  2s .co m
 * @param resources
 * @param since
 * @param until
 * @param showOperations
 * @param idProjects
 * @param user
  * @param idPMs
  * @return
 */
@SuppressWarnings("unchecked")
public List<ResourceTimeWrap> capacityRunning(List<Employee> resources, Date since, Date until,
        Boolean showOperations, Integer[] idProjects, Employee user, Integer[] idPMs) {

    List<ResourceTimeWrap> operations = new ArrayList<ResourceTimeWrap>();

    String where = "";
    String groupBy = "group by ";
    String orderBy = "order by ";

    String q = "SELECT NEW es.sm2.openppm.core.model.wrap.ResourceTimeWrap(" + "e.idEmployee, " + "c.fullName, "
            + "ts.initDate, " + "ts.endDate, "
            + "SUM(coalesce(ts.hoursDay1, 0) + coalesce(ts.hoursDay2, 0) + coalesce(ts.hoursDay3, 0) + coalesce(ts.hoursDay4, 0) + "
            + "coalesce(ts.hoursDay5, 0) + coalesce(ts.hoursDay6, 0) + coalesce(ts.hoursDay7, 0))" + ") "
            + "FROM Timesheet ts " + "LEFT JOIN ts.projectactivity pa " + "LEFT JOIN pa.project p "
            + "JOIN ts.employee e " + "JOIN e.contact c " + "LEFT JOIN p.employeeByProjectManager pm ";

    // Names where
    //
    where += FilterUtil.addFilterAnd(where, "ts.status = :status ");

    where += FilterUtil.addFilterAnd(where, "ts.projectactivity is not null ");

    if (ValidateUtil.isNotNull(idProjects)) {
        where += FilterUtil.addFilterAnd(where, "p.idProject IN (:idProjects) ");
    }
    if (ValidateUtil.isNotNull(resources)) {
        where += FilterUtil.addFilterAnd(where, "ts.employee IN (:resources) ");
    }
    if (since != null) {
        where += FilterUtil.addFilterAnd(where, "ts.initDate >= :initDate ");
    }
    if (until != null) {
        where += FilterUtil.addFilterAnd(where, "ts.initDate <= :endDate ");
    }

    // PM Projects
    if (user.getResourceprofiles().getIdProfile() == Constants.ROLE_PM) {
        where += FilterUtil.addFilterAnd(where, "pm.idEmployee = :idPM ");
    }
    //TODO no se si el filtro esta bien o deberia ser asi, lo comento para dejarlo como estaba
    //        else if (ValidateUtil.isNotNull(idPMs)) {
    //            where += FilterUtil.addFilterAnd(where, "pm.idEmployee IN (:idPMs) ");
    //        }

    // Group by
    groupBy += "e.idEmployee, c.fullName, ts.initDate, ts.endDate ";

    // Order by
    orderBy += "c.fullName, ts.initDate";

    // Query
    Query query = getSession().createQuery(q + where + groupBy + orderBy);

    // Values where
    //
    query.setString("status", Constants.TIMESTATUS_APP3);

    if (ValidateUtil.isNotNull(resources)) {
        query.setParameterList("resources", resources);
    }

    if (since != null) {
        query.setDate("initDate", since);
    }
    if (until != null) {
        query.setDate("endDate", until);
    }

    if (ValidateUtil.isNotNull(idProjects)) {
        query.setParameterList("idProjects", idProjects);
    }

    // PM Projects
    if (user.getResourceprofiles().getIdProfile() == Constants.ROLE_PM) {
        query.setParameter("idPM", user.getIdEmployee());
    }
    //TODO si hay un recurso que tiene proyectos con dos PMs saldran los datos de los dos proyectos aunque se filtre por uno
    //        else if (ValidateUtil.isNotNull(idPMs)) {
    //            query.setParameterList("idPMs", idPMs);
    //        }

    List<ResourceTimeWrap> activities = query.list();

    // Show operations
    if (showOperations != null && showOperations) {

        // Select for operations
        //
        where = "";
        groupBy = "group by ";
        orderBy = "order by ";

        q = "SELECT NEW es.sm2.openppm.core.model.wrap.ResourceTimeWrap(" + "op.operationName, "
                + "ts.initDate, " + "ts.endDate, " + "e.idEmployee, "
                + "SUM(coalesce(ts.hoursDay1, 0) + coalesce(ts.hoursDay2, 0) + coalesce(ts.hoursDay3, 0) + coalesce(ts.hoursDay4, 0) + "
                + "coalesce(ts.hoursDay5, 0) + coalesce(ts.hoursDay6, 0) + coalesce(ts.hoursDay7, 0))" + ") "
                + "FROM Timesheet ts " + "JOIN ts.operation op " + "JOIN ts.employee e ";

        // Names where
        where += FilterUtil.addFilterAnd(where, "ts.status = :status ");

        if (ValidateUtil.isNotNull(resources)) {
            where += FilterUtil.addFilterAnd(where, "ts.employee IN (:resources) ");
        }
        if (since != null) {
            where += FilterUtil.addFilterAnd(where, "ts.initDate >= :initDate ");
        }
        if (until != null) {
            where += FilterUtil.addFilterAnd(where, "ts.initDate <= :endDate ");
        }

        // Group by
        groupBy += "ts.initDate, ts.endDate, op.operationName, e.idEmployee ";

        // Order by
        orderBy += "ts.initDate";

        // Query
        query = getSession().createQuery(q + where + groupBy + orderBy);

        // Values where
        query.setString("status", Constants.TIMESTATUS_APP3);

        if (ValidateUtil.isNotNull(resources)) {
            query.setParameterList("resources", resources);
        }
        if (since != null) {
            query.setDate("initDate", since);
        }
        if (until != null) {
            query.setDate("endDate", until);
        }

        operations = query.list();
    }

    // Concatenate selects
    //
    List<ResourceTimeWrap> activitiesAndOperations = new ArrayList<ResourceTimeWrap>();

    activitiesAndOperations.addAll(activities);
    activitiesAndOperations.addAll(operations);

    return activitiesAndOperations;
}

From source file:eu.europa.ec.fisheries.uvms.spatial.service.dao.AbstractAreaDao.java

License:Open Source License

public List<Map<String, Object>> findSelectedAreaColumns(String namedQueryString, List<Long> gids) {
    Query query = getEntityManager().unwrap(Session.class).getNamedQuery(namedQueryString);
    query.setParameterList("ids", gids);
    query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
    return query.list();
}

From source file:eu.europa.ec.fisheries.uvms.spatial.service.dao.ServiceLayerDao.java

License:Open Source License

private <T> Query createNamedQueryWithParameterList(String nativeQuery, String parameterName,
        List<?> parameters, Class<T> dtoClass) {
    Query query = em.unwrap(Session.class).getNamedQuery(nativeQuery);
    query.setParameterList(parameterName, parameters);
    query.setResultTransformer(Transformers.aliasToBean(dtoClass));
    return query;
}

From source file:eu.europa.ec.fisheries.uvms.spatial.service.dao.ServiceLayerDao.java

License:Open Source License

public List<ServiceLayerEntity> findServiceLayerEntityByIds(List<Long> ids) {
    Map<String, List<Long>> parameters = ImmutableMap.<String, List<Long>>builder().put("ids", ids).build();

    Query query = em.unwrap(Session.class).getNamedQuery(ServiceLayerEntity.FIND_SERVICE_LAYERS_BY_ID);
    for (Map.Entry<String, List<Long>> entry : parameters.entrySet()) {
        query.setParameterList(entry.getKey(), entry.getValue());
    }//from w w w  .ja v  a2 s  .c  o  m
    return query.list();
}

From source file:eu.europa.ec.fisheries.uvms.spatial.service.dao.UserAreaDao.java

License:Open Source License

public List<AreaDto> findAllUserAreasByGids(List<Long> gids) {
    Query query = getSession().getNamedQuery(UserAreasEntity.FIND_ALL_USER_AREAS_BY_GIDS);
    query.setParameterList(GID_LIST, gids);
    query.setResultTransformer(Transformers.aliasToBean(AreaDto.class));
    return query.list();
}

From source file:eu.semlibproject.annotationserver.AdminDataHelper.java

License:Open Source License

public List<Annotations> getAnnotations(boolean all, int pageNumber, int pageSize, SearchingMode searchingMode,
        String searchingValue, List<String> aidlist) {
    List<Annotations> results = null;

    try {//from  ww w  .  j  a va  2 s  .  co m
        Session hSession = HibernateManager.getSessionFactory().getCurrentSession();
        hSession.beginTransaction();

        Query query;
        if (searchingMode == SearchingMode.NID) {
            query = hSession.createQuery("from Annotations where notebookid = :nid");
            query.setParameter("nid", searchingValue);
        } else if (searchingMode == SearchingMode.ID) {
            query = hSession.createQuery("from Annotations where annotationid = :nid");
            query.setParameter("nid", searchingValue);
        } else if (searchingMode == SearchingMode.UID || searchingMode == SearchingMode.UNAME) {
            query = hSession.createQuery("from Annotations as a where a.annotationid in :aidlist");
            query.setParameterList("aidlist", aidlist);
        } else {
            query = hSession.createQuery("from Annotations");
        }

        if (!all) {
            query.setFirstResult(pageSize * (pageNumber - 1));
            query.setMaxResults(pageSize);
        }

        results = query.list();

        hSession.getTransaction().commit();

    } catch (HibernateException he) {
        return null;
    }

    return results;
}

From source file:eu.semlibproject.annotationserver.AdminDataHelper.java

License:Open Source License

public List<Notebooks> getNotebooks(boolean all, int pageNumber, int pageSize, SearchingMode searchingMode,
        String searchingValue, List<String> nids) {

    List<Notebooks> results = null;

    try {//from   ww  w . j  a  v  a 2s  . co m
        Session hSession = HibernateManager.getSessionFactory().getCurrentSession();
        hSession.beginTransaction();

        Query query;
        if (searchingMode == SearchingMode.ID) {
            query = hSession.createQuery("from Notebooks as n where n.id = :nid");
            query.setParameter("nid", searchingValue);
        } else if (searchingMode == SearchingMode.UID) {
            query = hSession.createQuery("from Notebooks as n where n.ownerid = :uid");
            query.setParameter("uid", searchingValue);
        } else if ((searchingMode == SearchingMode.NAME || searchingMode == SearchingMode.UNAME)
                && nids != null) {
            query = hSession.createQuery("from Notebooks as n where n.id in :nidlist");
            query.setParameterList("nidlist", nids);
        } else {
            query = hSession.createQuery("from Notebooks");
        }

        if (!all) {
            query.setFirstResult(pageSize * (pageNumber - 1));
            query.setMaxResults(pageSize);
        }

        results = query.list();

        hSession.getTransaction().commit();

    } catch (HibernateException he) {
        return null;
    }

    return results;
}

From source file:fi.vm.sade.eperusteet.repository.custom.TekstiPalanenRepositoryCustomImpl.java

License:EUPL

private List<LokalisoituTekstiHakuDto> getTekstipalaset(List<Long> tekstiPalanenIds) {
    // In JPA 2.0 we can not select DTOs with native queries and LokalisoituTeksti is not an entity
    // (which can therefore neither be selected with JPA query language) -> doing in Hibernate level API:
    Session session = em.unwrap(Session.class);
    StringBuilder or = new StringBuilder();
    // Just to make sure that https://hibernate.atlassian.net/browse/HHH-1123 won't affect us:
    List<List<Long>> idChunks = Lists.partition(tekstiPalanenIds, 1000);
    int i = 0;//from   w w  w  . j a  va2s . c o m
    Map<String, List<Long>> params = new HashMap<>(idChunks.size());
    for (List<Long> ids : idChunks) {
        if (or.length() > 0) {
            or.append(" OR ");
        }
        or.append("t.tekstipalanen_id IN (:ids_").append(i).append(") ");
        params.put("ids_" + i, ids);
        ++i;
    }
    Query q = session
            .createSQLQuery("SELECT " + "   t.tekstipalanen_id as id, " + "   t.kieli as kieli, "
                    + "   t.teksti as teksti " + " FROM tekstipalanen_teksti t " + " WHERE (" + or
                    + ") ORDER BY t.tekstipalanen_id, t.kieli")
            .addScalar("id", LongType.INSTANCE).addScalar("kieli", enumType(session, Kieli.class))
            .addScalar("teksti", StringType.INSTANCE)
            .setResultTransformer(new AliasToBeanResultTransformer(LokalisoituTekstiHakuDto.class));
    for (Map.Entry<String, List<Long>> p : params.entrySet()) {
        q.setParameterList(p.getKey(), p.getValue());
    }
    return list(q);
}