Example usage for org.hibernate SQLQuery list

Introduction

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

Prototype

List<R> list();

Source Link

Document

Return the query results as a List.

Usage

From source file:at.ac.tuwien.ifs.tita.dao.test.util.NativeSqlExecutorDao.java

License:Apache License

/** {@inheritDoc} */
@Override//  ww  w  .  j a  v a 2  s  . com
@SuppressWarnings("unchecked")
@Transactional(propagation = Propagation.REQUIRED)
public List<Object> getQueryList(String str) {

    Session session = getSession();
    SQLQuery q;
    q = session.createSQLQuery(str.toUpperCase());
    return q.list();
}

---

From source file:at.ac.tuwien.ifs.tita.dao.time.EffortDao.java

License:Apache License

/** {@inheritDoc} */
@SuppressWarnings("unchecked")
@Override/*  w w w .  j a  v a2  s . c o  m*/
public List<UserProjectEffort> findEffortsForTiTAProjectAndTimeConsumerId(List<String> projectIds,
        List<String> tIds, String grouping) {
    String pIds = StringUtil.generateIdStringFromStringList(projectIds);
    String tcIds = StringUtil.generateIdStringFromStringList(tIds);

    String queryString = "select UUID() as ID, "
            + " sum(duration) as DURATION, username as USERNAME, project as PROJECT";

    if (grouping.equals("month")) {
        queryString += ", year as YEAR, month as MONTH, null as DAY";
    } else if (grouping.equals("day")) {
        queryString += ", year as YEAR, month as MONTH, day as DAY";
    } else if (grouping.equals("overall")) {
        queryString += ", null as YEAR, null as MONTH, null as DAY";
    }

    queryString += " from (select sum(e1.duration) as duration, tu.username as username,"
            + " tp.name as project ";

    if (grouping.equals("month")) {
        queryString += ", year(e1.date) as YEAR, " + " month(e1.date) as MONTH, null as DAY";
    } else if (grouping.equals("day")) {
        queryString += ", year(e1.date) as YEAR, " + " month(e1.date) as MONTH, " + " day(e1.date) as DAY";
    } else if (grouping.equals("overall")) {
        queryString += ", null as YEAR, null as MONTH, null as DAY";
    }

    queryString += " from effort e1 " + "join tita_task tt on e1.tita_task_id = tt.id "
            + "join tita_project tp on tt.tita_project_id = tp.id " + "join tita_user tu on tu.id = e1.user_id "
            + "where tp.name in (" + pIds + ") and tu.username in (" + tcIds + ") ";

    if (grouping.equals("month")) {
        queryString += " group by tp.name, tu.username, year(e1.date), " + " month(e1.date) ";
    } else if (grouping.equals("day")) {
        queryString += " group by tp.name, tu.username, year(e1.date), " + " month(e1.date), day(e1.date) ";
    } else if (grouping.equals("overall")) {
        queryString += " group by tp.name, tu.username ";
    }

    queryString += " union all" + " select sum(e2.duration) as duration, tu1.username as username, "
            + " tp2.name as project ";

    if (grouping.equals("month")) {
        queryString += ", year(e2.date) as YEAR, " + " month(e2.date) as MONTH, null as DAY";
    } else if (grouping.equals("day")) {
        queryString += ", year(e2.date) as YEAR, " + " month(e2.date) as MONTH, day(e2.date) as DAY";
    } else if (grouping.equals("overall")) {
        queryString += ", null as YEAR, null as MONTH, null as DAY";
    }

    queryString += " from effort e2 " + "join issue_tracker_task it on e2.issuet_task_id = it.id "
            + "join issue_tracker_project itp on it.issue_tracker_project_id ="
            + "itp.id join tita_project tp2 on tp2.id = itp.tita_project_id "
            + "join tita_user tu1 on tu1.id = e2.user_id " + "where tp2.name in (" + pIds
            + ") and tu1.username in (" + tcIds + ")";

    if (grouping.equals("month")) {
        queryString += " group by tp2.name, tu1.username, year(e2.date), " + " month(e2.date)";
    } else if (grouping.equals("day")) {
        queryString += " group by tp2.name, tu1.username, year(e2.date), " + " month(e2.date), day(e2.date)";
    } else if (grouping.equals("overall")) {
        queryString += " group by tp2.name, tu1.username";
    }

    queryString += ") as U group by project, username, year, month, day "
            + " order by project, year, month, day, duration, username";

    org.hibernate.SQLQuery q = getSession().createSQLQuery(queryString.toUpperCase());
    q.addEntity(UserProjectEffort.class);
    q.setFetchSize(C_FETCHSIZE);

    List<UserProjectEffort> efforts = null;

    try {
        efforts = q.list();
    } catch (NoResultException e) {
        // nothing to do
    }
    return efforts;
}

---

From source file:at.ac.tuwien.ifs.tita.dao.time.EffortDao.java

License:Apache License

/** {@inheritDoc} */
@SuppressWarnings("unchecked")
@Override//from   w w w .  j a va2  s. c  o m
public List<UserProjectEffort> findEffortsForTiTAProjectId(List<String> projectIds, String grouping) {
    String pIds = StringUtil.generateIdStringFromStringList(projectIds);

    String queryString = "select UUID() as ID,"
            + " sum(duration) as DURATION, project as PROJECT, null as USERNAME";

    if (grouping.equals("month")) {
        queryString += ", year as YEAR, month as MONTH, null as DAY";
    } else if (grouping.equals("day")) {
        queryString += ", year as YEAR, month as MONTH, day as DAY";
    } else if (grouping.equals("overall")) {
        queryString += ", null as YEAR, null as MONTH, null as DAY";
    }

    queryString += " from (select sum(e1.duration) as duration, tp.name as project ";

    if (grouping.equals("month")) {
        queryString += ", year(e1.date) as YEAR, " + " month(e1.date) as MONTH, null as DAY ";
    } else if (grouping.equals("day")) {
        queryString += ", year(e1.date) as YEAR, " + " month(e1.date) as MONTH, day(e1.date) as DAY";
    } else if (grouping.equals("overall")) {
        queryString += ", null as YEAR, null as MONTH, null as DAY";
    }

    queryString += " from effort e1 join tita_task tt on e1.tita_task_id = tt.id "
            + "join tita_project tp on tt.tita_project_id = tp.id " + "where tp.name in (" + pIds + ") ";

    if (grouping.equals("month")) {
        queryString += " group by tp.name, year(e1.date), " + " month(e1.date) ";
    } else if (grouping.equals("day")) {
        queryString += " group by tp.name, year(e1.date), " + " month(e1.date), day(e1.date) ";
    } else if (grouping.equals("overall")) {
        queryString += " group by tp.name ";
    }

    queryString += " union all" + " select sum(e2.duration) as duration, tp2.name as project ";

    if (grouping.equals("month")) {
        queryString += ", year(e2.date) as year, " + " month(e2.date) as month, null as day ";
    } else if (grouping.equals("day")) {
        queryString += ", year(e2.date) as year, " + "month(e2.date) as month, day(e2.date) as day";
    } else if (grouping.equals("overall")) {
        queryString += ", null as year, null as month, null as day";
    }

    queryString += " from effort e2 " + "join issue_tracker_task it on e2.issuet_task_id = it.id "
            + "join issue_tracker_project itp on it.issue_tracker_project_id ="
            + "itp.id join tita_project tp2 on tp2.id = itp.tita_project_id " + "where tp2.name in (" + pIds
            + ") ";

    if (grouping.equals("month")) {
        queryString += " group by tp2.name, year(e2.date), " + " month(e2.date) ";
    } else if (grouping.equals("day")) {
        queryString += " group by tp2.name, year(e2.date), " + " month(e2.date), day(e2.date) ";
    } else if (grouping.equals("overall")) {
        queryString += " group by tp2.name ";
    }

    queryString += ") as U group by project, year, month, day, username "
            + " order by project, year, month, day, duration";

    org.hibernate.SQLQuery q = getSession().createSQLQuery(queryString.toUpperCase());
    q.addEntity(UserProjectEffort.class);
    q.setFetchSize(C_FETCHSIZE);
    List<UserProjectEffort> efforts = new ArrayList<UserProjectEffort>();

    try {
        efforts = q.list();
    } catch (NoResultException e) {
        // nothing to do
    }
    return efforts;
}

---

From source file:at.ac.tuwien.ifs.tita.dao.time.EffortDao.java

License:Apache License

/** {@inheritDoc} */
@Override// w w w  .j  av a2 s. c om
public List<Effort> findEffortsForTiTAProjectAndTiTAUserOrdered(Long projectId, Long userId) {
    List<Effort> returnValue = new ArrayList<Effort>();

    String queryString = "select e1.ID, e1.DESCRIPTION, e1.TITA_TASK_ID, e1.ISSUET_TASK_ID, "
            + "e1.DATE, e1.START_TIME, e1.END_TIME, e1.DURATION, e1.DELETED, "
            + "e1.USER_ID, e1.COST_CENTER from Effort e1 " + "join tita_task tt on e1.tita_task_id = tt.id "
            + "join tita_project tp on tt.tita_project_id = tp.id " + "join tita_user tu on tu.id = e1.user_id "
            + "where tu.id = " + userId + " and tp.id = " + projectId + " and e1.deleted != true " + "UNION "
            + "select e2.ID, e2.DESCRIPTION, e2.TITA_TASK_ID, e2.ISSUET_TASK_ID, e2.DATE, "
            + "e2.START_TIME, e2.END_TIME, e2.DURATION, e2.DELETED, "
            + "e2.USER_ID, e2.COST_CENTER from Effort e2 "
            + "join issue_tracker_task itt on e2.issuet_task_id = itt.id "
            + "join issue_tracker_project itp on itt.issue_tracker_project_id = itp.id "
            + "join tita_project tp2 on tp2.id = itp.tita_project_id "
            + "join tita_user tu2 on tu2.id = e2.user_id " + "where tu2.id = " + userId + " and tp2.id = "
            + projectId + " and e2.deleted != true " + "order by date desc, end_time desc";

    org.hibernate.SQLQuery q = getSession().createSQLQuery(queryString.toUpperCase());
    q.addEntity(Effort.class);
    q.setFetchSize(C_FETCHSIZE);

    try {
        returnValue = q.list();
    } catch (NoResultException e) {
        // no results
    }
    return returnValue;
}

---

From source file:at.ac.tuwien.ifs.tita.dao.user.UserDAO.java

License:Apache License

/** {@inheritDoc} */
@SuppressWarnings("unchecked")
@Override//  w w  w. j  a  v  a  2s.  com
public List<TiTAUser> findUsersForProjectNames(List<String> projects) {
    String names = StringUtil.generateIdStringFromStringList(projects);

    String queryString = "select u.* from tita_user u join user_project up on "
            + "u.id = up.user_id join tita_project tp on up.tita_project_id = tp.id " + " where tp.name in ("
            + names + ")";

    org.hibernate.SQLQuery query = getSession().createSQLQuery(queryString.toUpperCase());

    List<TiTAUser> users = new ArrayList<TiTAUser>();

    query.addEntity(TiTAUser.class);

    try {
        users = query.list();
    } catch (NoResultException e) {
        // nothing to do
    }
    return users;
}

---

From source file:au.com.nicta.ct.solution.lineage.CtLineageModel.java

License:Open Source License

static ArrayList<CtAbstractPair<CtTracks, CtTracks>> findIntersectingTracks(CtSolutions s) {

    // working sql:
    //with tracks( pk_track, fk_detection ) AS
    //(//w ww  .j a  va 2s. co  m
    // select t1.pk_track, td1.fk_detection from ct_tracks t1
    // inner join ct_tracks_detections td1 on td1.fk_track = t1.pk_track
    // where t1.fk_solution = 11
    //)
    //select distinct t1.pk_track, t2.pk_track
    //from tracks t1, tracks t2
    //where ( t2.pk_track <> t1.pk_track )
    //and ( t2.fk_detection = t1.fk_detection )
    //order by t1.pk_track, t2.pk_track
    int pkSolution = s.getPkSolution();

    //        String hql = " with tracks1( pkTrack, ctDetection ) as "
    //                   + "( "
    //                   + " select t1.pkTrack, td1.ctDetection from ctTracks t1 "
    //                   + " inner join t1.ctTracksDetectionses td1 "
    //                   + " inner join t1.ctSolutions s "
    //                   + " where s.pkSolution = " + pkSolution + " "
    //                   + ") "
    //                   + "select x1.pkTrack, x2.pkTrack "
    //                   + " from tracks1 x1, tracks1 x2 "// inner join tracks1 x2 on x1.ctDetection = x2.ctDetection "
    //                   + "where ( x2.pkTrack <> x1.pkTrack ) "
    ////                   + "and ( t2.fk_detection = t1.fk_detection ) "
    //                   + "order by x1.pkTrack, x2.pkTrack ";

    String sql = " select t1.pk_track as dave, t2.pk_track as matt" + " from ct_tracks t1 "
            + " inner join ct_tracks_detections td1 on td1.fk_track = t1.pk_track " + " inner join ( "
            + " ct_tracks t2 " + " inner join ct_tracks_detections td2 on td2.fk_track = t2.pk_track "
            + " ) on td1.fk_detection = td2.fk_detection " + " where t1.fk_solution = " + pkSolution + " "
            + " and t2.fk_solution = " + pkSolution + " " + " and t2.pk_track <> t1.pk_track "
            + "order by t1.pk_track, t2.pk_track ";

    /*        String sql = " with tracks1( pk_track, fk_detection ) as "
           + "( "
           + " select t1.pk_track, td1.fk_detection from ct_tracks t1 "
           + " inner join ct_tracks_detections td1 on td1.fk_track = t1.pk_track "
           + " where t1.fk_solution = "+pkSolution+" "
           + "),"
           + " tracks2( pk_track, fk_detection ) as "
           + "( "
           + " select t1.pk_track, td1.fk_detection from ct_tracks t1 "
           + " inner join ct_tracks_detections td1 on td1.fk_track = t1.pk_track "
           + " where t1.fk_solution = "+pkSolution+" "
           + ") "
           + "select t1.pk_track, t2.pk_track "
    //                   + "from tracks t1, tracks t2 "
           + " from tracks1 t1 inner join tracks2 t2 on t1.fk_detection = t2.fk_detection "
           + "where ( t2.pk_track <> t1.pk_track ) "
    //                   + "and ( t2.fk_detection = t1.fk_detection ) "
           + "order by t1.pk_track, t2.pk_track ";*/
    //        String hql = "select t1.pkTrack,t2.pkTrack from CtTracks t1 "
    //                + " inner join t1.ctTracksDetectionses td1 "
    //                + " inner join t1.ctSolutions s "
    //                + " where s.pkSolution = '" + s.getPkSolution() + "'"
    //                + " and exists( "
    //                + " select t2.pkTrack from CtTracks t2 "
    //                + " inner join t2.ctTracksDetectionses td2 "
    //                + " inner join t2.ctSolutions s "
    //                + " where s.pkSolution = '" + s.getPkSolution() + "'"
    //                + " and t2.pkTrack <> t1.pkTrack "
    //                + " and td2.ctDetections = td1.ctDetections "
    //                + " ) ";

    Session session = CtSession.Current();
    session.beginTransaction();
    //        Query q = session.createQuery( hql );
    SQLQuery q = session.createSQLQuery(sql);
    List results = q.list();
    session.getTransaction().commit();

    ArrayList<CtAbstractPair<CtTracks, CtTracks>> al = new ArrayList<CtAbstractPair<CtTracks, CtTracks>>();

    Iterator i = results.iterator();

    while (i.hasNext()) {
        Object[] os = (Object[]) i.next();
        Integer n1 = (Integer) os[0];
        Integer n2 = (Integer) os[1];
        //System.out.println( "n1="+n1+" n2="+n2);
        CtTracks t1 = (CtTracks) CtSession.getObject(CtTracks.class, n1);
        CtTracks t2 = (CtTracks) CtSession.getObject(CtTracks.class, n2);
        CtAbstractPair<CtTracks, CtTracks> ap = new CtAbstractPair<CtTracks, CtTracks>(t1, t2);
        al.add(ap);
    }

    return al;
}

---

From source file:au.com.optus.mcas.sdp.bizservice.ott.ordertracking.batchjob.dao.impl.AbstractDaoImpl.java

License:Open Source License

/**
 * {@inheritDoc}/* w  ww  . jav a2  s .  c o  m*/
 */
public Object findByNativeQuery(final String sqlQuery) {
    SQLQuery q = getHibernateSession().createSQLQuery(sqlQuery);
    return q.list();
}

---

From source file:boeken.BoekenDao.java

List getBoekenVanSchrijver(String schrijver) {
    SQLQuery query = sessie.createSQLQuery("");
    query.addEntity("BOEKEN", Boek.class);//entities toevoegen juist??
    query.setString(0, schrijver);/*  w w w. j a v  a  2  s .co m*/
    return query.list();
}

---

From source file:br.com.bluesoft.pronto.dao.SprintDao.java

License:Open Source License

@SuppressWarnings("unchecked")
private void preencheTotaisDeEsforcoEValorDeNegocioDosSprints(final Collection<Sprint> sprints) {
    final String sql = "select sprint, sum(t.valor_de_negocio) as valor_de_negocio_total, sum(t.esforco) as esforco_total from ticket t where t.sprint is not null and t.pai is null group by sprint";
    final SQLQuery query = getSession().createSQLQuery(sql);
    query.addScalar("sprint", Hibernate.INTEGER);
    query.addScalar("valor_de_negocio_total", Hibernate.INTEGER);
    query.addScalar("esforco_total", Hibernate.DOUBLE);
    final List<Object[]> list = query.list();
    for (final Object[] o : list) {
        final Integer sprintKey = (Integer) o[0];
        final int valorDeNegocioTotal = (Integer) o[1];
        final double esforcoTotal = (Double) o[2];
        for (final Sprint s : sprints) {
            if (s.getSprintKey() == sprintKey) {
                s.setEsforcoTotal(esforcoTotal);
                s.setValorDeNegocioTotal(valorDeNegocioTotal);
            }/*from w w  w  .  ja  v a2s. com*/

        }
    }
}

---

From source file:br.com.jn.dao.CandidatoDAO.java

public List<Candidato> listar() {
    Authentication a = SecurityContextHolder.getContext().getAuthentication();
    //Authentication authentication = context.getAuthentication();
    Object principal = a.getPrincipal();
    String username = "";
    username = (((User) a.getPrincipal()).getUsername());
    //username = ((UserDetails) principal).getUsername();
    System.out.println("login selecionado:" + username);

    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    session.beginTransaction();//from  w  ww.  j ava 2s  .  c om

    String sql = "select * from users where username = :username";
    SQLQuery query = session.createSQLQuery(sql);
    query.addEntity(Users.class);
    query.setParameter("username", username);
    //Users usuario = (Users) query.uniqueResult();
    List logado = query.list();
    //List<Users> results = query.getResultList();

    String UserId = logado.get(0).toString();
    String usuario = "4";
    //System.out.println("Id do usuario selecionado:" + usuario.getUserId());
    //System.out.println("Id do usuario selecionado:" + usuario.getNome());

    //Integer UserId = usuario.getUserId();
    System.out.println("Id do usuario selecionado:" + UserId);

    Criteria crit = session.createCriteria(Candidato.class);
    crit.add(Restrictions.ge("notaEliminatoria", (5.0)));
    crit.add(Restrictions.eq("orientador1", username));
    crit.addOrder(Order.asc("nome"));

    List<Candidato> lista = crit.list();
    session.close();
    return lista;
}

---

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