MyBatis之动态SQL

最新推荐文章于 2025-04-07 22:20:39 发布

sanju_sanju

最新推荐文章于 2025-04-07 22:20:39 发布

阅读量148

点赞数

if 标签

<mapper namespace="com.how2java.pojo">
        <select id="listProduct" resultType="Product">
            select * from product_
            <if test="name!=null">
                where name like concat('%',#{name},'%')
            </if>        
        </select>

    </mapper>

Test类

public class TestMybatis {
    public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();

        System.out.println("查询所有的");
        List<Product> ps = session.selectList("listProduct");
        for (Product p : ps) {
            System.out.println(p);
        }

        System.out.println("模糊查询");
        Map<String,Object> params = new HashMap<>();
        params.put("name","a");
        List<Product> ps2 = session.selectList("listProduct",params);
        for (Product p : ps2) {
            System.out.println(p);
        }      

        session.commit();
        session.close();

    }
}

where 标签
如果要进行多条件判断，就会写成这样：

select * from product_

where name like concat(‘%’,#{name},’%’)

and price > #{price}

这么写问题是：当没有name参数，却有price参数的时候，执行的sql语句就会是：
select * from product_ and price > 10.
这样执行就会报错

标签会进行自动判断
如果任何条件都不成立，那么就在sql语句里就不会出现where关键字
如果有任何条件成立，会自动去掉多出来的 and 或者 or。

<mapper namespace="com.how2java.pojo">
    <select id="listProduct" resultType="Product">
        select * from product_
        <where>
            <if test="name!=null">
                and name like concat('%',#{name},'%')
            </if>        
            <if test="price!=null and price!=0">
                and price > #{price}
            </if>
        </where>     
    </select>  
    </mapper>

Test类

public class TestMybatis {
    public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();

        System.out.println("多条件查询");
        Map<String,Object> params = new HashMap<>();
//        params.put("name","a");
        params.put("price","10");
        List<Product> ps2 = session.selectList("listProduct",params);
        for (Product p : ps2) {
            System.out.println(p);
        }      

        session.commit();
        session.close();
    }
}

set 标签
与where标签类似，在update语句里也会碰到多个字段相关的问题。在这种情况下，就可以使用set标签。

<update id="updateProduct" parameterType="Product" >
        update product_
        <set>
            <if test="name != null">name=#{name},</if>
            <if test="price != null">price=#{price}</if>    
        </set>
         where id=#{id}   
    </update>

Test类

 public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();

        Product p = new Product();
        p.setId(6);
        p.setName("product zz");
        p.setPrice(99.99f);
        session.update("updateProduct",p);

        listAll(session);

        session.commit();
        session.close();
    }

    private static void listAll(SqlSession session) {
        Map<String,Object> params = new HashMap<>();
        List<Product> ps2 = session.selectList("listProduct",params);
        for (Product p : ps2) {
            System.out.println(p);
        }
    }
}

这里写图片描述

trim 标签
trim 用来定制想要的功能，比如where标签就可以用如下来替换

…

set标签就可以用如下来替换

…

<select id="listProduct" resultType="Product">
        select * from product_
        <trim prefix="WHERE" prefixOverrides="AND |OR ">
            <if test="name!=null">
                and name like concat('%',#{name},'%')
            </if>        
            <if test="price!=null and price!=0">
                and price > #{price}
            </if>
        </trim>      
    </select>

    <update id="updateProduct" parameterType="Product" >
        update product_
        <trim prefix="SET" suffixOverrides=",">
            <if test="name != null">name=#{name},</if>
            <if test="price != null">price=#{price}</if>        
        </trim>
         where id=#{id}   
    </update>

choose 里 when otherwise 标签
Mybatis里面没有else标签，但是可以使用when otherwise标签来达到这样的效果。

<select id="listProduct" resultType="Product">
              SELECT * FROM product_
              <where>
                <choose>
                  <when test="name != null">
                    and name like concat('%',#{name},'%')
                  </when>          
                  <when test="price !=null and price != 0">
                    and price > #{price}
                  </when>                
                  <otherwise>
                    and id >1
                  </otherwise>
                </choose>
              </where>
        </select>

Test类

 public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();

      Map<String,Object> params = new HashMap<>();
      List<Product> ps = session.selectList("listProduct",params);
      for (Product p : ps) {
          System.out.println(p);
      }
        session.commit();
        session.close();

    }

for each 标签
功能类似于：in
select * from hr.employees where salary in(3000,6000)
即薪水要么等于3000要么等于6000

separator分隔
<select id="listProduct" resultType="Product">
          SELECT * FROM product_
            WHERE ID in
                <foreach item="item" index="index" collection="list"
                    open="(" separator="," close=")">
                    #{item}
                </foreach>
    </select>

Test类

public class TestMybatis {
    public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();

        List<Integer> ids = new ArrayList();
        ids.add(1);
        ids.add(3);
        ids.add(5);
      List<Product> ps = session.selectList("listProduct",ids);
      for (Product p : ps) {
          System.out.println(p);
      }
         session.commit();
        session.close();
    }

如图查询出id等于1,3,5的数据出来
这里写图片描述

bind 标签
bind标签就像是再做一次字符串拼接，方便后续使用
如本例，在模糊查询的基础上，把模糊查询改为bind标签。

<mapper namespace="com.how2java.pojo">
        <!-- 本来的模糊查询方式 -->
<!--         <select id="listProduct" resultType="Product"> -->
<!--             select * from   product_  where name like concat('%',#{0},'%') -->
<!--         </select> -->

        <select id="listProduct" resultType="Product">
            <bind name="likename" value="'%' + name + '%'" />
            select * from   product_  where name like #{likename}
        </select>
    </mapper>

Test类

public class TestMybatis {
    public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();

        Map<String, String> params =new HashMap();
        params.put("name", "product");

  List<Product> ps = session.selectList("listProduct",params);
          for (Product p : ps) {
              System.out.println(p);
          }
        session.commit();
        session.close();
    }
}