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:gcom.atendimentopublico.RepositorioAtendimentoPublicoHBM.java

License:Open Source License

/**
 * /*  w  ww .java 2 s  .  com*/
 * [UC1186] Gerar Relatrio Ordem de Servio Cobrana p/Resultado
 * 
  * Obtm a coleo de OS a partir dos parmetros passados pela funcionalidade de Acompanhamento de Cobrana por Resultado.
  * 
  * @author Hugo Azevedo
  * @date 27/06/2011
  * 
  * @throws ErroRepositorioException
  */

public Collection obterColecaoImovelOSCobrancaResultado(String[] categoriaImovel, String[] perfilImovel,
        String[] gerenciaRegional, String[] unidadeNegocio, String idLocalidadeInicial,
        String idLocalidadeFinal, String idSetorComercialInicial, String idSetorComercialFinal,
        String idQuadraInicial, String idQuadraFinal, String tipoServico, String comando)
        throws ErroRepositorioException {

    Session sessao = HibernateUtil.getSession();
    String consulta = "";
    Collection retorno = null;

    consulta = "select distinct imcon.id, grc.id, grc.nome, unc.id, unc.nome "
            + "from EmpresaCobrancaConta emcobco "
            //+ "inner join fetch emcobco.contaGeral cong "
            // + "inner join fetch cong.conta con "
            + "inner join emcobco.imovel imcon " + "inner join imcon.imovelSubcategorias imconsubc "
            + "inner join imconsubc.comp_id.subcategoria subcc " + "inner join subcc.categoria catc "
            + "inner join imcon.imovelPerfil ipc " + "inner join imcon.localidade locc "
            + "inner join locc.gerenciaRegional grc " + "inner join locc.unidadeNegocio unc "
            + "inner join imcon.setorComercial scc " + "inner join imcon.quadra qc "
            /*+ "inner join cong.contaHistorico conh "
            + "inner join conh.imovel imconh "
            + "inner join imconh.imovelSubcategorias imconhsubch "
            + "inner join imconhsubch.comp_id.subcategoria subcch  "
            + "inner join subcch.categoria catch "
            + "inner join imconh.imovelPerfil ipch "   
            + "inner join imconh.localidade locch "
            + "inner join locch.gerenciaRegional grch "
            + "inner join locch.unidadeNegocio unch "
            + "inner join imconh.setorComercial scch "
            + "inner join imconh.quadra qch "*/
            + "where emcobco.comandoEmpresaCobrancaConta = :comando  and imcon.indicadorExclusao = :indicadorExclusao ";

    //Coleo de categorias
    if (categoriaImovel != null && categoriaImovel.length > 0) {
        consulta += "and catc.id in ( :categoria ) ";
    }

    //Coleo de perfis do imvel
    if (perfilImovel != null && perfilImovel.length > 0) {
        consulta += "and ipc.id in ( :perfil ) ";

    }

    //Coleo de gerncias regionais
    if (gerenciaRegional != null && gerenciaRegional.length > 0) {
        consulta += "and grc.id in ( :gerenciaR ) ";

    }

    //Coleo de unidades de negcio
    if (unidadeNegocio != null && unidadeNegocio.length > 0) {
        consulta += "and unc.id in ( :unidadeN ) ";

    }

    //Localidade inicial e final
    if (idLocalidadeInicial != null && !"".equals(idLocalidadeInicial) && idLocalidadeFinal != null
            && !"".equals(idLocalidadeFinal)) {
        consulta += "and locc.id between :localidadeI AND :localidadeF ";
    }

    //Setor comercial inicial e final
    if (idSetorComercialInicial != null && !"".equals(idSetorComercialInicial) && idSetorComercialFinal != null
            && !"".equals(idSetorComercialFinal)) {
        consulta += "and scc between :setorI AND :setorF ";

    }

    //Quadra inicial e final
    if (idQuadraInicial != null && !"".equals(idQuadraInicial) && idQuadraFinal != null
            && !"".equals(idQuadraFinal)) {

        consulta += "and qc between :quadraI AND :quadraF ";

    }

    //Criando a query
    Query query = sessao.createQuery(consulta);

    //Inserindo os parmetros nos seus respectivos campos
    query.setString("comando", comando).setInteger("indicadorExclusao",
            ConstantesSistema.INDICADOR_IMOVEL_ATIVO);
    if (categoriaImovel != null && categoriaImovel.length > 0)
        query.setParameterList("categoria", categoriaImovel);
    if (perfilImovel != null && perfilImovel.length > 0)
        query.setParameterList("perfil", perfilImovel);
    if (gerenciaRegional != null && gerenciaRegional.length > 0)
        query.setParameterList("gerenciaR", gerenciaRegional);
    if (unidadeNegocio != null && unidadeNegocio.length > 0)
        query.setParameterList("unidadeN", unidadeNegocio);
    if (idLocalidadeInicial != null && !"".equals(idLocalidadeInicial) && idLocalidadeFinal != null
            && !"".equals(idLocalidadeFinal))
        query.setInteger("localidadeI", new Integer(idLocalidadeInicial)).setInteger("localidadeF",
                new Integer(idLocalidadeFinal));
    if (idSetorComercialInicial != null && !"".equals(idSetorComercialInicial) && idSetorComercialFinal != null
            && !"".equals(idSetorComercialFinal))
        query.setInteger("setorI", new Integer(idSetorComercialInicial)).setInteger("setorF",
                new Integer(idSetorComercialFinal));
    if (idQuadraInicial != null && !"".equals(idQuadraInicial) && idQuadraFinal != null
            && !"".equals(idQuadraFinal))
        query.setInteger("quadraI", new Integer(idQuadraInicial)).setInteger("quadraF",
                new Integer(idQuadraFinal));

    try {
        retorno = query.list();

    } catch (HibernateException e) {
        throw new ErroRepositorioException(e, "Erro no Hibernate");
    } finally {
        HibernateUtil.closeSession(sessao);
    }

    return retorno;

}

