十二、动态SQL

电商平台:淘宝、京东

筛选商品

用户的筛选条件不同，完成筛选执行的SQL也不一样，我们可以一一列举不同条件的筛选，但是这种筛选过于繁琐，一旦筛选条件过多，那么组合方式也会很多，MyBatis就提供了动态SQL的配置方式来实现这种多条件查询

根据查询条件动态完成SQL的拼接

案例：查询自己喜欢的人

create table members(
	member_id int primary key auto_increment,
    member_nick varchar(20) not null,
    member_gender char(2) not null,
    member_age int not null,
    member_city varchar(30) not null
);

@Data
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class Member {
    private int memberId;
    private String memberNick;
    private String memberGender;
    private int memberAge;
    private String memberCity;
}

在dao接口中定义一个多条件查询的方法

    
    
    
    
    


    select member_id,member_nick,member_gender,member_age,member_city
    from members   
      
      
        and member_gender=#{gender}
    

      
        and member_age ">>= #{minAge} 
    

      
        and member_age <= #{maxAge}   
    

      
        and member_city = #{city}
    
    

  
  
    and member_gender=#{gender}


  
    and member_age ">>= #{minAge} 


  
    and member_age <= #{maxAge}   


  
    and member_city = #{city}


order by member_age  

  
    and member_gender=#{gender}


  
    and member_age ">>= #{minAge} 


  
    and member_age <= #{maxAge}   


  
    and member_city = #{city}

    select member_id,member_nick,member_gender,member_age,member_city
    from members  where member_city in
    
        #{cityName}