Example usage for org.hibernate SQLQuery addEntity

List of usage examples for org.hibernate SQLQuery addEntity

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

Introduction

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

Prototype

SQLQuery<T> addEntity(Class entityType);

Source Link

Document

Declare a "root" entity, without specifying an alias.

Usage

From source file:uk.ac.ebi.emma.manager.AllelesManager.java

License:Apache License

/**
 * Given a <code>Filter</code> object describing any/all of the following
 * fields://from  w  w  w  .  ja  v  a  2s  .  c  om
 * <ul>
 * <li>allele ID</li>
 * <li>allele name</li>
 * <li>allele symbol</li>
 * <li>allele MGI reference</li>
 * <li>gene ID</li>
 * <li>gene name</li>
 * <li>gene symbol</li>
 * <li>gene MGI reference</li></ul>
 * this method performs a query, ANDing all non-empty fields in a WHERE
 * clause against the alleles table (joined to the genes table for gene name
 * and gene symbol). The result is a <code>List&lt;Allele&gt;</code> of
 * qualifying results. A list is always returned, even if there are no results.
 * 
 * @param filter values to filter by
 * @return a list of <code>Allele</code>.
 * @throws NumberFormatException if ids are not numeric (commas and whitespace are OK)
 */
public List<Allele> getFilteredAllelesList(Filter filter) throws NumberFormatException {
    String alleleIdWhere = "";
    String alleleNameWhere = "";
    String alleleSymbolWhere = "";
    String alleleMgiReferenceWhere = "";
    String geneIdWhere = "";
    String geneNameWhere = "";
    String geneSymbolWhere = "";
    String geneMgiReferenceWhere = "";
    List<Allele> targetList = new ArrayList();

    String queryString = "SELECT * FROM alleles a\nJOIN genes g ON g.id_gene = a.gen_id_gene\nWHERE (1 = 1)\n";

    if ((filter.getAllele_key() != null) && (!filter.getAllele_key().isEmpty())) {
        String alleleIds = Utils.cleanIntArray(filter.getAllele_key());
        if (Utils.isValidIntArray(alleleIds)) {
            alleleIdWhere = "  AND (a.id_allel IN (" + alleleIds + "))\n";
            queryString += alleleIdWhere;
        }
    }
    if ((filter.getAlleleName() != null) && (!filter.getAlleleName().isEmpty())) {
        alleleNameWhere = "  AND (a.name LIKE :alleleName)\n";
        queryString += alleleNameWhere;
    }
    if ((filter.getAlleleSymbol() != null) && (!filter.getAlleleSymbol().isEmpty())) {
        alleleSymbolWhere = "  AND (a.alls_form LIKE :alleleSymbol)\n";
        queryString += alleleSymbolWhere;
    }
    if ((filter.getAlleleMgiReference() != null) && (!filter.getAlleleMgiReference().isEmpty())) {
        alleleMgiReferenceWhere = "  AND (a.mgi_ref LIKE :alleleMgiReference)\n";
        queryString += alleleMgiReferenceWhere;
    }
    if ((filter.getGeneMgiReference() != null) && (!filter.getGeneMgiReference().isEmpty())) {
        geneMgiReferenceWhere = "  AND (g.mgi_ref LIKE :geneMgiReference)\n";
        queryString += geneMgiReferenceWhere;
    }
    if ((filter.getGene_key() != null) && (!filter.getGene_key().isEmpty())) {
        String geneIds = Utils.cleanIntArray(filter.getGene_key());
        if (Utils.isValidIntArray(geneIds)) {
            geneIdWhere = "  AND (a.gen_id_gene IN (" + geneIds + "))\n";
            queryString += geneIdWhere;
        }
    }
    if ((filter.getGeneName() != null) && (!filter.getGeneName().isEmpty())) {
        geneNameWhere = "  AND (g.name LIKE :geneName)\n";
        queryString += geneNameWhere;
    }
    if ((filter.getGeneSymbol() != null) && (!filter.getGeneSymbol().isEmpty())) {
        geneSymbolWhere = "  AND (g.symbol LIKE :geneSymbol)\n";
        queryString += geneSymbolWhere;
    }
    queryString += "ORDER BY a.name\n";

    try {
        getCurrentSession().beginTransaction();
        SQLQuery query = getCurrentSession().createSQLQuery(queryString);
        if (!alleleNameWhere.isEmpty())
            query.setParameter("alleleName", "%" + filter.getAlleleName() + "%");
        if (!alleleSymbolWhere.isEmpty())
            query.setParameter("alleleSymbol", "%" + filter.getAlleleSymbol() + "%");
        if (!alleleMgiReferenceWhere.isEmpty())
            query.setParameter("alleleMgiReference", "%" + filter.getAlleleMgiReference() + "%");
        if (!geneMgiReferenceWhere.isEmpty())
            query.setParameter("geneMgiReference", "%" + filter.getGeneMgiReference() + "%");
        if (!geneNameWhere.isEmpty())
            query.setParameter("geneName", "%" + filter.getGeneName() + "%");
        if (!geneSymbolWhere.isEmpty())
            query.setParameter("geneSymbol", "%" + filter.getGeneSymbol() + "%");

        targetList = query.addEntity(Allele.class).list();
        getCurrentSession().getTransaction().commit();
    } catch (HibernateException e) {
        getCurrentSession().getTransaction().rollback();
        throw e;
    }

    return targetList;
}

