动态sql语句的几种方法

一、动态sql语句

1.if 语句
2. where 语句
3.choose when otherwise 等价于switch
3. trim 语句
4. for 语句 只能对数组，集合list,集合set,集合map
（1）测试数据库

DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `money` double DEFAULT NULL,
  `isdeleted` tinyint(4) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
)

if 条件判断+where

<select id="findByConditionWithIf" resultType="com.ykq.entity.Account">
         select * from account
        <where>
             <if test="name!=null and name!=''">
                 and name like concat('%',#{name},'%')
             </if>
             <if test="isdeleted!=null">
                  and isdeleted=#{isdeleted}
             </if>
        </where>
    </select>

choose+when+otherwise

<select id="findByConditionWithChoose" resultType="com.ykq.entity.Account">
         select * from account
         <where>
             <choose>
                 <when test="name!=null and name!=''">
                     and name like concat('%',#{name},'%')
                 </when>
                 <when test="isdeleted!=null">
                     and isdeleted=#{isdeleted}
                 </when>
                 <otherwise>
                     <![CDATA[and money <1000 ]]>
                 </otherwise>
             </choose>
         </where>
     </select>

trim

 <select id="findByConditionWithChoose" resultType="com.ykq.entity.Account">
          select * from account
                 <trim prefix="where" prefixOverrides="or|and" >
                       <choose>
                           <when test="name!=null and name!=''">
                                and name like concat('%',#{name},'%')
                           </when>
                           <when test="isdeleted!=null">
                               and isdeleted=#{isdeleted}
                           </when>
                           <otherwise>
                             <![CDATA[or money <1000 ]]>
                           </otherwise>
                       </choose>
                 </trim>
    </select>

foreach

<select id="findByConditionWithFor" resultType="com.ykq.entity.Account">
         select * from account
         <where>
              <if test="ids!=null and ids.length>0">
                  id in
                  <foreach collection="ids" open="(" close=")" separator="," item="id">
                      #{id}
                  </foreach>
              </if>
         </where>

    </select>

测试

public class text {
    public static void main(String[] args) throws Exception {
        Reader resourceAsReader = Resources.getResourceAsReader("batis.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsReader);
        SqlSession sqlSession = build.openSession();
        AccoundDao mapper = sqlSession.getMapper(AccoundDao.class);
        List<Accound> accounds = mapper.naMe1("小", 1);
        System.out.println(accounds);

    }
}