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.myewb.frame.servlet.GraphServlet.java

License:Open Source License

private JFreeChart getLastLogin(Session s) throws CloneNotSupportedException {

    Integer numCurrentLogins = ((Long) s
            .createQuery("select count(*) from UserModel "
                    + "where currentLogin is not null and currentLogin >= :date")
            .setDate("date", getStartDate()).uniqueResult()).intValue();

    List currentStats = s
            .createSQLQuery("SELECT DATE(currentLogin) as date, count( * ) as lastLogins "
                    + "FROM users where currentLogin is not null  and currentLogin >= :date "
                    + "GROUP BY DATE( currentLogin )")
            .addScalar("date", Hibernate.DATE).addScalar("lastLogins", Hibernate.INTEGER)
            .setDate("date", getStartDate()).list();

    TimeSeriesCollection theData = new TimeSeriesCollection();
    TimeSeriesCollection theData2 = new TimeSeriesCollection();

    TimeSeries current = new TimeSeries("Num Latest Sign-ins", Day.class);
    theData.addSeries(current);/*w  w  w  . j av a  2 s  . c  o m*/
    TimeSeries current2 = new TimeSeries("Signed-in Users Since", Day.class);
    theData2.addSeries(current2);

    for (Object ds : currentStats) {
        Date date = (Date) ((Object[]) ds)[0];
        Day day = new Day(date);
        Integer integer = (Integer) ((Object[]) ds)[1];
        current.add(day, integer);
        numCurrentLogins -= integer.intValue();
        current2.add(day, numCurrentLogins);

    }

    JFreeChart chart = ChartFactory.createTimeSeriesChart("Sign-in Recency", "Day", "Sign-ins", theData, true,
            true, true);

    XYPlot plot = (XYPlot) chart.getPlot();

    NumberAxis axis2 = new NumberAxis("Users");
    plot.setRangeAxis(1, axis2);
    plot.setRangeAxisLocation(1, AxisLocation.BOTTOM_OR_RIGHT);
    plot.setDataset(1, theData2);
    plot.mapDatasetToRangeAxis(1, 1);

    XYLineAndShapeRenderer renderer = (XYLineAndShapeRenderer) plot.getRenderer(0);
    renderer.setSeriesStroke(0, new BasicStroke(2.0f));
    renderer.setSeriesStroke(1, new BasicStroke(2.0f));
    renderer = (XYLineAndShapeRenderer) renderer.clone();
    renderer.setSeriesStroke(0, new BasicStroke(2.0f));
    renderer.setSeriesStroke(1, new BasicStroke(2.0f));
    plot.setRenderer(1, renderer);
    return chart;
}

---

From source file:ca.myewb.frame.servlet.GraphServlet.java

License:Open Source License

private JFreeChart getChapterRankPie(Session s, String url) {
    String[] path = url.split("/");
    String lastPiece = path[path.length - 1];
    Integer chapterId = new Integer(lastPiece.substring(0, lastPiece.length() - 4));
    GroupChapterModel chapter = (GroupChapterModel) s.get(GroupChapterModel.class, chapterId);

    JFreeChart chart;//from  www. j a  v  a 2  s  . c o m
    DefaultPieDataset ds = new DefaultPieDataset();

    String query = "select count(*) as num from roles r1, roles r2 "
            + "where r1.userid=r2.userid and r1.groupid=? and r1.level='m' and r2.level='m' "
            + "and r1.end is null and r2.end is null and r2.groupid=?";
    int numRegular = ((Integer) s.createSQLQuery(query).addScalar("num", Hibernate.INTEGER)
            .setInteger(0, chapter.getId()).setInteger(1, Helpers.getGroup("Regular").getId()).list().get(0))
                    .intValue();

    int numAssociate = ((Integer) s.createSQLQuery(query).addScalar("num", Hibernate.INTEGER)
            .setInteger(0, chapter.getId()).setInteger(1, Helpers.getGroup("Associate").getId()).list().get(0))
                    .intValue();
    int numUsers = chapter.getNumMembers();

    ds.setValue("Regular Members", numRegular);
    ds.setValue("Associate Members", numAssociate);
    ds.setValue("Mailing list Members", numUsers - numAssociate - numRegular);

    chart = ChartFactory.createPieChart("Status breakdown for " + numUsers + " chapter members", ds, false,
            false, false);

    PiePlot plot = ((PiePlot) chart.getPlot());
    StandardPieItemLabelGenerator n = new StandardPieItemLabelGenerator("{0} = {1} ({2})",
            new DecimalFormat("0"), new DecimalFormat("0.0%"));
    plot.setLabelGenerator(n);
    return chart;
}

