MyBatis拓展

MyBatis 拓展

1.配置解析

setting(设置)
typeAliases(类型别名)
environments(环境配置)
mappers(映射器)

注意顺序，顺序不对也会报错

1.1 typeAliases

之前在mapper.xml文件中写结果类型或参数类型都会使用全限定类名，使用 别名后，在结果类型处可直接使用别名，采用别名的方式有3种：

• 指定一个实体类给一个别名

  <typeAliases>
          <typeAlias type="com.zxjava.pojo.User" alias="User"/>
  </typeAliases>
  

• 指定一个包，扫描包下的Java Bean

      <typeAliases>
          <package name="com.zxjava.pojo"/>
      </typeAliases>
  

• 在实体类上使用注解

  @Alias("user")
  public class User {
     .....
  }
  

1.2 mappers

• 使用相对于类路径的资源引用

      <mappers>
          <mapper resource="com/zxjava/dao/UserMapper.xml"/>
      </mappers>
  

• 将包内的映射器接口全部注册为映射器

  （接口文件名和配置文件名需一致，并且在同一目录下）

      <mappers>
          <package name="com.zxjava.dao"/>
      </mappers>
  

2. ResultMap

ResultMap时解决数据库字段名和实体类属性名不一致的问题

    <resultMap id="userMap" type="user">
        <!--id是主键-->
        <id column="id" property="id"/>
        <result column="name" property="mingzi"/>
        <result column="password" property="mima"/>
    </resultMap>

3.日志工厂

首先在pom文件中导入log4j的包

        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>

在resources中新建log4j.properties

#将等级为DEBUG的日志信息输出到console和file这两个目的地，console和file的定义在下面的代码
log4j.rootLogger=DEBUG,console,file

#控制台输出的相关设置
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n

#文件输出的相关设置
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/ws.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n

#日志输出级别
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

在配置文件中加入settings

    <settings>
        <setting name="logImpl" value="LOG4J"/>
    </settings>

4. 分页

4.1 Limit分页

• ​ 语法：select * from table limit startIndex,pageSize

  ​ 从startIndex开始查找pageSize个

• ​ 语法：select * from table limit num

  ​ 从第0个开始查找num个 ， 相当于把上一种startIndex为0 省略

测试：

首先在userMapper中新增一个方法

    //分页查找
    List<User> selectByLimit(Map<String,Integer> map);

在配置文件中新增一条语句

    <select id="selectByLimit" parameterType="java.util.Map" resultType="user">
        select * from user limit #{currentPage},#{pageSize}
    </select>

在测试类中测试

    @Test
    public void selectByLimit(){
        SqlSession session = MybatisUtil.getSession();

        UserMapper mapper = session.getMapper(UserMapper.class);

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

        int currentPage = 2;//第几页

        int pageSize = 1;//每页几条

        map.put("currentPage",(currentPage-1)*pageSize);
        map.put("pageSize",pageSize);

        List<User> users = mapper.selectByLimit(map);

        for (User user : users) {
            System.out.println(user);
        }

        session.close();
    }

4.2 RowBounds分页

RowBounds分页不推荐使用

首先在userMapper中新增一个方法

    //RowBounds
    List<User> selectByRowBouns();

在配置文件中新增一条语句

    <select id="selectByRowBouns" resultType="user">
        select * from user
    </select>

在测试类中测试

    @Test
    public void selectByRowBouns(){
        SqlSession session = MybatisUtil.getSession();

        int currentPage = 2;//第几页

        int pageSize = 2;//每页几条

        RowBounds rowBounds = new RowBounds((currentPage-1)*pageSize,pageSize);

        List<User> users = session.selectList("com.zxjava.dao.UserMapper.selectByRowBouns",null,rowBounds);

        for (User user : users) {
            System.out.println(user);
        }



        session.close();
    }

4.3 PageHelper分页插件

PageHelper是一款比较好用的分页插件，官网有详细的使用教程，下面超链接可访问官网。
PageHelper官网

5. 使用注解开发

5.1查询

首先编写接口的方法

package com.zxjava.dao;


import com.zxjava.pojo.User;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface UserMapper {

    @Select("select * from user")
    List<User> selectUsers();

}

编写测试类

import com.zxjava.dao.UserMapper;
import com.zxjava.pojo.User;
import com.zxjava.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;


public class MybatisTest {

