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

License:Open Source License

/**
 * [UC1177] Gerar Relatrio de Ordens de Servio por Situao
 * /* w ww . j av a  2  s  .  co  m*/
 * O segundo parmetro (boletimGerado)  um booleano que
 * indica se para um dado grupo de cobrana e um ms referencia
 * foi gerado um boletim de medio.
 * 
 * @author Diogo Peixoto
 * @date 09/06/2011
 * 
 * @param FiltrarRelatorioOSSituacaoHelper
 * @param boletimGerado
 * @return Collection<FiltrarRelatorioOSSituacaoHelper>
 * @throws ErroRepositorioException
 */
public Collection<Object[]> filtrarRelatorioOSSituacao(FiltrarRelatorioOSSituacaoHelper helper,
        boolean boletimGerado) throws ErroRepositorioException {
    Collection<Object[]> relatorios = new ArrayList<Object[]>();

    /*
     * Esse mtodo pesquisa as ordens de servio por situao. O helper do parmetro
     * possui a situao da os com os seguintes valores:
     * 
     * 1 Descontadas
     * 2 Encerradas
     * 3 Executadas
     * 4 Fiscalizadas
     * 5 Justificadas
     * 6 Penalizadas por Fiscalizao
     * 7 Penalizadas por Decurso de Prazo
     * 8 Todas
      * 9 Encerradas com Execuo
     * 10 Encerradas por Decurso de Prazo
     * 11 Pendentes
     * 12 Fiscalizadas Boletim No Gerado
     * 13 Todas Boletim No Gerado
     * 
     */
    Session session = HibernateUtil.getSession();
    String consulta = "";
    Map<String, Object> parameters = new HashMap<String, Object>();
    StringBuilder sb = new StringBuilder();
    StringBuilder sbParametrosOpcionais = new StringBuilder();
    String clausulaWhere = "";
    String groupBy = "";
    String orderBy = "";

    try {
        if (!helper.getSituacaoOS().equals("2") && !helper.getSituacaoOS().equals("4")
                && !helper.getSituacaoOS().equals("8") && !helper.getSituacaoOS().equals("13")) {
            sb.append(this.filtrarRelatorioOSSituacaoSelect(helper, boletimGerado, false, null));
            sb.append("FROM atendimentopublico.ordem_servico orse ");
        }

        //Descontadas
        if (helper.getSituacaoOS().equals("1")) {
            sb.append(this.filtrarRelatorioOSSituacaoDescontadas());
            parameters.put("situacaoOS", "DESCONTADAS");

            //Executadas
        } else if (helper.getSituacaoOS().equals("3")) {
            sb.append(this.filtrarRelatorioOSSituacaoExecutadas());
            parameters.put("situacaoOS", "EXECUTADAS");

            //Justificadas
        } else if (helper.getSituacaoOS().equals("5")) {
            sb.append(this.filtrarRelatorioOSSituacaoJustificadas());
            parameters.put("situacaoOS", "JUSTIFICADAS");

            //Penalizadas por fiscalizao   
        } else if (helper.getSituacaoOS().equals("6")) {
            sb.append(this.filtrarRelatorioOSSituacaoPenalizadaFiscalizacao());
            parameters.put("situacaoOS", "PENALIZADAS POR FISCALIZAO");

            //Penalizadas por Decurso de Prazo
        } else if (helper.getSituacaoOS().equals("7")) {
            sb.append(this.filtrarRelatorioOSSituacaoPenalizadaDecursoPrazo());
            parameters.put("situacaoOS", "PENALIZADAS POR DECURSO DE PRAZO");

            //Encerradas com Execuo
        } else if (helper.getSituacaoOS().equals("9")) {
            sb.append(this.filtrarRelatorioOSSituacaoBoletimNaoGerado());
            clausulaWhere += " AND orse.orse_cdsituacao = 2 AND orse.amen_id != 32 AND encmt.amen_icexecucao = 1 ";
            parameters.put("situacaoOS", "ENCERRADAS COM EXECUO");

            //Pendentes
        } else if (helper.getSituacaoOS().equals("11")) {
            sb.append(this.filtrarRelatorioOSSituacaoBoletimNaoGerado());
            clausulaWhere += " AND orse.orse_cdsituacao = 1 ";
            parameters.put("situacaoOS", "PENDENTES");

            //Fiscalizadas Boletim No Gerado   
        } else if (helper.getSituacaoOS().equals("12")) {
            sb.append(this.filtrarRelatorioOSSituacaoBoletimNaoGerado());
            sb.append(
                    "INNER JOIN atendimentopublico.ordem_servico orseRef ON orseRef.orse_idreferencia = orse.orse_id ");
            parameters.put("situacaoOS", "FISCALIZADAS");

            //Encerradas com decurso de prazo
        } else if (helper.getSituacaoOS().equals("10")) {
            sb.append(this.filtrarRelatorioOSSituacaoBoletimNaoGerado());
            clausulaWhere += " AND orse.orse_cdsituacao = 2 AND orse.amen_id = 32 ";
            parameters.put("situacaoOS", "ENCERRADAS COM DECURSO DE PRAZO");
        }

        if (helper.getOpcaoOSCobranca() != null
                && helper.getOpcaoOSCobranca().equalsIgnoreCase("naoCobradasAutomaticamente")) {
            sbParametrosOpcionais.append(
                    "INNER JOIN atendimentopublico.fiscaliz_sit_serv_a_cob fiscACobrar ON orse.fzst_id = fiscACobrar.fzst_id ");
            clausulaWhere += " AND NOT EXISTS (SELECT dbtCo.imov_id ";
            clausulaWhere += "FROM faturamento.debito_a_cobrar dbtCo ";
            clausulaWhere += "WHERE dbtCo.imov_id = orse.imov_id AND dbtCo.dbtp_id = fiscACobrar.dbtp_id AND dbtCo.dbac_tmatudebito = orse.orse_tmencerramento) ";
            clausulaWhere += " AND NOT EXISTS (SELECT dbtHist.imov_id ";
            clausulaWhere += " FROM faturamento.deb_a_cobrar_hist dbtHist ";
            clausulaWhere += " WHERE dbtHist.imov_id = orse.imov_id AND dbtHist.dbtp_id = fiscACobrar.dbtp_id AND dbtHist.dahi_tmatudebito = orse.orse_tmencerramento) ";
        }

        if (helper.getServicoTipo() != null) {
            clausulaWhere += " AND orse.svtp_id = :servicoTipo ";
            parameters.put("servicoTipo", helper.getServicoTipo().getId());
        }

        boolean existeLocalidade;
        existeLocalidade = (helper.getLocalidade() != null ? existeLocalidade = true : false);
        // Localidade
        if (existeLocalidade) {
            sbParametrosOpcionais.append("INNER JOIN cadastro.localidade loca ON loca.loca_id = imov.loca_id ");
            clausulaWhere += " AND loca.loca_id = :idLocalidade ";
            parameters.put("idLocalidade", helper.getLocalidade().getId());
        }

        if (helper.getEloPolo() != null) {
            // Elo Polo
            if (!existeLocalidade) {
                sbParametrosOpcionais
                        .append("INNER JOIN cadastro.localidade loca ON loca.loca_id = imov.loca_id ");
                existeLocalidade = true;
            }
            clausulaWhere += " AND loca.loca_cdelo = :idEloPolo ";
            parameters.put("idEloPolo", helper.getEloPolo().getId());
        }

        // Gerncia Regional
        GerenciaRegional gerencia = helper.getGerenciaRegional();
        if (gerencia != null) {
            if (existeLocalidade) {
                sbParametrosOpcionais
                        .append("INNER JOIN cadastro.gerencia_regional ger ON ger.greg_id = loca.greg_id ");
            } else {
                sbParametrosOpcionais
                        .append("INNER JOIN cadastro.localidade loca ON loca.loca_id = imov.loca_id ");
                sbParametrosOpcionais
                        .append("INNER JOIN cadastro.gerencia_regional ger ON ger.greg_id = loca.greg_id ");
                existeLocalidade = true;
            }
            clausulaWhere += " AND ger.greg_id = :idGerencia ";
            parameters.put("idGerencia", gerencia.getId());
        }

        // Unidade Negcio
        UnidadeNegocio unidade = helper.getUnidadeNegocio();
        if (unidade != null) {
            if (existeLocalidade) {
                sbParametrosOpcionais
                        .append("INNER JOIN cadastro.unidade_negocio uni ON uni.uneg_id = loca.uneg_id ");
            } else {
                sbParametrosOpcionais
                        .append("INNER JOIN cadastro.localidade loca ON loca.loca_id = imov.loca_id ");
                sbParametrosOpcionais
                        .append("INNER JOIN cadastro.unidade_negocio uni ON uni.uneg_id = loca.uneg_id ");
            }
            clausulaWhere += " AND uni.uneg_id = :idUnidade ";
            parameters.put("idUnidade", unidade.getId());
        }

        // Setor Comercial
        if (helper.getSetorComercial() != null) {
            sbParametrosOpcionais
                    .append("INNER JOIN cadastro.setor_comercial setor ON setor.stcm_id = imov.stcm_id ");
            clausulaWhere += " AND setor.stcm_id = :idSetor ";
            parameters.put("idSetor", helper.getSetorComercial().getId());
        }

        // Quadra
        if (helper.getQuadra() != null) {
            sbParametrosOpcionais.append("INNER JOIN cadastro.quadra quadra ON quadra.qdra_id = imov.qdra_id ");
            clausulaWhere += " AND quadra.qdra_id = :idQuadra ";
            parameters.put("idQuadra", helper.getQuadra().getId());
        }

        if (helper.getSituacaoOS().equals("2")) {
            if (helper.getOpcaoRelatorio().equals("1")) {
                sb.append("SELECT DISTINCT aux.numeroOS, ");
                sb.append("aux.matriculaImovel, ");
                sb.append("aux.tipoServico, ");
                sb.append("aux.dataEncerramento, ");
                sb.append("aux.naoCobrada, ");
                sb.append("aux.valorConsumoFraudado, ");
                sb.append("aux.valorMulta, ");
                sb.append("aux.motivoEncerramento, ");
                sb.append("aux.retornoFiscalizacao, ");
                sb.append("aux.parecerEncerramento, ");
                sb.append("aux.situacaoOS ");
            } else if (helper.getOpcaoRelatorio().equals("2")) {
                sb.append("SELECT DISTINCT aux.tipoServico, ");
                sb.append("aux.motivoEncerramento, ");
                sb.append("aux.retornoFiscalizacao, ");
                sb.append("aux.situacaoOS AS situacaoOS, ");
                sb.append("SUM(aux.quantidade) AS quantidade ");
            }
            sb.append("FROM (");
            sb.append(this.filtrarRelatorioOSSituacaoSelect(helper, boletimGerado, true, null));
            sb.append("FROM atendimentopublico.ordem_servico orse ");
            sb.append(this.filtrarRelatorioOSSituacaoExecutadas());
            sb.append(sbParametrosOpcionais.toString());
            if (!clausulaWhere.trim().equals("")) {
                sb.append(" WHERE ");
                clausulaWhere = clausulaWhere.replaceFirst("AND", "");
                sb.append(clausulaWhere);
            }
            sb.append(" UNION ALL ");
            sb.append(this.filtrarRelatorioOSSituacaoSelect(helper, boletimGerado, true, null));
            sb.append("FROM atendimentopublico.ordem_servico orse ");
            sb.append(this.filtrarRelatorioOSSituacaoJustificadas());
            sb.append(sbParametrosOpcionais.toString());
            if (!clausulaWhere.trim().equals("")) {
                sb.append(" WHERE ");
                clausulaWhere = clausulaWhere.replaceFirst("AND", "");
                sb.append(clausulaWhere);
            }
            sb.append(") aux");
            parameters.put("situacaoOS", "ENCERRADAS");
        } else if (helper.getSituacaoOS().equals("4")) {
            sb.append(this.filtrarRelatorioOSSituacaoSelect(helper, boletimGerado, false, null));
            sb.append("FROM atendimentopublico.ordem_servico orse ");
            sb.append(this.filtrarRelatorioOSSituacaoFiscalizadas(helper, boletimGerado,
                    sbParametrosOpcionais.toString(), clausulaWhere));
            parameters.put("situacaoOS", "FISCALIZADAS");
        } else if (helper.getSituacaoOS().equals("8") || helper.getSituacaoOS().equals("13")) {
            sb.append(this.filtrarRelatorioOSSituacaoTodas(helper, boletimGerado,
                    sbParametrosOpcionais.toString(), clausulaWhere));
        } else {
            sb.append(sbParametrosOpcionais.toString());
        }

        if (!clausulaWhere.equals("") && !helper.getSituacaoOS().equals("2")
                && !helper.getSituacaoOS().equals("4") && !helper.getSituacaoOS().equals("8")
                && !helper.getSituacaoOS().equals("13")) {
            /*
             * Este trecho do cdigo retira o primeiro AND da clusula where e adiciona espao em branco
             * na ltima comparao para no dar erro na query.
             * Ex: 'WHERE AND os.orse_id' => 'WHERE os.orse_id '
             */
            sb.append("WHERE ");
            clausulaWhere = clausulaWhere.trim();
            clausulaWhere = clausulaWhere.replaceFirst("AND", "");
            clausulaWhere += " ";
            sb.append(clausulaWhere);
        }

        //Caso seja o relatrio sinttico
        if (helper.getOpcaoRelatorio().equals("2") && !helper.getSituacaoOS().equals("8")
                && !helper.getSituacaoOS().equals("13")) {
            groupBy += " GROUP BY tipoServico, motivoEncerramento, retornoFiscalizacao, situacaoOS ";
            orderBy += " ORDER BY tipoServico, motivoEncerramento, retornoFiscalizacao ";

            if (!helper.getSituacaoOS().equals("2")) {
                sb.append(") temp");
            }
        }

        sb.append(groupBy);
        sb.append(orderBy);

        consulta = sb.toString();
        SQLQuery sqlQuery = session.createSQLQuery(consulta);
        sqlQuery.setInteger("amReferencia", helper.getDataReferencia());
        sqlQuery.setInteger("cobrancaGrupoID", helper.getIdGrupoCobranca());

        if (helper.getOpcaoRelatorio().equalsIgnoreCase("1")) {
            sqlQuery = sqlQuery.addScalar("numeroOS", Hibernate.INTEGER)
                    .addScalar("matriculaImovel", Hibernate.INTEGER).addScalar("tipoServico", Hibernate.STRING)
                    .addScalar("dataEncerramento", Hibernate.DATE).addScalar("naoCobrada", Hibernate.STRING)
                    .addScalar("valorConsumoFraudado", Hibernate.BIG_DECIMAL)
                    .addScalar("valorMulta", Hibernate.BIG_DECIMAL)
                    .addScalar("motivoEncerramento", Hibernate.STRING)
                    .addScalar("retornoFiscalizacao", Hibernate.STRING)
                    .addScalar("parecerEncerramento", Hibernate.STRING)
                    .addScalar("situacaoOS", Hibernate.STRING);
        } else {

            sqlQuery = sqlQuery.addScalar("tipoServico", Hibernate.STRING)
                    .addScalar("motivoEncerramento", Hibernate.STRING)
                    .addScalar("retornoFiscalizacao", Hibernate.STRING)
                    .addScalar("situacaoOS", Hibernate.STRING).addScalar("quantidade", Hibernate.INTEGER);
        }

        //ITERA OS PARAMETROS E COLOCA 
        // OS MESMOS NA QUERY
        Set<String> set = parameters.keySet();
        Iterator<String> iterMap = set.iterator();
        while (iterMap.hasNext()) {
            String key = iterMap.next();
            sqlQuery.setParameter(key, parameters.get(key));
        }
        relatorios = sqlQuery.list();
    } catch (HibernateException e) {
        throw new ErroRepositorioException(e, "Erro no Hibernate");
    } finally {
        HibernateUtil.closeSession(session);
    }
    return relatorios;
}

