Example #1
0
 /**
  * Test表根据name查询
  *
  * @param name
  * @param pageNumber
  * @param pageSize
  * @return 对应考试名称的考试信息
  */
 public QueryResult queryByName(String name, int pageNumber, int pageSize) {
   Pager pager = dao().createPager(pageNumber, pageSize);
   List<Test> listByName =
       dao().query(Test.class, Cnd.where("test_name", "like", "%" + name + "%"), pager);
   pager.setRecordCount(dao().count(Test.class, Cnd.where("test_name", "like", "%" + name + "%")));
   return new QueryResult(listByName, pager);
 }
 /**
  * 分页获取帖子列表
  *
  * @param page 页数,默认为1
  * @param type 参数名叫tab,默认是ask,如果传all,也会变成ask
  * @param limit 每页数量
  * @param mdrender 是否渲染md
  * @api {get} /yvr/api/v1/topics 获取帖子列表
  * @apiGroup Topic
  * @apiVersion 1.0.0
  * @apiParam {int} [page=1] 页数,默认为1
  * @apiParam {String} [tab=ask] 分类
  * @apiParam {int} [limit=10] 分页
  * @apiParam {boolean} [mdrender=true] 是否渲染Markdown
  * @apiSuccess {Object[]} data 帖子列表数据
  * @apiSuccess {String} data.id 唯一标示符
  * @apiSuccess {String} data.title 标题
  * @apiSuccess {String} data.tab 类型
  * @apiSuccess {String} data.content 内容
  * @apiSuccess {String} [data.last_reply_at] 最后回复时间
  * @apiSuccess {boolean} data.top 是否置顶
  * @apiSuccess {boolean} data.good 是否为精华帖
  * @apiSuccess {int} data.reply_count 总回复数量
  * @apiSuccess {int} data.visit_count 总浏览数量
  * @apiSuccess {Object} data.author 作者信息
  * @apiSuccess {String} data.author.id 作者id
  * @apiSuccess {String} data.author.loginname 作者登陆名
  */
 @GET
 @At
 @Aop("redis")
 public Object topics(
     @Param("page") int page,
     @Param("tab") String type,
     @Param("limit") int limit,
     @Param("mdrender") String mdrender) {
   if (page < 1) page = 1;
   if (limit < 0 || limit > pageSize) limit = pageSize;
   Pager pager = dao.createPager(page, limit);
   if (type == null) type = "ask";
   else if ("all".equals(type)) type = "ask";
   HashMap<Integer, UserProfile> authors = new HashMap<Integer, UserProfile>();
   List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
   Set<String> ids =
       jedis()
           .zrevrangeByScore(
               RKEY_TOPIC_UPDATE + type,
               System.currentTimeMillis(),
               0,
               pager.getOffset(),
               pager.getPageSize());
   for (String id : ids) {
     Topic topic = dao.fetch(Topic.class, id);
     if (topic == null) continue;
     list.add(_topic(topic, authors, mdrender));
   }
   return _map("data", list);
 }
Example #3
0
 public void formatQuery(Sql sql) {
   Pager pager = sql.getContext().getPager();
   if (null != pager && pager.getPageNumber() > 0) {
     sql.setSourceSql(
         sql.getSourceSql()
             + String.format(" LIMIT %d OFFSET %d", pager.getPageSize(), pager.getOffset()));
   }
 }
Example #4
0
 /**
  * Test表查询一段时间的记录
  *
  * @param date1
  * @param date2
  * @param pageNumber
  * @param pageSize
  * @return 对应一段时间内的考试信息
  */
 public QueryResult queryByPeriodDate(Date date1, Date date2, int pageNumber, int pageSize) {
   Pager pager = dao().createPager(pageNumber, pageSize);
   List<Test> listByPeriodDate =
       dao().query(Test.class, Cnd.where("Date", ">=", date1).and("Date", "<=", date2), pager);
   pager.setRecordCount(
       dao().count(Test.class, Cnd.where("Date", ">=", date1).and("Date", "<=", date2)));
   return new QueryResult(listByPeriodDate, pager);
 }
Example #5
0
 public void formatQuery(Sql sql) {
   Pager pager = sql.getContext().getPager();
   if (null != pager && pager.getPageNumber() > 0) {
     String pre = "SELECT * FROM (SELECT ROW_NUMBER() OVER() AS ROWNUM, T.* FROM (";
     String last =
         String.format(
             ") T) AS A WHERE ROWNUM BETWEEN %d AND %d",
             pager.getOffset() + 1, pager.getOffset() + pager.getPageSize());
     sql.setSourceSql(pre + sql.getSourceSql() + last);
   }
 }