From source file:gcom.atendimentopublico.RepositorioAtendimentoPublicoHBM.java

License:Open Source License

/**
 * //  w w  w. j  a  v  a  2s . c o m
 * [UC1186] Gerar Relatrio Ordem de Servio Cobrana p/Resultado
 * 
  * Obtm a quantida de OS a partir dos parmetros passados pela funcionalidade de Acompanhamento de Cobrana por Resultado.
  * 
  * @author Hugo Azevedo
  * @date 27/06/2011
  * 
  * @throws ErroRepositorioException
  */

public Collection obterTotalOSColecaoImovelTipoServico(Collection colecaoImovel, Integer tipoServico)
        throws ErroRepositorioException {

    Session sessao = HibernateUtil.getSession();
    String consulta = "";
    Collection retorno = null;

    consulta = "select count(os.id) " + "from OrdemServico os " + "inner join os.imovel imo "
            + "inner join os.servicoTipo st " + "where imo.id in ( :colecaoImovel ) ";
    if (tipoServico != null && tipoServico.intValue() != -1)
        consulta += "and st.id = :tipoServico";

    try {
        Query query = sessao.createQuery(consulta);
        query.setParameterList("colecaoImovel", colecaoImovel);
        if (tipoServico != null && tipoServico.intValue() != -1)
            query.setInteger("tipoServico", tipoServico);
        retorno = query.list();

    } catch (HibernateException e) {
        throw new ErroRepositorioException(e, "Erro no Hibernate");
    } finally {
        HibernateUtil.closeSession(sessao);
    }

    return retorno;

}

From source file:gg.db.datamodel.Datamodel.java

License:Open Source License

/**
 * Gets a query object//from w w w  .j  a va2  s. c o  m
 * @param session Database session
 * @param searchCriteria Search criteria
 * @param searchFromStartDate Search from start? (if false, no filter on start date)
 * @param searchUntilEndDate Search until end? (if false, no filter on end date)
 * @param filterOnCategories Filter on categories?
 * @param select Select statement
 * @param where Where statement
 * @param groupBy Group by statement
 * @return Query object
 */