From source file:uk.ac.ebi.emma.manager.BackgroundsManager.java

License:Apache License

/**
 * Given a <code>Filter</code> object describing any/all of the following
 * fields://  w  w  w  .  j  a va 2s.  com
 * <ul>
 * <li>background ID (may be a comma-separated list)</li>
 * <li>background name</li>
 * <li>background symbol</li>
 * <li>curated (may 'Y', 'N', or null/empty)</li>
 * <li>inbred (may 'Y', 'N', or null/empty)</li>
 * this method performs a query, ANDing all non-empty fields in a WHERE
 * clause against the backgrounds table.
 * 
 * The result is a <code>List&lt;Background&gt;</code> of
 * qualifying results. A list is always returned, even if there are no results.
 * 
 * @param filter values to filter by
 * @return a list of matching <code>Background</code> instances.
 * @throws NumberFormatException if ids are not numeric (commas and whitespace are OK)
 */
public List<Background> getFilteredBackgroundsList(Filter filter) throws NumberFormatException {
    String backgroundKeyWhere = "";
    String backgroundNameWhere = "";
    String backgroundSymbolWhere = "";
    String backgroundIsCuratedWhere = "";
    String backgroundIsInbredWhere = "";

    List<Background> targetList = new ArrayList();

    String queryString = "SELECT b.*, GROUP_CONCAT(m.id) AS mutation_keys\n" + "FROM backgrounds b\n"
            + "LEFT OUTER JOIN mutations m ON m.bg_id_bg = b.id_bg\n" + "WHERE (1 = 1)\n";

    if ((filter.getBackground_key() != null) && (!filter.getBackground_key().isEmpty())) {
        String backgroundIds = Utils.cleanIntArray(filter.getBackground_key());
        if (Utils.isValidIntArray(backgroundIds)) {
            backgroundKeyWhere = "  AND (b.id_bg IN (" + backgroundIds + "))\n";
            queryString += backgroundKeyWhere;
        }
    }
    if ((filter.getBackgroundName() != null) && (!filter.getBackgroundName().isEmpty())) {
        backgroundNameWhere = "  AND (b.name LIKE :backgroundName)\n";
        queryString += backgroundNameWhere;
    }
    if ((filter.getBackgroundSymbol() != null) && (!filter.getBackgroundSymbol().isEmpty())) {
        backgroundSymbolWhere = "  AND (b.symbol LIKE :backgroundSymbol)\n";
        queryString += backgroundSymbolWhere;
    }
    if ((filter.getBackgroundIsCurated() != null) && (!filter.getBackgroundIsCurated().isEmpty())) {
        switch (filter.getBackgroundIsCurated()) {
        case "Y":
        case "y":
            backgroundIsCuratedWhere = "  AND (b.curated = 'Y')\n";
            break;

        case "N":
        case "n":
            backgroundIsCuratedWhere = "  AND (b.curated = 'N')\n";
            break;

        default:
            backgroundIsCuratedWhere = "  AND (((b.curated != 'Y') && (b.curated != 'N')) || (b.curated IS NULL))\n";
            break;
        }
        queryString += backgroundIsCuratedWhere;
    }
    if ((filter.getBackgroundIsInbred() != null) && (!filter.getBackgroundIsInbred().isEmpty())) {
        switch (filter.getBackgroundIsInbred()) {
        case "Y":
        case "y":
            backgroundIsInbredWhere = "  AND (b.inbred = 'Y')\n";
            break;

        case "N":
        case "n":
            backgroundIsInbredWhere = "  AND (b.inbred = 'N')\n";
            break;

        default:
            backgroundIsInbredWhere = "  AND (((b.inbred != 'Y') && (b.inbred != 'N')) || (b.inbred IS NULL))\n";
            break;
        }
        queryString += backgroundIsInbredWhere;
    }

    queryString += "ORDER BY b.name, b.symbol\n";

    try {
        getCurrentSession().beginTransaction();
        SQLQuery query = getCurrentSession().createSQLQuery(queryString);

        if (!backgroundNameWhere.isEmpty())
            query.setParameter("backgroundName", "%" + filter.getBackgroundName() + "%");
        if (!backgroundSymbolWhere.isEmpty())
            query.setParameter("backgroundSymbol", "%" + filter.getBackgroundSymbol() + "%");

        List resultSet = query.addEntity(Background.class).addScalar("mutation_keys").list();

        if (resultSet != null) {
            for (Object result : resultSet) {
                // Fetch any bound mutations.
                Object[] row = (Object[]) result;
                Background background = (Background) row[0];
                if (background != null) {
                    background.setMutation_keys((row[1] == null ? "" : row[1].toString())); // Add mutation_keys to transient Mutation instance.
                    targetList.add(background);
                }
            }
        }
        getCurrentSession().getTransaction().commit();
    } catch (HibernateException e) {
        getCurrentSession().getTransaction().rollback();
        throw e;
    }

    return targetList;
}

