Exemplo n.º 1
0
 /**
  * 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;
 }
Exemplo n.º 2
0
 @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;
 }
Exemplo n.º 3
0
 /**
  * 使用原生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;
 }
Exemplo n.º 5
0
 @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;
 }
Exemplo n.º 6
0
  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;
  }