Example #6
0
 @At
 public Object list(@Param("page") int page, @Param("rows") int rows) {
   if (rows < 1) rows = 10;
   Pager pager = dao().createPager(page, rows);
   List<Tmp1> list = dao().query(Tmp1.class, null, pager);
   Map<String, Object> map = new HashMap<String, Object>();
   if (pager != null) {
     pager.setRecordCount(dao().count(Tmp1.class));
     map.put("pager", pager);
   }
   map.put("list", list);
   return map;
 }
Example #7
0
 public void formatQuery(Pojo pojo) {
   Pager pager = pojo.getContext().getPager();
   // 需要进行分页
   if (null != pager && pager.getPageNumber() > 0) {
     // 之前插入
     pojo.insertFirst(
         Pojos.Items.wrap(
             "SELECT * FROM (" + "SELECT ROW_NUMBER() OVER() AS ROWNUM, " + "T.* FROM ("));
     // 之后插入
     pojo.append(
         Pojos.Items.wrapf(
             ") T) AS A WHERE ROWNUM BETWEEN %d AND %d",
             pager.getOffset() + 1, pager.getOffset() + pager.getPageSize()));
   }
 }
Example #8
0
  public List<Topic> getRecentReplyTopics(int userId, Pager pager) {

    Map<Integer, UserProfile> authors = new HashMap<Integer, UserProfile>();
    Cnd cnd = Cnd.where("userId", "=", userId);
    cnd.desc("createTime");

    Sql sql =
        Sqls.queryString("select DISTINCT topicId from t_topic_reply $cnd")
            .setEntity(dao.getEntity(TopicReply.class))
            .setVar("cnd", cnd);
    pager.setRecordCount(
        dao.execute(
                Sqls.fetchInt("select count(DISTINCT topicId) from t_topic_reply $cnd")
                    .setEntity(dao.getEntity(TopicReply.class))
                    .setVar("cnd", cnd))
            .getInt());
    sql.setPager(pager);
    String[] replies_topic_ids = dao.execute(sql).getObject(String[].class);
    List<Topic> recent_replies = new ArrayList<Topic>();
    for (String topic_id : replies_topic_ids) {
      Topic _topic = dao.fetch(Topic.class, topic_id);
      if (_topic == null) continue;
      recent_replies.add(_topic);
    }
    if (!recent_replies.isEmpty()) {
      for (Topic topic : recent_replies) {
        fillTopic(topic, authors);
      }
    }
    return recent_replies;
  }
  /**
   * 无and关系的tag查询
   *
   * @param tagIds 标签ids
   * @param order
   * @param pager
   * @return
   */
  public List<Resource> resourceTagSearch(Integer tagId, String order, Pager pager) {

    List<Resource> query = null;

    if (tagId != null) {
      Sql sql_resource =
          Sqls.create(
              "select * from resource as r, resource_tag as rt where rt.resource_id = r.id and rt.tag_id = "
                  + tagId);
      sql_resource.setCallback(Sqls.callback.entities());
      sql_resource.setEntity(dao.getEntity(Resource.class));
      dao.execute(sql_resource);
      query = sql_resource.getList(Resource.class);
      if (pager != null) {
        pager.setRecordCount(query.size());
      }

    } else {
      System.out.print("resourceservice: tagid无效");
    }
    for (Resource resource : query) {
      resourceRelationFull(resource);
    }
    return query;
  }
Example #10
0
  /**
   * 分页查找 resource
   *
   * @param categoryId 分类id好好
   * @param order
   * @param pager
   * @return
   */
  public List<Resource> search(Integer categoryId, Integer tagId, String order, Pager pager) {
    List<Resource> query = null;

    if (categoryId != null) {
      query = dao.query(Resource.class, Cnd.where("categoryId", "=", categoryId).desc("id"), pager);
      if (pager != null) {
        pager.setRecordCount(dao.count(Resource.class, Cnd.where("categoryId", "=", categoryId)));
      }

    } else {
      query = dao.query(Resource.class, Cnd.orderBy().desc("id"), pager);
      if (pager != null) {
        pager.setRecordCount(dao.count(Resource.class));
      }
    }
    for (Resource resource : query) {
      resourceRelationFull(resource);
    }
    return query;
  }
