Mybatis映射文件的sql编写合集(由简入难)

 首先创建一个java项目，配置好jar包

一、所需配置文件的编写

1.实体类

先来解释一下多对一和一对多

多对一：多个员工属于一个部门，每一个员工都有一个部门属性，所以在员工类中加入一个部门对象属性

一对多：一个部门拥有多个员工，多个员工就可以是一个数组/集合(我选择集合，方便操作),那就是在部门类中加入一个员工集合对象

字段说明

        部门：dept类

                id        主键自增        int

                dept_no        部门编号        int        唯一索引

                dept_name        部门名称        varchar

                     user类

                 id        主键自增        int

                user_name        员工姓名        varchar        默认索引

                password        员工密码        varchar

                age        员工年龄        int

                dept_no        部门编号        int

随便插入几条数据：

dept：

user：

下面是实体类：

1.Dept类

package ssm.domain;

import java.util.List;

/**
 * @TableName dept
 */
public class Dept{
    private Integer id;
    private Integer dept_no;
    private String dept_name;
    //一对多的体现
    private List<User> users;

    public Dept() {
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getDept_no() {
        return dept_no;
    }

    public void setDept_no(Integer dept_no) {
        this.dept_no = dept_no;
    }

    public String getDept_name() {
        return dept_name;
    }

    public void setDept_name(String dept_name) {
        this.dept_name = dept_name;
    }

    public List<User> getUsers() {
        return users;
    }

    public void setUsers(List<User> users) {
        this.users = users;
    }

    @Override
    public String toString() {
        String s= ", users=" + users;
        String str = users == null?"":users.get(0).getUserName()==null?"":s;
        return "Dept{" +
                "id=" + id +
                ", dept_no=" + dept_no +
                ", dept_name='" + dept_name + '\'' +
                str +
                '}' + "\n";
    }
}

 2.User类

package ssm.domain;

/**
 * 
 * @TableName user
 */
public class User {

    private Integer id;
    private String userName;
    private String password;
    private Integer age;
    private String deptNo;
    //多对一
    private Dept dept;

    public User() {
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getDeptNo() {
        return deptNo;
    }

    public void setDeptNo(String deptNo) {
        this.deptNo = deptNo;
    }

    public Dept getDept() {
        return dept;
    }

    public void setDept(Dept dept) {
        this.dept = dept;
    }

    @Override
    public String toString() {
        String s = ", dept=" + dept;
        String str = dept == null?"":dept.getDept_name()==null ? "":s;
        return "User{" +
                "id=" + id +
                ", userName='" + userName + '\'' +
                ", password='" + password + '\'' +
                ", age=" + age +
                ", deptNo='" + deptNo + '\'' +
                str +
                '}' + "\n";
    }
}

2.jdbc.properties

username=root
password=数据库密码
url=jdbc:mysql://localhost:3306/数据库名?characterEncoding=utf-8&serverTimezone=UTC
#下面是8.0的数据库的driver，5.x的把cj去掉
driver=com.mysql.cj.jdbc.Driver

3.sqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--引入jdbc数据库信息，即jdbc配置信息-->
    <properties resource="jdbc.properties"/>
    <!--设置是否启用驼峰命名，就是数据库的字段如果是x_a,那么xA就是它-->
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <!--给实体类起别名：包名.类名-->
    <typeAliases>
        <typeAlias type="ssm.domain.Dept" alias="dept"/>
    </typeAliases>
    <!--配置数据库信息，${这里写的是jdbc.properties里等号左边去掉"jdbc."的内容}-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <!--注册mapper，每一个mapper类都需要注册才能被识别使用-->
    <mappers>
        <mapper resource="UserMapper.xml"/>
        <mapper resource="DeptMapper.xml"/>
    </mappers>
</configuration>

4.全查

