实例说明ibatis动态查询

最近做了很多动态的查询,尤其是排序,以及一些状态字段,所以就做了一个总的动态查询,以不变应万变,呵呵

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 List getTopics(Map map) {   
  
        return getSqlMapClientTemplate().queryForList("getTopics", map);   
    }  

ibatis动态查询中的服务层代码:

Java代码

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

Java代码

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

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动态查询实例，您在实际工作中遇到相应的ibatis动态查询问题可以参考一下。

【编辑推荐】

1. ibatis标签详解
2. ibatis插件的安装方式
3. ibatis下加入c3p0连接池的方法
4. ibatis也能用proxool连接池
5. 避免ibatisN+1查询的方法