/**
* 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_"));
}