Mybatis-多条件查询和模糊查询

多条条件查询
sql语句：

 <select id="findByany" parameterType="String" resultMap="cuResultMap">
select *from customer where 1=1
        <choose>
            <when test="username !=null and username !='' ">
                and username like concat('%',#{username},'%')
            </when>
            <when test="jobs !=null and jobs !=''">
                and jobs=#{jobs}
            </when>
            <otherwise>
                and phone is not null
            </otherwise>
        </choose>
    </select>

测试类：

public void Test4()throws  Exception{
    MybatisUtil mybatisUtil=new MybatisUtil();
    SqlSession sqlSession=mybatisUtil.getSqlSession();
    CustomerMapper customerMapper=sqlSession.getMapper(CustomerMapper.class);
    Customer customer=new Customer();
    customer.setUsername("杨");
    customer.setJobs("老师");
    List<Customer> customers=customerMapper.findByany(customer);
    System.out.println("======Choose多条件查询=======");
    for(Customer cs:customers){
        System.out.println(cs);
    }
    sqlSession.close();
}

多条查询
sql语句：

<select id="foreachListByid" resultMap="cuResultMap">
    select *
    from customer
    where id in
    <foreach collection="list" item="item" index="index" open="(" separator="," close=")">
        #{item}
    </foreach>
</select>

<select id="foreachArrayByid" resultMap="cuResultMap">
    select  * from customer
    where id in
    <foreach collection="array" index="index" item="item" open="(" separator="," close=")">
        #{item}
    </foreach>
</select>
<!--    使用foreach元素，传多个参数类型-->
<select id="foreachMap" resultMap="cuResultMap">
    select * from customer where username like  concat('%',#{username},'%')
    and id in <foreach collection="idSet" item="item" open="(" separator="," close=")">
        #{item}
    </foreach>
</select>

测试类：

@Test
public void foreachTest1()throws  Exception{
    MybatisUtil mybatisUtil=new MybatisUtil();
    SqlSession sqlSession=mybatisUtil.getSqlSession();
    CustomerMapper customerMapper=sqlSession.getMapper(CustomerMapper.class);
    List<String> list=new ArrayList<>();
    list.add("1");
    list.add("4");
    list.add("42");
    List<Customer> customerList=customerMapper.foreachListByid(list);
    System.out.println("=======foreach List查询1=======");
    for(Customer cu:customerList){
        System.out.println(cu);
    }
}

模糊查询：
sqkl语句

<select id="bindSelect" parameterType="String" resultType="anhua">
    <bind name="bind_name" value="'%'+username+'%'"/>
    select *
     from customer
      where username like #{bind_name}
</select>

测试类：
@Test//模糊查询
public void bindTest()throws Exception{
MybatisUtil mybatisUtil=new MybatisUtil();
SqlSession sqlSession=mybatisUtil.getSqlSession();
CustomerMapper customerMapper=sqlSession.getMapper(CustomerMapper.class);
Customer customer=new Customer();
customer.setUsername(“李”);
List customerList=customerMapper.bindSelect(customer);
System.out.println("===模糊查询=");
for(Customer cu:customerList){
System.out.println(cu);
}
sqlSession.close();;
}
sql语句：

– 第一种写法

– 第二中写法
select *from customer
and username like concat (’%’,#{username},’%’)

– 第三中写法

</select>