Mybatis

关注

原创最新推荐文章于 2026-06-20 15:39:46 发布 225 阅读 AI 写同款· GEO 优化 ›

mybatis 和 mybatis-plus 配置文件：用于配置数据库连接和 MyBatis 运行时所需的各种特性，包含了设置和影响 MyBatis 行为的属性、映射文件的位置。mapper接口和mapper.xml之间需要遵循一定规则，才能成功的让mybatis将mapper接口和mapper.xml绑定起来。创建IUserMapper接口: 用于操作数据库CURD的方法。文件：用于mybatis配置文件读取数据库信息。resources 目录下创建。resources 目录下创建。resources 目录下的。类：用于跟数据库字段做映射。阅读详情

一种持久层框架。支持定制化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>

测试

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 、２、３…

//查询第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();
    }