MyBatis从零开始-MyBatis动态SQL

最新推荐文章于 2022-11-11 10:10:12 发布

_天雨

最新推荐文章于 2022-11-11 10:10:12 发布

阅读量1.1k

点赞数 1

分类专栏： JAVA # MyBatis 框架文章标签： java mysql

本文链接：https://blog.csdn.net/qq_33369215/article/details/114238989

版权

JAVA 同时被 3 个专栏收录

41 篇文章 0 订阅

订阅专栏

MyBatis

9 篇文章 0 订阅

订阅专栏

框架

6 篇文章 0 订阅

订阅专栏

系列博客目录：MyBatis从零开始博客目录

4. MyBatis动态SQL

4.1 if标签使用

4.1.1 在SELECT中查询列使用if

if标签通常用于WHERE语句中，通过判断参数值来决定是否使用某个查询条件，他也经用在UPDATE和INSERT语句中。示例如下：

UserMapper.java

List<User> selectByUser(User user);

UserMapper.xml

<!-- 
  if标签有一个必填的属性test，test的属性值是一个符合OGNL要求的判断表达式，表达式的结果是true或者false，
  除此之外所有的非0值都为true，只有0为false。为了方便，在表达式中，建议只用true或者false作为结果。

  1.判断条件property!=null或者property==null:适用于任何类型的字段，用于判断属性为空值；
  2.判断条件property!=''或者property=='':只适用于String字符类型，用于判断空字符串；
  3.and和or:当有多个条件的时候，使用and或者or进行连接，嵌套的判断可以使用小括号分割
  -->
<select id="selectByUser" resultType="com.xiangty.bean.User">
    SELECT id, username, password
    FROM user
    WHERE 1=1 
    <if test="username != null and username != ''">
        and username like concat('%',#{username},'%')
    </if>
    <if test="password != null and password != ''">		
        and password = #{password}
    </if>
</select>

UserTest.java

@Test
public void selectByUser(){
    SqlSession sqlSession = SqlSessionUtil.getSqlSession();
    try{
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setUsername("管");
        user.setPassword("");
        System.out.println(userMapper.selectByUser(user));
    } finally {
        sqlSession.close();
    }
}

输出结果：
DEBUG [main] - ==>  Preparing: SELECT id, username, password FROM user WHERE 1=1 and username like concat('%',?,'%') 
DEBUG [main] - ==> Parameters: 管(String)
TRACE [main] - <==    Columns: id, username, password
TRACE [main] - <==        Row: 1, 管理员, 123456
DEBUG [main] - <==      Total: 1
[User [id=1, username=管理员, password=123456]]

4.1.2 在UPDATE更新列中使用if

数据库中的数据如下:
$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cTwf1jJY-1614513712685)(C:\Users\xiangty\AppData\Roaming\Typora\typora-user-images\1588494814815.png)]$
UserMapper.java

/**
 * 根据主键更新
 * @param user
 * @return
 */
int updateByIdSelective(User user);

UserMapper.xml

<insert id="updateByIdSelective">
    UPDATE user
    SET	
    <if test="username != null and username != ''">
        username = #{username},
    </if>
    <if test="password != null and password != ''">
        password = #{password},
    </if>
    id = #{id}
    WHERE id = #{id}
</insert>

UserMapper.xml文件中，updateByIdSelective方法中需要注意两个地方：

1.每个if元素里面的SQL语句后面都跟了一个逗号；

2.where关键字前面id=#{id}条件。

如果全部的查询条件都是null或者空，最终SQL如下：

update user set id=#{id} where id=#{id}

如果没有id=#{id}这个条件的话，最终的SQL如下：

update user set where id=#{id}，这条SQL明显是错的。

如果只有一个条件例如username非空，最终SQL如下：

update user set username = #{username} , id = #{id} where id = #{id}

如果没有id=#{id}这个条件，最终SQL如下：

update user set username = #{username}, where id = #{id}

where 关键字前面直接一个逗号，这个SQL语句是有问题。

从上述情况看，id=#{id}这个条件避免了方法不出错。

UserTest.java