From source file:uk.ac.ebi.emma.manager.GenesManager.java

License:Apache License

/**
 * Given a <code>Filter</code> object describing any/all of the following
 * fields:/*  ww w.j a  va 2 s  .c  o m*/
 * <ul><li>gene chromosome</li>
 * <li>gene ID</li>
 * <li>gene name</li>
 * <li>gene symbol</li>
 * <li>MGI reference</li></ul>
 * this method performs a query, ANDing all non-empty fields in a WHERE
 * clause against the genes table. The result is a <code>List&lt;Gene
 * &gt;</code> of qualifying results. A list is always returned, even if
 * there are no results.
 * 
 * @param filter values to filter by
 * @return a list of <code>Gene</code>.
 * @throws NumberFormatException if primary keys are not numeric (commas and whitespace are OK),
 *         HibernateException if a hibernate error occurs
 */
public List<Gene> getFilteredGenesList(Filter filter) throws NumberFormatException, HibernateException {
    String chromosomeWhere = "";
    String geneIdWhere = "";
    String geneNameWhere = "";
    String geneSymbolWhere = "";
    String mgiReferenceWhere = "";
    List<Gene> targetList = new ArrayList();
    int gene_key = -1;

    String queryString = "SELECT * FROM genes\nWHERE (1 = 1)";
    if ((filter.getChromosome() != null) && (!filter.getChromosome().isEmpty())) {
        chromosomeWhere = "  AND (chromosome = :chromosome)\n";
        queryString += chromosomeWhere;
    }
    if ((filter.getGene_key() != null) && (!filter.getGene_key().isEmpty())) {
        String geneIds = Utils.cleanIntArray(filter.getGene_key());
        if (Utils.isValidIntArray(geneIds)) {
            geneIdWhere = "  AND (id_gene IN (" + geneIds + "))\n";
            queryString += geneIdWhere;
        }
    }
    if ((filter.getGeneName() != null) && (!filter.getGeneName().isEmpty())) {
        geneNameWhere = "  AND (name LIKE :name)\n";
        queryString += geneNameWhere;
    }
    if ((filter.getGeneSymbol() != null) && (!filter.getGeneSymbol().isEmpty())) {
        geneSymbolWhere = "  AND (symbol LIKE :symbol)\n";
        queryString += geneSymbolWhere;
    }
    if ((filter.getGeneMgiReference() != null) && (!filter.getGeneMgiReference().isEmpty())) {
        mgiReferenceWhere = "  AND (mgi_ref LIKE :mgiReference)\n";
        queryString += mgiReferenceWhere;
    }
    queryString += "ORDER BY name\n";

    try {
        getCurrentSession().beginTransaction();
        SQLQuery query = getCurrentSession().createSQLQuery(queryString);
        if (!chromosomeWhere.isEmpty())
            query.setParameter("chromosome", filter.getChromosome());
        if (!geneNameWhere.isEmpty())
            query.setParameter("name", "%" + filter.getGeneName() + "%");
        if (!geneSymbolWhere.isEmpty())
            query.setParameter("symbol", "%" + filter.getGeneSymbol() + "%");
        if (!mgiReferenceWhere.isEmpty())
            query.setParameter("mgiReference", "%" + filter.getGeneMgiReference() + "%");

        targetList = query.addEntity(Gene.class).list();
        getCurrentSession().getTransaction().commit();
    } catch (HibernateException e) {
        getCurrentSession().getTransaction().rollback();
        throw e;
    }

    return targetList;
}

