@SuppressWarnings("unchecked") public static <E> List<E> find( Session session, String sql, Class<E> clazz, PageInfo pageInfo, Object... params) { List<E> list = new ArrayList<E>(); SQLQuery sqlQuery = session.createSQLQuery(sql); setParameter(sqlQuery, params); if (pageInfo != null) { Integer first = (pageInfo.getPageIndex() - 1) * pageInfo.getPageSize(); // if(ValidateUtil.isEmpty(first)){ // first = 1; // } sqlQuery.setFirstResult(first); @SuppressWarnings("unused") Integer pageSize = pageInfo.getPageSize(); // if(Validator.isEmpty(pageSize)){ // pageSize = PageInfoUtil.getDefaultPagesize(); // } sqlQuery.setMaxResults(pageInfo.getPageSize()); Integer total = getCount(session, sql, params); pageInfo.setPageTotal(total); } sqlQuery = sqlQuery.addEntity("model", clazz); list = sqlQuery.list(); return list; }
/** * SQL 分页查询 * * @param page * @param sqlString * @param resultClass * @param parameter * @return */ @SuppressWarnings("unchecked") public <E> Page<E> findBySql( Page<E> page, String sqlString, Parameter parameter, Class<?> resultClass) { // get count if (!page.isDisabled() && !page.isNotCount()) { String countSqlString = "select count(*) " + removeSelect(removeOrders(sqlString)); // page.setCount(Long.valueOf(createSqlQuery(countSqlString, // parameter).uniqueResult().toString())); Query query = createSqlQuery(countSqlString, parameter); List<Object> list = query.list(); if (list.size() > 0) { page.setCount(Long.valueOf(list.get(0).toString())); } else { page.setCount(list.size()); } if (page.getCount() < 1) { return page; } } // order by String sql = sqlString; if (StringUtils.isNotBlank(page.getOrderBy())) { sql += " order by " + page.getOrderBy(); } SQLQuery query = createSqlQuery(sql, parameter); // set page if (!page.isDisabled()) { query.setFirstResult(page.getFirstResult()); query.setMaxResults(page.getMaxResults()); } setResultTransformer(query, resultClass); page.setList(query.list()); return page; }
public List<Object[]> findUrgeLogByPage(int startRow, int pageSize) { List<Object[]> list = new ArrayList<Object[]>(); String sql = getUgreLogListSql(); SQLQuery query = this.getHibernateTemplate().getSessionFactory().getCurrentSession().createSQLQuery(sql); list = query.setFirstResult(startRow).setMaxResults(pageSize).list(); return list; }
/** * 使用原生sql根据应用专题分页查询应用信息列表 * * @param page * @param sql * @return [参数说明] * @return List<DpAppInfo> [返回类型说明] * @exception throws [违例类型] [违例说明] */ @SuppressWarnings("unchecked") @Override public List<DpAppInfo> queryForPageBySql(Page<AppSubjectType> page, String sql) { Session session = getSession(); SQLQuery sq = session.createSQLQuery(sql); sq.addEntity(DpAppInfo.class); sq.setFirstResult(page.getBeginCount()); sq.setMaxResults(page.getPageSize()); return sq.list(); }
/** * Obtiene la lista del Log * * @param id_usuario Id. del usuario * @param id_tipo Id. del tipo (APPBS_USUARIOS_LOG_LEGEND) * @param nivel Nivel del log * @param fec_from Fecha de inicio * @param fec_to Fecha final * @param start Elemento en el que se comienza * @param limit Cantidad de elementos * @return Lista de Log */ public static List<APPBS_USUARIOS_LOG> listLog( Long id_usuario, Long id_tipo, Long nivel, Date fec_from, Date fec_to, int start, int limit, String sort, String dir) { Session session = HibernateUtil.currentSession(); SQLQuery query = (SQLQuery) session .createSQLQuery( getListQuery(id_usuario, id_tipo, nivel, fec_from, fec_to, false, sort, dir)) .setCacheable(false); if (start != -1 && limit != -1) { query.setFirstResult(start); query.setMaxResults(limit); } List qRes = query.list(); List<APPBS_USUARIOS_LOG> result = new ArrayList<APPBS_USUARIOS_LOG>(); for (int i = 0; i < qRes.size(); i++) { Object[] item = (Object[]) qRes.get(i); APPBS_USUARIOS_LOG uLog = new APPBS_USUARIOS_LOG(); if (Constants.DB_TYPE == Constants.DB_TYPE_ORACLE) { uLog.setId_usuario(((BigDecimal) item[0]).longValue()); uLog.setNivel(((BigDecimal) item[2]).longValue()); uLog.setId_tipo(((BigDecimal) item[3]).longValue()); } else { uLog.setId_usuario(((Integer) item[0]).longValue()); uLog.setNivel(((Integer) item[2]).longValue()); uLog.setId_tipo(((Integer) item[3]).longValue()); } uLog.setFecha((Timestamp) item[1]); uLog.setUsuario((String) item[4]); uLog.setDescripcion((String) item[5]); uLog.setTexto1((String) item[6]); uLog.setTexto2((String) item[7]); uLog.setTexto3((String) item[8]); uLog.setTexto4((String) item[9]); uLog.setTexto5((String) item[10]); uLog.setSesion_id((String) item[11]); result.add(uLog); } return result; }
@Override public Page getUserPage(int pageNow) { // TODO Auto-generated method stub Page page = new Page(); String queryString = "SELECT users.id,users.account,users.`name`,users.`password`,users.`level`,users.registertime,users.logintime,users.active," + "s_users.openid,s_users.type,s_users.age,s_users.sex,s_users.mail,s_users.phone," + "s_users.address,s_users.education,s_users.school,s_users.id AS susersid " + "FROM users LEFT JOIN s_users ON users.id=s_users.p_users_id"; SQLQuery query = this.getCurrentSession().createSQLQuery(queryString); page.setTotalCount(query.list().size()); page.setPageNow(pageNow); int pageSize = page.getPageSize(); page.setPageCount((page.getTotalCount() + pageSize - 1) / pageSize); query.setFirstResult((pageNow - 1) * pageSize); query.setMaxResults(pageSize); page.setDataList(query.list()); return page; }
public List<BlogEntry> findBlogEntriesInDirectory( WikiDirectory startDir, WikiDocument ignoreDoc, Pager pager, Integer year, Integer month, Integer day, String tag, boolean countComments) { final Map<Long, BlogEntry> blogEntryMap = new HashMap<Long, BlogEntry>(); StringBuilder queryString = new StringBuilder(); queryString.append("select").append(" "); for (int i = 0; i < getWikiDocumentSQLColumnNames().length; i++) { queryString.append(getWikiDocumentSQLColumnNames()[i]); if (i != getWikiDocumentSQLColumnNames().length - 1) queryString.append(", "); } queryString.append(", '0' as COMMENT_COUNT").append(" "); queryString.append(getblogEntryFromClause(tag)); queryString.append(getBlogEntryWhereClause(ignoreDoc, year, month, day, tag)); queryString.append(" "); queryString.append("order by doc2.CREATED_ON desc"); SQLQuery query = getSession().createSQLQuery(queryString.toString()); bindBlogEntryWhereClause(query, startDir, ignoreDoc, year, month, day, tag); query.setComment("Finding all blogEntry documents recursively in dir: " + startDir.getName()); query.addEntity(WikiDocument.class); query.addScalar("COMMENT_COUNT", Hibernate.LONG); query.setFirstResult(pager.getQueryFirstResult()); query.setMaxResults(pager.getQueryMaxResults()); query.setResultTransformer( new ResultTransformer() { public Object transformTuple(Object[] result, String[] aliases) { BlogEntry be = new BlogEntry(); be.setEntryDocument((WikiDocument) result[0]); blogEntryMap.put( be.getEntryDocument().getId(), be); // Put in map so we can attach comment count later return be; } public List transformList(List list) { return list; } }); List<BlogEntry> result = (List<BlogEntry>) query.list(); if (countComments && result.size() > 0) { // The risk here is that pager.getQueryMaxResults() is too large for the IN() operator of some // DBs... StringBuilder commentQueryString = new StringBuilder(); commentQueryString .append("select doc.NODE_ID as DOC_ID, count(c1.NODE_ID) as COMMENT_COUNT") .append(" "); commentQueryString.append("from WIKI_DOCUMENT doc").append(" "); commentQueryString .append("left outer join WIKI_NODE c1 on doc.NODE_ID = c1.PARENT_NODE_ID") .append(" "); commentQueryString.append("where doc.NODE_ID in (:blogEntriesIds)").append(" "); commentQueryString.append("group by doc.NODE_ID"); SQLQuery commentQuery = getSession().createSQLQuery(commentQueryString.toString()); commentQuery.setComment("Finding comment count for blog entries"); commentQuery.addScalar("DOC_ID"); commentQuery.addScalar("COMMENT_COUNT"); commentQuery.setParameterList("blogEntriesIds", blogEntryMap.keySet()); commentQuery.setResultTransformer( new ResultTransformer() { public Object transformTuple(Object[] result, String[] aliases) { BlogEntry be = blogEntryMap.get(((BigInteger) result[0]).longValue()); be.setCommentCount(((BigInteger) result[1]).longValue()); return null; } public List transformList(List list) { return list; } }); commentQuery.list(); } return result; }