Example usage for org.hibernate Session createSQLQuery

Introduction

In this page you can find the example usage for org.hibernate Session createSQLQuery.

Prototype

@Override
    NativeQuery createSQLQuery(String queryString);

Source Link

Usage

From source file:ca.usask.gmcte.currimap.action.ProgramManager.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<ProgramOutcomeCourseContribution> getProgramOutcomeServiceCourseContributionForProgram(
        Program program) {/*ww  w.ja va2  s . com*/
    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    session.beginTransaction();
    List<ProgramOutcomeCourseContribution> toReturn = new ArrayList<ProgramOutcomeCourseContribution>();
    try {
        StringBuffer sql = new StringBuffer();
        sql.append(
                " SELECT lccpo.course_id AS courseId, lppo.program_outcome_id AS programOutcomeId, cov.calculation_value AS contributionSum, mov.calculation_value AS masterySum ");
        sql.append("   from link_course_contribution_program_outcome lccpo");
        sql.append("      , link_program_program_outcome lppo ");
        sql.append("      , contribution_option_value cov ");
        sql.append("      , mastery_option_value mov ");
        sql.append("  WHERE lccpo.link_program_program_outcome_id = lppo.id ");
        sql.append("    AND lccpo.contribution_option_id = cov.id");
        sql.append("    AND lccpo.mastery_option_id = mov.id");
        sql.append("    AND lppo.program_id=:programId");
        logger.debug("getProgramOutcomeServiceCourseContributionForProgram :" + sql.toString());
        List<ProgramOutcomeCourseContribution> fromCourses = (List<ProgramOutcomeCourseContribution>) session
                .createSQLQuery(sql.toString())
                .setResultTransformer(Transformers.aliasToBean(ProgramOutcomeCourseContribution.class))
                .setParameter("programId", program.getId()).list();

        toReturn.addAll(fromCourses);
        session.getTransaction().commit();
    } catch (Exception e) {
        HibernateUtil.logException(logger, e);
    }
    return toReturn;
}

---

From source file:ca.usask.gmcte.currimap.action.ProgramManager.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<CourseAssessmentOption> getProgramCourseAssessmentOptions(Program program, List<String> terms) {
    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    session.beginTransaction();/*from  w  w w. j  ava  2  s .  c  om*/
    List<CourseAssessmentOption> toReturn = new ArrayList<CourseAssessmentOption>();
    try {
        StringBuffer sql = new StringBuffer();
        sql.append(
                " select sum(lcoa.weight) as weight, ato.display_index as optionId, co.course_id as courseId  ");
        sql.append("    from course_offering co ");
        sql.append("        ,link_course_offering_assessment lcoa ");
        sql.append("        ,link_course_program lcp ");
        sql.append("        ,assessment_time_option ato ");
        sql.append("  WHERE ");
        sql.append(HibernateUtil.getListAsString("co.term", terms, true));
        sql.append("        lcp.program_id = :programId ");
        sql.append("    AND lcp.course_id = co.course_id ");
        sql.append("    AND co.id = lcoa.course_offering_id ");
        sql.append("    AND ato.id =  lcoa.assessment_time_option_id ");
        sql.append("group by co.course_id, ato.display_index  ");
        sql.append("order by co.course_id, ato.display_index ");
        //one record for each course that is part of the program.  Each record contains courseid, sum of weights for each time-option and the number of course_offerings for each.
        List<CourseAssessmentOption> fromOfferings = (List<CourseAssessmentOption>) session
                .createSQLQuery(sql.toString())
                .setResultTransformer(Transformers.aliasToBean(CourseAssessmentOption.class))
                .setParameter("programId", program.getId()).list();
        toReturn.addAll(fromOfferings);
        session.getTransaction().commit();
    } catch (Exception e) {
        HibernateUtil.logException(logger, e);
    }
    return toReturn;
}

---