From source file:uk.ac.ebi.emma.manager.MutationsManager.java

License:Apache License

/**
 * Given a <code>Filter</code> object describing any/all of the following
 * fields://w ww.  j  av a2 s. c o  m
 * <ul>
 * <li>mutation ID (may be a comma-separated list)</li>
 * <li>mutation type</li>
 * <li>mutation subtype</li>
 * <li>strain ID (may be a comma-separated list)</li>
 * <li>allele ID (may be a comma-separated list)</li>
 * <li>background ID (may be a comma-separated list)</li>
 * <li>gene ID (may be a comma-separated list)</li>
 * <li>gene symbol</li>
 * 
 * this method performs a query, ANDing all non-empty fields in a WHERE
 * clause against the mutations table (joined to the strains, alleles, and
 * backgrounds tables for strain, allele, and background Ids).
 * 
 * The result is a <code>List&lt;Mutation&gt;</code> of
 * qualifying results. A list is always returned, even if there are no results.
 * 
 * @param filter values to filter by
 * @return a list of matching <code>Mutation</code> instances.
 * @throws NumberFormatException if ids are not numeric (commas and whitespace are OK)
 */
public List<Mutation> getFilteredMutationsList(Filter filter) throws NumberFormatException {
    String mutationKeyWhere = "";
    String mutationTypeWhere = "";
    String mutationSubtypeWhere = "";
    String strainKeyWhere = "";
    String alleleKeyWhere = "";
    String backgroundKeyWhere = "";
    String geneKeyWhere = "";
    String geneSymbolWhere = "";

    List<Mutation> targetList = new ArrayList();

    String queryString = "SELECT m.*, GROUP_CONCAT(ms.str_id_str) AS strain_keys\n" + "FROM mutations m\n"
            + "LEFT OUTER JOIN mutations_strains ms ON ms.mut_id = m.id\n"
            + "JOIN alleles a ON a.id_allel = m.alls_id_allel\n" + "JOIN genes g ON g.id_gene = a.gen_id_gene\n"
            + "WHERE (1 = 1)\n";

    if ((filter.getMutation_key() != null) && (!filter.getMutation_key().isEmpty())) {
        String mutationIds = Utils.cleanIntArray(filter.getMutation_key());
        if (Utils.isValidIntArray(mutationIds)) {
            mutationKeyWhere = "  AND (m.id IN (" + mutationIds + "))\n";
            queryString += mutationKeyWhere;
        }
    }
    if ((filter.getMutationType() != null) && (!filter.getMutationType().isEmpty())) {
        mutationTypeWhere = "  AND (m.main_type = :mutationType)\n";
        queryString += mutationTypeWhere;
    }
    if ((filter.getMutationSubtype() != null) && (!filter.getMutationSubtype().isEmpty())) {
        mutationSubtypeWhere = "  AND (m.sub_type = :mutationSubtype)\n";
        queryString += mutationSubtypeWhere;
    }
    if ((filter.getStrain_key() != null) && (!filter.getStrain_key().isEmpty())) {
        String strainIds = Utils.cleanIntArray(filter.getStrain_key());
        if (Utils.isValidIntArray(strainIds)) {
            strainKeyWhere = "  AND (ms.str_id_str IN (" + strainIds
                    + "))\n";/* changed mutations str_id_str to mutations_strains as this is not always populated (legacy) Mike Relac probably not aware philw  JIRA EMMA-673*/
            queryString += strainKeyWhere;
        }
    }
    if ((filter.getAllele_key() != null) && (!filter.getAllele_key().isEmpty())) {
        String alleleIds = Utils.cleanIntArray(filter.getAllele_key());
        if (Utils.isValidIntArray(alleleIds)) {
            alleleKeyWhere = "  AND (m.alls_id_allel in (" + alleleIds + "))\n";
            queryString += alleleKeyWhere;
        }
    }
    if ((filter.getBackground_key() != null) && (!filter.getBackground_key().isEmpty())) {
        String backgroundIds = Utils.cleanIntArray(filter.getBackground_key());
        if (Utils.isValidIntArray(backgroundIds)) {
            backgroundKeyWhere = "  AND (m.bg_id_bg IN (" + backgroundIds + "))\n";
            queryString += backgroundKeyWhere;
        }
    }
    if ((filter.getGene_key() != null) && (!filter.getGene_key().isEmpty())) {
        String geneIds = Utils.cleanIntArray(filter.getGene_key());
        if (Utils.isValidIntArray(geneIds)) {
            geneKeyWhere = "  AND (g.id_gene IN (" + geneIds + "))\n";
            queryString += geneKeyWhere;
        }
    }
    if ((filter.getGeneSymbol() != null) && (!filter.getGeneSymbol().isEmpty())) {
        geneSymbolWhere = "  AND (g.symbol = :geneSymbol)\n";
        queryString += geneSymbolWhere;
    }

    queryString += "GROUP BY ms.mut_id\n" + "ORDER BY m.main_type, m.sub_type\n";

    try {
        getCurrentSession().beginTransaction();
        SQLQuery query = getCurrentSession().createSQLQuery(queryString);

        if (!mutationTypeWhere.isEmpty())
            query.setParameter("mutationType", filter.getMutationType());
        if (!mutationSubtypeWhere.isEmpty())
            query.setParameter("mutationSubtype", filter.getMutationSubtype());
        if (!geneSymbolWhere.isEmpty())
            query.setParameter("geneSymbol", filter.getGeneSymbol());
        List resultSet = query.addEntity(Mutation.class).addScalar("strain_keys").list();

        if (resultSet != null) {
            for (Object result : resultSet) {
                Object[] row = (Object[]) result;
                Mutation mutation = (Mutation) row[0];
                if (mutation != null) {
                    mutation.setStrain_keys((row[1] == null ? "" : row[1].toString())); // Add strain_keys to transient Mutation instance.
                    targetList.add(mutation);
                }
            }
        }
        getCurrentSession().getTransaction().commit();
    } catch (HibernateException e) {
        getCurrentSession().getTransaction().rollback();
        throw e;
    }
    System.out.println("GENERATED SQL is " + queryString);
    return targetList;
}

