MyBatis中的动态SQL,实现不确定条件的CRUD-----使用Map来传递查询的参数

约定不等于承诺〃 2022-07-13 01:12 56阅读 0赞

1、MyBatis中的动态SQL,实现不确定条件的CRUD的实现

将查询条件封装到map里面,通过 if 和 where 标签的使用,实现sql的动态性

2、动态SQL，实现不确定条件的查询

2.1  不确定条件查询的案列

![20161218145915931][]

2.2  不确定条件查询的实现

2.2.1  mapper接口

public interface UserMapper {  
        // 动态查询用户  
        public List<User> findUserByIdDongTaiSql(Map<String, Object> map);  
    }

2.2.2  mapper.xml  **<where> 和 <if> 标签的用法**

<select id="findUserByIdDongTaiSql" parameterType="map"  
        resultType="user">  
        select * from user  
        <where>  
            <if test="id!=null">  
                and id = #{id}  
            </if>  
            <if test="username!=null">  
                and username like '%${username}%'  
            </if>  
        </where>  
    </select>

2.2.3  测试代码

public class Mybatis_Four_Test {  
        // 会话工厂  
        private SqlSessionFactory sqlSessionFactory;  
      
        @Before  
        public void createSqlSessionFactory() throws IOException {  
            // 配置文件  
            String resource = "SqlMapConfig.xml";  
            InputStream inputStream = Resources.getResourceAsStream(resource);  
            // 使用SqlSessionFactoryBuilder从xml配置文件中创建SqlSessionFactory  
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);  
        }  
      
        // one list foreach  
        @Test  
        public void testfindAll() {  
            // 获取session  
            SqlSession session = sqlSessionFactory.openSession();  
            // 获取mapper接口的代理对象  
            UserMapper userMapper = session.getMapper(UserMapper.class);  
            HashMap<String, Object> map = new HashMap<String, Object>();  
            map.put("id", 16);  
            map.put("username", "小明");  
            List<User> users = userMapper.findUserByIdDongTaiSql(map);  
            for (int i = 0; i < users.size(); i++) {  
                System.out.println(users.get(i));  
            }  
            // 关闭session  
            session.close();  
        }  
    }

3、动态SQL，实现不确定条件的更新

3.1  不确定条件更新的案列

![20161218155947641][]

3.2  不确定条件更新的实现

3.2.1  mapper接口

// 动态更新用户  
    public void updateUserDongTaiSql(Map<String, Object> map);

3.2.2  mapper.xml

<update id="updateUserDongTaiSql" parameterType="map">  
         update user  
           
         <set>  
           <if test="id!=null">  
             username = #{username},  
           </if>  
           <if test="username!=null">  
             address = #{address},  
          </if>  
        </set>  
         where id = #{id}  
    </update>

3.2.3  测试代码

public void testupdateUserDongTaiSql() {  
    // 获取session  
    SqlSession session = sqlSessionFactory.openSession();  
    // 获取mapper接口的代理对象  
    UserMapper userMapper = session.getMapper(UserMapper.class);  
    HashMap<String, Object> map = new HashMap<String, Object>();  
    map.put("id", 29);  
    map.put("username", "张小三");  
    map.put("address", "浙江省");  
    userMapper.updateUserDongTaiSql(map);  
    session.commit();  
    // 关闭session  
    session.close();

4、动态SQL，实现不确定条件的删除

4.1  不确定条件删除的案列

![20161218160421802][]

4.2  不确定条件删除的实现

4.2.1  Array形式的实现

4.2.1.1  mapper接口

// 动态删除,Array的形式  
        public void deleteUserDongTaiSqlArray(int[] ids);

4.2.1.2  mapper.xml

<delete id="deleteUserDongTaiSqlArray" parameterType="map">  
        delete from user where id in  
        <foreach collection="array" open="(" close=")" separator=","  
            item="id">  
            #{id}  
        </foreach>  
    </delete>

4.2.1.3  测试代码

// 数组的形式  
        @Test  
        public void testdeleteUserDongTaiSqlArray() {  
            // 获取session  
            SqlSession session = sqlSessionFactory.openSession();  
            // 获取mapper接口的代理对象  
            UserMapper userMapper = session.getMapper(UserMapper.class);  
            int ids[] = { 26, 29 };  
            session.commit();  
            userMapper.deleteUserDongTaiSqlArray(ids);  
            // 提交  
            session.commit();  
            // 关闭session  
            session.close();  
        }

4.2.2  AList形式 的实现

4.2.2.1  mapper接口

// 动态删除用户,List的形式  
        public void deleteUserDongTaiSqlList(List<Integer> ids);

4.2.2.2  mapper.xml

<delete id="deleteUserDongTaiSqlList" parameterType="java.util.List">  
            delete from user where id in  
            <foreach collection="list" open="(" close=")" separator=","  
                item="id">  
                #{id}  
            </foreach>  
        </delete>

4.2.2.3  测试代码

// list的形式  
    @Test  
    public void testdeleteUserDongTaiSqlList() {  
        // 获取session  
        SqlSession session = sqlSessionFactory.openSession();  
        // 获取mapper接口的代理对象  
        UserMapper userMapper = session.getMapper(UserMapper.class);  
        List<Integer> list = Arrays.asList(34, 35, 36);  
        userMapper.deleteUserDongTaiSqlList(list);  
        session.commit();  
        // 关闭session  
        session.close();  
    }

5、动态SQL，实现不确定条件的插入

5.1  不确定条件查询的案列

![20161218160841465][]

5.2  不确定条件查询的实现

5.2.1  mapper接口

// 动态插入用户  
    public void insertUserDongTaiSql(User user);

5.2.2  mapper.xml

<sql id="key">  
            <trim suffixOverrides=",">  
                <if test="username!=null">  
                    username,  
                </if>  
                <if test="sex!=null">  
                    sex,  
                </if>  
            </trim>  
        </sql>  
          
        <sql id="value">  
            <trim suffixOverrides=",">  
                <if test="username!=null">  
                    #{username},  
                </if>  
                <if test="sex!=null">  
                    #{sex},  
                </if>  
            </trim>  
        </sql>  
          
        <insert id="insertUserDongTaiSql" parameterType="user">  
            insert into user(  
            <include refid="key" />  
            ) values(  
            <include refid="value" />  
            )  
        </insert>

5.2.3  测试代码

public void testinsertUserDongTaiSqlList() {  
            // 获取session  
            SqlSession session = sqlSessionFactory.openSession();  
            // 获取mapper接口的代理对象  
            UserMapper userMapper = session.getMapper(UserMapper.class);  
            User user = new User();  
            user.setAddress("广东釜山");  
            user.setSex("1");  
            user.setUsername("李小三");  
            userMapper.insertUserDongTaiSql(user);  
            session.commit();  
            // 关闭session  
            session.close();  
        }

[20161218145915931]: /images/20220713/322686a225ce41c49ca997ab3fcb66ab.png
[20161218155947641]: /images/20220713/0f22e5f245a7483b8e8be7cd156eb097.png
[20161218160421802]: /images/20220713/359bdd24e3fb4bd6994838a17ed979c5.png
[20161218160841465]: /images/20220713/ffb8ce611a274df0868828b458147b38.png