实例说明ibatis动态查询
最近做了很多动态的查询,尤其是排序,以及一些状态字段,所以就做了一个总的动态查询,以不变应万变,呵呵
ibatis动态查询里面的sql代码:
ibatis动态查询Xml代码：
?
?
<select id="getTopics" resultparameterWHERE "><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" resultWHERE "><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>ibatis动态查询Xml代码：<select id="getTopics" resultparameterWHERE "><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" resultWHERE "><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。
另外在ibatis动态查询里要注意它的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的，ibatis动态查询自然无法实现。
ibatis动态查询中的数据访问层代码:
public List getTopics(Map map) {return getSqlMapClientTemplate().queryForList("getTopics", map);}?
?
?
ibatis动态查询中的服务层代码:
public List getTopicsByMarketIdList(Long authorId, List marketIdList,Integer orderby, Integer status, Pagination pagination) {Map map = new HashMap();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();} else {pagination.setTotal(total);List res = topicDao.getTopics(map);return res;}}?
?
public List getTopicsByMarketIdList(Long authorId, List marketIdList,Integer orderby, Integer status, Pagination pagination) {Map map = new HashMap();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();} else {pagination.setTotal(total);List 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 省略...}Java代码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 省略...} ibatis动态查询中的Pagination代码:Java代码：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); } ?
?
虽然代码量有些大，但是这是一个总的ibatis动态查询实例，您在实际工作中遇到相应的ibatis动态查询问题可以参考一下。
?
转自http://developer.51cto.com/art/200907/136553.htm