您现在的位置是：首页 > 个人日记个人日记

mybatis-05-动态SQL

2019-02-02 17:27:37【个人日记】484人已围观

简介主要是mybatis提供的一些标签,可以处理一些复杂SQL,使用起来方便

动态SQL

where...if...

    <select id="findList" parameterType="java.util.Map" resultType="com.huangxin.model.ProductCategory">
        SELECT * FROM product
        <!--WHERE标签可以去掉查询条件中的AND关键词-->
        <!--如果只有一个条件则去掉AND,否则只去掉第一个-->
        <where>
            <if test="productName!=null and productName!=''">
                AND productName LIKE '%${productName}%'
            </if>
            <if test="brand!=null and brand!=''">
                AND brand = #{brand}
            </if>
            <if test="supplier!=null and supplier!=''">
                AND supplier = #{supplier}
            </if>
             <if test="endTime!=null and startTime!=null">
                AND BETWEEN endTime = #{endTime} AND startTime = #{startTime}
            </if>
        </where>
    </select>

接口代码

    /**
     * 动态多条件查询
     *
     * @param param
     * @return
     */
    List<ProductCategory> findList(Map<String, Object> param);

可以动态改变SQL语句

   @Test
    public void findListl() {
        Map<String,Object> param = new HashMap<String, Object>();
//      param.put("productName","耳机");
        param.put("brand","未知");
        SqlSession sqlSession = factory.openSession();
        //通过mybatis的动态代理机制,生成了一个ProductCategory接口实现类
        ProductCategoryMapper mapper = sqlSession.getMapper(ProductCategoryMapper.class);
        System.out.println(mapper.findList(param));
    }

控制台输出

2018-12-29 12:04:13 [DEBUG] PooledDataSource forcefully closed/removed all connections. 
2018-12-29 12:04:13 [DEBUG] PooledDataSource forcefully closed/removed all connections. 
2018-12-29 12:04:13 [DEBUG] PooledDataSource forcefully closed/removed all connections. 
2018-12-29 12:04:13 [DEBUG] PooledDataSource forcefully closed/removed all connections. 
2018-12-29 12:04:13 [DEBUG] Opening JDBC Connection 
2018-12-29 12:04:13 [DEBUG] Created connection 1824837049. 
2018-12-29 12:04:13 [DEBUG] Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@6cc4cdb9] 
2018-12-29 12:04:13 [DEBUG] >  Preparing: SELECT * FROM product WHERE brand = ?  
2018-12-29 12:04:13 [DEBUG] > Parameters: 未知(String) 
2018-12-29 12:04:13 [DEBUG] <      Total: 7

以上SQL中有的数据才会拼接,没有则查询全部

查询数据形参为Map类型

SQL片段

解决代码重复,提高复用性

 <sql id="repeat"><!--ID则是引入时的唯一标志-->
        <if test="productName!=null and productName!=''">
            AND productName LIKE '%${productName}%'
        </if>
        <if test="brand!=null and brand!=''">
            AND brand = #{brand}
        </if>
        <if test="supplier!=null and supplier!=''">
            AND supplier = #{supplier}
        </if>
    </sql>

下面为引入之后的代码

    <!--通过条件查询条数-->
    <select id="findCount" parameterType="java.util.Map" resultType="java.lang.Long">
        SELECT COUNT (*) FROM product
        <where>
        <!--refid中填入SQL片段的ID-->
        <include refid="repeat"></include>
        </where>
    </select>
     <!--动态查询,使用where...if...-->
    <select id="findList" parameterType="java.util.Map" resultType="com.huangxin.model.ProductCategory">
        SELECT * FROM product
        <!--WHERE 标签可以去掉查询条件中的and关键词-->
        <where>
            <include refid="repeat"></include>
        </where>
    </select>

不能引入where

foreach遍历传入sql

1) collection表示传入集合名称
2) item表示每次遍历生成的对象
3) open表示开始遍历时的拼接
4) separator表示遍历用什么分割
5) close表示结束时的拼接

 <select id="findByIds" resultType="com.huangxin.model.ProductCategory" parameterType="java.util.List">
        <foreach 
        <!--使用collection前记得命名-->
        collection="ids" 
        item="id" 
        open="IN (" 
        separator="," 
        close=")">
            <!--这里给括号中赋值,id则是item起的名字(列名)-->
        id=#{id}
        </foreach>
    </select>

接口中添加方法

public interface ProductCategoryMapper {
    /**
     * 通过多个id查询数据
     *
     * @param list
     * @return
     */
     //一定要命名
    List<ProductCategory> findByIds(@Param("ids") List<Long> list);