@Test
public void updateByIdSelective() {
    SqlSession sqlSession = SqlSessionUtil.getSqlSession();
    try {
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setId(12);
        user.setUsername("demo");
        user.setPassword("demo");

        int result = userMapper.updateByIdSelective(user);
        sqlSession.commit();

        if (result > 0) {
            System.out.println("updateByIdSelective方法成功");
        } else {
            System.out.println("updateByIdSelective方法失败");
        }
    } catch (Exception e) {
        // 发生异常回滚事务
        sqlSession.rollback();
        e.printStackTrace();
    } finally {
        sqlSession.close();
    }
}

输出结果：
DEBUG [main] - ==>  Preparing: UPDATE user SET username = ?, password = ?, id = ? WHERE id = ? 
DEBUG [main] - ==> Parameters: demo(String), demo(String), 12(Integer), 12(Integer)
DEBUG [main] - <==    Updates: 1
updateByIdSelective方法成功

更新后的数据库信息如下:
在这里插入图片描述

4.1.3 在INSERT动态插入列中if

UserMapper.java

/**
 * 新增
 * @param user
 * @return
 */
int insert4(User user);

UserMapper.xml

<insert id="insert4" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO user(
    <if test="username != null and username != ''">username, </if>
    <if test="password != null and password != ''">password,</if>
    id)
    VALUES(
    <if test="username != null and username != ''">#{username}, </if>
    <if test="password != null and password != ''"> #{password},</if>
    #{id})
</insert>

UserTest.java

@Test
public void insert4() {
    SqlSession sqlSession = SqlSessionUtil.getSqlSession();
    try {
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setUsername("insert4");
        user.setPassword("insert4");
        int result = userMapper.insert4(user);

        sqlSession.commit();
        if (result > 0) {
            System.out.println("insert4方法添加的数据，返回的主键值为：" + user.getId());
        } else {
            System.out.println("添加失败");
        }
    } catch (Exception e) {
        // 发生异常回滚事务
        sqlSession.rollback();
        e.printStackTrace();
    } finally {
        sqlSession.close();
    }
}

输出结果：
DEBUG [main] - ==>  Preparing: INSERT INTO user( username, password, id) VALUES( ?, ?, ?) 
DEBUG [main] - ==> Parameters: insert4(String), insert4(String), null
DEBUG [main] - <==    Updates: 1
insert4方法添加的数据，返回的主键值为：13

4.2 choose标签使用

UserMapper.java

User selectByIdOrUsername(User user);

UserMapper.xml

<!-- 
  choose标签下包含when和otherwise两个标签，when标签至少存在一个，有0个或者1个otherwise。
  -->
<select id="selectByIdOrUsername" resultType="com.xiangty.bean.User">
    SELECT id, username, password
    FROM user
    WHERE 1=1
    <choose>
        <when test="id != null">
            and id = #{id}
        </when>
        <when test="username != null and username != ''">
            and username = #{username}
        </when>
        <otherwise>
            and 1=2
        </otherwise>
    </choose>
</select>

UserTest.java

@Test
public void selectByIdOrUsername() {
    SqlSession sqlSession = SqlSessionUtil.getSqlSession();
    try {
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setId(13);
        user.setUsername("insert4");
        System.out.println("根据id和username查询："+userMapper.selectByIdOrUsername(user));
        user.setId(null);
        user.setUsername("insert4");
        System.out.println("id为空和username查询："+userMapper.selectByIdOrUsername(user));
        user.setUsername(null);
        System.out.println("id和username为空时查询："+userMapper.selectByIdOrUsername(user));
    } finally {
        sqlSession.close();
    }
}

输出结果：
DEBUG [main] - ==>  Preparing: SELECT id, username, password FROM user WHERE 1=1 and id = ? 
DEBUG [main] - ==> Parameters: 13(Integer)
TRACE [main] - <==    Columns: id, username, password
TRACE [main] - <==        Row: 13, insert4, insert4
DEBUG [main] - <==      Total: 1
根据id和username查询：User [id=13, username=insert4, password=insert4]
DEBUG [main] - ==>  Preparing: SELECT id, username, password FROM user WHERE 1=1 and username = ? 
DEBUG [main] - ==> Parameters: insert4(String)
TRACE [main] - <==    Columns: id, username, password
TRACE [main] - <==        Row: 13, insert4, insert4
DEBUG [main] - <==      Total: 1
id为空和username查询：User [id=13, username=insert4, password=insert4]
DEBUG [main] - ==>  Preparing: SELECT id, username, password FROM user WHERE 1=1 and 1=2 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - <==      Total: 0
id和username为空时查询：null

4.3 where、set标签使用

4.3.1 where标签使用

修改原有的selectByUser方法代码如下：

UserMapper.java

List<User> selectByUser(User user);

UserMapper.xml

<!-- 
  where标签的作用：如果该标签包含的元素中有返回值，就插入一个where；如果where后面的字符串是AND或者OR开头的，就将它们剔除。
  -->
<select id="selectByUser" resultType="com.xiangty.bean.User">
    SELECT id, username, password
    FROM user
    <where> 
        <if test="username != null and username != ''">
            and username like concat('%',#{username},'%')
        </if>
        <if test="password != null and password != ''">		
            and password = #{password}
        </if>
    </where>
</select>

UserTest.java

@Test
public void selectByUser() {
    SqlSession sqlSession = SqlSessionUtil.getSqlSession();
    try {
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setUsername("管");
        user.setPassword("");
        System.out.println(userMapper.selectByUser(user));
    } finally {
        sqlSession.close();
    }
}

输出结果：
DEBUG [main] - ==>  Preparing: SELECT id, username, password FROM user WHERE username like concat('%',?,'%') 
DEBUG [main] - ==> Parameters: 管(String)
TRACE [main] - <==    Columns: id, username, password
TRACE [main] - <==        Row: 1, 管理员, 123456
DEBUG [main] - <==      Total: 1
[User [id=1, username=管理员, password=123456]]

4.3.2 set标签使用

在原来的updateByIdSelective方法上修改代码如下：

UserMapper.java

/**
 * 根据主键更新
 * @param user
 * @return
 */
int updateByIdSelective(User user);

UserMapper.xml

<!-- 
 set标签的作用：如果该标签包含的元素中有返回值，就插入一个set；如果set后面的字符串是以逗号结尾，会将这个逗号剔除。
  -->
<insert id="updateByIdSelective">
    UPDATE user
    <set>	
        <if test="username != null and username != ''">
            username = #{username},
        </if>
        <if test="password != null and password != ''">
            password = #{password},
        </if>
        id = #{id} 
    </set>
    WHERE id = #{id}
</insert>

UserTest.java

@Test
public void updateByIdSelective() {
    SqlSession sqlSession = SqlSessionUtil.getSqlSession();
    try {
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setId(12);
        user.setUsername("demo4");
        user.setPassword("demo4");

        int result = userMapper.updateByIdSelective(user);
        sqlSession.commit();

        if (result > 0) {
            System.out.println("updateByIdSelective方法成功");
        } else {
            System.out.println("updateByIdSelective方法失败");
        }
    } catch (Exception e) {
        // 发生异常回滚事务
        sqlSession.rollback();
        e.printStackTrace();
    } finally {
        sqlSession.close();
    }
}

输出结果：
DEBUG [main] - ==>  Preparing: UPDATE user SET username = ?, password = ?, id = ? WHERE id = ? 
DEBUG [main] - ==> Parameters: demo4(String), demo4(String), 12(Integer), 12(Integer)
DEBUG [main] - <==    Updates: 1
updateByIdSelective方法成功

4.4 foreach 用法

4.4.1 foreach 实现in集合

UserMapper.java

/**
 * 根据id集合查询
 * @param idList
 * @return
 */
List<User> selectByIdList(List<Integer> idList);

UserMapper.xml

<!-- 
  collection：必填，值为要迭代循环的属性名。这个属性值的情况有很多
  item：变量名，值为从迭代对象中取出的每一个值
  index：索引的属性名，在集合数组情况下值为当前索引值，当迭代循环的对象是Map类型，这个值为Map的key
  open：整个循环内容开头的字符串
  close：整个循环内容结尾的字符串
  spearator：每次循环的分隔符
  -->
<select id="selectByIdList" resultType="com.xiangty.bean.User">
    SELECT id, username, password
    FROM user
    WHERE 1=1
    AND id in 
    <foreach collection="list" open="(" close=")" separator="," item="id" index="i">
        #{id}
    </foreach>
</select>

UserTest.java

@Test
public void selectByIdList(){
    SqlSession sqlSession = SqlSessionUtil.getSqlSession();
    try {
        List<Integer> idList = Arrays.asList(1,2,3,4);
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        List<User> userList = userMapper.selectByIdList(idList);
        userList.forEach(user -> System.out.println(user));
    } finally {
        sqlSession.close();
    }
}

输出结果：
DEBUG [main] - ==>  Preparing: SELECT id, username, password FROM user WHERE 1=1 AND id in ( ? , ? , ? , ? ) 
DEBUG [main] - ==> Parameters: 1(Integer), 2(Integer), 3(Integer), 4(Integer)
TRACE [main] - <==    Columns: id, username, password
TRACE [main] - <==        Row: 1, 管理员, 123456
TRACE [main] - <==        Row: 2, 路人甲, 123456
TRACE [main] - <==        Row: 3, testname, testpassword
DEBUG [main] - <==      Total: 3
User [id=1, username=管理员, password=123456]
User [id=2, username=路人甲, password=123456]
User [id=3, username=testname, password=testpassword]

4.4.2 foreach 实现批量插入

UserMapper.java

/**
 * 批量新增用户
 * @param userList
 * @return
 */
int insertList(List<User> userList);

UserMapper.xml

<insert id="insertList">
    insert into user (username, password)
    values
    <foreach collection="list" item="user" separator=",">
        (#{user.username},#{user.password})
    </foreach>
</insert>

UserTest.java

@Test
public void insertList() {
    SqlSession sqlSession = SqlSessionUtil.getSqlSession();
    try {
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = null;
        List<User> userList = new ArrayList<User>();
        for (int i = 0; i < 10; i++) {
            user = new User();
            user.setUsername("username" + i);
            user.setPassword("password" + i);
            userList.add(user);
        }

        int result = userMapper.insertList(userList);

        sqlSession.commit();
        if (result > 0) {
            System.out.println("修改的数据条数：" + result);
        } else {
            System.out.println("添加失败");
        }
    } catch (Exception e) {
        // 发生异常回滚事务
        sqlSession.rollback();
        e.printStackTrace();
    } finally {
        sqlSession.close();
    }
}

输出结果：
DEBUG [main] - ==>  Preparing: insert into user (username, password) values (?,?) , (?,?) , (?,?) , (?,?) , (?,?) , (?,?) , (?,?) , (?,?) , (?,?) , (?,?) 
DEBUG [main] - ==> Parameters: username0(String), password0(String), username1(String), password1(String), username2(String), password2(String), username3(String), password3(String), username4(String), password4(String), username5(String), password5(String), username6(String), password6(String), username7(String), password7(String), username8(String), password8(String), username9(String), password9(String)
DEBUG [main] - <==    Updates: 10
修改的数据条数：10

4.4.3 foreach 实现动态UPDATE

UserMapper.java

/**
 * 通过Map更新列
 * @param map
 * @return
 */
int updateByMap(Map<String, Object> map);

当参数是Map类型的时候，foreach标签的index属性值对应的不是索引值，而是Map中的key，利用这个key可以实现动态UPDATE。

这里的key作为列名，对应的值作为该列的值，通过foreach将需要更新的字段拼接在SQL语句中。

UserMapper.xml

<update id="updateByMap">
    update user
    set
    <foreach collection="_parameter" item="val" index="key" separator=",">
        ${key} = #{val}
    </foreach>
    where id = #{id}
</update>

UserTest.java

@Test
public void updateByMap() {
    SqlSession sqlSession = SqlSessionUtil.getSqlSession();
    try {
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        System.out.println("id=24的用户信息：" + userMapper.selectById(24));

        Map<String, Object> map = new HashMap<String, Object>();
        // 查询条件，同意也是更新字段，必须保证该值存在
        map.put("id", 24);

        map.put("username", "username0_test");
        map.put("password", "password0_test");

        int result = userMapper.updateByMap(map);

        sqlSession.commit();
        if (result > 0) {
            System.out.println("修改的数据条数：" + result);
        } else {
            System.out.println("添加失败");
        }

        System.out.println("修改后的id=24的用户信息：" + userMapper.selectById(24));
    } catch (Exception e) {
        // 发生异常回滚事务
        sqlSession.rollback();
        e.printStackTrace();
    } finally {
        sqlSession.close();
    }
}

输出结果：
DEBUG [main] - ==>  Preparing: SELECT id, username, password FROM user WHERE id = ? 
DEBUG [main] - ==> Parameters: 24(Integer)
TRACE [main] - <==    Columns: id, username, password
TRACE [main] - <==        Row: 24, username0, password0
DEBUG [main] - <==      Total: 1
id=24的用户信息：User [id=24, username=username0, password=password0]
DEBUG [main] - ==>  Preparing: update user set password = ? , id = ? , username = ? where id = ? 
DEBUG [main] - ==> Parameters: password0_test(String), 24(Integer), username0_test(String), 24(Integer)
DEBUG [main] - <==    Updates: 1
修改的数据条数：1
DEBUG [main] - ==>  Preparing: SELECT id, username, password FROM user WHERE id = ? 
DEBUG [main] - ==> Parameters: 24(Integer)
TRACE [main] - <==    Columns: id, username, password
TRACE [main] - <==        Row: 24, username0_test, password0_test
DEBUG [main] - <==      Total: 1
修改后的id=24的用户信息：User [id=24, username=username0_test, password=password0_test]

4.5 bind 用法

bind标签可以使用OGNL表达式创建一个变量并将其绑定到上下文中。bind标签的两个属性都是必选项，name为绑定到上下文的变量名，value为OGNL表达式。创建一个bind标签的变量后，就可以在下面直接使用，使用bind拼接字符串不仅可以避免因更数据库而改SQL，也能防止SQL。

直接修改selectByUser方法，代码如下：

UserMapper.java

List<User> selectByUser(User user);

UserMapper.xml

<select id="selectByUser" resultType="com.xiangty.bean.User">
    SELECT id, username, password
    FROM user
    <where> 
        <if test="username != null and username != ''">
            <!-- 方法一 -->
            <!-- and username like concat('%',#{username},'%') -->
            <!-- 方法二 -->
            <bind name="usernameLike" value="'%'+username+'%'"/>
            and username like #{usernameLike}
        </if>
        <if test="password != null and password != ''">	
            and password = #{password}
        </if>
    </where>
</select>

UserTest.java

@Test
public void selectByUser() {
    SqlSession sqlSession = SqlSessionUtil.getSqlSession();
    try {
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setUsername("管");
        user.setPassword("");
        System.out.println(userMapper.selectByUser(user));
    } finally {
        sqlSession.close();
    }
}

输出结果：
DEBUG [main] - ==>  Preparing: SELECT id, username, password FROM user WHERE username like ? 
DEBUG [main] - ==> Parameters: %管%(String)
TRACE [main] - <==    Columns: id, username, password
TRACE [main] - <==        Row: 1, 管理员, 123456
DEBUG [main] - <==      Total: 1
[User [id=1, username=管理员, password=123456]]

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zPA06dON-1614513937815)(C:\Users\xiangty\Desktop\s8.jpg)]$

如果文档中有任何问题，可以直接联系我，便于我改正和进步。希望文档对您有所帮助。文档中代码GitHub地址：https://gitee.com/xiangty1/learn-MyBatis/

_天雨

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
2
评论
MyBatis从零开始-MyBatis动态SQL

4. MyBatis动态SQL4.1 if标签使用4.1.1 在SELECT中查询列使用if if标签通常用于WHERE语句中，通过判断参数值来决定是否使用某个查询条件，他也经用在UPDATE和INSERT语句中。示例如下：UserMapper.javaList<User> selectByUser(User user);UserMapper.xml<!-- if标签有一个必填的属性test，test的属性值是一个符合OGNL要求的判断表达式，表达式的结果是tru
复制链接

扫一扫

专栏目录