Example usage for org.hibernate SQLQuery setMaxResults

List of usage examples for org.hibernate SQLQuery setMaxResults

Introduction

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

Prototype

@Override
    Query<R> setMaxResults(int maxResult);

Source Link

Usage

From source file:org.jcvi.ometa.hibernate.dao.EventDAO.java

License:Open Source License

public List<Event> getAllEvents(Long flexId, String identifier, String sSearch, String sortCol, String sortDir,
        int start, int count, String fromd, String tod, Session session) throws DAOException {
    List<Event> eventList = new ArrayList<Event>();
    try {//from   ww  w  .j  a v  a 2s.co  m
        List results = null;

        String sql = " select E.*, S.sample_name sample, CONCAT(A.actor_last_name,',',A.actor_first_name) user, LV.lkuvlu_name eventType  "
                + " from event E " + "   left join sample S on E.event_sampl_id=S.sample_id "
                + "   left join lookup_value LV on E.event_type_lkuvl_id=LV.lkuvlu_id "
                + "   left join actor A on E.event_actor_created_by=A.actor_id where ";

        if ("Sample".equals(identifier))
            sql += "E.event_sampl_id=";
        else if ("Eventlist".equals(identifier))
            sql += "E.event_projet_id=";
        else if ("Event".equals(identifier))
            sql += "E.event_id=";
        else
            sql += "E.event_sampl_id is null and E.event_projet_id=";
        sql += flexId;

        if (sSearch != null && !sSearch.isEmpty()) {
            sSearch = "%" + sSearch + "%";
            sql += " and (LOWER(LV.lkuvlu_name) like '" + sSearch + "' or LOWER(S.sample_name) like '" + sSearch
                    + "' " + " or ((LOWER(A.actor_first_name) like '" + sSearch
                    + "' or LOWER(A.actor_last_name) like '" + sSearch + "')))";
        }

        if (fromd != null && !fromd.isEmpty())
            sql += " and date(E.event_create_date)>='" + fromd + "'";
        if (tod != null && !tod.isEmpty())
            sql += " and date(E.event_create_date)<='" + tod + "'";

        if (sortCol != null && !sortCol.isEmpty() && sortDir != null && !sortDir.isEmpty()) {
            sql += " order by";
            if (sortCol.equals("event"))
                sql += " eventType ";
            else if (sortCol.equals("user"))
                sql += " user ";
            else if (sortCol.equals("sample"))
                sql += " sample ";
            else if (sortCol.equals("date"))
                sql += " event_create_date ";
            sql += sortDir;
        }

        SQLQuery query = session.createSQLQuery(sql);
        query.addEntity("E", Event.class);
        if (start >= 0 && count >= 0) {
            query.setFirstResult(start);
            query.setMaxResults(count);
        }
        results = query.list();

        if (results != null) {
            for (Object result : results) {
                eventList.add((Event) result);
            }
        }
    } catch (Exception ex) {
        throw new DAOException(ex);
    }

    return eventList;
}

From source file:org.jessma.dao.hbn.HibernateFacade.java

License:Apache License

/**
 * /*from  www  .j  a v a 2  s  .  c  o  m*/
 * ?SQL
 * 
 * @param firstResult   : 
 * @param maxResults   : 
 * @param sql         : SQL ?
 * @param params      : ?
 * @return            : 
 * 
 */
protected <T> List<T> sqlQuery(int firstResult, int maxResults, String sql, Object... params) {
    SQLQuery sqlQuery = getSession().createSQLQuery(sql);

    for (int i = 0; i < params.length; i++)
        sqlQuery.setParameter(i, params[i]);

    if (firstResult > 0)
        sqlQuery.setFirstResult(firstResult);
    if (maxResults > 0)
        sqlQuery.setMaxResults(maxResults);

    return sqlQuery.list();
}

From source file:org.jessma.dao.hbn.HibernateFacade.java

License:Apache License

/**
 * /*from ww w  .j  ava  2 s. c om*/
 * ?SQLScalars?
 * 
 * @param firstResult   : 
 * @param maxResults   : 
 * @param sql         : SQL ?
 * @param scalars      : ??
 * @param params      : ?
 * @return            : 
 * 
 */
protected <T> List<T> sqlQuery2(int firstResult, int maxResults, String sql, KV<String, Type>[] scalars,
        Object... params) {
    SQLQuery sqlQuery = getSession().createSQLQuery(sql);

    for (int i = 0; i < params.length; i++)
        sqlQuery.setParameter(i, params[i]);

    if (scalars != null) {
        for (KV<String, Type> scalar : scalars) {
            String key = scalar.getKey();
            Type value = scalar.getValue();

            if (value != null)
                sqlQuery.addScalar(key, value);
            else
                sqlQuery.addScalar(key);
        }
    }

    if (firstResult > 0)
        sqlQuery.setFirstResult(firstResult);
    if (maxResults > 0)
        sqlQuery.setMaxResults(maxResults);

    return sqlQuery.list();
}

