@Test // @Ignore( "Support for locking on native-sql queries not yet implemented" ) public void testNativeSql() { Session session = openSession(); session.beginTransaction(); SQLQuery qry = session.createSQLQuery( "select * from door" ); qry.addRoot( "door", Door.class ); qry.getLockOptions().setLockMode( LockMode.PESSIMISTIC_WRITE ); qry.setFirstResult( 2 ); qry.setMaxResults( 2 ); @SuppressWarnings("unchecked") List results = qry.list(); assertEquals( 2, results.size() ); for ( Object door : results ) { assertEquals( LockMode.PESSIMISTIC_WRITE, session.getCurrentLockMode( door ) ); } session.getTransaction().commit(); session.close(); }
@Override public boolean hasNext() { if (firstResultIndex < maxResultIndex) { // TODO:namedquery, JPQL, HQL => iterate, native => then don't result = sqlQuery.setFirstResult(firstResultIndex++).setMaxResults(1).list(); } return result != null && !result.isEmpty() && fetchSize != 0; }
@SuppressWarnings("unchecked") public List<Map<String, Object>> findMapBySql(String sql, int page, int rows) { SQLQuery sqlQuery = this.getCurrentSession().createSQLQuery(sql); return sqlQuery.setFirstResult((page - 1) * rows).setMaxResults(rows).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list(); }
@SuppressWarnings("unchecked") public <T> List<T> findBySql(String sql, Map<String, Object> params, int page, int rows, Class<T> clazz) { SQLQuery sqlQuery = this.getCurrentSession().createSQLQuery(sql); sqlQuery=getSqlQueryByMap(sqlQuery,params); sqlQuery.addEntity(clazz); return sqlQuery.setFirstResult((page - 1) * rows).setMaxResults(rows).list(); }
@SuppressWarnings("unchecked") public List<Map<String, Object>> findMapBySql(String sql, Map<String, Object> params, int page, int rows) { SQLQuery sqlQuery = this.getCurrentSession().createSQLQuery(sql); sqlQuery=getSqlQueryByMap(sqlQuery,params); return sqlQuery.setFirstResult((page - 1) * rows).setMaxResults(rows).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list(); }
public List findMapBySql(String sql, Map<String, Object> params, int page, int rows,Class clazz) { SQLQuery sqlQuery = this.getCurrentSession().createSQLQuery(sql); sqlQuery=getSqlQueryByMap(sqlQuery,params); if(clazz==null){ sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); }else{ sqlQuery.setResultTransformer(Transformers.aliasToBean(clazz)); } return sqlQuery.setFirstResult((page - 1) * rows).setMaxResults(rows).list(); }
@Override public NativeQuery setFirstResult(int firstResult) { query.setFirstResult(firstResult); return this; }
@Override public boolean hasNext() { if (firstResultIndex < maxResultIndex) { // TODO:namedquery, JPQL, HQL => iterate, native => then don't result = sqlQuery.setFirstResult(firstResultIndex++).setMaxResults(1).list(); } return result != null && !result.isEmpty() && fetchSize != 0; }
// the corresponding method: private SQLQuery createQuery(HibernateTransaction t,int lowLimit,int pageSize) { final SQLQuery query = t.fullSQLQuery(MY_QUERY_STRING); query.addScalar("column1", Hibernate.LONG); query.addScalar("column2", Hibernate.LONG); query.setFirstResult(lowLimit); query.setMaxResults(pageSize); query.setResultTransformer(new AliasToBeanResultTransformer(MyDTO.class)); return query; }
protected SQLQuery getQuery(String queryString, Map<String, Object> where, int start, int limit, boolean returnMap) { Session session = this.getSession(); SQLQuery query = null; if (start > -1 && limit > 0) { query = session.createSQLQuery(queryString); query.setFirstResult(start); query.setMaxResults(limit); } else { query = session.createSQLQuery(queryString); } if (where != null) { for (Entry<String, Object> entry : where.entrySet()) { Object value = entry.getValue(); String key = entry.getKey(); if (value instanceof Collection) { query.setParameterList(key, (Collection) value); } else if (value instanceof Object[]) { query.setParameterList(key, (Object[]) value); } else { query.setParameter(key, value); } } } if (returnMap) { query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); } return query; }
@Transactional(propagation = Propagation.REQUIRED, readOnly = false) public <T> List<T> executeSP(final String spName, final String[] parameterNames, final String[] parameterValues, final String[] resultColumnNames, final int offset, final int size, final Class<T> returnType) { validateParams(parameterNames, parameterValues); return (List<T>) hibernateTemplate .execute(new HibernateCallback<List<T>>() { @SuppressWarnings("unchecked") public List<T> doInHibernate(final Session session) { SQLQuery query = session.createSQLQuery("call " + spName); if (returnType != null) { query.addEntity(returnType); } setQueryParams(parameterNames, parameterValues, query); setResultColumnNames(resultColumnNames, query); if (offset >= 0) { query.setFirstResult(offset); } if (size > 0) { query.setMaxResults(size); } return query.list(); } }); }
/** * * 根据起始记录和最大记录数执行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(); }
.setFirstResult(Integer.parseInt(sqlSourceHelper.getCurrentIndex()));
@Override public Object doInHibernate(Session session) throws HibernateException, SQLException { SQLQuery query=session.createSQLQuery("select id,job_id,start_time,end_time,execute_host,status,trigger_type,illustrate,operator,properties,statis_end_time,timezone,cycle from zeus_job_history" + " where job_id=? order by id desc"); query.setParameter(0, Long.valueOf(jobId)); query.setMaxResults(limit); query.setFirstResult(start); List<Object[]> list=query.list(); List<JobHistory> result=new ArrayList<JobHistory>(); for(Object[] o:list){ JobHistoryPersistence p=new JobHistoryPersistence(); p.setId(((Number)o[0]).longValue()); p.setJobId(((Number)o[1]).longValue()); p.setStartTime((Date)o[2]); p.setEndTime((Date)o[3]); p.setExecuteHost((String)o[4]); p.setStatus((String)o[5]); p.setTriggerType(o[6]==null?null:((Number)o[6]).intValue()); p.setIllustrate((String)o[7]); p.setOperator((String)o[8]); p.setProperties((String)o[9]); p.setStatisEndTime(o[10]==null?null:(Date)o[10]); p.setTimezone((String)o[11]); p.setCycle((String)o[12]); result.add(PersistenceAndBeanConvert.convert(p)); } return result; } });
sqlQuery.setFirstResult(firstResult); if(maxResults > 0) sqlQuery.setMaxResults(maxResults);
sqlQuery.setFirstResult(firstResult); if(maxResults > 0) sqlQuery.setMaxResults(maxResults);
query.setFirstResult((param.getPageIndex() - 1) * param.getPageSize()); query.setMaxResults(param.getPageSize());