From source file:gemlite.core.internal.support.jpa.files.dao.GmBatchDaoImpl.java

License:Apache License

@Override
public Map queryJobExecutionById(Long executionId) {
    StringBuilder sb = new StringBuilder();
    sb.append("SELECT a.*,b.job_name");
    sb.append(//  ww w . j a va  2s . c  o  m
            " from BATCH_JOB_EXECUTION a left join batch_job_instance b on a.job_instance_id = b.job_instance_id where a.JOB_EXECUTION_ID = ?");

    Query query = em.createNativeQuery(sb.toString());
    SQLQuery nativeQuery = query.unwrap(SQLQuery.class);
    nativeQuery.setParameter(0, executionId);
    nativeQuery.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
    List<Map> list = nativeQuery.list();

    return (list == null || list.isEmpty()) ? null : toLowerCaseKey(list).get(0);
}

From source file:gemlite.core.internal.support.jpa.files.dao.GmBatchDaoImpl.java

License:Apache License

@Override
public List<Map> queryStepNamesForJob(String jobName) {
    String sql = "SELECT E.JOB_EXECUTION_ID, E.START_TIME, E.END_TIME, E.STATUS, E.EXIT_CODE, E.EXIT_MESSAGE, E.CREATE_TIME, E.LAST_UPDATED, E.VERSION, I.JOB_INSTANCE_ID, I.JOB_NAME"
            + " FROM BATCH_JOB_EXECUTION E, BATCH_JOB_INSTANCE I WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID and I.JOB_NAME=? ORDER BY JOB_EXECUTION_ID DESC";

    Query query = em.createNativeQuery(sql);
    SQLQuery nativeQuery = query.unwrap(SQLQuery.class);
    nativeQuery.setParameter(0, jobName);
    nativeQuery.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
    List<Map> list = nativeQuery.list();

    return toLowerCaseKey(list);
}