---

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

License:Open Source License

@SuppressWarnings("unchecked")
public List<Characteristic> getCharacteristicsForCourseOfferingOutcome(Program p, CourseOffering courseOffering,
        CourseOutcome o) {//from   w  w w.j  a va  2s  .  c  o  m
    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    session.beginTransaction();
    List<Characteristic> list = null;
    try {

        StringBuilder sql = new StringBuilder();
        sql.append("   SELECT {c.*} ");
        sql.append("     FROM characteristic c, ");
        sql.append("          link_courseOffering_outcome_characteristic lcoc, ");
        sql.append("          link_courseOffering_outcome lco, ");
        sql.append("          link_program_characteristic_type lpct ");
        sql.append("    WHERE lcoc.characteristic_id = c.id ");
        sql.append("      AND lco.id = lcoc.link_courseOffering_outcome_id ");
        sql.append("      AND lco.courseOffering_id = :courseOfferingId ");
        sql.append("      AND lco.outcome_id = :outcomeId ");
        sql.append("      AND lpct.program_id = :programId ");
        sql.append("      AND lpct.characteristic_type_id = c.characteristic_type_id ");
        sql.append(" ORDER BY lpct.display_index ");

        logger.error(
                "p=" + p.getId() + " c=" + courseOffering.getId() + " o=" + o.getId() + "\n" + sql.toString());

        list = (List<Characteristic>) session.createSQLQuery(sql.toString())
                .addEntity("c", Characteristic.class).setParameter("courseOfferingId", courseOffering.getId())
                .setParameter("outcomeId", o.getId()).setParameter("programId", p.getId()).list();

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

---

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

License:Open Source License

@SuppressWarnings("unchecked")
public List<CourseOffering> getCourseOfferingsWithCharacteristicForProgram(Program p, Characteristic c) {
    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    session.beginTransaction();/*  w  w w. j  ava 2 s  .c  om*/
    List<CourseOffering> toReturn = null;
    try {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT {co.*} ");
        sql.append("  FROM courseOffering co, ");
        sql.append("       link_courseOffering_outcome lco,");
        sql.append("       link_courseOffering_outcome_characteristic lcoc, ");
        sql.append("       characteristic c, ");
        sql.append("       link_courseOffering_program lpc");
        sql.append(" WHERE c.id=lcoc.characteristic_id  ");
        sql.append("   AND lcoc.link_courseOffering_outcome_id = lco.id ");
        sql.append("   AND lco.courseOffering_id=co.id ");
        sql.append("   AND c.id=:charId ");
        sql.append("   AND lpc.courseOffering_id = c.id");
        sql.append("   AND lpc.program_id = :programId ");
        sql.append("ORDER BY co.subject, co.courseOffering_number ");

        logger.error(sql.toString());

        toReturn = (List<CourseOffering>) session.createSQLQuery(sql.toString())
                .addEntity("co", CourseOffering.class).setParameter("charId", 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.CourseManager.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<Organization> getOrganizationForCourse(Course course, Session session) {
    List<Organization> toReturn = null;
    StringBuilder sql = new StringBuilder();
    sql.append(" select {d.*} ");
    sql.append("   from organization d ");
    sql.append("       ,link_course_organization lcd");
    sql.append(" where lcd.organization_id = d.id");
    sql.append("   and lcd.course_id = :courseId");
    toReturn = (List<Organization>) session.createSQLQuery(sql.toString()).addEntity("d", Organization.class)
            .setParameter("courseId", course.getId()).list();
    return toReturn;
}

---

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

License:Open Source License

public int getOfferingCountForTerm(String term) {
    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    session.beginTransaction();/* w w  w  .j  ava 2 s. com*/
    int toReturn = 0;
    try {

        toReturn = ((BigInteger) session.createSQLQuery("select count(*) from course_offering where term=:term")
                .setParameter("term", term).uniqueResult()).intValue();
        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

public List<Characteristic> getCharacteristicsForCourseOfferingOutcome(CourseOffering courseOffering,
        CourseOutcome o, Organization organization, Session session) {
    StringBuilder sql = new StringBuilder();
    sql.append("   SELECT {c.*} ");
    sql.append("     FROM characteristic c, ");
    sql.append("          link_course_offering_outcome_characteristic lcoc, ");
    sql.append("          link_course_offering_outcome lco, ");
    sql.append("          link_organization_characteristic_type ldct ");
    sql.append("    WHERE lcoc.characteristic_id = c.id ");
    sql.append("      AND lco.id = lcoc.link_course_offering_outcome_id ");
    sql.append("      AND lco.course_offering_id = :courseOfferingId ");
    sql.append("      AND lco.course_outcome_id = :outcomeId ");
    sql.append("      AND ldct.characteristic_type_id = c.characteristic_type_id ");
    sql.append("      AND ldct.organization_id = :organizationId ");
    sql.append(" ORDER BY ldct.display_index ");

    logger.debug(" c=" + courseOffering.getId() + " o=" + o.getId() + "\n" + sql.toString());

    @SuppressWarnings("unchecked")
    List<Characteristic> list = (List<Characteristic>) session.createSQLQuery(sql.toString())
            .addEntity("c", Characteristic.class).setParameter("courseOfferingId", courseOffering.getId())
            .setParameter("organizationId", organization.getId()).setParameter("outcomeId", o.getId()).list();
    return list;/*from  ww w.j  a  va2 s  .c o  m*/
}

---

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

License:Open Source License

public List<Characteristic> getCharacteristicsForProgramOutcome(Program program, ProgramOutcome o,
        Organization organization, Session session) {
    StringBuilder sql = new StringBuilder();
    sql.append("   SELECT {c.*} ");
    sql.append("     FROM characteristic c, ");
    sql.append("          link_program_program_outcome_characteristic lcoc, ");
    sql.append("          link_program_program_outcome lco, ");
    sql.append("          link_organization_characteristic_type ldct ");
    sql.append("    WHERE lcoc.characteristic_id = c.id ");
    sql.append("      AND lco.id = lcoc.link_program_program_outcome_id ");
    sql.append("      AND lco.program_id = :programId ");
    sql.append("      AND lco.program_outcome_id = :outcomeId ");
    sql.append("      AND ldct.characteristic_type_id = c.characteristic_type_id ");
    sql.append("      AND ldct.organization_id = :organizationId ");
    sql.append(" ORDER BY ldct.display_index ");

    logger.debug(" c=" + program.getId() + " o=" + o.getId() + "\n" + sql.toString());

    @SuppressWarnings("unchecked")
    List<Characteristic> list = (List<Characteristic>) session.createSQLQuery(sql.toString())
            .addEntity("c", Characteristic.class).setParameter("programId", program.getId())
            .setParameter("organizationId", organization.getId()).setParameter("outcomeId", o.getId()).list();
    return list;/*from   w  ww  .ja  v  a  2s.  c  o m*/
}

---

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

License:Open Source License

@SuppressWarnings("unchecked")
public List<CourseOutcome> getOutcomesForProgramContaining(Program program, String text) {
    text = "%" + text + "%";
    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    session.beginTransaction();//from  w ww  .  jav a 2  s. c  o  m
    List<CourseOutcome> toReturn = null;
    try {
        StringBuilder sql = new StringBuilder();

        sql.append("       SELECT {o.*} ");
        sql.append("         FROM outcome o ");
        sql.append("        WHERE (o.name like :text ");
        sql.append("                OR  o.description like :text) ");
        sql.append("          AND (o.program_specific ='N' ");
        sql.append("                OR (o.program_specific = 'Y' AND o.program_id=:programId) )");
        sql.append("     ORDER BY o.name ");

        toReturn = (List<CourseOutcome>) session.createSQLQuery(sql.toString())
                .addEntity("o", CourseOutcome.class).setParameter("text", text)
                .setParameter("programId", program.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<ProgramOutcome> getProgramOutcomesForProgram(Program program) {
    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    session.beginTransaction();//from  w w  w  .  ja  v  a 2s.c o  m
    List<ProgramOutcome> toReturn = null;
    try {
        StringBuilder sql = new StringBuilder();

        sql.append("       SELECT {o.*} ");
        sql.append("         FROM program_outcome o ");
        sql.append("             , program_outcome_group og");
        sql.append("        WHERE o.program_outcome_group_id = og.id");
        sql.append("        AND (og.program_specific ='N' ");
        sql.append("                OR (og.program_specific = 'Y' AND og.program_id=:programId) )");
        sql.append("     ORDER BY og.name, o.name ");

        toReturn = (List<ProgramOutcome>) session.createSQLQuery(sql.toString())
                .addEntity("o", ProgramOutcome.class).setParameter("programId", program.getId()).list();

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

---

• «
• 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
• »