From source file:uk.chromis.utils.DataLogicKitchen.java

License:Open Source License

public List<Orders> selectByOrderId(String orderid) {
    if (Boolean.valueOf(AppConfig.getInstance().getProperty("screen.allorders"))) {
        sql_query = "SELECT * FROM ORDERS WHERE ORDERID ='" + orderid + "' ORDER BY AUXILIARY ";
    } else {//from   ww  w  .  j a  v a 2s . c o m
        sql_query = "SELECT * FROM ORDERS WHERE ORDERID ='" + orderid + "' AND DISPLAYID = "
                + Integer.parseInt(AppConfig.getInstance().getProperty("screen.displaynumber"))
                + " ORDER BY AUXILIARY ";
    }

    SQLQuery query = HibernateUtil.getSessionFactory().openSession().createSQLQuery(sql_query);
    query.addEntity(Orders.class);
    List<Orders> results = query.list();
    return results;
}

From source file:util.Cargar.java

public Object resultadoUnico(String QuerySql, Class tipo) {
    sessionHibernate();/*from   w  ww .j  ava2s .  c  o  m*/
    Object retorno = null;
    try {
        try {
            SQLQuery consulta = st.createSQLQuery(QuerySql);
            consulta.addEntity(tipo);
            retorno = consulta.uniqueResult();
            if (retorno == null) {
                JOptionPane.showMessageDialog(null, "La sentencia no ha retornado ningun valor.", "Aviso",
                        JOptionPane.ERROR_MESSAGE);
            }
        } catch (HibernateException ex) {
            JOptionPane.showMessageDialog(null,
                    "La sentencia Select ha retornado mas de un valor. " + "\n" + ex.getMessage(), "Aviso",
                    JOptionPane.ERROR_MESSAGE);
        }
    } catch (Exception ex) {
        JOptionPane.showMessageDialog(null, "Ha ocurrido un error." + ex.getMessage(), "Aviso",
                JOptionPane.ERROR_MESSAGE);
    }
    return retorno;
}