From source file:gemlite.core.internal.support.jpa.files.dao.GmBatchDaoImpl.java

License:Apache License

@Override
public List<Map> queryStepExecutionsById(Long jobExecutionId) {
    StringBuilder sb = new StringBuilder();
    sb.append("SELECT STEP_EXECUTION_ID, STEP_NAME, START_TIME, END_TIME, STATUS, COMMIT_COUNT,");
    sb.append(" READ_COUNT, FILTER_COUNT, WRITE_COUNT, EXIT_CODE, EXIT_MESSAGE, READ_SKIP_COUNT,");
    sb.append(" WRITE_SKIP_COUNT, PROCESS_SKIP_COUNT, ROLLBACK_COUNT, LAST_UPDATED, VERSION from ");
    sb.append(" BATCH_STEP_EXECUTION where JOB_EXECUTION_ID = ?");
    sb.append(" order by STEP_EXECUTION_ID");

    Query query = em.createNativeQuery(sb.toString());
    SQLQuery nativeQuery = query.unwrap(SQLQuery.class);
    nativeQuery.setParameter(0, jobExecutionId);
    nativeQuery.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);

    List<Map> list = nativeQuery.list();
    return toLowerCaseKey(list);
}

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

License:BSD License

private SQLQuery buildQuery(AuditLogSearchCriteria criteria, StringBuffer sb) {
    SQLQuery q = getCurrentSession().createSQLQuery(sb.toString());
    q.setParameterList("GROUP_NAMES", getGroupNames());
    if (StringUtils.isNotBlank(criteria.getUsername())) {
        q.setParameter("username", criteria.getUsername());
    }/*from w  ww  .  ja  va 2 s.c o  m*/
    if (StringUtils.isNotBlank(criteria.getMessage())) {
        q.setParameter("message", "%" + criteria.getMessage().toLowerCase() + "%");
    }
    return q;
}

