# mybatis-dynamic-sql

**Repository Path**: wei_dongdong/mybatis-dynamic-sql

## Basic Information

- **Project Name**: mybatis-dynamic-sql
- **Description**: mybatis动态sql学习笔记
- **Primary Language**: Java
- **License**: GPL-3.0
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2020-08-30
- **Last Updated**: 2020-12-19

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# mybatis-dynamic-sql

#### 介绍
mybatis动态sql学习笔记

#### 软件架构
软件架构说明


#### 安装教程

##### 动态sql

###### 1、dao层接口

```java
public interface UserDao {
    /**
     * 根据用户名和密码查询用户
     */
    List<User> getUserByCon(@Param("name") String userName,@Param("pass") String password);

    /**
     * 根据用户名和密码查询用户
     */
    List<User> getUserByCon1(@Param("name") String userName,@Param("pass") String password);

    /**
     * 更新用户
     */
    Integer updateUser(User user);

    /**
     * 根据用户名或者password查询 如果没有用户名和密码默认按照id查询
     * 只能按照三个条件中的一个查询
     */
    List<User> getUserByCondition(@Param("id") Integer id,@Param("userName") String userName,@Param("password") String password);

    /**
     * 用集合查询满足集合内条件的记录
     */
    List<User> getUser(List<String> passwordList);
}
```

###### 2、mapper文件

```xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.kgc.dao.UserDao">
    <resultMap id="userList" type="user">
        <id property="id" column="id"/>
        <result property="name" column="userName"/>
        <result property="password" column="password"/>
    </resultMap>
    <!--提取重复的sql语句-->
    <sql id="selAll">
        select * from user
    </sql>

    <!--1、where + if 根据用户名和密码查询用户-->
    <select id="getUserByCon" resultMap="userList">
        <include refid="selAll"></include>
        <where>
            <if test="name != null and name != ''">
                userName like concat('%',#{name},'%')
            </if>
            <if test="pass != null and pass != ''">
                and password like concat('%',#{pass},'%')
            </if>
        </where>
    </select>

    <!--2、trim + if 根据用户名和密码查询用户-->
    <select id="getUserByCon1" resultMap="userList">
        <include refid="selAll"></include>
        <trim prefix="where" prefixOverrides="and | or">
            <if test="name != null and name != ''">
                userName like concat('%',#{name},'%')
            </if>
            <if test="pass != null and pass != ''">
                and password like concat('%',#{pass},'%')
            </if>
        </trim>
    </select>

    <!--3、更新用户，set标签的使用-->
    <update id="updateUser" parameterType="user">
        update user
        <set>
            <if test="name != null and name != ''">
                userName = #{name},
            </if>
            <if test="password != null and password != ''">
                password = #{password}
            </if>
        </set>
        where id = #{id}
    </update>
    <!--4、choose(when+otherwise)，单选查询-->
    <select id="getUserByCondition" resultMap="userList">
        <include refid="selAll"></include>
        <where>
            <choose>
                <when test="userName != null and userName != ''">
                    userName like concat('%',#{userName},'%')
                </when>
                <when test="password != null and password != ''">
                    password like concat('%',#{password},'%')
                </when>
                <otherwise>
                    id = #{id}
                </otherwise>
            </choose>
        </where>
    </select>
    <!--foreach-->
    <select id="getUser" parameterType="list" resultMap="userList">
        <include refid="selAll"></include>
        <where>
            password in
            <foreach collection="list" item="user" open="(" separator="," close=")">
                #{user}
            </foreach>
        </where>
    </select>
</mapper>
```

###### 3、测试

```java
public class UserDaoTest {

    //获取sqlSession
    SqlSession sqlSession = MyBatisUtil.createSqlsession();
    //获取接口
    UserDao userDao = sqlSession.getMapper(UserDao.class);

    @Test
    public void getUserByCon() {
        List<User> users = userDao.getUserByCon("e", "5");
        for (User user : users) {
            System.out.println(user);
        }
        MyBatisUtil.close(sqlSession);
    }

    @Test
    public void getUserByCon1() {
        List<User> users = userDao.getUserByCon1("e", "");
        for (User user : users) {
            System.out.println(user);
        }
        MyBatisUtil.close(sqlSession);
    }

    @Test
    public void updateUser() {
        User user = new User();
        user.setId(12);
        user.setName("BIG SHOW");
        user.setPassword("bigshow");
        Integer count = userDao.updateUser(user);
        if (count>0){
            System.out.println("更新成功！");
        }else {
            System.out.println("更新失败！");
        }
        MyBatisUtil.close(sqlSession);
    }

    @Test
    public void getUserByCondition() {
        List<User> users = userDao.getUserByCondition(10, "", "");
        for (User user : users) {
            System.out.println(user);
        }
        MyBatisUtil.close(sqlSession);
    }

    @Test
    public void getUser() {
        List<String> passwordList = new ArrayList<>();
        passwordList.add("hhhy");
        passwordList.add("wwe");
        List<User> users = userDao.getUser(passwordList);
        for (User user : users) {
            System.out.println(user);
        }
        MyBatisUtil.close(sqlSession);
    }
}
```

#### 使用说明

1.  xxxx
2.  xxxx
3.  xxxx

#### 参与贡献

1.  Fork 本仓库
2.  新建 Feat_xxx 分支
3.  提交代码
4.  新建 Pull Request


#### 码云特技

1.  使用 Readme\_XXX.md 来支持不同的语言，例如 Readme\_en.md, Readme\_zh.md
2.  码云官方博客 [blog.gitee.com](https://blog.gitee.com)
3.  你可以 [https://gitee.com/explore](https://gitee.com/explore) 这个地址来了解码云上的优秀开源项目
4.  [GVP](https://gitee.com/gvp) 全称是码云最有价值开源项目，是码云综合评定出的优秀开源项目
5.  码云官方提供的使用手册 [https://gitee.com/help](https://gitee.com/help)
6.  码云封面人物是一档用来展示码云会员风采的栏目 [https://gitee.com/gitee-stars/](https://gitee.com/gitee-stars/)