From source file:ca.usask.gmcte.currimap.action.ProgramManager.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<CourseTeachingMethodOption> getProgramCourseTeachingMethodOptions(Program program,
        List<String> terms) {
    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    session.beginTransaction();//from  www  .ja v  a 2 s .c  o  m
    List<CourseTeachingMethodOption> toReturn = new ArrayList<CourseTeachingMethodOption>();
    try {
        StringBuffer sql = new StringBuffer();
        sql.append(
                "  select sum(tmpo.comparative_value) as weight, tm.display_index as teachingMethodIndex, count(distinct lctm.course_offering_id) as offeringCount, co.course_id as courseId  ");
        sql.append("    from course_offering co ");
        sql.append("        ,link_course_offering_teaching_method lctm ");
        sql.append("        ,link_course_program lcp ");
        sql.append("        ,teaching_method tm ");
        sql.append("        ,teaching_method_portion_option tmpo ");
        sql.append("  WHERE ");
        sql.append(HibernateUtil.getListAsString("co.term", terms, true));
        sql.append(" lcp.program_id = :programId ");
        sql.append("    AND lcp.course_id = co.course_id ");
        sql.append("    AND co.id = lctm.course_offering_id ");
        sql.append("    AND lctm.teaching_method_id = tm.id ");
        sql.append("    AND lctm.teaching_method_portion_option_id = tmpo.id ");
        sql.append("group by co.course_id, tm.display_index  ");
        sql.append("order by co.course_id, tm.display_index ");
        //one record for each course that is part of the program.  Each record contains courseid, sum of weights for each time-option and the number of course_offerings for each.
        List<CourseTeachingMethodOption> fromOfferings = (List<CourseTeachingMethodOption>) session
                .createSQLQuery(sql.toString())
                .setResultTransformer(Transformers.aliasToBean(CourseTeachingMethodOption.class))
                .setParameter("programId", program.getId()).list();
        toReturn.addAll(fromOfferings);
        session.getTransaction().commit();
    } catch (Exception e) {
        HibernateUtil.logException(logger, e);
    }
    return toReturn;
}

---

From source file:ca.usask.gmcte.currimap.action.ProgramManager.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<CourseAssessmentOption> getProgramAssessmentGroups(Program program, List<String> terms) {
    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    session.beginTransaction();/*from  w w w.  j av  a  2  s  .  c  o m*/
    List<CourseAssessmentOption> toReturn = new ArrayList<CourseAssessmentOption>();
    try {
        StringBuffer sql = new StringBuffer();
        sql.append(
                " select co.course_id as courseId,gr.display_index as optionId, sum(lcoa.weight) as weight ");
        sql.append("  from assessment_group gr ");
        sql.append("     , assessment a ");
        sql.append("     , link_course_offering_assessment lcoa ");
        sql.append("     , course_offering co ");
        sql.append("     , link_course_program lcp ");
        sql.append("where  ");
        sql.append(HibernateUtil.getListAsString("co.term", terms, true));
        sql.append("lcp.program_id=:programId  ");
        sql.append("  and lcp.course_id = co.course_id ");
        sql.append("  and co.id = lcoa.course_offering_id ");
        sql.append("  and lcoa.assessment_id = a.id ");
        sql.append("  and a.assessment_group_id = gr.id ");
        sql.append("  group by co.course_id,gr.display_index ");
        //one record for each course that is part of the program.  Each record contains courseid, sum of weights for each time-option and the number of course_offerings for each.
        List<CourseAssessmentOption> fromOfferings = (List<CourseAssessmentOption>) session
                .createSQLQuery(sql.toString())
                .setResultTransformer(Transformers.aliasToBean(CourseAssessmentOption.class))
                .setParameter("programId", program.getId()).list();
        toReturn.addAll(fromOfferings);
        session.getTransaction().commit();
    } catch (Exception e) {
        HibernateUtil.logException(logger, e);
    }
    return toReturn;
}

---

From source file:ca.usask.gmcte.currimap.action.ProgramManager.java

License:Open Source License

public Map<String, Integer> getCourseOfferingCounts(Program p, List<String> terms) {

    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    session.beginTransaction();//from  w  w  w.  j  a  v a  2 s . co  m
    Map<String, Integer> toReturn = new TreeMap<String, Integer>();
    try {
        StringBuffer sql = new StringBuffer();
        sql.append("  select co.course_id as a, count( distinct co.id) as b");
        sql.append(" from course_offering co ");
        sql.append("    , link_course_program lcp ");
        sql.append("where ");
        sql.append(HibernateUtil.getListAsString("co.term", terms, true));
        sql.append(" lcp.program_id = :programId ");
        sql.append(" and lcp.course_id = co.course_id ");
        sql.append(" and co.id in (select course_offering_id from link_course_offering_assessment) ");
        sql.append(" group by co.course_id; ");

        @SuppressWarnings("unchecked")
        List<Pair> counts = (List<Pair>) session.createSQLQuery(sql.toString())
                .setResultTransformer(Transformers.aliasToBean(Pair.class)).setParameter("programId", p.getId())
                .list();
        for (Pair pair : counts) {
            toReturn.put("" + pair.getA(), pair.getB());
        }
        session.getTransaction().commit();
    } catch (Exception e) {
        HibernateUtil.logException(logger, e);
    }
    return toReturn;
}

---

From source file:ca.usask.gmcte.currimap.action.ProgramManager.java

License:Open Source License