From source file:gov.nih.nci.cabig.caaers.accesscontrol.query.impl.AbstractIdFetcher.java

License:BSD License

@SuppressWarnings("unchecked")
public List<?> search(final AbstractQuery query) {
    String queryString = query.getQueryString();
    if (log.isDebugEnabled())
        log.debug("::: " + queryString);
    return (List<?>) getHibernateTemplate().execute(new HibernateCallback() {

        public Object doInHibernate(final Session session) throws HibernateException, SQLException {
            if (query instanceof NativeSQLQuery) {
                org.hibernate.SQLQuery nativeQuery = session.createSQLQuery(query.getQueryString());
                Map<String, org.hibernate.type.Type> scalarMap = ((NativeSQLQuery) query).getScalarMap();
                for (String key : scalarMap.keySet()) {
                    nativeQuery.addScalar(key, scalarMap.get(key));
                }/*from   w  w w.  ja  v a 2  s . c  om*/
                Map<String, Object> queryParameterMap = query.getParameterMap();
                for (String key : queryParameterMap.keySet()) {
                    Object value = queryParameterMap.get(key);
                    nativeQuery.setParameter(key, value);
                }
                return nativeQuery.list();
            } else {
                org.hibernate.Query hibernateQuery = session.createQuery(query.getQueryString());
                Map<String, Object> queryParameterMap = query.getParameterMap();
                for (String key : queryParameterMap.keySet()) {
                    Object value = queryParameterMap.get(key);
                    if (value instanceof Collection) {
                        hibernateQuery.setParameterList(key, (Collection) value);
                    } else {
                        hibernateQuery.setParameter(key, value);
                    }

                }
                return hibernateQuery.list();
            }
        }

    });
}