From source file:vn.edu.vnu.uet.fit.model.CourseReportModel.java

public List<CourseReportOverall> getCourseReports() {
    Session session = HibernateUtil.getSessionFactory().openSession();
    Transaction trans = session.beginTransaction();
    String queryStr = "select F.courseId as reportId, F.courseId, F.numberOfStudent, F.averageScore ,count(problemId) as numberOfProblem from\n"
            + "  (select courseId, count(userId) as numberOfStudent, avg(totalScore) as averageScore from\n"
            + "    (select courseId, userId, COALESCE(sum(resultScore),0) as totalScore from  \n"
            + "      (select C.courseId, courseusers.userId, submit.problemId, submit.result, submit.resultScore, submit.isActive\n"
            + "        from courseusers \n"
            + "        left join (select submissions.submitId, submissions.courseId, submissions.problemId, submissions.userId, submissions.result, submissions.resultScore, submissions.isActive \n"
            + "          from submissions\n"
            + "            inner join (select courseId, problemId, userId, max(resultScore) as maxscore from submissions where submissions.courseId is not null and isActive != 0 GROUP BY courseId, problemId, userId) as B\n"
            + "            on submissions.courseId = B.courseId and submissions.problemId = B.problemId and submissions.userId = B.userId\n"
            + "          where submissions.resultScore = B.maxscore and isActive != 0) as submit on courseusers.courseId = submit.courseId and courseusers.userId = submit.userId\n"
            + "        right join (select * from courses) as C on courseusers.courseId = C.courseId) as D\n"
            + "      group by courseId, userId) as E\n" + "    group by courseId\n"
            + "  )as F left join courseproblems on F.courseId = courseproblems.courseId\n"
            + "  group by F.courseId";
    SQLQuery query = session.createSQLQuery(queryStr);
    query.addEntity(CourseReportOverall.class);
    List<CourseReportOverall> result = query.list();
    trans.commit();//from   w w  w .j a  v  a 2 s  .  c o m
    session.close();
    return result;
}

From source file:vn.edu.vnu.uet.fit.model.CourseReportModel.java

