java web 之mybatis使用教程(四)

动态SQL语句

条件查询：

<!-- 查询功能，parameterType 设置参数类型，resultType 设置返回值类型 -->
    <select id="findAllByCondition" parameterType="com.cx.pojo.User" resultType="User">
        SELECT id,name,age FROM user
        <where>
            <if test="name =! null">
                name = #{name}
            </if>
            <if test="age =! null">
                and age = #{age}
            </if>
        </where>
    </select>

条件更新：

<!-- 修改功能 -->
    <update id="updateByCondition" parameterType="User">
        UPDATE user
        <set>
            <if test="name =! null">
                name = #{name},
            </if>
            <if test="age =! null">
                age=#{age},
            </if>
        </set>
        WHERE id = #{id}
    </update>

删除功能：

删除数组，删除list，删除map

<!-- 批量删除，Array 类型 -->
    <delete id="deleteArray" parameterType="integer">
        DELETE FROM user WHEN id IN
        <foreach collection="array" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </delete>

    <!-- 批量删除，List 类型 -->
    <delete id="deleteList" parameterType="integer">
        DELETE FROM user WHEN id IN
        <foreach collection="list" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </delete>

    <!-- 批量删除，Map 类型 -->
    <delete id="deleteMap" parameterType="Map">
        DELETE FROM user WHERE id IN
        <foreach collection="ids" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
        AND  age = #{age}
    </delete>

Test代码如下：

package com.cx.test;

import com.cx.pojo.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.*;

import java.io.IOException;
import java.io.Reader;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class UserTest {

    @BeforeClass
    public static void globalInit() {  // 在所有方法执行之前执行
        System.out.println("@BeforeClass标注的方法，在所有方法执行之前执行...");
    }

    @AfterClass
    public static void globalDestory() {  // 在所有方法执行之后执行
        System.out.println("@AfterClass标注的方法，在所有方法执行之后执行...");
    }

    @Before
    public void setUp() {  // 在每个测试方法之前执行
        System.out.println("@Before标注的方法，在每个测试方法之前执行...");
    }

    @After
    public void tearDown() {  // 在每个测试方法之后执行
        System.out.println("@After标注的方法，在每个测试方法之后执行...");
    }

    @Test
    public void testFindById() {
        String resource ="mybatis-config.xml";
        Reader reader = null;
        try {
            reader = Resources.getResourceAsReader(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            SqlSession session = sqlSessionFactory.openSession();
            User user = session.selectOne("findById",2);
            session.commit();
            System.out.println(user.getName());
        }catch (IOException e){
            e.printStackTrace();
        }
    }

    @Test
    public void testFindAll() {
        String resource ="mybatis-config.xml";
        Reader reader = null;
        try {
            reader = Resources.getResourceAsReader(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            SqlSession session = sqlSessionFactory.openSession();

           List<User> list =  session.selectList("findAll");

            session.commit();
            System.out.println("list.size=======" + list.size());
        }catch (IOException e){
            e.printStackTrace();
        }
    }

    @Test
    public void testFindAllByCondition() {
        String resource ="mybatis-config.xml";
        Reader reader = null;
        try {
            reader = Resources.getResourceAsReader(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            SqlSession session = sqlSessionFactory.openSession();

            User user = new User();
            user.setAge(12);
            List<User> list =  session.selectList("findAllByCondition",user);

            session.commit();
            System.out.println("size=====" + list.size());
            for (User u : list) {
                System.out.println(u.getName());
            }
        }catch (IOException e){
            e.printStackTrace();
        }
    }

    @Test
    public void testInsert(){

        String resource ="mybatis-config.xml";
        Reader reader = null;
        try {
            reader = Resources.getResourceAsReader(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            SqlSession session = sqlSessionFactory.openSession();
            User user = new User();
            user.setId(5);
            user.setName("gavin");
            user.setAge(12);
            session.insert("insert", user);
            session.commit();
        }catch (IOException e){
            e.printStackTrace();
        }
    }


    @Test
    public void testUpdate(){

        String resource ="mybatis-config.xml";
        Reader reader = null;
        try {
            reader = Resources.getResourceAsReader(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            SqlSession session = sqlSessionFactory.openSession();

            User user = session.selectOne("findById",2);
            user.setName("adadf");
            user.setAge(12);
            session.insert("update", user);

            session.commit();
        }catch (IOException e){
            e.printStackTrace();
        }
    }

    @Test
    public void testUpdateByCondition(){

        String resource ="mybatis-config.xml";
        Reader reader = null;
        try {
            reader = Resources.getResourceAsReader(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            SqlSession session = sqlSessionFactory.openSession();

            User user = new User();
            user.setId(2);
            user.setName("teststeset");
            user.setAge(12);
            session.insert("updateByCondition", user);

            session.commit();
        }catch (IOException e){
            e.printStackTrace();
        }
    }

    @Test
    public void testDelete(){

        String resource ="mybatis-config.xml";
        Reader reader = null;
        try {
            reader = Resources.getResourceAsReader(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            SqlSession session = sqlSessionFactory.openSession();

            session.delete("deleteById",5);

            session.commit();
        }catch (IOException e){
            e.printStackTrace();
        }
    }

    @Test
    public void testDeleteArray(){

        String resource ="mybatis-config.xml";
        Reader reader = null;
        try {
            reader = Resources.getResourceAsReader(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            SqlSession session = sqlSessionFactory.openSession();

            session.delete("deleteArray",new Integer[]{1,2,3});

            session.commit();
        }catch (IOException e){
            e.printStackTrace();
        }
    }

    @Test
    public void testDeleteList(){

        String resource ="mybatis-config.xml";
        Reader reader = null;
        try {
            reader = Resources.getResourceAsReader(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            SqlSession session = sqlSessionFactory.openSession();


            List<Integer> uList = new ArrayList<Integer>();

            uList.add(2);
            uList.add(3);
            uList.add(4);
            session.delete("deleteList",uList);

            session.commit();
        }catch (IOException e){
            e.printStackTrace();
        }
    }

    @Test
    public void testDeleteMap(){

        String resource ="mybatis-config.xml";
        Reader reader = null;
        try {
            reader = Resources.getResourceAsReader(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            SqlSession session = sqlSessionFactory.openSession();


            Map<String, Object> map = new HashMap<String, Object>();

            map.put("ids", new Integer[]{2, 3, 4});
            map.put("age",18);
            session.delete("deleteMap",map);

            session.commit();
        }catch (IOException e){
            e.printStackTrace();
        }
    }
}

总结

在使用动态 SQL 语句的时候，咱们需要多注意以下几点：

1. 通过 if 标签来判断字段是否为空，如果为空，则默认不参与到 SQL 语句中，并且可以自动省略逗号；
2. 通过 where 标签来输出条件完成判断，其可以自动省略多余的 and 和 逗号；
3. 通过 set 标签来完成修改操作，当字段值为 null 时，其不参与到 SQL 语句中；
4. 在 foreach 标签中，collection 属性表示传入的参数集合， item 表示每个元素变量的名字，open 表示开始字符，close 表示结束字符，separator 表示分隔符；
5. 任何参数都可以封装到 Map 中，其以 key 来取值。