    @Test
    public void selectUsers(){

        SqlSession session = MybatisUtil.getSession();

        UserMapper mapper = session.getMapper(UserMapper.class);

        List<User> users = mapper.selectUsers();

        for (User user : users) {
            System.out.println(user);
        }

        session.close();
    }
}

5.2 根据ID查询

首先编写接口的方法

    @Select("select * from user where id = #{id}")
    User selectUserById(@Param("id") int id);

@param中的参数与数据库保持一致即可

编写测试类

    @Test
    public void selectUserById(){

        SqlSession session = MybatisUtil.getSession();

        UserMapper mapper = session.getMapper(UserMapper.class);

        User user = mapper.selectUserById(1);

        System.out.println(user);

        session.close();
    }

5.3增加

首先编写接口的方法

    @Insert("insert into user(id,name,password) values (#{id},#{name},#{password})")
    void insertUser(User user);

编写测试类

    @Test
    public void insertUser(){
        SqlSession session = MybatisUtil.getSession();

        UserMapper mapper = session.getMapper(UserMapper.class);

        User user = new User(4,"钢铁侠","hero");

        mapper.insertUser(user);

        session.commit();
        session.close();
    }

5.4 修改

首先编写接口的方法

    @Update("update user set name = #{name} , password = #{password} where id = #{id}")
    void updateUser(User user);

编写测试类

    @Test
    public void updateUser(){

        SqlSession session = MybatisUtil.getSession();

        UserMapper mapper = session.getMapper(UserMapper.class);

        User user = new User(4,"美国队长","coolman");

        mapper.updateUser(user);

        session.commit();
        session.close();

    }

5.5 删除

首先编写接口的方法

    @Delete("delete from user where id = #{id}")
    void deleteUser(@Param("id") int id);

编写测试类

    @Test
    public void deleteUser(){
        SqlSession session = MybatisUtil.getSession();

        UserMapper mapper = session.getMapper(UserMapper.class);

        mapper.deleteUser(4);

        session.commit();
        session.close();
    }

6.多对一和一对多

创建老师表,添加数据

create table teacher(
    id int(10) not null,
    name varchar(30),
    primary key (id));
    
insert into teacher(id,name) values (1,"王老师");

创建学生表,添加数据

create table student(
    id int(10) not null, 
    name varchar(30) default null, 
    tid int(10) default null,
    primary key (id), 
    foreign key (tid) references teacher (id));
    
    
   
insert into student(id , name, tid) values (1,"小李",1);
insert into student(id , name, tid) values (2,"小陈",1);
insert into student(id , name, tid) values (3,"小花",1);

创建老师实体类

package com.zxjava.pojo;


import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Teacher {
    private int id;
    private String name;
}

创建学生实体类

package com.zxjava.pojo;


import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Student {
    private int id;
    private String name;
    private Teacher teacher;
}

(接口和配置文件省略)

6.1多对一（第一种）

多个学生对应（关联）一个老师

获取所有学生对应老师的信息

首先编写接口的方法

package com.zxjava.dao;

import com.zxjava.pojo.Student;

import java.util.List;

public interface StudentMapper {

    List<Student> getStudents();
}

编写配置文件

<?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="com.zxjava.dao.StudentMapper">



    <select id="getStudents" resultMap="StudentTeacher">
        select * from student
    </select>

    <resultMap id="StudentTeacher" type="Student">
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>

    <select id="getTeacher" resultType="teacher">
        select * from teacher where id = #{id}
    </select>

</mapper>

编写测试类

    @Test
    public void getStudents(){
        SqlSession session = MybatisUtil.getSession();

        StudentMapper mapper = session.getMapper(StudentMapper.class);

        List<Student> students = mapper.getStudents();

        for (Student student : students) {
            System.out.println(student);
        }


        session.close();
    }

6.2 多对一（第二种）

多对一的第二种方式是按结果集映射

首先在接口中编写一个方法

    //按结果集映射
    List<Student> getStudents2();

编写对应的Mapper文件

    <select id="getStudents2" resultMap="StudentTeacher2">
        select s.id sid,s.name sname,t.id tid,t.name tname from student s,teacher t where s.tid = t.id
    </select>

    <resultMap id="StudentTeacher2" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="id" column="tid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>

编写测试类

    @Test
    public void getStudents2(){
        SqlSession session = MybatisUtil.getSession();

        StudentMapper mapper = session.getMapper(StudentMapper.class);

        List<Student> students2 = mapper.getStudents2();

        for (Student student : students2) {
            System.out.println(student);
        }

        session.close();
    }

