List of usage examples for org.hibernate SQLQuery setFirstResult
@Override
Query<R> setFirstResult(int startPosition);
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 .ja va 2 s. 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 w w w .ja 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
/** * /*w w 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
/** * //from w w w . j a v a 2s. c om * ?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.openmrs.module.muzima.api.db.hibernate.HibernateCoreDao.java
License:Open Source License
@Override @Transactional(readOnly = true)/*from w ww . ja v a 2s .co 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/* w ww . j a va 2s .c om*/ @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.openxdata.server.dao.hibernate.HibernateEditableDAO.java
License:Apache License
/** * Runs a given <code>SQL</code> statement within a given * <code>sessionFactory</code>. * /*from ww w . j av a2s. c o m*/ * @param SQL * <code>SQL</code> to run. * @param editableId * Optional <code>Id</code> for the <code>Editable</code>. * @param sessionFactory * <code>sessionFactory</code> to create session in which the SQL * will be run. * * @return <code> Boolean</code> */ @SuppressWarnings("unchecked") private Boolean runSQLToAscertainDataExistence(String SQL, int editableId) { Boolean hasData = false; Session session = getSession(); SQLQuery query = session.createSQLQuery(SQL); query.setInteger("id", editableId); query.setFirstResult(0); query.setFetchSize(1); List<FormDataHeader> items = query.list(); if (items != null) { if (items.size() > 0) hasData = true; else hasData = false; } return hasData; }
From source file:org.snaker.engine.access.hibernate3.HibernateAccess.java
License:Apache License
@SuppressWarnings("unchecked") @Override//w w w . j a va2 s . c om 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(); } }
From source file:org.trpr.dataaccess.orm.handler.HibernateHandler.java
License:Apache License
/** * Helper method to execute a native SQL query and return the results as a List of PersistentEntity instances. * Note that this implementation doesnot support scalar values to be returned and instead expects all resultant data to be * mapped to a PersistentEntity defined for the purpose. This approach helps to maintain consistency in the {@link #findEntities(Criteria)} * interface implementation and also provide for standard Hibernate-to-Java entity type mapping semantics. *///from w w w . j a v a 2 s. com @SuppressWarnings("unchecked") private List<PersistentEntity> findObjectBySQLQuery(final Criteria criteria) { return (List<PersistentEntity>) this.getTemplate().execute(new HibernateCallback() { public Object doInHibernate(Session session) throws HibernateException, SQLException { SQLQuery sqlQueryObject = session.createSQLQuery(criteria.getQuery()); sqlQueryObject.addEntity(criteria.getManagedClass()); sqlQueryObject.setFirstResult(criteria.getFirstResult()); if (criteria.getMaxResults() > 0) { sqlQueryObject.setMaxResults(criteria.getMaxResults()); } return sqlQueryObject.list(); } }); }
From source file:org.xerela.provider.telemetry.TelemetryProvider.java
License:Mozilla Public License
/** {@inheritDoc} */ @SuppressWarnings({ "unchecked", "nls" }) public MacPageData getMacTable(MacPageData pageData, String ipAddress, String managedNetwork) { ZDeviceCore device = getDevice(ipAddress, managedNetwork); if (device == null) { pageData.setMacEntries(new MacTableEntry[0]); pageData.setTotal(0);//from w w w . ja v a 2 s. c o m return pageData; } boolean ownTransaction = TransactionElf.beginOrJoinTransaction(); try { Session session = TelemetryActivator.getSessionFactory().getCurrentSession(); String fromClause = "FROM discovery_mac WHERE device_id = " + device.getDeviceId(); SQLQuery query = session .createSQLQuery("SELECT mac_address, interface, vlan " + fromClause + " ORDER BY interface"); query.addScalar("mac_address", Hibernate.LONG); query.addScalar("interface", Hibernate.STRING); query.addScalar("vlan", Hibernate.STRING); query.setFirstResult(pageData.getOffset()).setMaxResults(pageData.getPageSize()); query.scroll(ScrollMode.SCROLL_INSENSITIVE); List<Object[]> resultList = (List<Object[]>) query.list(); if (resultList == null || resultList.isEmpty()) { pageData.setMacEntries(new MacTableEntry[0]); pageData.setTotal(0); return pageData; } else { if (pageData.getOffset() == 0) { // Set the total result size into the page data. query = session.createSQLQuery("SELECT count(mac_address) " + fromClause); pageData.setTotal(getCount(query)); } List<MacTableEntry> macTable = new ArrayList<MacTableEntry>(); for (Object[] resultEntry : resultList) { MacTableEntry entry = new MacTableEntry(); entry.setMacAddress((Long) resultEntry[0]); entry.setPort((String) resultEntry[1]); entry.setVlan((String) resultEntry[2]); macTable.add(entry); } pageData.setMacEntries(macTable.toArray(new MacTableEntry[0])); return pageData; } } finally { if (ownTransaction) { TransactionElf.commit(); } } }