最近做了很多动态的查询,尤其是排序,以及一些状态字段,所以就做了一个总的动态查询,以不变应万变,呵呵
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 "> <iterate property="marketIdList" conjunction="," close=")" open=" marketId in ("> #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>注意 iterate 的property属性 ,虽然你上面的isNotNull什么的都有这句,但这里一定要写清楚,否则ibatis会找不到你的list的
数据访问层代码:
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 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 { this.size = (int) total; } offset=page * pageSize; } public int getOffset() { return offset; } public int getSize() { return size; } public void setSize(int size) { this.size = size; } public boolean isTopOnly() { return topOnly; } public void setTopOnly(boolean topOnly) { this.topOnly = topOnly; } } 相关资源：敏捷开发V1.0.pptx