Mybatis

 一种持久层框架。支持定制化SQL、存储过程以及高级映射。

 避免了几乎所有的JDBC代码和手动设置参数以及获取结果集。

 支持简单的XML或注解来配置和映射原生类型、接口和java的POJO为数据库中的记录。

 

 如何获取Mybatis？

<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.7</version>
</dependency>

 

第一个Mybatis程序

 1.搭建环境

   MYSQL–Database&table

CREATE DATABASE mybatis_demo；

create table `user`(
	id int(20) PRIMARY KEY,
	name	VARCHAR(30),
	pwd	VARCHAR(30)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

 2.新建Maven项目

   mybatisDemo

 3.导入Maven依赖

   pom.xml

<dependencies>
    <!--MySQL驱动-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.32</version>
    </dependency>
    <!--MyBatis-->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId> 
        <version>3.5.7</version>
    </dependency>
    <!--junit-->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>
</dependencies>

 4.编写Mybatis的核心文件

   resource/mybatis-config.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>
    <!--环境、可配置多个-->
    <environments default="development">
        <environment id="development">
            <!--事务管理-->
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis_demo" />
                <property name="username" value="root"  />
                <property name="password" value="123" />
            </dataSource>
        </environment>
    </environments>

    <!--每个Mapper.xml都需要在核心配置文件中注册-->
    <mappers>
        <mapper resource="UserMapper.xml"></mapper>
    </mappers>
</configuration>

 5.编写Mybatis工具类

   主要通过配置文件来获取Mybatis间的相关类

public class MyBatisUtils  {

    private static SqlSessionFactory sqlSessionFactory;

    static {
        //固定的写法，因此写成工具类
        try {
            //获取Mybaits的sqlSessionFactory对象
            String resource ="mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //获取通过sqlSessionFactory来获取SqlSession类
    //SqlSession包含了面向数据库执行SQL语句所需的所有方法
    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession();
    }
}

 6.编写Mybatis代码

​    目录结构

目录
	utils
		MyBatisUtils
	pojo
		User
	dao
		UserDao
resourcrs
	mybaits-config.xml
	UserMapper.xml

   实体层 pojo/User

public class User {
    private int id;
    private String name;
    private String pwd;

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", pwd='" + pwd + '\'' +
                '}';
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

    public User(int id, String name, String pwd) {

        this.id = id;
        this.name = name;
        this.pwd = pwd;
    }

    public User() {

    }
}

   dao层

    dao/UserDao

public interface UserDao {
    List<User> getUsers();
}

  申明一个接口后，需要一个相对应的mapper文件来处理。

    dao/UserDaoMapper.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">
<!--namespace = "相对应的Mapper接口"-->
<mapper namespace="com.rz.dao.UserDao">
    <!--
        select查询语句
        id = "对应的方法名"
		resultType ： 接收 封装结果集实体
    -->
    <select id="getUsers" resultType="com.rz.pojo.User">
        select * from `user`
    </select>
</mapper>

7. 测试

public class UserDaoTest {
    @Test
    public void test(){
        //1.获取SqlSession对象
        SqlSession sqlSession = MyBatisUtils.getSqlSession();

        //2.执行SQL语句
        UserDao mapper = sqlSession.getMapper(UserDao.class);
        List<User> users = mapper.getUsers();

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

        //3.关闭SqlSession
        sqlSession.close();
    }
}

 注：

​   第一次上手时，容易忽略细节，产生错误

org.apache.ibatis.binding.BindingException: Type interface com.rz.dao.UserDao is not known to the MapperRegistry.

​   产生原因：未在Mybatis配置文件注册

 

CRUD操作

 CRUD的具体语句都在xxxMapper.xml上书写

 

SELECT

1.在UserDao接口添加需要方法

 //根据ID查询用户
    User getUserById(int id);

2.在UserMapper.xml中写入语句

<!--
    select查询语句
    id = "对应的方法名"
    resultType:SQL语句执行的返回值
    parameterType 需要传入的值
-->
<select id="getUserById" resultType="com.rz.pojo.User" parameterType="int">
    select * from `user` where id = #{id}
</select>

3.编写测试

@Test
    public void Test1(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();

        UserDao mapper = sqlSession.getMapper(UserDao.class);
        User userById = mapper.getUserById(1);
        System.out.println(userById); //User{id=1, name='Tom', pwd='123'}

        sqlSession.close();
    }

 

INSERT

1.在UserDao接口添加需要方法

int addUser(User user);

2.在UserMapper.xml中写入语句

<!--插入操作-->
<!-- parameterType ：传入的参数
     其中 #{xxx} 须与 对象的属性名保持一致	
-->
<insert id="addUser" parameterType="com.rz.pojo.User">
    insert into user(id,name,pwd) values (#{id},#{name},#{pwd})
</insert>

3.编写测试

@Test
public void Test(){
    SqlSession sqlSession = MyBatisUtils.getSqlSession();

    UserDao mapper = sqlSession.getMapper(UserDao.class);
    int flag = mapper.addUser(new User(4, "Jack", "123"));

    /*
         Mybaits 若执行insert、delete、update操作，则需要提交事务
    	 因为Mybaits默认是关闭事务操作的
    */
    if (flag > 0){
        sqlSession.commit();
    }

    sqlSession.close();
}

 

UPDATE

1.在UserDao接口添加需要方法

int updateUser(User user);

2.在UserMapper.xml中写入语句

<update id="updateUser" parameterType="com.rz.pojo.User">
    update user set name=#{name},pwd=#{pwd} where id =#{id}
</update>

3.编写测试

@Test
public void Test(){
    SqlSession sqlSession = MyBatisUtils.getSqlSession();

    UserDao mapper = sqlSession.getMapper(UserDao.class);
    int flag = mapper.updateUser(new User(4, "JackWu", "231"));

    //增删改需要提交事务
    if (flag > 0){
        sqlSession.commit();
    }

    sqlSession.close();
}

 

DELETE

1.在UserDao接口添加需要方法

int delUser(int id);

2.在UserMapper.xml中写入语句

<delete id="delUser">
    delete from user where id = #{id}
</delete>

3.编写测试

@Test
public void Test(){
    SqlSession sqlSession = MyBatisUtils.getSqlSession();

    UserDao mapper = sqlSession.getMapper(UserDao.class);
    int flag = mapper.delUser(4);

    //增删改需要提交事务
    if (flag > 0){
        sqlSession.commit();
    }

    sqlSession.close();
}

 

开发小技巧：

​ 当字段或参数过多时，可以考虑通过Map来传参

1.在UserDao接口添加需要方法

int addUserOfMap(Map<String,Object> map);

2.在UserMapper.xml中写入语句

<insert id="addUserOfMap" parameterType="map">
    insert into user(id,name) values (#{id},#{name})
</insert>

3.编写测试

@Test
public void Test(){
    SqlSession sqlSession = MyBatisUtils.getSqlSession();

    UserDao mapper = sqlSession.getMapper(UserDao.class);

    Map map = new HashMap<String, Object>();
    map.put("id",4);
    map.put("name","jack");


    int flag = mapper.addUserOfMap(map);

    //增删改需要提交事务
    if (flag > 0){
        sqlSession.commit();
    }

    sqlSession.close();
}

 

核心配置文件

​ mybatis-config.xml

 文档结构

configuration	(配置)
	properties	(属性)
	setting	 (设置)
	typeAliases	 (类型别名)
	typeHandlers	(类型处理器)
	objectFactory	(对象工厂)
	plugins	 (插件)
	environments	(环境配置)
		environment (环境变量)
			transactionManager	(事务管理器)
			dataSource	(数据源)
	databaseidProvider	(数据库厂商标识)
	mappers	(映射器)

​

​ environments

  Mybatis可以配置多种环境 ，但每一个SqlSessionFactory实例只能选择一种环境

eg：

<!-
	决定使用哪个环境便 改变 environments标签中的 default 属性
	eg：
		<environments default="development">
		<environments default="test">
	注：
		默认<transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
-->
<environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager> <!--指定事务管理类型为JDBC-->
            <dataSource type="POOLED"> <!--指定当前数据源类型是连接池-->
                <!--数据源配置的基本参数-->
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis_demo" />
                <property name="username" value="root"  />
                <property name="password" value="123" />
            </dataSource>
        </environment>
        
        <environment id="test">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis_demo" />
                <property name="username" value="root"  />
                <property name="password" value="123" />
            </dataSource>
        </environment>
    </environments>

 

​ properties

  可以通过properties属性来引用配置文件

  eg：

​    1.编写一个配置文件 db.properties

driver = com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis_demo
username=root
password=123

​    2.在核心文件中引入

<?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>
    <!--引入外部配置文件-->
    <properties resource="db.properties"/>


    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <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.xml都需要在核心配置文件中注册-->
    <mappers>
        <mapper resource="UserMapper.xml"></mapper>
    </mappers>
</configuration>

 注：

​   可以直接引入外部文件、可以在其中增加一些属性配置

​   若两个文件中拥有用一字段，则优先使用外部配置文件

 

typeAliases

​  为java类型设置一个名字，可以用来减少类完全限定名的冗余

 eg：

​   1.在配置文件引入

​     a1.引入实体类

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

​     a2.引入包，默认名为这个类的类名，其中 首字母需小写。

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

  2.在Mapper.xml中使用

<select id="getUsers" resultType="user" >
        select * from `user`
</select>
<!-- 有区别于-->
<insert id="addUser" parameterType="com.rz.pojo.User">
    insert into user(id,name,pwd) values (#{id},#{name},#{pwd})
</insert>

 

mappers

<!--每个Mapper.xml都需要在核心配置文件中注册-->
    <mappers>
        <!--使用相对于类路径的资源引用-->
        <mapper resource="UserMapper.xml"></mapper>
        <!--使用完全限定资源定位法-->
        <mapper url="xxx/UserMapper.xml" />  <!--通常不用-->
        <!-- 
			使用映射器接口实现类的完全限定类名
				注： 接口和Mapper必须同名、且在同一路径
		-->
        <mapper class="com.rz.dao.UserMapper"/>
        <!-- 将包内的映射器接口实现全部注册为映射器-->
        <package name="com.rz.dao" />
    </mappers>

 

分页

 减少数据的处理量

​ 使用Limit分页

select * from user limit 0,2;
select * from user limit 2,4;

使用Mybatis实现分页

 1.接口

public interface UserDao {
    //分页
    List<User> getUserByLimt(Map<String,Object> map);
}

 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="com.rz.dao.UserDao">

    <!--分页查询-->
    <select id="getUserByLimt" parameterType="map" resultType="user">
        select * from user limit #{startIndex},#{pageSize};
    </select>

</mapper>

 3.测试

@Test
    public void Test(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();

        UserDao mapper = sqlSession.getMapper(UserDao.class);

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

        map.put("startIndex",0);
        map.put("pageSize",2);

        List<User> userByLimt = mapper.getUserByLimt(map);

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


        sqlSession.close();
    }

分页插件 PageHelper

  MyBatis 分页插件 PageHelper

 

解决属性名和字段名不一致的问题

 eg：

  实体类的相关属性

import lombok.Data;

@Data
public class User {
    private int id;
    private String name;
    private String password;
}

  而数据库中的字段为 pwd 与 属性password不符。根据上面查询的方法 得出的结果 password 全部为 null

 为了解决这个问题，将在Mapper.xml中引用resultMap[结果集映射]

<!-- id：名字，type：类名 -->
    <resultMap id="userMap" type="user">
        <!-- column：数据库中的字段名 property：类中的属性名 -->
        <result column="id" property="id" />
        <result column="name" property="name" />
        <result column="pwd" property="password" />
    </resultMap>

<!-- 并且相关标签中的属性也需要变化-->
<select id="getUsers" resultMap="userMap" >
        select * from `user`
 </select>

关系对关系

 关系存在着1对1，1对多，多对1三种情况。而1对1的情况在上文的例子中已经展示出来了。

 此处将采用老师和学生的关系进行展示。

 

 老师与学生：

  多个学生，对应着一个老师【多对一】(关联)

  一个老师 教 多少学生【一对多】 (集合)

 

多对一

 查询所有学生的信息 以及 对应的老师信息

 Mapper

public interface StudentMapper {
    public List<Student> getStudent();
}

Mapper.xml

 （按照查询嵌套处理）

​   查询所有的学生信息

​   根据所查询出来的tid，找到对应的老师

<?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.rz.dao.StudentMapper">

    <resultMap id="StudentAndTeacher" type="Student">
        <!--result 只能处理简单的属性-->
        <result property="id" column="id" />
        <result property="name" column="name" />
        <!--
            复杂的属性，则需要单独处理：
                对象 -> association
                集合 -> collection
        -->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
        <!--
            select 是将 column值 作为参数代入 所需的select语句
        -->
    </resultMap>

    <select id="getStudent" resultMap="StudentAndTeacher">
        select * from student;
    </select>

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


</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.rz.dao.StudentMapper">

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

    <select id="getStudent" resultMap="StudentAndTeacher">
        select s.id sid,s.name sname,t.name tname
          from student s,teacher t
          where s.tid=t.id;
    </select>
</mapper>

测试

@Test
    public void Test(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();

        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> student = mapper.getStudent();

        for (Student s: student) {
            System.out.println(s);
        }
        sqlSession.close();
    }

 

一对多处理

 环境搭建：

  1.实体类

@Data
public class Student {
    private  int id;
    private String name;
    private int tid;
}

@Data
public class Teacher {
    private int id;
    private String name;
    //拥有多个学生
    private List<Student> students;

}

 2.Mapper

public interface TeacherMapper {

    //获取老师及其管理学生
    Teacher getTeacherAndStudents(@Param("id") int id);
}

 3.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="com.rz.dao.TeacherMapper">

    <resultMap id="TeacherAndStudent" type="Teacher">
        <result  property="id" column="tid" />
        <result  property="name" column="tname" />
        <!--
            javaType="" 指定属性的类型
            集合中的泛型信息，我们使用ofType获取
        -->
        <collection property="students" ofType="Student">
            <result property="id" column="sid" />
            <result property="name" column="sname"/>
        </collection>
    </resultMap>
    
    <select id="getTeacherAndStudents" resultMap="TeacherAndStudent">
        select s.id sid,s.name sname,t.name tname,t.id tid
          from student s, teacher t
          where s.tid = t.id and t.id = #{id};
    </select>
</mapper>

 4.测试

 @Test
    public void Test(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();

        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacherAndStudents(1);

        System.out.println(teacher);


        sqlSession.close();
    }

 

动态SQL

 根据不同的条件生成不同的SQL语句

 SQL标签

if
choose(when,otherwise)
trim(where,set)
foreach

环境搭建

 SQL

create table `blog`(
	id varchar(50),
	title varchar(100) ,
	author varchar(30),
	create_time datetime,
	views int(30)
)ENGINE=INNODB DEFAULT CHARSET=utf8

 工程相关内容

  1.实体类

@Data
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;
    private int views;
}

 2.接口
  Blog Mapper.class

public interface BlogMapper {
    //插入数据
    int addBook(Blog blog);

}

  BlogMapper.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="com.rz.dao.BlogMapper">
    <insert id="addBlog" parameterType="Blog">
    INSERT INTO `blog`
    VALUES (#{id},#{title},#{author},#{createTime},#{views})
</insert>


</mapper>

 3.配置

  mybatis-config.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>
    <!--引入外部配置文件-->
    <properties resource="db.properties"/>

    <settings>
        <!--
            是否开启自动驼峰命名规则 -> 处理字段名和属性名不一致
        -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    
    <typeAliases>
        <package name="com.rz.pojo"/>
    </typeAliases>


    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <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>

    <!--绑定接口-->
    <mappers>
        <mapper resource="com/rz/dao/BlogMapper.xml" />
    </mappers>

</configuration>

 4.测试

@Test
    public void Test(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();

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

        Blog blog = new Blog();
        blog.setId(IdUtils.getId());
        blog.setTitle("Java_");
        blog.setAuthor("Tom");
        blog.setCreateTime(new Date());
        blog.setViews(5361);

        mapper.addBlog(blog);

        blog.setId(IdUtils.getId());
        blog.setTitle("JavaWeb_");
        mapper.addBlog(blog);

        blog.setId(IdUtils.getId());
        blog.setTitle("Spring_");
        mapper.addBlog(blog);

        blog.setId(IdUtils.getId());
        blog.setTitle("Mybatis_");
        mapper.addBlog(blog);

     sqlSession.close();
    }

 

IF语句

 1.添加方法

//查询博客
    List<Blog> queryBlogByIf(Map map);

 2.xml的修改

  <!--
        首先 where 1 = 1
            其次添加 if标签
            <if test="条件">
    -->
    <select id="queryBlogByIf" parameterType="map" resultType="Blog">
        select * from `blog` where 1 =1
        <if test="title != null">
            and title=#{title}
        </if>
        <if test="author != null">
            and  author =#{author}
        </if>
    </select>

3.测试

@Test
    public void Test(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();

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

        HashMap hashMap = new HashMap();
        hashMap.put("title","javaWeb_");
        hashMap.put("author","Tom");

        List<Blog> blogs = mapper.queryBlogByIf(hashMap);

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


        sqlSession.close();
    }

 

trim(where,set)

​ where

  为了让if标签有效，且避免 where 1=1 的重复标签，则采取 where标签

​   where元素只会在至少有一个子元素的条件返回SQL子句的情况下才去插入WHERE子句。并且会自动将IF语句中开头的AND 或 OR 去除掉，

BlogMapper.xml

<select id="queryBlogByIf" parameterType="map" resultType="Blog">
        select * from `blog`
        <where>
            <if test="title != null">
                 title=#{title}
            </if>
            <if test="author != null">
                and  author =#{author}
            </if>
        </where>

    </select>

test

@Test
    public void Test(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();

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

        HashMap hashMap = new HashMap();
        hashMap.put("title","javaWeb_");
//        hashMap.put("author","Tom");

        List<Blog> blogs = mapper.queryBlogByIf(hashMap);

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


        sqlSession.close();
    }

 

set

​  用于UPDATE语句中set属性.通过set的标签来进行动态SQL语句处理.

​  此时set元素会动态前置SET关键字,并删除无关的逗号
 
 1.方法

int updateBlog(Map map);

2.xml的修改

<update id="updateBlog" parameterType="map" >
        update `blog`
        <set>
            <if test="title != null">
                title=#{title},
            </if>
            <if test="author != null">
                author =#{author}
            </if>
        </set>
        where id = #{id}
    </update>

3.测试

@Test
    public void Test(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();

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

        HashMap hashMap = new HashMap();
        hashMap.put("title","javaWeb_");
        hashMap.put("author","Jerry");
        hashMap.put("id","1fcbe3963c3143409d4ae49603aa0e51"); //为了测试，直接从数据库调

        mapper.updateBlog(hashMap);


        sqlSession.close();
    }

trim

 无论是where还是set 本质上都是trim

 格式:

<trim prefix="" prefixOverrides="" suffix="" suffixOverrides="">
            ...
</trim>

 prefix 前缀 prefixOverrides是覆盖什么 eg: prefix=“where”
 prefixOverrides=“AND | OR”
 suffixOverrides 后缀覆盖什么 eg:suffixOverrides =","
 

choose,when,otherwise

 有时候并不想应用到所有的条件语句，而需要其中一项。这是可以采用choose元素。

List<Blog> queryBlogChoose(Map map);

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

@Test
    public void Test(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();

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

        HashMap hashMap = new HashMap();
        //因为Choose只需要一个 所以只要有一个生效，后面的 都将会不被理睬。
        hashMap.put("title","javaWeb_");
//        hashMap.put("author","Tom");
        hashMap.put("views",5361);

        List<Blog> blogs = mapper.queryBlogChoose(hashMap);

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


        sqlSession.close();
    }

动态SQL有利于我们在SQL层面处理一些逻辑代码

 

Foreach

 对集合进行遍历[通常是在构建IN条件语句的时候]->区间范围

  1.首先将表中数据的id改为1 、２、３…

  2.

//查询第1-2-3号的博客
List<Blog> queryBlogForeach(Map map);

<!--
        collection 拿到的集合名
        item 集合元素
        open 添加前缀
        close 添加后缀
        separator 分隔符
    -->
    <select id="queryBlogForeach" parameterType="map" resultType="Blog">
        select * from `blog`
        <where>
          id IN
          <foreach collection="ids" item="id" open="(" close=")" separator=",">
            #{id}
          </foreach>
        </where>
    </select>

@Test
    public void Test(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();

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

        HashMap hashMap = new HashMap();
        ArrayList<Integer> ids = new ArrayList<Integer>();

        hashMap.put("ids",ids);
        ids.add(1);
        ids.add(2);
        List<Blog> blogs = mapper.queryBlogForeach(hashMap);

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


        sqlSession.close();
    }

 

SQL片段

若在开发中遇到 SQL公共部分,则可以进行抽离,方便使用.

1.首先将共同部分抽离

<sql id="selectBlog">
        select * from `blog`
</sql>

2.在语句中引用 [采用include标签]

<select id="queryBlogByIf" parameterType="map" resultType="Blog">
        <include refid="selectBlog" />
        <where>
            <if test="title != null">
                 title=#{title}
            </if>
            <if test="author != null">
                and  author =#{author}
            </if>
        </where>
    </select>

 注:

​   1.最好基于单表来定义SQL片段

​   2.不要存在where标签

 

使用注解开发

注解名	作用
@Insert	实现新增
@Update	更新
@Delete	删除
@Select	查询
@Result	实现封装结果集
@Results	封装多个结果集
@One	实现一对多结果集封装
@Many	实现多对一结果集封装

​  eg：

  1.UserDao.

public interface UserDao {
    @Select("select * from user")
    List<User> getUsers();
}

  2.核心配置文件绑定接口

<!--绑定接口-->
    <mappers>
        <mapper class="com.rz.dao.UserDao" />
    </mappers>

  3.测试

public class UserDaoTest {

    @Test
    public void Test(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();

        UserDao mapper = sqlSession.getMapper(UserDao.class);

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

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


        sqlSession.close();
    }
}

 注：

  注解处理简单的十分高效，但若遇到复杂的时候，采用xml配置则会更高效。

 

实现增删改查

开启自动提交事务

//获取通过sqlSessionFactory来获取SqlSession类
    //SqlSession包含了面向数据库执行SQL语句所需的所有方法
    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession(true);
    }

​

SELECT

1.添加接口方法

//当方法存在多个参数时，每个参数前必须引入 @Param("value")说明
    @Select("select * from user where id = #{id}")
    User getUserById(@Param("id")int id);

2.测试

@Test
    public void Test(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();

        UserDao mapper = sqlSession.getMapper(UserDao.class);

        User users = mapper.getUserById(1);

        System.out.println(users);


        sqlSession.close();
    }

INSERT

@Insert("insert into user(id,name,pwd) values (#{id},#{name},#{pwd})")
    int addUser(User user);

@Test
    public void Test(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();

        UserDao mapper = sqlSession.getMapper(UserDao.class);
        int flag = mapper.addUser(new User(4,"Jack","123"));

        //增删改需要提交事务
        if (flag > 0){
            sqlSession.commit();
        }

        sqlSession.close();
    }

UPDATE

@Insert("insert into user(id,name,pwd) values (#{id},#{name},#{pwd})")
int addUser(User user);

@Test
    public void Test(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();

        UserDao mapper = sqlSession.getMapper(UserDao.class);
        int flag = mapper.updateUser(new User(4, "JackWu", "231"));

        //增删改需要提交事务
        if (flag > 0){
            sqlSession.commit();
        }

        sqlSession.close();
    }

DELETE

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

@Test
    public void Test(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();

        UserDao mapper = sqlSession.getMapper(UserDao.class);
        int flag = mapper.delUser(4);

        //增删改需要提交事务
        if (flag > 0){
            sqlSession.commit();
        }

        sqlSession.close();
    }

 

一对一注解 @One

​ OrderMapper

public interface OrderMapper {

    @Select("SELECT *,o.id oid FROM `orders` o,user u where o.uid = u.id;")
    @Results({
            @Result(column = "oid" ,property = "id"),
            @Result(column="total" ,property="total"),
            @Result(column="uid",property="user.id"),
            @Result(column="username",property="user.username"),
            @Result(column="password",property="user.password"),
    }) //Results 约定于resultMap
    public List<Order> findAll();
}

Test

@Test
    public void test() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);

        List<Order> all = mapper.findAll();

        for (Order order : all) {
            System.out.println(order);
        }
    }

 

例外一种方式

 通过封装集体

 其中one 是传入column的值，通过UserMapper的方法找到 想要的相关数据

public interface OrderMapper {
    
    @Select("SELECT *,o.id oid FROM orders o")
    @Results({
            @Result(column = "oid" ,property = "id"),
            @Result(column="total" ,property="total"),
            @Result(
                    property = "user", //要封装的属性名称
                    column = "uid" ,//根据哪个字段查询user表的数据
                    javaType = User.class,  //要封装的实体类型
                    one =@One(select = "com.rz.mapper.UserMapper.findById")
            )
    })
    public List<Order> findAll();
}

 

一对多注解 @Many

 OrderMapper.java

 增加了一个通过uid获得相关数据的方法

public interface OrderMapper {

    @Select("SELECT *,o.id oid FROM orders o")
    @Results({
            @Result(column = "oid" ,property = "id"),
            @Result(column="total" ,property="total"),

            @Result(
                    property = "user", //要封装的属性名称
                    column = "uid" ,//根据哪个字段查询user表的数据
                    javaType = User.class,  //要封装的实体类型
                    one =@One(select = "com.rz.mapper.UserMapper.findById")
            )
    })
    public List<Order> findAll();

    @Select("select * from orders where uid = #{uid}")
    public List<Order> findByUId(int uid);
}

UserMapper

@Select("select * from user")
    @Results({
            @Result(column = "id" ,property = "id"),
            @Result(column="username" ,property="username"),
            @Result(column = "password",property = "password"),

            @Result(
                    property = "orderList",
                    column = "id" ,
                    javaType = List.class,
                    one =@One(select = "com.rz.mapper.OrderMapper.findByUId")
            )
    })
    public List<User> findUserAndOrderAll();

Test

@Test
    public void test() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();

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

        List<User> userList = mapper.findUserAndOrderAll();

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

        sqlSession.close();
    }