最近做了很多动态的查询,尤其是排序,以及一些状态字段,所以就做了一个总的动态查询,以不变应万变,呵呵
ibatis 里面的sql代码:
- <select id="getTopics" resultClass="topic" parameterClass="map">
- <![CDATA[
- select * from p_Topic
- ]]>
- <dynamic prepend=" WHERE ">
- <isPropertyAvailable property="authorId">
- <isNotNull property="authorId" prepend=" and ">
- authorId=#authorId#
- </isNotNull>
- </isPropertyAvailable>
- <isPropertyAvailable property="marketId">
- <isNotNull property="marketId" prepend=" and ">
- marketId=#marketId#
- </isNotNull>
- </isPropertyAvailable>
- <isPropertyAvailable property="isDelete">
- <isNotNull property="isDelete" prepend=" and ">
- isDelete=#isDelete#
- </isNotNull>
- </isPropertyAvailable>
- <isPropertyAvailable property="isBest">
- <isNotNull property="isBest" prepend=" and ">
- isBest=#isBest#
- </isNotNull>
- </isPropertyAvailable>
- <isPropertyAvailable property="statusStr">
- <isNotNull property="statusStr" prepend=" and ">
- $statusStr$
- </isNotNull>
- </isPropertyAvailable>
- <isPropertyAvailable property="marketIdList">
- <isNotNull property="marketIdList" prepend=" and marketId in ">
- <iterate property="marketIdList" conjunction="," close=")" open="(">
- #marketIdList[]#
- </iterate>
- </isNotNull>
- </isPropertyAvailable>
- </dynamic>
- <dynamic prepend=" order by ">
- <isPropertyAvailable property="orderStr">
- <isNotNull property="orderStr">
- $orderStr$
- </isNotNull>
- </isPropertyAvailable>
- </dynamic>
- <dynamic>
- <isPropertyAvailable property="begin">
- <isNotNull property="begin">
- limit #begin#
- </isNotNull>
- </isPropertyAvailable>
- <isPropertyAvailable property="max" prepend=" , ">
- <isNotNull property="max">
- #max#
- </isNotNull>
- </isPropertyAvailable>
- </dynamic>
- </select>
- <select id="getTopicCount" resultClass="java.lang.Long"
- parameterClass="map">
- <![CDATA[
- select count(id) from p_Topic
- ]]>
- <dynamic prepend=" WHERE ">
- <isPropertyAvailable property="authorId">
- <isNotNull property="authorId" prepend=" and ">
- authorId=#authorId#
- </isNotNull>
- </isPropertyAvailable>
- <isPropertyAvailable property="marketId">
- <isNotNull property="marketId" prepend=" and ">
- marketId=#marketId#
- </isNotNull>
- </isPropertyAvailable>
- <isPropertyAvailable property="isDelete">
- <isNotNull property="isDelete" prepend=" and ">
- isDelete=#isDelete#
- </isNotNull>
- </isPropertyAvailable>
- <isPropertyAvailable property="isBest">
- <isNotNull property="isBest" prepend=" and ">
- isBest=#isBest#
- </isNotNull>
- </isPropertyAvailable>
- <isPropertyAvailable property="statusStr">
- <isNotNull property="statusStr" prepend=" and ">
- $statusStr$
- </isNotNull>
- </isPropertyAvailable>
- <isPropertyAvailable property="marketIdList">
- <isNotNull property="marketIdList" prepend=" and marketId in ">
- <iterate property="marketIdList" conjunction="," close=")" open="(">
- #marketIdList[]#
- </iterate>
- </isNotNull>
- </isPropertyAvailable>
- </dynamic>
- </select>
<select id="getTopics" resultClass="topic" parameterClass="map"> <![CDATA[ select * from p_Topic ]]> <dynamic prepend=" WHERE "> <isPropertyAvailable property="authorId"> <isNotNull property="authorId" prepend=" and "> authorId=#authorId# </isNotNull> </isPropertyAvailable> <isPropertyAvailable property="marketId"> <isNotNull property="marketId" prepend=" and "> marketId=#marketId# </isNotNull> </isPropertyAvailable> <isPropertyAvailable property="isDelete"> <isNotNull property="isDelete" prepend=" and "> isDelete=#isDelete# </isNotNull> </isPropertyAvailable> <isPropertyAvailable property="isBest"> <isNotNull property="isBest" prepend=" and "> isBest=#isBest# </isNotNull> </isPropertyAvailable> <isPropertyAvailable property="statusStr"> <isNotNull property="statusStr" prepend=" and "> $statusStr$ </isNotNull> </isPropertyAvailable> <isPropertyAvailable property="marketIdList"> <isNotNull property="marketIdList" prepend=" and marketId in "> <iterate property="marketIdList" conjunction="," close=")" open="("> #marketIdList[]# </iterate> </isNotNull> </isPropertyAvailable> </dynamic> <dynamic prepend=" order by "> <isPropertyAvailable property="orderStr"> <isNotNull property="orderStr"> $orderStr$ </isNotNull> </isPropertyAvailable> </dynamic> <dynamic> <isPropertyAvailable property="begin"> <isNotNull property="begin"> limit #begin# </isNotNull> </isPropertyAvailable> <isPropertyAvailable property="max" prepend=" , "> <isNotNull property="max"> #max# </isNotNull> </isPropertyAvailable> </dynamic> </select> <select id="getTopicCount" resultClass="java.lang.Long" parameterClass="map"> <![CDATA[ select count(id) from p_Topic ]]> <dynamic prepend=" WHERE "> <isPropertyAvailable property="authorId"> <isNotNull property="authorId" prepend=" and "> authorId=#authorId# </isNotNull> </isPropertyAvailable> <isPropertyAvailable property="marketId"> <isNotNull property="marketId" prepend=" and "> marketId=#marketId# </isNotNull> </isPropertyAvailable> <isPropertyAvailable property="isDelete"> <isNotNull property="isDelete" prepend=" and "> isDelete=#isDelete# </isNotNull> </isPropertyAvailable> <isPropertyAvailable property="isBest"> <isNotNull property="isBest" prepend=" and "> isBest=#isBest# </isNotNull> </isPropertyAvailable> <isPropertyAvailable property="statusStr"> <isNotNull property="statusStr" prepend=" and "> $statusStr$ </isNotNull> </isPropertyAvailable> <isPropertyAvailable property="marketIdList"> <isNotNull property="marketIdList" prepend=" and marketId in "> <iterate property="marketIdList" conjunction="," close=")" open="("> #marketIdList[]# </iterate> </isNotNull> </isPropertyAvailable> </dynamic> </select>
这里需要注意的是:
①#xxx# 代表xxx是属性值,map里面的key或者是你的pojo对象里面的属性,ibatis会自动在它的外面加上引号,表现在sql语句是这样的 where xxx = 'xxx' ;
而$xxxx$ 则是把xxxx作为字符串拼接到你的sql语句中,比如 order by topicId , 如果你不用$来拼接而用#的话,外面就会被加上引号的哦 比如你的语句这样写 ... order by #xxx# (xxx就是你传进来的字符串topicId),ibatis 就会把他翻译成 order by 'topicId' 这样就报错了 ,用$的结果就是这样 order by topicId
②这里的iterate
- <isPropertyAvailable property="marketIdList">
- <isNotNull property="marketIdList" prepend=" and marketId in ">
- <iterate property="marketIdList" conjunction="," close=")" open="(">
- #marketIdList[]#
- </iterate>
- </isNotNull>
- </isPropertyAvailable>
<isPropertyAvailable property="marketIdList">
<isNotNull property="marketIdList" prepend=" and marketId in ">
<iterate property="marketIdList" conjunction="," close=")" open="(">
#marketIdList[]#
</iterate>
</isNotNull>
</isPropertyAvailable>
注意 iterate 的property属性 ,虽然你上面的isNotNull什么的都有这句,但这里一定要写清楚,否则ibatis会找不到你的list的
数据访问层代码:
- public List<Topic> getTopics(Map<String, Object> map) {
- return getSqlMapClientTemplate().queryForList("getTopics", map);
- }
public List<Topic> getTopics(Map<String, Object> map) {
return getSqlMapClientTemplate().queryForList("getTopics", map);
}
服务层代码:
- public List<Topic> getTopicsByMarketIdList(Long authorId,List<Long> marketIdList,
- Integer orderby, Integer status, Pagination pagination) {
- Map<String, Object> map = new HashMap<String, Object>();
- map.put("authorId", authorId);
- map.put("isDelete", false);
- map.put("marketIdList", marketIdList);
- map.put("orderStr", "这里你组装你的order字符串");
- map.put("statusStr","这里你组装你的status字符串");
- map.put("begin", pagination.getOffset());
- map.put("max", pagination.getPageSize());
- //这个getTopicCount()方法和getTopics()大体是一致的,所以我的dao里面省略了它
- Long total = topicDao.getTopicCount(map);
- if (total == 0) {
- return new ArrayList<Topic>();
- } else {
- pagination.setTotal(total);
- List<Topic> res = topicDao.getTopics(map);
- return res;
- }
- }
public List<Topic> getTopicsByMarketIdList(Long authorId,List<Long> marketIdList,
Integer orderby, Integer status, Pagination pagination) {
Map<String, Object> map = new HashMap<String, Object>();
map.put("authorId", authorId);
map.put("isDelete", false);
map.put("marketIdList", marketIdList);
map.put("orderStr", "这里你组装你的order字符串");
map.put("statusStr","这里你组装你的status字符串");
map.put("begin", pagination.getOffset());
map.put("max", pagination.getPageSize());
//这个getTopicCount()方法和getTopics()大体是一致的,所以我的dao里面省略了它
Long total = topicDao.getTopicCount(map);
if (total == 0) {
return new ArrayList<Topic>();
} else {
pagination.setTotal(total);
List<Topic> res = topicDao.getTopics(map);
return res;
}
}
- public class Topic extends BaseObject implements Serializable {
- /**
- *
- */
- private static final long serialVersionUID = -851973667810710701L;
- private Long id;
- private Long authorId;
- private String authorName;
- private Long marketId;
- private String title;
- private String tags;
- private String content;
- private Date pubdate;
- private Integer isBest;
- private Integer status;
- private Integer isDelete;
- private Integer clickCount;
- private Integer replyCount;
- private Date lastReplyTime;
- //getter and setter 省略...
- }
public class Topic extends BaseObject implements Serializable {
/**
*
*/
private static final long serialVersionUID = -851973667810710701L;
private Long id;
private Long authorId;
private String authorName;
private Long marketId;
private String title;
private String tags;
private String content;
private Date pubdate;
private Integer isBest;
private Integer status;
private Integer isDelete;
private Integer clickCount;
private Integer replyCount;
private Date lastReplyTime;
//getter and setter 省略...
}
Pagination代码:
- public class Pagination {
- /**
- * 要查看的页码
- */
- private int page;
- /**
- * 每页显示数
- */
- private int pageSize;
- /**
- * 一共有多少页
- */
- private int totalPage;
- /**
- * 一共有多少条记录
- */
- private long total;
- /**
- * 当前页的记录数
- */
- private int size;
- /**
- * 只需要topxx,不需要页数信息了
- */
- private boolean topOnly;
- /**
- *从第几条记录开始
- */
- private int offset;
- public void setOffset(int offset) {
- this.offset = offset;
- }
- public Pagination(int page, int pageSize) {
- this.page = page;
- this.pageSize = pageSize;
- }
- public Pagination() {
- }
- public boolean require() {
- return pageSize > 0 ? true : false;
- }
- public int from() {
- return page * pageSize;
- }
- public int to() {
- return from() + size;
- }
- public int getPage() {
- return page;
- }
- public void setPage(int page) {
- this.page = page;
- }
- public int getPageSize() {
- return pageSize;
- }
- public void setPageSize(int pageSize) {
- this.pageSize = pageSize;
- }
- public int getTotalPage() {
- return totalPage;
- }
- public void setTotalPage(int totalPage) {
- this.totalPage = totalPage;
- }
- public long getTotal() {
- return total;
- }
- public void setTotal(long total) {
- this.total = total;
- if (pageSize > 0) {
- this.totalPage = (int) Math.ceil(total / (double) pageSize);
- } else {
- this.totalPage = 1;
- }
- if (page >= totalPage) {
- page = totalPage - 1;
- }
- if (page < 0)
- page = 0;
- if (pageSize > 0) {
- if (page < totalPage - 1)
- this.size = pageSize;
- else
- this.size = (int) (total % pageSize);
- } else