public Map<String, Integer> getProgramOutcomeContributions(Program p, List<String> terms) {

    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    session.beginTransaction();//w w  w . ja  v  a  2 s  .co m
    Map<String, Integer> toReturn = new TreeMap<String, Integer>();
    try {

        StringBuffer sql = new StringBuffer();
        sql.append("   SELECT a.outcome,a.course,sum(a.calc)/count(section) as value FROM ");
        sql.append("   (");
        sql.append(
                "   select cov.calculation_value as calc, po.id as outcome, co.course_id as course, co.id as section");
        sql.append("   from link_course_offering_contribution_program_outcome lcocpo");
        sql.append("   , link_program_program_outcome lppo");
        sql.append("   , program_outcome po");
        sql.append("   , contribution_option_value cov");
        sql.append("   , course_offering co");
        sql.append("   WHERE lppo.id = lcocpo.link_program_program_outcome_id");
        sql.append("     AND po.id=lppo.program_outcome_id");
        sql.append("     AND cov.id = lcocpo.contribution_option_id ");
        sql.append("     AND co.id = lcocpo.course_offering_id");
        sql.append("     AND lppo.program_id=");
        sql.append(p.getId());
        sql.append("    UNION  ");
        sql.append(
                "   select cov.calculation_value as calc, po.id as outcome, lccpo.course_id as course, -1 as section");
        sql.append("   from link_course_contribution_program_outcome lccpo");
        sql.append("   , link_program_program_outcome lppo");
        sql.append("   , program_outcome po");
        sql.append("   , contribution_option_value cov");
        sql.append("   WHERE lppo.id = lccpo.link_program_program_outcome_id");
        sql.append("     AND po.id=lppo.program_outcome_id");
        sql.append("     AND cov.id = lccpo.contribution_option_id ");
        sql.append("     AND lppo.program_id=");
        sql.append(p.getId());
        sql.append("     ) a");
        sql.append("    group by a.outcome,a.course");
        sql.append("    order by a.outcome,a.course");

        @SuppressWarnings("unchecked")
        List<Pair> counts = (List<Pair>) session.createSQLQuery(sql.toString())
                .setResultTransformer(Transformers.aliasToBean(Pair.class)).setParameter("programId", p.getId())
                .list();
        for (Pair pair : counts) {
            toReturn.put("" + pair.getA(), pair.getB());
        }
        session.getTransaction().commit();
    } catch (Exception e) {
        HibernateUtil.logException(logger, e);
    }
    return toReturn;
}

---

From source file:ch.algotrader.dao.AbstractDao.java

License:Open Source License

private Query createQuery(final String queryString, final QueryType type) {

    Session currentSession = getCurrentSession();
    switch (type) {
    case HQL:// w  w w  . jav  a2  s  . co  m
        return currentSession.createQuery(queryString);
    case SQL:
        return currentSession.createSQLQuery(queryString);
    case BY_NAME:
        return currentSession.getNamedQuery(queryString);
    default:
        throw new IllegalStateException("Unexpected query type: " + type);
    }
}

---

From source file:ch.algotrader.dao.AbstractDao.java

License:Open Source License

protected SQLQuery prepareSQLQuery(final LockOptions lockOptions, final String queryString,
        final int maxResults) {

    Session currentSession = getCurrentSession();
    SQLQuery query = currentSession.createSQLQuery(queryString);
    if (lockOptions != null) {
        query.setLockOptions(lockOptions);
    }//from  ww w . ja  v  a2 s.  c o m
    if (maxResults > 0) {
        query.setMaxResults(maxResults);
    }
    return query;
}

---

From source file:ch.algotrader.dao.AbstractDao.java

License:Open Source License

protected SQLQuery prepareSQLQuery(final LockOptions lockOptions, final String queryString,
        final int maxResults, final Object... params) {

    Session currentSession = getCurrentSession();
    SQLQuery query = currentSession.createSQLQuery(queryString);
    if (lockOptions != null) {
        query.setLockOptions(lockOptions);
    }//from  w ww.j  av a 2  s .c  om
    if (maxResults > 0) {
        query.setMaxResults(maxResults);
    }
    applyParameters(query, params);
    return query;
}

---

From source file:ch.algotrader.dao.AbstractDao.java

License:Open Source License

protected SQLQuery prepareSQLQuery(final LockOptions lockOptions, final String queryString,
        final int maxResults, final NamedParam... params) {

    Session currentSession = getCurrentSession();
    SQLQuery query = currentSession.createSQLQuery(queryString);
    if (lockOptions != null) {
        query.setLockOptions(lockOptions);
    }//w  ww.  j  av  a2  s  .com
    if (maxResults > 0) {
        query.setMaxResults(maxResults);
    }
    applyParameters(query, params);
    return query;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »