Example usage for org.hibernate SQLQuery list

List of usage examples for org.hibernate SQLQuery list

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. SQLQuery
  6. 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:com.mobileman.projecth.persistence.patient.impl.PatientKPIDaoImpl.java

License:Apache License

/**
 * {@inheritDoc}//from  ww  w. java2  s .  c om
 * @see com.mobileman.projecth.persistence.patient.PatientKPIDao#findPatientsKpiAverageScoreByDisease(java.lang.Long)
 */
@Override
@SuppressWarnings("unchecked")
public List<Object[]> findPatientsKpiAverageScoreByDisease(Long doctorId) {
    if (log.isDebugEnabled()) {
        log.debug("findPatientsKpiAverageScoreByDisease(Long) - start"); //$NON-NLS-1$
    }

    final String sqlQuery = "select kpit.id as kpitid, kpit.code as kpitcode, kpit.description as kpitdesc, "
            + "disease2_.id as did, disease2_.code as dcode, disease2_.name as dname, " + "avg(pkpi.kpi_value) "
            + "from patient_key_performance_indicator pkpi "
            + "inner join key_performance_indicator_type kpit on kpit.id=pkpi.validation_type_id "
            + "inner join disease disease2_ on disease2_.id=kpit.disease_id " + "where pkpi.patient_id in ("
            + "   select (case when userconnec3_.user_id=? then userconnec3_.owner_id else userconnec3_.user_id end) "
            + "   from user_connection userconnec3_ "
            + "   where userconnec3_.state='A' and (userconnec3_.owner_id=? or userconnec3_.user_id=?)" + ") "
            + "group by kpit.id, kpit.code, kpit.description," + "disease2_.id, disease2_.code, disease2_.name "
            + "order by 1, 2";
    SQLQuery query = getSession().createSQLQuery(sqlQuery);
    query.setParameter(0, doctorId);
    query.setParameter(1, doctorId);
    query.setParameter(2, doctorId);

    List<Object[]> dataList = query.list();

    List<Object[]> result = new ArrayList<Object[]>(dataList.size());
    for (int i = 0; i < dataList.size(); i++) {
        Object[] data = dataList.get(i);
        int index = 0;
        KeyPerformanceIndicatorType kpit = new KeyPerformanceIndicatorType(
                Number.class.cast(data[index++]).longValue(), (String) data[index++], (String) data[index++]);
        Disease disease = new Disease(Number.class.cast(data[index++]).longValue(), (String) data[index++],
                (String) data[index++]);
        kpit.setDisease(disease);
        double kpiAvg = Number.class.cast(data[index++]).doubleValue();
        result.add(new Object[] { data[0], kpit, Double.valueOf(kpiAvg) });
    }

    if (log.isDebugEnabled()) {
        log.debug("findPatientsKpiAverageScoreByDisease(Long) - returns"); //$NON-NLS-1$
    }
    return result;
}

From source file:com.mothsoft.alexis.dao.DocumentDaoImpl.java

License:Apache License