Example #11
0
  @At
  @Ok("json")
  public JDataGrid list_grid(
      @Attr(scope = Scope.SESSION, value = "account") Account acc,
      @Param("::columns") List<?> columns,
      @Param("::search") Map<?, ?> search,
      int draw,
      int start,
      int length) {
    start = start / length;
    Pager pager = new Pager();
    pager.setPageNumber(start + 1);
    pager.setPageSize(length);

    Cnd cnd = Cnd.where("accountid", "=", acc.accountid);

    if (!Lang.isEmpty(search.get("value")) && !search.get("value").toString().equals("")) {
      cnd.and("name", "like", search.get("value"));
    }

    List<?> aaData = dao.query(Store.class, cnd);
    pager.setRecordCount(dao.count(Store.class, cnd));

    JDataGrid ret = new JDataGrid();
    ret.draw = draw;
    ret.data = aaData;
    ret.recordsTotal = pager.getRecordCount();
    ret.recordsFiltered = pager.getRecordCount();
    return ret;
  }
Example #12
0
  public List<Topic> getRecentTopics(int userId, Pager pager) {
    List<Topic> recent_topics =
        dao.query(Topic.class, Cnd.where("userId", "=", userId).desc("createTime"), pager);

    Map<Integer, UserProfile> authors = new HashMap<Integer, UserProfile>();
    if (!recent_topics.isEmpty()) {
      for (Topic topic : recent_topics) {
        fillTopic(topic, authors);
      }
    }
    pager.setRecordCount(dao.count(Topic.class, Cnd.where("userId", "=", userId)));
    return recent_topics;
  }
Example #13
0
  /**
   * 按照tag分页查找 resource
   *
   * @param tagId 标签id
   * @param order
   * @param pager
   * @return
   */
  public List<Resource> resourceTagSearchAnd(String tagIds, String order, Pager pager) {

    List<Resource> query = null;
    // 将tagids字符串拆分成id数组进行and查询
    String[] ids = tagIds.split(",");
    if (ids.length > 0) {
      String sqlTagsResource =
          "select * from resource as r, resource_tag as rt where (rt.resource_id = r.id and rt.tag_id = "
              + Integer.parseInt(ids[0])
              + ")";
      for (int j = 1; j < ids.length; j++) {
        sqlTagsResource =
            sqlTagsResource
                + " and (r.id in (select r"
                + j
                + ".id from resource as r"
                + j
                + ", resource_tag as rt"
                + j
                + " where (rt"
                + j
                + ".resource_id = r"
                + j
                + ".id and rt"
                + j
                + ".tag_id = "
                + Integer.parseInt(ids[j])
                + ")))";
      }
      Sql sql_resource = Sqls.create(sqlTagsResource);
      sql_resource.setCallback(Sqls.callback.entities());
      sql_resource.setEntity(dao.getEntity(Resource.class));
      dao.execute(sql_resource);
      query = sql_resource.getList(Resource.class);
      if (pager != null) {
        pager.setRecordCount(query.size());
      }
      for (Resource resource : query) {
        resourceRelationFull(resource);
      }
    } else {
      System.out.print("resourservice: tagids无效");
    }
    return query;
  }
Example #14
0
  /**
   * 分页查找 resource
   *
   * @param categoryId 分类id好好
   * @param order
   * @param pager
   * @return
   */
  public List<Resource> search(Integer categoryId, String order, Pager pager, Integer userId) {
    List<Resource> query = null;

    Cnd cnd = Cnd.NEW();
    if (userId != null) {
      cnd = cnd.and("author", "=", userId);
    }

    if (categoryId != null && categoryId != 0) {
      cnd = cnd.and("categoryId", "=", categoryId);
    }

    if (pager != null) {
      pager.setRecordCount(dao.count(Resource.class, cnd));
    }
    query = dao.query(Resource.class, cnd.desc("id"), pager);

    for (Resource resource : query) {
      resourceRelationFull(resource);
    }
    return query;
  }
Example #15
0
  /**
   * 根据用户id资源查询
   *
   * @param userId 用户id
   * @param order
   * @param pager
   * @return
   */
  public List<Resource> userResourceSearch(Integer userId, String order, Pager pager) {

    List<Resource> query = null;

    if (userId != null) {
      Sql sql_resource = Sqls.create("select * from resource as r where r.author = " + userId);
      sql_resource.setCallback(Sqls.callback.entities());
      sql_resource.setEntity(dao.getEntity(Resource.class));
      dao.execute(sql_resource);
      query = sql_resource.getList(Resource.class);
      if (pager != null) {
        pager.setRecordCount(query.size());
      }

    } else {
      System.out.print("resourceservice: userid无效");
    }
    for (Resource resource : query) {
      resourceRelationFull(resource);
    }
    return query;
  }
Example #16
0
 public Pager createPager(int pageNumber, int pageSize) {
   Pager pager = new Pager();
   pager.setPageNumber(pageNumber);
   pager.setPageSize(pageSize);
   return pager;
 }
