Example usage for org.hibernate Session createSQLQuery

List of usage examples for org.hibernate Session createSQLQuery

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. Session
  6. 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.OutcomeManager.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<CourseOutcome> getOutcomesForOrganization(Organization d) {
    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    session.beginTransaction();/*from  www.  j a v  a 2s . c  o m*/
    List<CourseOutcome> toReturn = null;
    try {
        StringBuilder sql = new StringBuilder();

        sql.append("       SELECT {o.*} ");
        sql.append("         FROM course_outcome o ");
        sql.append("             , course_outcome_group og");
        sql.append("        WHERE o.course_outcome_group_id = og.id");
        sql.append("        AND (og.organization_specific ='N' ");
        sql.append("                OR (og.organization_specific = 'Y' AND og.organization_id=:orgId) )");
        sql.append("     ORDER BY og.name, o.name ");

        toReturn = (List<CourseOutcome>) session.createSQLQuery(sql.toString())
                .addEntity("o", CourseOutcome.class).setParameter("orgId", d.getId()).list();

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

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

License:Open Source License

@SuppressWarnings("unchecked")
public List<CourseOutcome> getOutcomesForCourseOfferingHavingCharacteristic(CourseOffering courseOffering,
        Characteristic charac) {/* w  ww  .  j av a 2 s. c o m*/
    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    session.beginTransaction();
    List<CourseOutcome> toReturn = null;
    try {
        StringBuilder sql = new StringBuilder();

        sql.append("       SELECT {o.*} ");
        sql.append("         FROM link_course_offering_outcome lco, ");
        sql.append("              link_course_offering_outcome_characteristic lcoc, ");
        sql.append("              course_outcome o ");
        sql.append("        WHERE o.id = lco.course_outcome_id ");
        sql.append("          AND lco.course_offering_id = :courseOfferingId ");
        sql.append("          AND lcoc.link_course_offering_outcome_id = lco.id ");
        sql.append("          AND lcoc.characteristic_id = :charId ");
        sql.append("     ORDER BY o.name ");

        logger.debug(sql.toString());
        logger.debug(" courseOfferingId=" + courseOffering.getId());
        toReturn = (List<CourseOutcome>) session.createSQLQuery(sql.toString())
                .addEntity("o", CourseOutcome.class).setParameter("courseOfferingId", courseOffering.getId())
                .setParameter("charId", charac.getId()).list();
        session.getTransaction().commit();
    } catch (Exception e) {
        HibernateUtil.logException(logger, e);
    }
    return toReturn;
}

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

License:Open Source License

@SuppressWarnings("unchecked")
public List<CourseOutcome> getOutcomesWithCharacteristicTypeForProgramAndCourseOffering(Program p,
        CharacteristicType c, CourseOffering courseOffering) {
    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    session.beginTransaction();/* ww w .  j  av a  2s.c o m*/
    List<CourseOutcome> toReturn = null;
    try {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT {co.*} ");
        sql.append("  FROM outcome co, ");
        sql.append("       link_outcome_outcome lco,");
        sql.append("       link_outcome_outcome_characteristic lcoc, ");
        sql.append("       characteristic c, ");
        sql.append("       characteristic_type ct, ");
        sql.append("       link_outcome_program lpc");

        sql.append(" WHERE c.id=lcoc.characteristic_id  ");
        sql.append("   AND lcoc.link_outcome_outcome_id = lco.id ");
        sql.append("   AND lco.outcome_id=co.id ");
        sql.append("   AND c.characteristic_type = ct.id ");
        sql.append("   AND ct.id=:charTypeId ");
        sql.append("   AND lpc.outcome_id = c.id");
        sql.append("   AND lpc.program_id = :programId ");
        sql.append("ORDER BY co.subject, co.outcome_number ");

        toReturn = (List<CourseOutcome>) session.createSQLQuery(sql.toString())
                .addEntity("co", CourseOutcome.class).setParameter("charTypeId", c.getId())
                .setParameter("programId", p.getId()).list();
        session.getTransaction().commit();
    } catch (Exception e) {
        HibernateUtil.logException(logger, e);
    }
    return toReturn;
}

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

License:Open Source License

public HashMap<String, Organization> getOrganizationsForUser(String userid) {
    HashMap<String, Organization> toReturn = new HashMap<String, Organization>();
    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    session.beginTransaction();//www  . j  ava  2s. co m
    try {

        StringBuilder sql = new StringBuilder();
        sql.append("   select {o.*} ");
        sql.append("  from organization o");
        sql.append("      ,organization_admin oa ");
        sql.append(" where oa.organization_id = o.id");
        sql.append("   and oa.name =:userid and type='Userid' ");
        @SuppressWarnings("unchecked")
        List<Organization> organizations = (List<Organization>) session.createSQLQuery(sql.toString())
                .addEntity("o", Organization.class).setParameter("userid", userid).list();

        for (Organization o : organizations) {
            @SuppressWarnings("unchecked")
            List<Organization> children = session
                    .createQuery("FROM Organization WHERE parentOrganization.id =:orgId")
                    .setParameter("orgId", o.getId()).list();
            for (Organization o2 : children) {
                toReturn.put("" + o2.getId(), o2);
            }
            toReturn.put("" + o.getId(), o);
        }

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

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

License:Open Source License

public HashMap<String, CourseOffering> getOfferingsForUser(String userid,
        HashMap<String, Organization> userHasAccessToOrganizations) {
    HashMap<String, CourseOffering> toReturn = new HashMap<String, CourseOffering>();
    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    session.beginTransaction();//from   w w  w.  j  a v  a2s .c om
    try {

        StringBuilder sql = new StringBuilder();
        sql.append(" select {co.*} ");
        sql.append("  from  program p ");
        sql.append("      ,link_course_program lcp ");
        sql.append("      ,course_offering co");
        sql.append(" where lcp.program_id = p.id");
        sql.append("   and lcp.course_id = co.course_id");
        sql.append("   and p.organization_id in (");
        sql.append(buildIn(userHasAccessToOrganizations.keySet()));
        sql.append(")");
        sql.append("  UNION");
        sql.append("  select {co.*} ");
        sql.append("  from course_offering co");
        sql.append("     , link_course_offering_instructor coi");
        sql.append("     , instructor instruc");
        sql.append(" where instruc.userid = :userid ");
        sql.append("   and coi.instructor_id = instruc.id");
        sql.append("   and coi.course_offering_id");
        sql.append("   and coi.course_offering_id = co.id");
        logger.error(sql);
        @SuppressWarnings("unchecked")
        List<CourseOffering> offerings = (List<CourseOffering>) session.createSQLQuery(sql.toString())
                .addEntity("co", CourseOffering.class).setParameter("userid", userid).list();

        for (CourseOffering o : offerings) {
            toReturn.put("" + o.getId(), o);
        }

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

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

License:Open Source License

@SuppressWarnings("unchecked")
public List<Organization> getOrganizationsForUser(String userid, boolean sysadmin, boolean activeOnly)
        throws Exception {
    List<Organization> organizations = null;
    if (userid == null) {
        return null;
    }//  w  w w .j av a 2s . co m
    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    session.beginTransaction();

    try {
        if (sysadmin) {
            String query = activeOnly ? "FROM Organization WHERE active='Y' ORDER BY lower(name)"
                    : "FROM Organization ORDER BY lower(name)";
            organizations = (List<Organization>) session.createQuery(query).list();
        } else {

            StringBuilder sql = new StringBuilder();
            sql.append("   select {o.*} ");
            sql.append("  from organization o");
            sql.append("      ,organization_admin oa ");
            sql.append(" where oa.organization_id = o.id");
            if (activeOnly) {
                sql.append(" and o.active='Y' ");
            }
            sql.append("   and oa.name =:userid and type='Userid' ");
            organizations = (List<Organization>) session.createSQLQuery(sql.toString())
                    .addEntity("o", Organization.class).setParameter("userid", userid).list();

        }
        session.getTransaction().commit();

    } catch (Exception e) {
        HibernateUtil.logException(logger, e);
    }
    return organizations;
}

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

License:Open Source License

@SuppressWarnings("unchecked")
public boolean hasAccessToCourseOffering(String userid, CourseOffering offering, boolean sysadmin)
        throws Exception {
    Set<LinkCourseOfferingInstructor> instructors = offering.getInstructors();
    LinkCourseOfferingInstructor temp = new LinkCourseOfferingInstructor();
    Instructor instructor = CourseManager.instance().getInstructorByUserid(userid);
    temp.setInstructor(instructor);/*from w w w .  j a v a 2 s .  c  o  m*/
    if (instructors.contains(temp)) //user is instructor of the course offering
    {
        return true;
    }
    List<Organization> accessOrganizations = getOrganizationsForUser(userid, sysadmin, false);
    if (accessOrganizations == null || accessOrganizations.isEmpty()) { //user is NOT in any organizations
        return false;
    }

    StringBuilder in = new StringBuilder();
    for (Organization org : accessOrganizations) {

        in.append(org.getId());
        in.append(",");
    }
    Course course = offering.getCourse();
    StringBuilder sql = new StringBuilder();
    sql.append("SELECT {d.*} ");
    sql.append("  FROM organization d,");
    sql.append("       program_admin pa,");
    sql.append("       link_course_program lcp");
    sql.append(" WHERE d.id = pa.name");
    sql.append("   AND pa.type='orgId' ");
    sql.append("   AND pa.program_id = lcp.program_id");
    sql.append("   AND lcp.course_id = :courseId");
    sql.append("   AND d.id in (");
    sql.append(in.substring(0, in.length() - 1));
    sql.append(")");

    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    session.beginTransaction();
    List<Organization> organizationsWithAccessToCourseForUser = null;
    try {
        organizationsWithAccessToCourseForUser = (List<Organization>) session.createSQLQuery(sql.toString())
                .addEntity("d", Organization.class).setParameter("courseId", course.getId()).list();

        session.getTransaction().commit();
    } catch (Exception e) {
        HibernateUtil.logException(logger, e);
    }
    return !organizationsWithAccessToCourseForUser.isEmpty();
}

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

License:Open Source License

@SuppressWarnings("unchecked")
public List<CourseOfferingContribution> getContributionForProgramOutcome(ProgramOutcome programOutcome,
        Program program, List<String> terms) {
    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    session.beginTransaction();//from  www  .  ja  va  2 s .com
    List<CourseOfferingContribution> toReturn = new ArrayList<CourseOfferingContribution>();
    try {
        StringBuffer sql = new StringBuffer();
        //sql.append(" SELECT co.course_id AS {p.courseId}, lppo.program_outcome_id AS {p.programOutcomeId}, sum(cov.calculation_value) AS {p.contributionSum},count( distinct course_offering_id) AS {p.contributionCount} ");
        //   sql.append(" SELECT co.course_id, lppo.program_outcome_id, sum(cov.calculation_value),count( distinct course_offering_id)");
        sql.append(
                " SELECT co.id AS courseOfferingId, cov.calculation_value AS contributionObject, mov.calculation_value as masteryObject");

        sql.append("  FROM course_offering co");
        sql.append("      ,link_course_program lcp");
        sql.append("      ,link_program_program_outcome lppo");
        sql.append("      ,link_course_offering_contribution_program_outcome lcopo");
        sql.append("      ,contribution_option_value cov ");
        sql.append("      ,mastery_option_value mov ");
        sql.append(" WHERE ");
        sql.append(HibernateUtil.getListAsString("co.term", terms, true));
        sql.append("       co.course_id = lcp.course_id");
        sql.append("   AND co.id = lcopo.course_offering_id");
        sql.append("   AND lcopo.link_program_program_outcome_id = lppo.id");
        sql.append("   AND lppo.program_id = lcp.program_id");
        sql.append("   AND lcopo.contribution_option_id = cov.id");
        sql.append("   AND lcopo.mastery_option_id = mov.id");
        sql.append("   AND lcp.program_id = :programId");
        sql.append("   AND lppo.program_outcome_id = :programOutcomeId");
        sql.append("   AND (cov.calculation_value + mov.calculation_value) > 0 ");

        logger.debug("getContributionForProgramOutcome Core contributions:" + programOutcome.getId() + " "
                + program.getId() + " " + sql.toString());
        List<CourseOfferingContribution> fromOfferings = (List<CourseOfferingContribution>) session
                .createSQLQuery(sql.toString())
                .setResultTransformer(Transformers.aliasToBean(CourseOfferingContribution.class))
                .setParameter("programId", program.getId())
                .setParameter("programOutcomeId", programOutcome.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<CourseOfferingContribution> getServiceCourseContributionForProgramOutomce(
        ProgramOutcome programOutcome, Program program) {
    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    session.beginTransaction();//from   w  ww. j  a  v  a 2  s .c om
    List<CourseOfferingContribution> toReturn = new ArrayList<CourseOfferingContribution>();
    try {
        StringBuffer sql = new StringBuffer();
        sql.append(
                " SELECT lccpo.course_id AS courseOfferingId, cov.calculation_value AS contributionObject, mov.calculation_value AS masteryObject ");
        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");
        sql.append("    AND lppo.program_outcome_id = :programOutcomeId");
        sql.append("    AND (cov.calculation_value + mov.calculation_value)  > 0 ");
        logger.debug("getServiceCourseContributionForProgramOutomce " + sql.toString());
        List<CourseOfferingContribution> fromCourses = (List<CourseOfferingContribution>) session
                .createSQLQuery(sql.toString())
                .setResultTransformer(Transformers.aliasToBean(CourseOfferingContribution.class))
                .setParameter("programId", program.getId())
                .setParameter("programOutcomeId", programOutcome.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<ProgramOutcomeCourseContribution> getProgramOutcomeCoreCourseContributionForProgram(
        Program program) {//from  w ww  .  ja  v  a2 s  . c o m
    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    session.beginTransaction();
    List<ProgramOutcomeCourseContribution> toReturn = new ArrayList<ProgramOutcomeCourseContribution>();
    try {
        StringBuffer sql = new StringBuffer();
        //sql.append(" SELECT co.course_id AS {p.courseId}, lppo.program_outcome_id AS {p.programOutcomeId}, sum(cov.calculation_value) AS {p.contributionSum},count( distinct course_offering_id) AS {p.contributionCount} ");
        //   sql.append(" SELECT co.course_id, lppo.program_outcome_id, sum(cov.calculation_value),count( distinct course_offering_id)");
        sql.append(
                " SELECT co.course_id AS courseId, lppo.program_outcome_id AS programOutcomeId, sum(cov.calculation_value) AS contributionSumObject,  sum(mov.calculation_value) AS masterySumObject  ");

        sql.append("  FROM course_offering co");
        sql.append("      ,link_course_program lcp");
        sql.append("      ,link_program_program_outcome lppo");
        sql.append("      ,link_course_offering_contribution_program_outcome lcopo");
        sql.append("      ,contribution_option_value cov ");
        sql.append("      ,mastery_option_value mov ");
        sql.append(" WHERE co.course_id = lcp.course_id");
        sql.append("   AND co.id = lcopo.course_offering_id");
        sql.append("   AND lcopo.link_program_outcome_id = lppo.id");
        sql.append("   AND lppo.program_id = lcp.program_id");
        sql.append("   AND lcopo.contribution_option_id = cov.id");
        sql.append("   AND lcopo.mastery_option_id = mov.id");
        sql.append("   AND lcp.program_id = :programId");
        sql.append("   GROUP BY  co.course_id, lppo.program_outcome_id");
        logger.debug("getProgramOutcomeCoreCourseContributionForProgram Core contributions:" + sql.toString());
        List<ProgramOutcomeCourseContribution> fromOfferings = (List<ProgramOutcomeCourseContribution>) session
                .createSQLQuery(sql.toString())
                .setResultTransformer(Transformers.aliasToBean(ProgramOutcomeCourseContribution.class))
                .setParameter("programId", program.getId()).list();
        toReturn.addAll(fromOfferings);
        session.getTransaction().commit();
    } catch (Exception e) {
        HibernateUtil.logException(logger, e);
    }
    return toReturn;
}