ibatis怎么動態查詢里面的sql代碼

這篇文章主要講解了“ibatis怎么動態查詢里面的sql代碼”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“ibatis怎么動態查詢里面的sql代碼”吧！

ibatis動態查詢里面的sql代碼:

ibatis動態查詢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動態查詢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對象里面的屬性,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。

Java代碼

<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動態查詢中的數據訪問層代碼:

Java代碼

public ListgetTopics(Mapmap) {   

return getSqlMapClientTemplate().queryForList("getTopics", map);   
}  

ibatis動態查詢中的服務層代碼:

Java代碼

public ListgetTopicsByMarketIdList(Long authorId,ListmarketIdList,   
Integer orderby, Integer status, Pagination pagination) {   
Mapmap = 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);   
Listres = topicDao.getTopics(map);   
return res;   
}   
}

Java代碼

public ListgetTopicsByMarketIdList(Long authorId,ListmarketIdList,  
Integer orderby, Integer status, Pagination pagination) {  
Mapmap = 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);  
Listres = topicDao.getTopics(map);  
return res;  
}  
}

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 省略...   
}

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);   
} else

感謝各位的閱讀，以上就是“ibatis怎么動態查詢里面的sql代碼”的內容了，經過本文的學習后，相信大家對ibatis怎么動態查詢里面的sql代碼這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！