From source file:org.jessma.dao.hbn.HibernateFacade.java

License:Apache License

/**
 * // ww w  . jav a  2  s  .c  o  m
 * ?SQL?EntitiesJoins
 * 
 * @param firstResult   : 
 * @param maxResults   : 
 * @param sql         : SQL ?
 * @param entities      : ??
 * @param joins         : ??
 * @param params      : ?
 * @return            : 
 * 
 */
protected <T> List<T> sqlQuery4(int firstResult, int maxResults, String sql, KV<String, Object>[] entities,
        KV<String, String>[] joins, Object... params) {
    SQLQuery sqlQuery = getSession().createSQLQuery(sql);

    for (int i = 0; i < params.length; i++)
        sqlQuery.setParameter(i, params[i]);

    if (entities != null) {
        for (int i = 0; i < entities.length; i++) {
            KV<String, Object> entity = entities[i];

            String key = entity.getKey();
            Object value = entity.getValue();
            Class<?> v1 = (value instanceof Class) ? (Class<?>) value : null;
            String v2 = v1 == null ? (String) value : null;

            if (key == null || key.length() == 0) {
                if (v1 != null)
                    sqlQuery.addEntity(v1);
                else
                    sqlQuery.addEntity(v2);
            } else {
                if (v1 != null)
                    sqlQuery.addEntity(key, v1);
                else
                    sqlQuery.addEntity(key, v2);
            }
        }
    }

    if (joins != null) {
        for (KV<String, String> join : joins)
            sqlQuery.addJoin(join.getKey(), (String) join.getValue());
    }

    if (firstResult > 0)
        sqlQuery.setFirstResult(firstResult);
    if (maxResults > 0)
        sqlQuery.setMaxResults(maxResults);

    return sqlQuery.list();
}

From source file:org.jpos.ee.pm.core.monitor.SQLMonitorSource.java

License:Open Source License

public MonitorLine getLastLine() throws Exception {
    MonitorLine result = new MonitorLine();
    DB db = new DB();
    db.open();/*from   www .  jav  a  2 s  .c  om*/
    try {
        SQLQuery c = db.session().createSQLQuery(getLastLineQuery().trim());
        c.setMaxResults(1);
        Object item = c.uniqueResult();
        if (item instanceof Object[]) {
            Object[] objects = (Object[]) item;
            result.setId(objects[getIdColumn()]);
            result.setValue(objects);
        } else {
            result.setId(item);
            Object[] objects = { item };
            result.setValue(objects);
        }
    } finally {
        db.close();
    }
    return result;
}

From source file:org.openbp.server.persistence.hibernate.HibernatePersistenceContext.java

License:Apache License

/**
 * Runs the given SQL select statement./*from   w  w w  . j  av a 2s. com*/
 *
 * @param sql SQL query to run
 * @param maxResults Maximum number of result rows or 0 for unlimited
 * @return A list of result elements (contains Object or Object[] elements, depending if this was a single column or multi-column query)
 * @throws PersistenceException On error
 */
public Collection executeSelect(String sql, int maxResults) throws PersistenceException {
    TransactionGuard tg = new TransactionGuard(this);
    try {
        SQLQuery query = getHibernateSession().createSQLQuery(sql);
        if (maxResults > 0) {
            query.setMaxResults(maxResults);
        }

        Collection root = query.list();
        return new DeferedOnLoadCollection(root, this);
    } catch (HibernateException e) {
        tg.doCatch();
        throw createLoggedException(e);
    } finally {
        tg.doFinally();
    }
}

From source file:org.openiam.idm.srvc.qry.service.QueryDataServiceImpl.java

License:Open Source License

public List<Object> executeQuery(Query qry) {

    Session session = sessionFactory.getCurrentSession();

    SQLQuery sqlQry = session.createSQLQuery(qry.getSql());
    sqlQry.addEntity(qry.getObjectClass());

    // add the parameters for the query if they exists
    List<QueryParam> paramList = qry.getParamList();
    if (paramList != null && paramList.size() > 0) {
        for (QueryParam param : paramList) {
            if (param.getParamType() == ParameterTypeEnum.STRING) {
                System.out.println(" - Setting parameters in query service.");
                sqlQry.setString(param.getParamName(), (String) param.getParamValue());
            }//w  ww .  j  ava  2  s .  c om
        }
    }

    if (qry.getMaxRowCount() != 0) {
        sqlQry.setFetchSize(qry.getMaxRowCount());
        sqlQry.setMaxResults(qry.getMaxRowCount());
    }
    return sqlQry.list();

}