From source file:gov.nih.nci.cabig.caaers.dao.CaaersDao.java

License:BSD License

/**
 * A paginated query. //  ww  w  .j a  v a 2  s .  c  o  m
 * @param query - The query to be executed. 
 * @param firstResult - The starting index
 * @param maxResults - The number of objects to be returned. 
 * @return - A list of objects returned by the query, matching the pagination criteria. 
 */
@SuppressWarnings("unchecked")
public List<?> search(final AbstractQuery query, final Integer firstResult, final Integer maxResults) {
    String queryString = query.getQueryString();
    if (log.isDebugEnabled())
        log.debug("::: " + queryString);
    return (List<?>) getHibernateTemplate().execute(new HibernateCallback() {

        public Object doInHibernate(final Session session) throws HibernateException, SQLException {
            if (query instanceof NativeSQLQuery) {
                org.hibernate.SQLQuery nativeQuery = session.createSQLQuery(query.getQueryString());
                setResultSetBoundaries(nativeQuery, firstResult, maxResults);
                Map<String, Type> scalarMap = ((NativeSQLQuery) query).getScalarMap();
                for (String key : scalarMap.keySet()) {
                    nativeQuery.addScalar(key, scalarMap.get(key));
                }
                Map<String, Object> queryParameterMap = query.getParameterMap();
                for (String key : queryParameterMap.keySet()) {
                    Object value = queryParameterMap.get(key);
                    if (value instanceof Collection) {
                        nativeQuery.setParameterList(key, (Collection) value);
                    } else {
                        nativeQuery.setParameter(key, value);
                    }
                }
                return nativeQuery.list();
            } else {
                org.hibernate.Query hibernateQuery = session.createQuery(query.getQueryString());
                setResultSetBoundaries(hibernateQuery, firstResult, maxResults);
                Map<String, Object> queryParameterMap = query.getParameterMap();
                for (String key : queryParameterMap.keySet()) {
                    Object value = queryParameterMap.get(key);
                    if (value instanceof Collection) {
                        hibernateQuery.setParameterList(key, (Collection) value);
                    } else {
                        hibernateQuery.setParameter(key, value);
                    }

                }
                return hibernateQuery.list();
            }
        }

    });
}