ps: 多对一和一对多选择第一种方式还是第二种方式根据自己的习惯，个人推荐第二种，按结果集映射

6.3 一对多

一个老师对应多个学生

编写老师实体类

package com.zxjava.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

import java.util.List;

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Teacher {

    private int id;

    private String name;

    private List<Student> students;
}

编写学生实体类

package com.zxjava.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Student {
    private int id;

    private String name;

    private  int tid;
}

编写一对多的接口

package com.zxjava.dao;

import com.zxjava.pojo.Teacher;

public interface TeacherMapper {

    Teacher getTeacher( int id);
}

接口对应的Mapper文件

<?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="com.zxjava.dao.TeacherMapper">


    <select id="getTeacher" resultMap="TeacherStudent">
        select t.id tid,t.name tname,s.id sid,s.name sname,s.tid stid from teacher t,student s where t.id = s.tid and t.id = #{id};
    </select>

    <resultMap id="TeacherStudent" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <collection property="students" ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="stid"/>
        </collection>
    </resultMap>

</mapper>

测试类测试

import com.zxjava.dao.TeacherMapper;
import com.zxjava.pojo.Teacher;
import com.zxjava.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

public class MyTest {

    @Test
    public void getTeacher(){
        SqlSession session = MybatisUtil.getSession();

        TeacherMapper mapper = session.getMapper(TeacherMapper.class);

        Teacher teacher = mapper.getTeacher(1);

        System.out.println(teacher);

        session.close();
    }
}

测试结果

展示目录结构方便开发：

最后做个总结的话就是：

• 多对一或一对多按结果集映射比较推荐
• 多对一用association 一对多用collection

7.动态SQL

动态SQL是Mybatis最强大的特性之一，通过if 、choose、when、otherwise、where、set、foreach等标签组合成非常灵活的SQL语句

首先创建表格

 create table blog(
            id varchar(50) not null primary key ,
            title varchar(100) not null ,
            author varchar(30) not null ,
            create_time datetime not null ,
            views int(30) not null 
        )

创建Blog实体类

package com.zxjava.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

import java.util.Date;


@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Blog {
    private String id;

    private String title;

    private String author;

    private Date createTime;

    private int views;
}

编写Mybatis核心配置文件，下划线驼峰自动转换

<settings>
    <setting name="logImpl" value="LOG4J"/>
    <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

写一个生成UUID的工具类

package com.zxjava.utils;

import java.util.UUID;

public class UUIDutils {
    public static String getId(){
        return UUID.randomUUID().toString().replace("-","");
    }
}

7.1 回顾

回顾一下之前的增加方法

在接口中编写一个增加方法

package com.zxjava.dao;

import com.zxjava.pojo.Blog;

public interface BlogMapper {

    void addBlog(Blog blog);
}

编写对应的配置文件