From source file:org.openmrs.module.muzima.api.db.hibernate.HibernateCoreDao.java

License:Open Source License

@Override
@Transactional(readOnly = true)//from  w w  w  .j av  a2  s .  c  o  m
@SuppressWarnings("unchecked")
public List<Patient> getPatients(final String cohortUuid, final Date syncDate, final int startIndex,
        final int size) throws DAOException {
    String hqlQuery = " select p.patient_id from patient p, cohort c, cohort_member m "
            + " where c.uuid = :uuid and p.patient_id = m.patient_id " + " and c.cohort_id = m.cohort_id "
            + " and c.voided = false and p.voided = false ";
    if (syncDate != null) {
        hqlQuery = hqlQuery
                + " and ( (c.date_created is not null and c.date_changed is null and c.date_voided is null and c.date_created >= :syncDate) or "
                + "       (c.date_created is not null and c.date_changed is not null and c.date_voided is null and c.date_changed >= :syncDate) or "
                + "       (c.date_created is not null and c.date_changed is not null and c.date_voided is not null and c.date_voided >= :syncDate) ) "
                + " and ( (p.date_created is not null and p.date_changed is null and p.date_voided is null and p.date_created >= :syncDate) or "
                + "       (p.date_created is not null and p.date_changed is not null and p.date_voided is null and p.date_changed >= :syncDate) or "
                + "       (p.date_created is not null and p.date_changed is not null and p.date_voided is not null and p.date_voided >= :syncDate) ) ";
    }
    SQLQuery query = getSessionFactory().getCurrentSession().createSQLQuery(hqlQuery);
    query.setParameter("uuid", cohortUuid);
    if (syncDate != null) {
        query.setParameter("syncDate", syncDate);
    }
    query.setMaxResults(size);
    query.setFirstResult(startIndex);
    List patientIds = query.list();

    if (!patientIds.isEmpty()) {
        Criteria criteria = getSessionFactory().getCurrentSession().createCriteria(Patient.class);
        criteria.add(Restrictions.in("patientId", patientIds));
        return criteria.list();
    }
    return Collections.emptyList();
}

From source file:org.openxdata.server.dao.hibernate.HibernateEditableDAO.java

License:Apache License

@SuppressWarnings("unchecked")
@Override//ww w.jav  a  2  s  .c  o m
@Secured("Perm_View_Form_Data")
public List<Object[]> getResponseData(String formBinding, String[] questionBindings, int offset, int limit,
        String sortField, boolean ascending) {
    StringBuilder sql = new StringBuilder();
    sql.append("select openxdata_form_data_id,");
    sql.append(StringUtils.arrayToCommaDelimitedString(questionBindings));
    sql.append(" from ");
    sql.append(formBinding);
    if (sortField != null && !sortField.trim().equals("")) {
        sql.append(" order by ");
        sql.append(sortField);
        if (!ascending)
            sql.append(" DESC");
    }
    log.debug("executing sql: " + sql + " firstResult=" + offset + " maxResults=" + limit);
    // execute + limit results for page
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.setFirstResult(offset);
    query.setFetchSize(limit);
    query.setMaxResults(limit);
    List<Object[]> data = (List<Object[]>) query.list();
    return data;
}

From source file:org.snaker.engine.access.hibernate3.HibernateAccess.java

License:Apache License

@SuppressWarnings("unchecked")
@Override//  w  ww .j  av  a  2s . co m
public <T> List<T> queryList(Page<T> page, Class<T> T, String sql, Object... args) {
    try {
        String countSQL = "select count(1) from (" + sql + ") c ";
        String querySQL = sql;
        if (page.isOrderBySetted()) {
            querySQL = querySQL + StringHelper.buildPageOrder(page.getOrder(), page.getOrderBy());
        }
        SQLQuery countQuery = getSession().createSQLQuery(countSQL);
        SQLQuery pageQuery = getSession().createSQLQuery(querySQL);
        pageQuery.addEntity(T);
        if (args.length > 0) {
            for (int i = 0; i < args.length; i++) {
                pageQuery.setParameter(i, args[i]);
                countQuery.setParameter(i, args[i]);
            }
        }
        //???pageSize
        if (page.getPageSize() != Page.NON_PAGE) {
            pageQuery.setFirstResult((page.getPageNo() - 1) * page.getPageSize());
            pageQuery.setMaxResults(page.getPageSize());
        }
        List<T> list = pageQuery.list();
        Object total = countQuery.uniqueResult();
        page.setResult(list);
        page.setTotalCount(ClassHelper.castLong(total));
        return list;
    } catch (RuntimeException e) {
        log.error(e.getMessage(), e);
        return Collections.emptyList();
    }
}