 实体类和配置文件都已经写好了，下面开始编写接口，实现方式有两种，一种是注解的方式，一种是配置文件的方式。我们从最简单的语句开始，比如全查

我们在DeptMapper接口和UserMapper接口中都写一个全查的方法：

//这是注解的方式，注解是非常简单的开发方式，省去了配置文件里复杂的标签
@Select("select * from dept")
List<Dept> selectAll();

虽然注解的方式非常简单，但是复杂的查询语句(如多表联查)用配置文件会更加简单，接下来展示DeptMapper.xml文件里的内容

<!--首先是文件头-->
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--接着是mapper标签对，namespace属性是用来指定mapper接口文件的-->
<mapper namespace="ssm.mapper.DeptMapper">
    <!--我们实体类中添加了不属于dept表中字段的属性，就需要配置属性与表中字段一一对应
        id是自己起的名字，type是对应的实体类的全路径，如果在sqlMapConfig.xml文件中配置了类别名，则直接使用类名即可
    -->
    <resultMap id="BaseResultMap" type="ssm.domain.Dept">
        <!--
            id标签：对应的是主键
            result标签：对应的是普通的字段
            property属性：对应的是实体类中的属性名
            column属性：对应的是数据库中的字段名
            jdbcType：数据库中字段的数据类型，其实不需要写
        -->
        <id property="id" column="id" jdbcType="INTEGER"/>
        <result property="dept_no" column="dept_no" jdbcType="VARCHAR"/>
        <result property="dept_name" column="dept_name" jdbcType="VARCHAR"/>
        <!--dept实体类中有一个user类的集合对象，所以还要配置它(有两种方式，下面这个是最常用的方式)，集合的标签是collection
            property：实体类中的属性名
            javaType：实体类中属性的类型，这里就是List
            ofType：list集合中存放的是什么类型的数据，这里就是user
        -->
        <collection property="users" javaType="java.util.List" ofType="ssm.domain.User">
            <result property="id" column="id"/>
            <result property="userName" column="user_name"/>
            <result property="password" column="password"/>
            <result property="age" column="age"/>
            <result property="deptNo" column="dept_no"/>
        </collection>
        <!--还有第二种方式，比上面的简略一些-->
        <!--
            column:是select属性所指向的查询语句所需要的参数
            因为是根据部门id查询员工，那么传入的dept_no就是dept类中的dept_no
        -->
        <!--<collection property="users" column="dept_no"
                    javaType="java.util.List" ofType="ssm.domain.User" select="指向mapper接口的全路径.方法名，如果是在本配置文件中编写的，那就直接写方法名，下面贴一个在本配置文件下写的代码图，这里就不做演示了"/>-->
    </resultMap>
    <!--现在操作的表只有零星几个字段，如果字段多了那么每一条查询语句都写会非常麻烦，这里有一个sql标签，用于存储数据库中的字段名,id是自己起的名字，见名知意-->
    <sql id="Base_Column_List">
        id,dept_no,dept_name
    </sql>
    <!--下面是全查功能的实现，有些人可能会想我写*也行，可以但不建议，因为全查dept返回的结果里没有list集合，那返回的结果类型可以直接用实体类-->
    <select id="selectAll" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from dept
    </select>

到这里我们已经写好了部门类的实体类，接口，配置文件里实现了一个全查的功能，我们简单测试一下(采用单元测试的方式测试)

public class UserTest {
    //因为sqlSession是可以公共使用的，@Befor就是在每一个单元测试启动的时候都加载，将每次要用的公共方法放在这里，提高代码复用性
    static SqlSession sqlSession;

    @Before
    public void setUp() throws IOException {
        //获取sqlmap-config配置文件
        InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
        //获取工厂
        SqlSessionFactoryBuilder bu = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = bu.build(in);
        //创建session
        sqlSession = factory.openSession();
    }

    //对Dept的全查
    @Test
    public void selectAllForDept() {
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> z = mapper.selectAllByUserName("张");
        System.out.println(z);
    }
    //和@Before相对的就是after，适合放关闭资源等代码
    @After
    public void tearDown() {
        sqlSession.commit();
        sqlSession.close();
    }

内部方法的截图：

