/** * 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); }
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())); } }
/** * 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); }
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); } }
@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; }
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())); } }
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; }
/** * 分页查找 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; }
@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; }
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; }
/** * 按照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; }
/** * 分页查找 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; }
/** * 根据用户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; }
public Pager createPager(int pageNumber, int pageSize) { Pager pager = new Pager(); pager.setPageNumber(pageNumber); pager.setPageSize(pageSize); return pager; }
/** * 填充记录总数 * * @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; }
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())); }
/** * 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); }
/** * 填充记录总数 * * @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; }
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"); }
public void setRecordCount(Pager pager, Dao dao, Sql newSql) { pager.setRecordCount(dao.count("(" + newSql.toString() + ") _table_")); }