From source file:gov.nih.nci.cabig.caaers.service.workflow.WorkflowServiceImpl.java

License:BSD License

/**
 * Find DCC reviewer from the workflow/*  w  w w .  ja  va2s.  co m*/
 * @param wfId
 * @return User instance
 */
public User findCoordinatingCenterReviewer(Integer wfId) {
    String strQuery = "SELECT jbpm_pooledactor.actorid_ "
            + "FROM public.jbpm_pooledactor, public.jbpm_taskactorpool, public.jbpm_taskinstance "
            + "WHERE jbpm_pooledactor.id_ = jbpm_taskactorpool.pooledactor_ "
            + "AND jbpm_taskactorpool.taskinstance_= jbpm_taskinstance.id_ "
            + "AND jbpm_taskinstance.name_ IN ('Coordinating Center Review', 'Data Coordinator Review') "
            + "AND jbpm_taskinstance.procinst_= :wfId";
    final NativeSQLQuery query = new NativeSQLQuery(strQuery);
    query.setParameter("wfId", wfId.intValue());
    query.setScalar("actorid_", StandardBasicTypes.STRING);
    List userList = jbpmTemplate.getHibernateTemplate().executeFind(new HibernateCallback() {

        public Object doInHibernate(Session session) throws HibernateException, SQLException {
            org.hibernate.SQLQuery nativeQuery = session.createSQLQuery(query.getQueryString());
            Map<String, org.hibernate.type.Type> scalarMap = ((NativeSQLQuery) query).getScalarMap();
            for (String key : scalarMap.keySet()) {
                nativeQuery.addScalar(key, scalarMap.get(key));
            }
            Map<String, Object> queryParameterMap = query.getParameterMap();
            for (String key : queryParameterMap.keySet()) {
                Object value = queryParameterMap.get(key);
                nativeQuery.setParameter(key, value);
            }
            return nativeQuery.list();
        }
    });

    User reviewer = null;
    if (CollectionUtils.isNotEmpty(userList)) {
        reviewer = userRepository.getUserByLoginName((String) userList.get(0));
    }

    return reviewer;

}