private static Query getQuery(Session session, SearchCriteria searchCriteria, boolean searchFromStartDate,
        boolean searchUntilEndDate, boolean filterOnCategories, boolean filterOnPayees,
        boolean filterOnKeywords, String select, String where, String groupBy) {
    log.entering(CLASS_NAME, "getQuery", new Object[] { searchCriteria, searchFromStartDate, searchUntilEndDate,
            filterOnCategories, filterOnPayees, filterOnKeywords, select, where, groupBy });
    if (searchCriteria == null) {
        throw new IllegalArgumentException("The parameter 'searchCriteria' is null");
    }

    Category transferCategory = getCategory(Category.TRANSFER.getGrisbiCategoryId(),
            Category.TRANSFER.getGrisbiSubCategoryId());
    String queryString = "";

    // SELECT clause
    if (select != null) {
        queryString = select + " ";
    }

    // FROM clause
    String fromClause = "from Transaction t inner join t.account as a";

    // WHERE clause
    List<String> whereClause = new ArrayList<String>();
    whereClause.add("a.active=true");
    if (searchCriteria.hasAccountsFilter()) {
        whereClause.add("t.account in (:accounts)");
    } else if (searchCriteria.hasCurrencyFilter()) {
        whereClause.add("a.currency=:currency");
    }

    whereClause.add("t.parentTransaction is null");
    if (where != null) {
        whereClause.add(where);
    }
    if (searchCriteria.hasPeriodFilter() && searchFromStartDate) {
        whereClause.add("t.date>=:start");
    }
    if (searchCriteria.hasPeriodFilter() && searchUntilEndDate) {
        whereClause.add("t.date<=:end");
    }
    if (searchCriteria.hasCategoriesFilter() && filterOnCategories) {
        whereClause.add("t.category in (:categories)");
    }
    if (searchCriteria.hasPayeesFilter() && filterOnPayees) {
        whereClause.add("t.payee in (:payees)");
    }
    if (!searchCriteria.isIncludeTransferTransactions()) {
        whereClause.add("t.category<>:categoryTransfer");
    }
    if (searchCriteria.hasKeywordsFilter() && filterOnKeywords) {
        whereClause.add("upper(t.comment) like :keyword");
    }

    // Compute the WHERE statement
    queryString += fromClause;
    if (whereClause.size() > 0) {
        queryString += " where ";
    }
    Iterator<String> it = whereClause.iterator();
    String whereItem;
    while (it.hasNext()) {
        whereItem = it.next();
        queryString += whereItem;

        if (it.hasNext()) {
            queryString += " and ";
        }
    }

    // Group by statement
    queryString += " " + groupBy;

    // Create query
    Query query = session.createQuery(queryString);

    // Add the entities to the query
    if (searchCriteria.hasAccountsFilter()) {
        query.setParameterList("accounts", searchCriteria.getAccounts());
    } else if (searchCriteria.hasCurrencyFilter()) {
        query.setParameter("currency", searchCriteria.getCurrency());
    }
    if (searchCriteria.hasPeriodFilter() && searchFromStartDate) {
        query.setParameter("start", searchCriteria.getPeriod().getStart());
    }
    if (searchCriteria.hasPeriodFilter() && searchUntilEndDate) {
        query.setParameter("end", searchCriteria.getPeriod().getEnd());
    }
    if (searchCriteria.hasCategoriesFilter() && filterOnCategories) {
        query.setParameterList("categories", searchCriteria.getCategories());
    }
    if (searchCriteria.hasPayeesFilter() && filterOnPayees) {
        query.setParameterList("payees", searchCriteria.getPayees());
    }
    if (!searchCriteria.isIncludeTransferTransactions()) {
        query.setParameter("categoryTransfer", transferCategory);
    }
    if (searchCriteria.hasKeywordsFilter() && filterOnKeywords) {
        query.setParameter("keyword", "%" + searchCriteria.getKeywords().toUpperCase() + "%");
    }

    log.exiting(CLASS_NAME, "getQuery", query.getQueryString());
    return query;
}

From source file:gov.nih.nci.caarray.dao.ArrayDaoImpl.java

License:BSD License

/**
 * {@inheritDoc}//from w ww  .  java  2 s  . c  o m
 */
@Override
@SuppressWarnings("unchecked")
public Map<String, Long> getLogicalProbeNamesToIds(ArrayDesign design, List<String> names) {
    final String queryString = "select lp.name, lp.id from " + LogicalProbe.class.getName()
            + " lp where lp.name in (:names) and lp.arrayDesignDetails = :details";
    final Query query = getCurrentSession().createQuery(queryString);
    query.setParameterList("names", names);
    query.setParameter("details", design.getDesignDetails());
    final List<Object[]> results = query.list();
    final Map<String, Long> namesToIds = new HashMap<String, Long>();
    for (final Object[] result : results) {
        namesToIds.put((String) result[0], (Long) result[1]);
    }
    return namesToIds;
}

From source file:gov.nih.nci.caarray.dao.ArrayDaoImpl.java

License:BSD License

/**
 * {@inheritDoc}//from w ww.  j av  a 2 s .  co m
 */