public List<StudentReportByCourse> getStudentResultByCourse(int courseId) {
    Session session = HibernateUtil.getSessionFactory().openSession();
    Transaction trans = session.beginTransaction();
    String queryStr = "SELECT @s\\:=@s+1 as reportId, courseId, userId, COALESCE(sum(isActive),0) as summitedExercise, COALESCE(SUM(score), 0) as courseScore FROM\n"
            + "  (SELECT \n" + "      courseusers.courseId as courseId, \n"
            + "      courseusers.userId as userId, \n" + "      B.problemId as problemId, \n"
            + "      B.submitId as submitId, \n" + "      B.result as result, \n"
            + "      B.resultScore as score,\n" + "      B.isActive as isActive\n"
            + "    FROM courseusers left outer join (\n"
            + "      select submitId, courseId, problemId, userId, result, max(resultScore) as resultScore, isActive from submissions \n"
            + "        where submissions.courseId is not null and isActive != 0\n"
            + "      GROUP BY courseId, problemId, userId) as B on courseusers.courseId = B.courseId and courseusers.userId = B.userId\n"
            + "  ) as C,\n" + "  (SELECT @s\\:= 0) AS s\n" + "GROUP BY courseId, userId \n"
            + "HAVING C.courseId = :courseId";
    SQLQuery query = session.createSQLQuery(queryStr);
    query.setInteger("courseId", courseId);
    query.addEntity(StudentReportByCourse.class);
    List<StudentReportByCourse> result = query.list();
    trans.commit();//from w w  w  .  ja va  2s.c o  m
    session.close();
    return result;
}

From source file:vn.edu.vnu.uet.fit.model.CourseReportModel.java

public List<StudentDetailReportByCourse> getStudentDetailResultByCourse(int courseId, int userId) {
    Session session = HibernateUtil.getSessionFactory().openSession();
    Transaction trans = session.beginTransaction();
    String queryStr = "select @s\\:=@s+1 as reportId, submissions.submitId, submissions.courseId, submissions.problemId, submissions.userId, submissions.result, submissions.resultScore, submissions.isActive \n"
            + "  from submissions\n"
            + "    inner join (select courseId, problemId, userId, max(resultScore) as maxscore from submissions where submissions.courseId is not null and isActive != 0 GROUP BY courseId, problemId, userId) as B\n"
            + "    on submissions.courseId = B.courseId and submissions.problemId = B.problemId and submissions.userId = B.userId,\n"
            + "    (SELECT @s\\:= 0) AS s\n"
            + "  where submissions.resultScore = B.maxscore and submissions.isActive != 0 and submissions.userId = :userId and submissions.courseId = :courseId";
    SQLQuery query = session.createSQLQuery(queryStr);
    query.setInteger("courseId", courseId);
    query.setInteger("userId", userId);
    query.addEntity(StudentDetailReportByCourse.class);
    List<StudentDetailReportByCourse> result = query.list();
    trans.commit();/*from   www  . j  a  v  a2s.  c  om*/
    session.close();
    return result;
}

From source file:vn.edu.vnu.uet.fit.model.ReportModel.java

public List<StudentDetailReportByCourse> getStudentDetailResultByCourse(int courseId) {
    Session session = HibernateUtil.getSessionFactory().openSession();
    Transaction trans = session.beginTransaction();
    String queryStr = "SELECT C.* FROM \n" + "  (SELECT\n" + "    @s\\:=@s+1 as reportId, \n"
            + "    courseusers.courseId as courseId, \n" + "    courseusers.userId as userId, \n"
            + "    B.problemId as problemId, \n" + "    B.submitId as submitId, \n"
            + "    B.result as result, \n" + "    B.resultScore as score,\n" + "    B.isActive as isActive\n"
            + "  FROM courseusers left outer join \n" + "    (SELECT * FROM submissions\n"
            + "    WHERE courseId = 1 and isActive != 0) as B on courseusers.courseId = B.courseId and courseusers.userId = B.userId,\n"
            + "      (SELECT @s\\:= 0) AS s\n" + "  WHERE courseusers.courseId = :courseId\n"
            + "  ORDER BY userId, problemId, score desc) AS C\n" + "  GROUP BY courseId, userId, problemId";
    SQLQuery query = session.createSQLQuery(queryStr);
    query.setInteger("courseId", courseId);
    query.addEntity(StudentDetailReportByCourse.class);
    List<StudentDetailReportByCourse> result = query.list();
    trans.commit();//from   ww  w .  j  a v a2  s.co  m
    session.close();
    return result;
}