From source file:gradebook.StudentAssignmentHelper.java

public List<Student> getStudentsFromAssignment(int gaid) {
    // Create the list
    List<Student> studentList = null;

    String sql = "SELECT * FROM student "
            + "INNER JOIN gradebook_student ON student.student_id = gradebook_student.student_id "
            + "INNER JOIN student_assignment ON student_assignment.STUDENT_ID = gradebook_student.STUDENT_ID "
            + "WHERE student_assignment.GRADEBOOK_ASSIGNMENT_ID = :gaid";

    try {//from w w  w  . j  a  va2  s  . c om
        // Begin new transaction if we have an inactive one
        if (!this.session.getTransaction().isActive()) {
            session.beginTransaction();
        }

        // Create an SQL query from the SQL string
        SQLQuery query = session.createSQLQuery(sql);

        // Add an entity
        query.addEntity(Student.class);

        // Binding parameters
        query.setParameter("gaid", gaid);

        // Execute query
        studentList = (List<Student>) query.list();
    } catch (Exception e) {
        e.printStackTrace();
    }

    // Return gradebooks
    return studentList;

}

From source file:gradebook.StudentAssignmentHelper.java

public int insertStudentToAssignment(int sid, int gaid, int grade) {
    int result = 0;

    String sql = "INSERT INTO Student_Assignment (STUDENT_ID, GRADEBOOK_ASSIGNMENT_ID, STUDENT_ASSIGNMENT_GRADE) "
            + "VALUES (:sid, :gaid, :sag)";

    try {//from w w  w.j  a v  a  2  s  . c  om

        if (!this.session.getTransaction().isActive()) {
            session.beginTransaction();
        }

        SQLQuery query = session.createSQLQuery(sql);
        query.addEntity(StudentAssignment.class);
        query.setParameter("sid", sid);
        query.setParameter("gaid", gaid);
        query.setParameter("sag", grade);

        result = query.executeUpdate();

        session.getTransaction().commit();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return result;

}