@SuppressWarnings("unchecked")
public Graph getRelatedTerms(final String queryString, final Long userId, final int howMany) {
    final StopWatch stopWatch = new StopWatch();
    stopWatch.start();//from   w  w w  . ja v a2 s . c om

    final FullTextQuery fullTextQuery = this.buildFullTextQuery(queryString, userId, NO_DATE, NO_DATE, false,
            DocumentState.MATCHED_TO_TOPICS, FullTextQuery.ID);

    // find the specified number of terms from the most recent 100 documents
    // that match the query
    final Sort sort = new Sort(new SortField("creationDate", SortField.LONG, true));
    fullTextQuery.setSort(sort);
    fullTextQuery.setFirstResult(0);
    fullTextQuery.setMaxResults(100);

    final List<Long> documentIds = new ArrayList<Long>(100);
    final List<Long> termIds = new ArrayList<Long>(100);

    final List<Object[]> results = fullTextQuery.list();

    for (final Object[] ith : results) {
        final Long id = (Long) ith[0];
        documentIds.add(id);
    }

    final Map<String, Node> nodes = new LinkedHashMap<String, Node>();
    final Node root = new Node(queryString, Boolean.TRUE);
    nodes.put(queryString, root);

    final Map<String, Edge> edges = new HashMap<String, Edge>();

    if (!documentIds.isEmpty()) {
        final Session session = (Session) this.em.getDelegate();
        final org.hibernate.SQLQuery termsQuery = session.createSQLQuery("SELECT term.id "
                + "        FROM document_term dt INNER JOIN term on term.id = dt.term_id "
                + "        WHERE dt.document_id IN (:documentIds) GROUP BY term.id ORDER BY SUM(dt.tf_idf) DESC");
        termsQuery.setParameterList("documentIds", documentIds);
        termsQuery.setMaxResults(100);
        termIds.addAll((List<Long>) termsQuery.list());
    }

    if (!documentIds.isEmpty() && !termIds.isEmpty()) {

        final Session session = (Session) this.em.getDelegate();
        final org.hibernate.SQLQuery associationsQuery = session.createSQLQuery(
                "SELECT CONCAT(a.term_value) term_a_value, CONCAT(b.term_value) term_b_value, SUM(da.association_weight) sum_weight "
                        + "      FROM document_association da "
                        + "      INNER JOIN term a ON da.term_a_id = a.id "
                        + "        AND a.part_of_speech NOT IN (1, 3, 18, 19, 25, 39, 40) "
                        + "        AND length(a.term_value) > 2 "
                        + "      INNER JOIN term b ON da.term_b_id = b.id "
                        + "        AND b.part_of_speech NOT IN (1, 3, 18, 19, 25, 39, 40) "
                        + "        AND length(b.term_value) > 2 "
                        + "      WHERE da.document_id IN (:documentIds) AND (da.term_a_id IN (:termIds) OR da.term_b_id IN (:termIds)) "
                        + "      GROUP BY a.id, b.id ORDER BY sum_weight DESC");
        associationsQuery.setParameterList("documentIds", documentIds);
        associationsQuery.setParameterList("termIds", termIds);
        associationsQuery.setMaxResults(howMany);

        final List<Object[]> relatedTermsResults = associationsQuery.list();

        final Set<String> aNodeKeys = new HashSet<String>();
        final Set<String> bNodeKeys = new HashSet<String>();

        for (final Object[] ith : relatedTermsResults) {
            final String a = (String) ith[0];
            final String b = (String) ith[1];

            if (!nodes.containsKey(a)) {
                final Node node = new Node(a);
                nodes.put(a, node);
            }

            if (!nodes.containsKey(b)) {
                final Node node = new Node(b);
                nodes.put(b, node);
            }

            if (a.equals(b)) {
                continue;
            }

            final String edgeKey = a + "||" + b;
            final String edgeKeyInverse = b + "||" + a;
            if (!edges.containsKey(edgeKey) && !edges.containsKey(edgeKeyInverse)) {
                final Node nodeA = nodes.get(a);
                final Node nodeB = nodes.get(b);

                aNodeKeys.add(a);
                bNodeKeys.add(b);

                final Edge edge = new Edge(nodeA, nodeB);
                edges.put(edgeKey, edge);
            }
        }

        // "orphan" handling, any b that is not also an a needs an edge from
        // root
        final Set<String> orphanKeys = new HashSet<String>();
        orphanKeys.addAll(bNodeKeys);
        orphanKeys.removeAll(aNodeKeys);

        for (final String orphanKey : orphanKeys) {
            final Node orphan = nodes.get(orphanKey);
            final Edge orphanToParent = new Edge(root, orphan);
            edges.put(root.getName() + "||" + orphan.getName(), orphanToParent);
        }
    }

    final List<Node> nodeList = new ArrayList<Node>(nodes.size());
    // keep root as first element
    nodes.remove(root.getName());
    nodeList.add(root);
    nodeList.addAll(nodes.values());

    final Graph graph = new Graph(nodeList, new ArrayList<Edge>(edges.values()));

    stopWatch.stop();
    logger.info("Related terms search took: " + stopWatch.toString());

    return graph;
}

From source file:com.mpos.controller.ControllerSelect.java

public static String getISO(String country) {
    String ISO = "";
    Session session = factory.openSession();
    Transaction tx = null;// ww w. j  a va  2s  .c o m
    try {
        tx = session.beginTransaction();
        String sql = "SELECT * FROM abbreviation WHERE Country = '" + country + "'";
        SQLQuery query = session.createSQLQuery(sql);
        query.addEntity(ModelAbbrevilation.class);
        List location = query.list();
        if (location.isEmpty()) {
            ISO = "NULL";
        } else {
            for (Iterator iterator = location.iterator(); iterator.hasNext();) {
                ModelAbbrevilation place = (ModelAbbrevilation) iterator.next();

                ISO = place.getISO();
            }
        }
        tx.commit();
    } catch (HibernateException e) {
        if (tx != null) {
            tx.rollback();
        }
        e.printStackTrace();
    }
    return ISO;
}

From source file:com.mpos.controller.ControllerSelect.java

public static ArrayList<String> selector(String country) {

    ArrayList<String> data = new ArrayList<>();
    String ISO = getISO(country);
    Session session = factory.openSession();
    Transaction tx = null;//  w  w w . j a va  2s.  c o  m
    try {
        tx = session.beginTransaction();
        String sql = "SELECT * FROM location WHERE ISO = '" + ISO + "'";
        SQLQuery query = session.createSQLQuery(sql);
        query.addEntity(ModelLocation.class);
        List location = query.list();
        if (location.isEmpty()) {
            data.add("NULL");
        } else {
            for (Iterator iterator = location.iterator(); iterator.hasNext();) {
                ModelLocation place = (ModelLocation) iterator.next();

                data.add(place.getName());
                data.add(String.valueOf(place.getLat()));
                data.add(String.valueOf(place.getLng()));
                data.add(String.valueOf(place.getISO()));
                data.add(String.valueOf(place.getProvince()));
            }
        }
        tx.commit();
    } catch (HibernateException e) {
        if (tx != null) {
            tx.rollback(); // = clear transection ?
        }
        e.printStackTrace();
    } finally {
        session.close(); // CLOSE CONNECTION
        //factory.close();
    }
    return data;
}

From source file:com.msi.tough.query.serviceloadbalancer.CreateServiceAction.java

License:Apache License

@Override
public String process0(final Session s, final HttpServletRequest req, final HttpServletResponse resp,
        final Map<String, String[]> map) throws Exception {

    final String hostname = QueryUtil.getString(map, "hostname");
    final String inport = QueryUtil.getString(map, "inport");
    final String outport = QueryUtil.getString(map, "outport");
    final String backend = QueryUtil.getString(map, "backend");
    if (hostname == null || inport == null || outport == null || backend == null) {
        return "All paremeters not sent";
    }/*from   www  . ja  v a2  s. com*/
    SQLQuery q = s.createSQLQuery("select * from ts_inst_lb  where hostnm='" + hostname + "' and inport="
            + inport + " and outport=" + outport + " and  backend='" + backend + "'");
    if (q.list().size() > 0) {
        return "Already Exists";
    }
    SQLQuery i = s.createSQLQuery("insert into ts_inst_lb (hostnm,inport,outport,backend) values ('" + hostname
            + "'," + inport + "," + outport + ",'" + backend + "')");
    i.executeUpdate();
    ServiceLBUtil.reconfigure(s);
    return "CREATED";
}

From source file:com.msi.tough.query.serviceloadbalancer.DeleteServiceAction.java

License:Apache License

@Override
public String process0(final Session s, final HttpServletRequest req, final HttpServletResponse resp,
        final Map<String, String[]> map) throws Exception {

    final String hostname = QueryUtil.getString(map, "hostname");
    final String inport = QueryUtil.getString(map, "inport");
    final String outport = QueryUtil.getString(map, "outport");
    final String backend = QueryUtil.getString(map, "backend");
    if (hostname == null || inport == null || outport == null || backend == null) {
        return "All paremeters not sent";
    }/*from  w  w w .  j av  a 2 s. c o m*/
    SQLQuery q = s.createSQLQuery("select * from ts_inst_lb  where hostnm='" + hostname + "' and inport="
            + inport + " and outport=" + outport + " and  backend='" + backend + "'");
    if (q.list().size() == 0) {
        return "Does not exist";
    }
    SQLQuery i = s.createSQLQuery("delete from ts_inst_lb where hostnm='" + hostname + "' and inport=" + inport
            + " and outport=" + outport + " and backend='" + backend + "'");
    i.executeUpdate();
    ServiceLBUtil.reconfigure(s);
    return "DELETED";
}

From source file:com.msi.tough.query.serviceloadbalancer.ListServiceAction.java

License:Apache License

@SuppressWarnings("unchecked")
@Override/*from  w  w w.j av  a2 s . com*/
public String process0(final Session s, final HttpServletRequest req, final HttpServletResponse resp,
        final Map<String, String[]> map) throws Exception {
    SQLQuery q = s.createSQLQuery(
            "select hostnm, inport, outport, backend from ts_inst_lb order by inport, hostnm, outport, backend");
    List<Object[]> l = q.list();
    final XMLNode xn = new XMLNode("ListServiceResponse");
    final XMLNode servs = QueryUtil.addNode(xn, "Services");
    for (Object[] i : l) {
        final XMLNode serv = QueryUtil.addNode(servs, "Service");
        QueryUtil.addNode(serv, "hostname", i[0].toString());
        QueryUtil.addNode(serv, "inport", i[1].toString());
        QueryUtil.addNode(serv, "outport", i[2].toString());
        QueryUtil.addNode(serv, "backend", i[3].toString());
    }
    return xn.toString();
}

From source file:com.msi.tough.query.serviceloadbalancer.ServiceLBUtil.java