 程序运行的截图：

5.一个参数的查询(非id)

对于员工类的全查就不做详细介绍了，大家自己写一写吧

下面演示根据一个参数查询(以dept_no演示吧，正好一会要用到，写在UserMapper接口中，返回的是List集合)

下面是接口：

List<User> selectByDeptNo(@Param("deptNo") String deptNo);

下面是UserMapper.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="ssm.mapper.UserMapper">
    <resultMap id="BaseResultMap" type="ssm.domain.User">
        <id property="id" column="id" jdbcType="INTEGER"/>
        <result property="userName" column="user_name" jdbcType="VARCHAR"/>
        <result property="password" column="password" jdbcType="VARCHAR"/>
        <result property="age" column="age" jdbcType="INTEGER"/>
        <result property="deptNo" column="dept_no" jdbcType="VARCHAR"/>
        <!--这里演示select的方式，findOneByDeptNo方法在DeptMapper接口中，并在DeptMapper.xml里实现方法，这个一定要有，不然就注释掉，否则运行会报错，单个对象用association-->
        <association property="dept" javaType="ssm.domain.Dept" select="ssm.mapper.DeptMapper.findAllByDept_no" column="dept_no"/>
    </resultMap>

    <sql id="Base_Column_List">
        id,user_name,password,
        age,dept_no
    </sql>

    <select id="selectByDeptNo" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from user
        where
        dept_no = #{deptNo,jdbcType=VARCHAR}
    </select>
</mapper>

简单测试一下(dept为null是正常的，因为我并没有去查dept的内容)

    @Test
    public void selectByDeptNo(){
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = mapper.selectByDeptNo(1001);
        System.out.println(users);
    }

6.多对一查询

上文解释的一对多，还有上一步在UserMapper.xml里的配置返回结果集，其实已经实现多对一，还是上面的代码，把association这一行放出来，把它select所指向的方法实现，如下：

//DeptMapper.java
    List<Dept> findAllByDept_no(@Param("dept_no") Integer dept_no);

<!--DeptMapper.xml-->
    <select id="findAllByDept_no" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from dept
        where
        dept_no = #{dept_no,jdbcType=NUMERIC}
    </select>

 现在再去运行刚才的测试方法，就可以得到一个多对一的结果啦

 一开始看到多对一和一对多真的觉得好绕啊，这么一写是不是觉得简单很多

7.一对多查询

 理清思绪，一对多就等于查询一个部门的所有员工，那么这个查询语句当然要写在部门接口中啦

//一对多的体现，既然是一，那返回的就是一个部门对象
    Dept findByDept_name(@Param("dept_name") String dept_name);

上文我们在dept中是将user对象的字段拿出来一个一个做了对照，那我们这边就要用到外连接查询，不知不自觉的竟然将多表联查也讲到了，记得在sql标签中将id和dept_no前加上"表名."，因为user表中也有id和dept_no，需要告诉数据库我查的是谁的id和dept_no

//Deptmapper.java
    //一对多的体现，既然是一，那返回的就是一个部门对象
    Dept findByDept_name(@Param("dept_no") Integer dept_no);

<!--DeptMapper.xml-->
    <select id="findByDept_name" resultMap="BaseResultMap" parameterType="integer">
        select
        <include refid="Base_Column_List"/>,u.*
        from dept left join user u on dept.dept_no = u.dept_no
        where
        dept.dept_no = #{dept_no,jdbcType=VARCHAR}
    </select>

测试一下

    //一对多的体现
    @Test
    public void selectMoreToOne(){
        DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
        Dept b = mapper.findByDept_name(1001);
        System.out.println(b);
    }

8.动态sql语句

1.where-if

先写好接口中的方法，在user接口中下如下方法

 //where--它是条件查询，用id查询就没意思了，加入模糊查询那么返回的就可能是一个集合
    List<User> findOneByUserNameAndDeptNo(@Param("userName") String userName, @Param("deptNo") Integer deptNo);

先看普通的where写法，可以看到需要一个恒为true的表达式作为where和条件的衔接，否则会出现where and 这样的情况

