實(shí)例說(shuō)明ibatis動(dòng)態(tài)查詢
最近做了很多動(dòng)態(tài)的查詢,尤其是排序,以及一些狀態(tài)字段,所以就做了一個(gè)總的動(dòng)態(tài)查詢,以不變應(yīng)萬(wàn)變,呵呵
ibatis動(dòng)態(tài)查詢里面的sql代碼:
ibatis動(dòng)態(tài)查詢Xml代碼：
<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>
ibatis動(dòng)態(tài)查詢Xml代碼：
<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對(duì)象里面的屬性,ibatis會(huì)自動(dòng)在它的外面加上引號(hào),表現(xiàn)在sql語(yǔ)句是這樣的 where xxx = 'xxx' ;而$xxxx$ 則是把xxxx作為字符串拼接到你的sql語(yǔ)句中,比如 order by topicId , 如果你不用$來(lái)拼接而用#的話,外面就會(huì)被加上引號(hào)的哦 比如你的語(yǔ)句這樣寫 ... order by #xxx# (xxx就是你傳進(jìn)來(lái)的字符串topicId),ibatis 就會(huì)把他翻譯成 order by 'topicId' 這樣就報(bào)錯(cuò)了，用$的結(jié)果就是這樣 order by topicId。
另外在ibatis動(dòng)態(tài)查詢里要注意它的iterate。
Java代碼
<isPropertyAvailable property="marketIdList"> 注意 iterate 的property屬性 ,雖然你上面的isNotNull什么的都有這句,但這里一定要寫清楚,否則ibatis會(huì)找不到你的list的，ibatis動(dòng)態(tài)查詢自然無(wú)法實(shí)現(xiàn)。 ibatis動(dòng)態(tài)查詢中的數(shù)據(jù)訪問(wèn)層代碼: Java代碼 public List ibatis動(dòng)態(tài)查詢中的服務(wù)層代碼: Java代碼 public List Java代碼 public List Java代碼 public class Topic extends BaseObject implements Serializable { Java代碼 public class Topic extends BaseObject implements Serializable { ibatis動(dòng)態(tài)查詢中的Pagination代碼: Java代碼： public class Pagination { 雖然代碼量有些大，但是這是一個(gè)總的ibatis動(dòng)態(tài)查詢實(shí)例，您在實(shí)際工作中遇到相應(yīng)的ibatis動(dòng)態(tài)查詢問(wèn)題可以參考一下。 【編輯推薦】
<isNotNull property="marketIdList" prepend=" and marketId in ">
<iterate property="marketIdList" conjunction="," close=")" open="(">
#marketIdList[]#
</iterate>
</isNotNull>
</isPropertyAvailable>
return getSqlMapClientTemplate().queryForList("getTopics", map);
}
Integer orderby, Integer status, Pagination pagination) {
Map
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());
//這個(gè)getTopicCount()方法和getTopics()大體是一致的,所以我的dao里面省略了它
Long total = topicDao.getTopicCount(map);
if (total == 0) {
return new ArrayList
} else {
pagination.setTotal(total);
List
return res;
}
}
Integer orderby, Integer status, Pagination pagination) {
Map
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());
//這個(gè)getTopicCount()方法和getTopics()大體是一致的,所以我的dao里面省略了它
Long total = topicDao.getTopicCount(map);
if (total == 0) {
return new ArrayList
} else {
pagination.setTotal(total);
List
return res;
}
}
/**
*
*/
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 省略...
}
/**
*
*/
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 省略...
}
/**
* 要查看的頁(yè)碼
*/
private int page;
/**
* 每頁(yè)顯示數(shù)
*/
private int pageSize;
/**
* 一共有多少頁(yè)
*/
private int totalPage;
/**
* 一共有多少條記錄
*/
private long total;
/**
* 當(dāng)前頁(yè)的記錄數(shù)
*/
private int size;
/**
* 只需要topxx，不需要頁(yè)數(shù)信息了
*/
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