Example #17
0
 /**
  * 填充记录总数
  *
  * @param pager 分页对象,如果为null就不进行任何操作
  * @param dao Dao实例
  * @param tableName 表名
  * @param cnd 查询条件
  * @return 传入的Pager参数
  */
 public static Pager updatePagerCount(Pager pager, Dao dao, String tableName, Condition cnd) {
   if (null != pager) {
     pager.setRecordCount(dao.count(tableName, cnd));
   }
   return pager;
 }
Example #18
0
 public void formatQuery(Pojo pojo) {
   Pager pager = pojo.getContext().getPager();
   // 需要进行分页
   if (null != pager && pager.getPageNumber() > 0)
     pojo.append(Pojos.Items.wrapf(" LIMIT %d OFFSET %d", pager.getPageSize(), pager.getOffset()));
 }
Example #19
0
 /**
  * Test表查询所有
  *
  * @param pageNumber
  * @param pageSize
  * @return 所有考试信息(分页)
  */
 public QueryResult query(int pageNumber, int pageSize) {
   Pager pager = dao().createPager(pageNumber, pageSize);
   List<Test> list = dao().query(Test.class, null, pager);
   pager.setRecordCount(dao().count(Test.class));
   return new QueryResult(list, pager);
 }
Example #20
0
 /**
  * 填充记录总数
  *
  * @param pager 分页对象,如果为null就不进行任何操作
  * @param dao Dao实例
  * @param entityType 实体类,可以通过dao.getEntity获取
  * @param cnd 查询条件
  * @return 传入的Pager参数
  */
 public static Pager updatePagerCount(Pager pager, Dao dao, Class<?> entityType, Condition cnd) {
   if (null != pager) {
     pager.setRecordCount(dao.count(entityType, cnd));
   }
   return pager;
 }
Example #21
0
  private void _runSelect(Connection conn, DaoStatement st) throws SQLException {

    Object[][] paramMatrix = st.getParamMatrix();
    // -------------------------------------------------
    // 以下代码,就为了该死的游标分页!!
    // -------------------------------------------------
    int startRow = -1;
    int lastRow = -1;
    if (st.getContext().getResultSetType() == ResultSet.TYPE_SCROLL_INSENSITIVE) {
      Pager pager = st.getContext().getPager();
      if (pager != null) {
        startRow = pager.getOffset();
        lastRow = pager.getOffset() + pager.getPageSize();
      }
    }
    // -------------------------------------------------
    // 生成 Sql 语句
    String sql = st.toPreparedStatement();
    // 打印调试信息

    ResultSet rs = null;
    Statement stat = null;
    try {

      // 木有参数,直接运行
      if (null == paramMatrix || paramMatrix.length == 0 || paramMatrix[0].length == 0) {
        if (log.isDebugEnabled()) log.debug(st);
        stat = conn.createStatement(st.getContext().getResultSetType(), ResultSet.CONCUR_READ_ONLY);
        if (lastRow > 0) stat.setMaxRows(lastRow); // 游标分页,现在总行数
        if (st.getContext().getFetchSize() > 0) stat.setFetchSize(st.getContext().getFetchSize());
        rs = stat.executeQuery(sql);
      }
      // 有参数,用缓冲语句
      else {

        // 打印调试信息
        if (paramMatrix.length > 1) {
          if (log.isWarnEnabled())
            log.warnf("Drop last %d rows parameters for:\n%s", paramMatrix.length - 1, st);
        }
        if (log.isDebugEnabled()) {
          log.debug(st);
        }

        // 准备运行语句
        ValueAdaptor[] adaptors = st.getAdaptors();
        // 创建语句并设置参数
        stat =
            conn.prepareStatement(
                sql, st.getContext().getResultSetType(), ResultSet.CONCUR_READ_ONLY);
        if (lastRow > 0) stat.setMaxRows(lastRow);
        for (int i = 0; i < paramMatrix[0].length; i++) {
          adaptors[i].set((PreparedStatement) stat, paramMatrix[0][i], i + 1);
        }
        rs = ((PreparedStatement) stat).executeQuery();
      }
      if (startRow > 0) rs.absolute(startRow);
      // 执行回调
      st.onAfter(conn, rs);
    } finally {
      Daos.safeClose(stat, rs);
    }
    // 打印更详细的调试信息
    if (log.isTraceEnabled()) log.trace("...DONE");
  }
Example #22
0
 public void setRecordCount(Pager pager, Dao dao, Sql newSql) {
   pager.setRecordCount(dao.count("(" + newSql.toString() + ") _table_"));
 }