       select id="findOneByUserNameAndDeptNo" resultType="domain.User">
        select
        <include refid="Base_Column_List"/>
        from user
        where 1=1
            <if test="userName!=null and userName!=''">
                AND user_name like concat('%',#{userName,jdbcType=VARCHAR},'%')
            </if>
            <if test="deptNo!=null and deptNo!=''">
                AND dept_no = #{deptNo,jdbcType=VARCHAR}
            </if>
    </select>

 再看<where></where>标签对，标签对直接对and/or做了处理，注意哦，它只处理字符串开头的and/or

select id="findOneByUserNameAndDeptNo" resultType="domain.User">
        select
        <include refid="Base_Column_List"/>
        from user
        <where>
            <if test="userName!=null and userName!=''">
                AND user_name like concat('%',#{userName,jdbcType=VARCHAR},'%')
            </if>
            <if test="deptNo!=null and deptNo!=''">
                AND dept_no = #{deptNo,jdbcType=VARCHAR}
            </if>
        </where>
    </select>

这边只有if单支判断，如果是多支捏，那就用到了choose--when--otherwise，等同于java的switch--case--default

select id="findOneByUserNameAndDeptNo" resultType="domain.User">
        select
        <include refid="Base_Column_List"/>
        from user
        <where>
            <choose>
                <when test="userName!=null and userName!=''">
                    user_name like concat('%',#{userName,jdbcType=VARCHAR},'%')
                </when>
                <when test="deptNo!=null and deptNo!=''">
                    dept_no = #{deptNo,jdbcType=VARCHAR}
                </when>
                <otherwise>
                    1=1
                </otherwise>
            </choose>
        </where>
    </select>

 我这边因为功能都是相同的，所以结果都是一样的，下面是测试类中的代码

 //where
    @Test
    public void whereTest(){
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> oneByUserNameAndDeptNo = mapper.findOneByUserNameAndDeptNo("张", null);
        System.out.println("oneByUserNameAndDeptNo = " + JSON.toJSONString(oneByUserNameAndDeptNo));
    }

 下面是结果图和执行的sql语句

2.where-in-foreach

大家肯定在都见到过一些网页上/软件上的批量删除，sql中用的就是where in()子查询，下面是mybatis中的写法，首先附上接口中的方法

    //参数可以传数组，集合和可变长度参数列表
    int deleteById(@Param("ids") Integer... ids);

然后来看xml里的代码

 <delete id="deleteById">
        delete
        from user
        where id in
        /*
        collection:方法里param里是啥这里是啥
        item：取此次循环所对应的值
        open：以什么开始
        close:以什么结束
        separator：以什么分割
        */
        <foreach collection="ids" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </delete>

 foreach还是很简单的，下面是测试类的代码(增删改都需要提交事务，sqlsession.commit())，我的数据库里有id为5，6，7的数据，那我们就删除5，6，7

    //批量删除
    @Test
    public void DeletesTest() {
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int i = mapper.deleteById(5, 6, 7);
        System.out.println("成功删除" + i + "条");
    }

可以看到结果删除成功了三条数据

3.set

我们看到set那就应该想到更新数据吧，毕竟它们两个是搭档啊，set+if实现传入参数字段的修改，首先附上接口中的方法

    int updateUser(User user);

 <update id="updateUser">
        update user
        <set>
            <if test="userName!=null and userName!=''">
                user_name = #{userName,jdbcType=NUMERIC}
            </if>
            <if test="password!=null and password!=''">
                password = #{password,jdbcType=NUMERIC}
            </if>
            <if test="age!=null and age!=''">
                age = #{age,jdbcType=NUMERIC}
            </if>
            <if test="deptNo!=null and deptNo!=''">
                deptNo = #{deptNo,jdbcType=NUMERIC}
            </if>
        </set>
        where id = #{id}
    </update>

常用的动态语句所用标签大致就这么些，如有新发现，另做补充。