License:Apache License

@SuppressWarnings("unchecked")
public static void reconfigure(final Session s) throws Exception {
    String localhostname = java.net.InetAddress.getLocalHost().getHostName();
    Map<String, Object> config = new HashMap<String, Object>();
    List<Map<String, Object>> inports = new ArrayList<Map<String, Object>>();
    config.put("inports", inports);
    SQLQuery q = s.createSQLQuery(
            "select hostnm, inport, outport, backend from ts_inst_lb order by inport, hostnm, backend");
    List<Object[]> l = q.list();

    String inport = "";
    Map<String, Object> inportm = null;

    String hostnm = "";
    List<Map<String, Object>> hosts = null;
    Map<String, Object> hostm = null;

    String backendnm = "";
    List<Map<String, Object>> backends = null;
    Map<String, Object> backendm = null;

    for (Object[] i : l) {
        if (!inport.equals(i[1].toString())) {
            inportm = new HashMap<String, Object>();
            inports.add(inportm);//www. j a  v a  2 s  .  c o m
            inport = i[1].toString();
            hosts = new ArrayList<Map<String, Object>>();
            inportm.put("hosts", hosts);
            hostnm = "";
        }
        inportm.put("value", i[1].toString());

        if (!hostnm.equals(i[0].toString())) {
            hostm = new HashMap<String, Object>();
            hosts.add(hostm);
            hostnm = i[0].toString();
            backends = new ArrayList<Map<String, Object>>();
            hostm.put("backends", backends);
            backendnm = "";
        }
        hostm.put("value", i[0].toString());

        if (!backendnm.equals(i[3].toString())) {
            backendm = new HashMap<String, Object>();
            backends.add(backendm);
            backendnm = i[3].toString();
        }
        backendm.put("value", i[3].toString());
        backendm.put("outport", i[2].toString());
    }

    ChefUtil.putNodeAttribute(localhostname, DBNAME, DBNAME);
    ChefUtil.createDatabag(DBNAME);
    String json = JsonUtil.toJsonString(config);
    ChefUtil.createDatabagItem(DBNAME, "config", json);
    ChefUtil.putNodeRunlist(localhostname, "role[transcend_service_loadbalancer]");
    //final String keyDir = (String) ConfigurationUtil
    //      .getConfiguration(Arrays.asList(new String[] { "KEYS_DIR" }));
    CFUtil.executeCommand(null, null, "chef-client");
}

From source file:com.mtech.springsecurity.dao.AccountingPeriodDaoImpl.java

public Integer getMaxBatch(SMEEntity entity) {
    Session iSess = getSession();//  w ww . j a va 2  s.com
    SQLQuery query = iSess.createSQLQuery(
            "select max(ap.batchNumber) batchnumber from accounting_periods ap where ap.sme_id = :id");
    query.setParameter("id", entity.getId());
    query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
    List data = query.list();
    logger.warn("accountingperiod::" + data.size());
    Integer number = null;
    for (Object object : data) {
        java.util.Map row = (java.util.Map) object;
        System.out.print("Max number: " + row.get("batchnumber"));
        number = (Integer) row.get("batchnumber");
    }
    return number;
}

From source file:com.mtech.springsecurity.service.AnalyticsService.java

public String getSalesPerMonth(SMEEntity entity) {
    JSONArray jArray = new JSONArray();
    SQLQuery query = getSession()
            .createSQLQuery("select tab.end_date, tab.mon, coalesce(tab.sales, 0) vals from(\n"
                    + "select ap.end_date, to_char(ap.start_date, 'Mon') mon, sum(sales_value) sales\n"
                    + "    from accounting_periods ap\n"
                    + "        left join sales sa on to_char(sa.sale_date, 'Mon') = to_char(ap.start_date, 'Mon') \n"
                    + "    where ap.batchnumber = " + apDao.getMaxBatch(entity) + "\n"
                    + "group by ap.end_date, to_char(ap.start_date, 'Mon') \n" + ") as tab order by end_date");
    query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
    List data = query.list();
    logger.warn("accountingperiod::" + data.size());
    for (Object object : data) {
        JSONObject jsonObject = new JSONObject();
        java.util.Map row = (java.util.Map) object;
        jsonObject.put("label", row.get("mon"));
        jsonObject.put("value", row.get("vals"));
        jArray.add(jsonObject);/*from  w w w  .j a va 2 s  .c  o  m*/
    }
    String salesPerProduct = getSalesPerProduct(entity);
    JSONObject wrapperObject = new JSONObject();
    wrapperObject.put("data", jArray);
    wrapperObject.put("product", salesPerProduct);
    wrapperObject.put("success", true);
    return wrapperObject.toString();
}