List of usage examples for org.hibernate SQLQuery setMaxResults
@Override
Query<R> setMaxResults(int maxResult);
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(); } }