@Override
public List<PhysicalProbe> getPhysicalProbeByNames(ArrayDesign design, List<String> names) {
    if (names.isEmpty()) {
        return Collections.emptyList();
    }
    final Map<String, List<? extends Serializable>> inParams = new HashMap<String, List<? extends Serializable>>();
    final String inClause = getHibernateHelper().buildInClauses(names, "pp.name", inParams);
    final String queryString = "select pp from " + PhysicalProbe.class.getName()
            + " pp where pp.arrayDesignDetails = :details and " + inClause;
    final Query query = getCurrentSession().createQuery(queryString);
    query.setParameter("details", design.getDesignDetails());
    for (final Map.Entry<String, List<? extends Serializable>> e : inParams.entrySet()) {
        query.setParameterList(e.getKey(), e.getValue());
    }
    @SuppressWarnings("unchecked")
    final List<PhysicalProbe> results = query.list();
    return results;
}

From source file:gov.nih.nci.caarray.dao.ArrayDaoImpl.java

License:BSD License

/**
 * {@inheritDoc}/*  w  w  w  .  j  a  v a 2s. com*/
 */
@Override
@SuppressWarnings("unchecked")
public List<ArrayDesign> getArrayDesignsWithReImportable() {
    final String q = "select distinct a from " + ArrayDesign.class.getName()
            + " a left join a.designFiles f where f.status = :status and f.type in (:types) order by a.id";
    final Query query = getCurrentSession().createQuery(q);
    query.setParameter("status", FileStatus.IMPORTED_NOT_PARSED.name());
    query.setParameterList("types", Sets.newHashSet(Iterables
            .transform(this.typeRegistry.getParseableArrayDesignTypes(), new Function<FileType, String>() {
                @Override
                public String apply(FileType ft) {
                    return ft.getName();
                }
            })));
    return query.list();
}

From source file:gov.nih.nci.caarray.dao.FileDaoImpl.java

License:BSD License

/**
 * {@inheritDoc}/*from w ww . jav a2  s  . co m*/
 */
@Override
@SuppressWarnings("unchecked")
public List<CaArrayFile> getDeletableFiles(Long projectId) {
    final String hql = "from " + CaArrayFile.class.getName()
            + " f where f.project.id = :projectId and f.status in (:deletableStatuses) "
            + " and (f.status <> :importedStatus or not exists (select h from "
            + AbstractArrayData.class.getName()
            + " ad join ad.hybridizations h where ad.dataFile = f order by f.name))";
    final Query q = getCurrentSession().createQuery(hql);
    q.setLong("projectId", projectId);
    q.setParameterList("deletableStatuses", CaArrayUtils.namesForEnums(FileStatus.DELETABLE_FILE_STATUSES));
    q.setString("importedStatus", FileStatus.IMPORTED.name());
    return q.list();
}

From source file:gov.nih.nci.caarray.dao.MultipartBlobDaoImpl.java

License:BSD License

@Override
public void deleteByIds(Iterable<Long> ids) {
    // we want to avoid loading the MultipartBlobs into memory
    // and HQL bulk delete doesn't handle associations, so we have to drop to SQL
    // to delete the blob parts
    final List<Long> idList = Lists.newArrayList(ids);
    if (idList.isEmpty()) {
        // nothing to do
        return;//  w  ww. ja v a  2s. c o  m
    }

    final List<Long> blobPartIds = getBlobPartIds(idList);
    if (!blobPartIds.isEmpty()) {
        deleteBlobHolderAssociations(blobPartIds);
        deleteBlobHolders(blobPartIds);
    }

    final String hql = "delete from " + MultiPartBlob.class.getName() + " where id in (:blobIds)";
    final Query q = getCurrentSession().createQuery(hql);
    q.setParameterList("blobIds", idList);
    q.executeUpdate();
    this.flushSession();
    this.clearSession();
}

From source file:gov.nih.nci.caarray.dao.MultipartBlobDaoImpl.java

License:BSD License

private void deleteBlobHolderAssociations(Collection<Long> blobPartIds) {
    final String sql = "delete from multipart_blob_blob_parts where blob_parts in (:bpIds)";
    final Query q = getCurrentSession().createSQLQuery(sql);
    q.setParameterList("bpIds", blobPartIds);
    q.executeUpdate();//  w w  w  .  j  a v a  2s.  com
}

From source file:gov.nih.nci.caarray.dao.MultipartBlobDaoImpl.java

License:BSD License

private void deleteBlobHolders(Collection<Long> blobPartIds) {
    final String sql = "delete from blob_holder where id in (:bpIds)";
    final Query q = getCurrentSession().createSQLQuery(sql);
    q.setParameterList("bpIds", blobPartIds);
    q.executeUpdate();//  w  w  w. j  a  v  a  2s  . co  m
}