<?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="com.zxjava.dao.BlogMapper">


    <insert id="addBlog" parameterType="blog">
        insert into blog(id,title,author,create_time,views) values (#{id},#{title},#{author},#{createTime},#{views})
    </insert>

</mapper>

编写测试类

import com.zxjava.dao.BlogMapper;
import com.zxjava.pojo.Blog;
import com.zxjava.utils.MybatisUtil;
import com.zxjava.utils.UUIDutils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.Date;

public class MyTest {

    @Test
    public void addBlog(){
        SqlSession session = MybatisUtil.getSession();

        BlogMapper mapper = session.getMapper(BlogMapper.class);

        Blog blog = new Blog();
        blog.setId(UUIDutils.getId());
        blog.setTitle("动态SQL");
        blog.setAuthor("小王");
        blog.setCreateTime(new Date());
        blog.setViews(2);

        mapper.addBlog(blog);

        session.commit();
        session.close();
    }
}

7.2 if

如果我们想根据作者名字和标题来查询博客，但如果标题为空，怎么办？

在接口中增加一个方法

List<Blog> selectBlog(Map<String,String> map);

在接口对应的Mapper文件中增加语句

    <select id="selectBlog" parameterType="java.util.Map" resultType="blog">
        select * from blog where
        <if test="author != null">
            author = #{author}
        </if>

        <if test="title != null">
            and title = #{title}
        </if>
    </select>

编写测试类

    @Test
    public void selectBlog(){
        SqlSession session = MybatisUtil.getSession();

        BlogMapper mapper = session.getMapper(BlogMapper.class);

        Map<String,String> map = new HashMap<>();
        map.put("author","小王");

        List<Blog> blogs = mapper.selectBlog(map);

        for (Blog blog : blogs) {

            System.out.println(blog);

        }

        session.close();
    }

执行的语句

if语句并不难理解，但在使用if时，可以在映射文件中明显看到还是有很大的局限性，比如 如果author为空而title不为空时，会多出一个and符号，从而致使sql语法错误，而下面说的Where就是解决这个问题的。

7.3 where

如果标签返回的内容是以 and 或 or 开头的，where标签会把它们去掉。

修改上面的SQL语句

    <select id="selectBlog" parameterType="java.util.Map" resultType="blog">
        select * from blog
        <where>
            <if test="author != null">
                author = #{author}
            </if>

            <if test="title != null">
                and title = #{title}
            </if>
        </where>

    </select>

7.4 set

set标签用在update语句上，如果标签返回的内容是以，（逗号） 开头的，set标签会把它们去掉。

在接口中新增一个修改方法

void updateBlog(Map<String,String> map);

在映射文件中新增一个语句

    <update id="updateBlog" parameterType="java.util.Map">
        update blog
        <set>
            <if test="author != null">
                author = #{author}
            </if>

            <if test="title != null">
                ,title = #{title}
            </if>
            where id = #{id};
        </set>
    </update>

新增一个测试方法

    @Test
    public void updateBlog(){

        SqlSession session = MybatisUtil.getSession();

        BlogMapper mapper = session.getMapper(BlogMapper.class);

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

        map.put("author","老王啊");
        map.put("id","9b3708392cee4cddbad9aca6a31c425b");

        mapper.updateBlog(map);

        session.commit();
        session.close();

    }

注意: where标签和set标签去掉的是以 and or 或 ，***开头***而不是结尾的

7.5 choose

choose语句类似于Java中的switch语句，在我们只想选择一个查询条件时，可以用choose

在接口中新增一个方法

List<Blog> selectByChoose(Map<String,String> map);

在映射文件中新增一个语句

    <select id="selectByChoose" parameterType="map" resultType="blog">
        select * from blog
        <where>
            <choose>
                <when test="title != null">
                    title = #{title}
                </when>
                <when test="author != null">
                    author = #{author}
                </when>
                <otherwise>
                    views  = #{views}
                </otherwise>
            </choose>
        </where>
    </select>

编写测试类

    @Test
    public void selectByChoose(){
        SqlSession session = MybatisUtil.getSession();

        BlogMapper mapper = session.getMapper(BlogMapper.class);

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

        map.put("author","小王");

        List<Blog> blogs = mapper.selectByChoose(map);

        for (Blog blog : blogs) {
            System.out.println(blog);
        }

        session.close();
    }

7.6 sql片段

sql片段就是把一段sql代码抽离出来，然后再sql语句中再通过include引用这段sql代码即可,那我们以上面的choose标签为例，把映射文件的语句修改一下：

    <sql id="choose-title-author">
        <choose>
            <when test="title != null">
                title = #{title}
            </when>
            <when test="author != null">
                author = #{author}
            </when>
            <otherwise>
                views  = #{views}
            </otherwise>
        </choose>
    </sql>

    <select id="selectByChoose" parameterType="map" resultType="blog">
        select * from blog
        <where>
            <include refid="choose-title-author"></include>
        </where>
    </select>

7.7 foreach

foreach标签中的属性：

• collection:传递的集合
• item:每次遍历生成的对象
• open:开始遍历拼接的字符
• close:结束遍历拼接的字符
• separator:每次遍历之间的分隔符

在接口中新建一个方法

List<Blog> selectForeach(Map<String,List> map);

在映射文件中新增一个sql语句

    <select id="selectForeach" parameterType="map" resultType="blog">
        select * from blog
        <where>
            <foreach collection="authors" item="author" open="(" close=")" separator="or">
                author = #{author}
            </foreach>
        </where>
    </select>

测试方法

    @Test
    public void selectForeach(){
        SqlSession session = MybatisUtil.getSession();

        BlogMapper mapper = session.getMapper(BlogMapper.class);

        List<String> authors = new ArrayList<>();

        authors.add("小王");
        authors.add("老王啊");

        Map<String,List> map = new HashMap<>();
        map.put("authors",authors);

        List<Blog> blogs = mapper.selectForeach(map);

        for (Blog blog : blogs) {

            System.out.println(blog);

        }

        session.close();
    }