Mybatis学习笔记

最新推荐文章于 2022-11-21 21:14:08 发布

勿忘∮心安

最新推荐文章于 2022-11-21 21:14:08 发布

阅读量225

点赞数

分类专栏：个人笔记 Mybatis

本文链接：https://blog.csdn.net/qq_45309822/article/details/115465573

版权

个人笔记同时被 2 个专栏收录

3 篇文章 0 订阅

订阅专栏

Mybatis

1 篇文章 0 订阅

订阅专栏

文章目录

Mybatis

Mybatis

1、准备Mybatis

Maven仓库：https://mvnrepository.com/artifact/org.mybatis/mybatis/3.4.6

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

github：https://github.com/search?q=mybatis
中文文档：https://mybatis.org/mybatis-3/zh/index.html

2、第一个Mybatis程序

2.1 创建数据库和表

create database mybatis character set utf8 collate utf8_bin;
use mybatis;
create table user (
    id int(20) not null primary key,
    name varchar(10),
    password varchar(20)
)
insert into user(id,name,password) values (1,'lucas','123456'),(2,'jack','123456'),(3,'lucy','123456');

2.2 创建一个普通Maven项目

2.3 导入依赖

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.16</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.4.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/junit/junit -->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
    <scope>test</scope>
</dependency>

2.4 编写mybatis-config.xml

模板

注意 XML 头部的声明，它用来验证 XML 文档的正确性。environment 元素体中包含了事务管理和连接池的配置。mappers 元素则包含了一组映射器（mapper），这些映射器的 XML 映射文件包含了 SQL 代码和映射定义信息。

<?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"/>
      <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="org/mybatis/example/BlogMapper.xml"/>
  </mappers>
</configuration>

我的

<?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"/>
        <dataSource type="POOLED">
            <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
            <property name="url" value="jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf8&amp;useSSL=false&amp;serverTimezone=Asia/Shanghai"/>
            <property name="username" value="root"/>
            <property name="password" value="123456"/>
        </dataSource>
    </environment>
</environments>
    <mappers>
        <mapper resource="UserMapper.xml"/>
    </mappers>
</configuration>

2.5 编写MybatisUtil

public class MybatisUtil {
    private static SqlSessionFactory sqlSessionFactory;
    static {
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession() {
        return sqlSessionFactory.openSession();
    }
}

2.5 创建User实体类

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

    public User() {
    }

    public User(int id, String name, String password) {
        this.id = id;
        this.name = name;
        this.password = password;
    }

    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 getPassword() {
        return password;
    }

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

    @Override
    public String toString() {
        final StringBuffer sb = new StringBuffer("User{");
        sb.append("id=").append(id);
        sb.append(", name='").append(name).append('\'');
        sb.append(", password='").append(password).append('\'');
        sb.append('}');
        return sb.toString();
    }
}

2.6 创建UserMapper接口

public interface UserMapper {
    List<User> getUserList();
}

2.7 编写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="top.lucas9.dao.UserMapper">
    <!--id:对应的方法名-->
    <select id="getUserList" resultType="top.lucas9.entity.User">
        select * from user
    </select>
</mapper>

2.8 测试

public class UserMapperTest {
    @Test
    public void getUserList() {
        SqlSession sqlSession = null;
        try {
            sqlSession = MybatisUtil.getSqlSession();
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            List<User> userList = userMapper.getUserList();
            for (User user : userList) {
                System.out.println(user);
            }
        } finally {
            sqlSession.close();
        }
    }
}

输出：
User{id=1, name='lucas', password='123456'}
User{id=2, name='jack', password='123456'}
User{id=3, name='lucy', password='123456'}

3、xml实现CRUD

3.1 UserMapper.java

public interface UserMapper {
    List<User> getUserList();
    int addUser(User user);
    User selectUserById(int id);
    int updateUserById(User user);
    int deleteUserById(int id);
}

3.2 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="top.lucas9.dao.UserMapper">
    <!--id:对应的方法名-->
    <select id="getUserList" resultType="top.lucas9.entity.User">
        select * from user
    </select>

    <insert id="addUser" parameterType="top.lucas9.entity.User">
        insert into user (id,name,password) values (#{id},#{name},#{password})
    </insert>

    <select id="selectUserById" parameterType="int" resultType="top.lucas9.entity.User">
        select * from user where id = #{id}
    </select>

    <update id="updateUserById" parameterType="top.lucas9.entity.User">
        update user set name = #{name}, password = #{password} where id = #{id}
    </update>

    <delete id="deleteUserById" parameterType="int">
        delete from user where id = #{id}
    </delete>
</mapper>

3.3 UserMapperTest.java

注意：

需要在mybatis-config.xml中绑定Usermapper.xml
mybatis默认不提交事物，所以增加、更新、删除需要提交事务 sqlSession.commit();。
可以在sqlSessionFactory.openSession()的时候传递参数true设置事务自动提交sqlSessionFactory.openSession(true)。

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import top.lucas9.dao.UserMapper;
import top.lucas9.entity.User;
import top.lucas9.utils.MybatisUtil;

import java.util.List;

/**
 * @author lucas9
 * @version 1.0
 * @date 2021/2/12 23:20
 */
public class UserMapperTest {

    @Test
    public void getUserList() {
        SqlSession sqlSession = null;
        try {
            sqlSession = MybatisUtil.getSqlSession();
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            List<User> userList = userMapper.getUserList();
            for (User user : userList) {
                System.out.println(user);
            }
        } finally {
            sqlSession.close();
        }
    }

    @Test
    public void addUser() {
        SqlSession sqlSession = null;
        try {
            sqlSession = MybatisUtil.getSqlSession();
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            userMapper.addUser(new User(4,"tom","abc"));
            sqlSession.commit();
        } finally {
            sqlSession.close();
        }
    }

    @Test
    public void selectUserById() {
        SqlSession sqlSession = null;
        try {
            sqlSession = MybatisUtil.getSqlSession();
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            User user = userMapper.selectUserById(4);
            System.out.println(user);
        } finally {
            sqlSession.close();
        }
    }

    @Test
    public void updateUserById() {
        SqlSession sqlSession = null;
        try {
            sqlSession = MybatisUtil.getSqlSession();
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            userMapper.updateUserById(new User(4,"tom","123456"));
            sqlSession.commit();
        } finally {
            sqlSession.close();
        }
    }

    @Test
    public void deleteUserById() {
        SqlSession sqlSession = null;
        try {
            sqlSession = MybatisUtil.getSqlSession();
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            userMapper.deleteUserById(4);
            sqlSession.commit();
        } finally {
            sqlSession.close();
        }
    }
}

4、注解实现CRUD

通过注解实现不需要对应的xml文件，但是仍然需要进行mapper的绑定，以此告诉 MyBatis 去哪里找映射文件。

4.1 mapper绑定

<mappers>
    <mapper class="top.lucas9.dao.UserMapper"/>
</mappers>

4.2 UserMapper.java

package top.lucas9.dao;

import org.apache.ibatis.annotations.*;
import top.lucas9.entity.User;

import java.util.List;

public interface UserMapper {
    @Select("select * from user")
    List<User> getUserList();

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

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

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

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

注：注解只适合简单的SQL语句，不推荐使用注解实现SQL。

5、传递多个参数的解决方案

方案一般有三种：实体类、map、注解。

5.1 UserMapper.java

public interface UserMapper {
    int addUserByUser(User user);
    int addUserByMap(Map map);
    int addUserByAnnotation(@Param("annotationId") int id, @Param("annotationName") String name, @Param("annotationPassword") String password);
}

5.2 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="top.lucas9.dao.UserMapper">
    <!--id:对应的方法名-->
    <insert id="addUserByUser" parameterType="top.lucas9.entity.User">
        insert into user (id,name,password) values (#{id},#{name},#{password})
    </insert>

    <insert id="addUserByMap" parameterType="map">
        insert into user (id,name,password) values (#{mapId},#{mapName},#{mapPassword})
    </insert>

    <insert id="addUserByAnnotation">
        insert into user (id,name,password) values (#{annotationId},#{annotationName},#{annotationPassword})
    </insert>
</mapper>

5.3 测试

public class UserMapperTest {

    @Test
    public void addUserByUser() {
        SqlSession sqlSession = null;
        try {
            sqlSession = MybatisUtil.getSqlSession();
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            userMapper.addUserByUser(new User(4,"user","user"));
            sqlSession.commit();
        } finally {
            sqlSession.close();
        }
    }
    
    @Test
    public void addUserByMap() {
        SqlSession sqlSession = null;
        try {
            sqlSession = MybatisUtil.getSqlSession();
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            HashMap map = new HashMap();
            map.put("mapId",5);
            map.put("mapName","map");
            map.put("mapPassword","map");
            userMapper.addUserByMap(map);
            sqlSession.commit();
        } finally {
            sqlSession.close();
        }
    }
    
    @Test
    public void addUserByAnnotation() {
        SqlSession sqlSession = null;
        try {
            sqlSession = MybatisUtil.getSqlSession();
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            userMapper.addUserByAnnotation(6,"annotation","annotation");
            sqlSession.commit();
        } finally {
            sqlSession.close();
        }
    }
}

5.4 总结

实体类

缺点：引用参数名要和实体类属性名对应，不适合不常使用的多个组合参数（即参数不在同一个实体类中，需要为此新建实体类）。
map

缺点：可读性差。
注解

缺点：不适合参数过多的情况。

实体类和注解配合使用，根据实际情况（参数个数、有无实体类）选择合适的方案。不推荐使用map。

6、XML配置

官网文档

7、结果映射ResultMap

官方文档

ResultMap是解决列名不匹配的一种方式。

7.1 简单使用

7.2 多对一

7.3 一对多

8、日志

8.1 概览

设置名	描述	有效值	默认值
logImpl	指定 MyBatis 所用日志的具体实现，未指定时将自动查找。	SLF4J 、 LOG4J 、 LOG4J2 、 JDK_LOGGING 、 COMMONS_LOGGING 、 STDOUT_LOGGING 、 NO_LOGGING	未设置

8.2 日志信息的优先级

日志记录的优先级，分为OFF、FATAL、ERROR、WARN、INFO、DEBUG、ALL或者您定义的级别。

Log4j建议只使用四个级别，优先级从高到低分别是ERROR、WARN、INFO、DEBUG。而且在企业中一般也只使用着四种。

下面是各个级别的介绍：

OFF 最高等级，用于关闭所有日志记录。
FATAL 指出每个严重的错误事件将会导致应用程序的退出。
ERROR 指出虽然发生错误事件，但仍然不影响系统的继续运行。
WARN 表明会出现潜在的错误情形。
INFO 一般和在粗粒度级别上，强调应用程序的运行全程。
DEBUG 一般用于细粒度级别上，对调试应用程序非常有帮助。
ALL 最低等级，用于打开所有日志记录。

8.3 配置日志

在mybatis-config.xml的configuration标签中添加settings标签，在settings中设置日志服务，并指定选择的值。

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

STDOUT_LOGGING

8.4 LOG4J

配置LOG4J

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

导入依赖

<!-- https://mvnrepository.com/artifact/log4j/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.encoding = utf8
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/lucas9.log
log4j.appender.file.encoding = UTF-8
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

测试

public class UserMapperTest {
    // 注意：Logger导入的包应该是org.apache.log4j.Logger;
    private Logger logger = Logger.getLogger(UserMapperTest.class);
    @Test
    public void log4jTest() {
        logger.error("error");
        logger.warn("warn");
        logger.info("info");
        logger.debug("debug");
    }
}

9、分页

9.1 真假分页

假分页

从数据库中取出所有的数据，然后分页在界面上显示。访问一次数据库，但由于选择的数据量比较大，所以第一次花费时间比较长，但之后每一页的显示都是直接、快速的，避免对数据库的多次访问。
真分页

确定要显示的数量和内容，然后每次都去数据库取出该少量数据，优点是数据量小，缺点是访问数据库频繁。

9.2 使用limit实现真分页

limit<M,N>分页实际上就是从结果集中“截取”出第M~N条记录（数据库从0开始标号）。

用法： limit #{startIndex},#{pageSize} 或 limit #{startIndex} offset #{pageSize}

UserMapper.java

public interface UserMapper {
    List<User> getUserByLimit(@Param("startIndex")int startIndex,  @Param("pageSize") int pageSize);
}

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="top.lucas9.dao.UserMapper">
    <!--id:对应的方法名-->
    <select id="getUserByLimit" resultType="top.lucas9.entity.User">
        select * from user limit #{startIndex},#{pageSize}
    </select>
</mapper>

Test

public class UserMapperTest {

    @Test
    public void getUserByLimit() {
        SqlSession sqlSession = null;
        try {
            sqlSession = MybatisUtil.getSqlSession();
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            int currentPage = 2, pageSize = 3;
            List<User> userList = userMapper.getUserByLimit((currentPage - 1) * pageSize, pageSize);
            for (User user : userList) {
                System.out.println(user);
            }
        } finally {
            sqlSession.close();
        }
    }
}

结果

数据库内容

查询内容

User{id=4, name='user', password='user'}
User{id=5, name='map', password='map'}
User{id=6, name='annotation', password='annotation'}

10、动态SQL

动态 SQL 是 MyBatis 的强大特性之一。他可以根据不同的条件简单地拼接出sql语句。

10.1 sql片段

在实际项目开发中，为了提高代码的复用性，在mybatis中把复用性很高的sql抽取出来，作为sql片段，方便其它地方引用。

基本用法

<?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="top.lucas9.dao.UserMapper">
    <sql id="resultPram">
        name, password
    </sql>
    
    <select id="getUserList" resultType="top.lucas9.entity.User">
        select <include refid="resultPram"/> from user
        -- 等同于 select name, password from user
    </select>
</mapper>

复用

<sql id="userColumns"> ${alias}.id,${alias}.username,${alias}.password </sql>

<select id="selectUsers" resultType="map">
  select
    <include refid="userColumns"><property name="alias" value="t1"/></include>,
    <include refid="userColumns"><property name="alias" value="t2"/></include>
  from some_table t1
    cross join some_table t2
</select>

跨文件引用

对于多个xml文件需要同时引用一段相同的slq片段，可以在某个xml 中定义这个 sql 代码片段,在需要引用的地方使用全称引用即可。

A.xml
<mapper namespace="top.lucas9.A">       
    <sql id="sql">
    </sql>          
</mapper>

B.xml
<mapper namespace="top.lucas9.B">       
    <select id="select" >
        select
        <include refid="top.lucas9.A.sql"/>
        from tableName
    </select>          
</mapper>

10.2 if

使用动态 SQL 最常见情景是根据条件包含 where 子句的一部分。

interface

public interface UserMapper {
    List<User> getUserByIf(@Param("name") String name, @Param("password") String password);
}

<?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="top.lucas9.dao.UserMapper">
    <select id="getUserByIf" resultType="top.lucas9.entity.User">
        select * from user where id > 3
        <if test="null != name">
            and name = #{name}
        </if>
        <if test="null != password">
            and password = #{password}
        </if>
    </select>
</mapper>

test

@Test
public void getUserByIf() {
    SqlSession sqlSession = null;
    try {
        sqlSession = MybatisUtil.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.getUserByIf(null, null);
        for (User user : userList) {
            System.out.println(user);
        }

        userList = userMapper.getUserByIf("user", null);
        for (User user : userList) {
            System.out.println(user);
        }

        userList = userMapper.getUserByIf(null, "map");
        for (User user : userList) {
            System.out.println(user);
        }
    } finally {]
        sqlSession.close();
    }
}

10.3 choose、when、otherwise

有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。

interface

public interface UserMapper {
    List<User> getUserByChoose(@Param("name") String name, @Param("password") String password);
}

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="top.lucas9.dao.UserMapper">
    <select id="getUserByChoose" resultType="top.lucas9.entity.User">
        select * from user where 1 = 1
        <choose>
            <when test="null != name">
                and name = #{name}
            </when>
            <when test="null != password">
                and password = #{password}
            </when>
            <otherwise>
                and id > 3
            </otherwise>
        </choose>
    </select>
</mapper>

test

@Test
public void getUserByChoose() {
    SqlSession sqlSession = null;
    try {
        sqlSession = MybatisUtil.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.getUserByChoose(null, null);
        for (User user : userList) {
            System.out.println(user);
        }

        userList = userMapper.getUserByChoose("user", null);
        for (User user : userList) {
            System.out.println(user);
        }

        userList = userMapper.getUserByChoose(null, "map");
        for (User user : userList) {
            System.out.println(user);
        }
    } finally {
        sqlSession.close();
    }
}

10.4 where

where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。

interface

public interface UserMapper {
    List<User> getUserByWhere(@Param("name") String name, @Param("password") String password);
}

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="top.lucas9.dao.UserMapper">
    <select id="getUserByWhere" resultType="top.lucas9.entity.User">
        select * from user
        <where>
            <if test="null != name">
                and name = #{name}
            </if>
            <if test="null != password">
                and password = #{password}
            </if>
        </where>
    </select>
</mapper>

test

@Test
public void getUserByWhere() {
    SqlSession sqlSession = null;
    try {
        sqlSession = MybatisUtil.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.getUserByWhere(null, null);
        for (User user : userList) {
            System.out.println(user);
        }

        userList = userMapper.getUserByWhere("user", null);
        for (User user : userList) {
            System.out.println(user);
        }

        userList = userMapper.getUserByWhere(null, "map");
        for (User user : userList) {
            System.out.println(user);
        }

        userList = userMapper.getUserByWhere("annotation", "annotation");
        for (User user : userList) {
            System.out.println(user);
        }
    } finally {
        sqlSession.close();
    }
}

10.5 set

用于动态更新语句的类似解决方案叫做 set。set 元素可以用于动态包含需要更新的列，忽略其它不更新的列。set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号（这些逗号是在使用条件语句给列赋值时引入的）。

interface

public interface UserMapper {
    List<User> updateUserBySet(@Param("id") int id, @Param("name") String name, @Param("password") String password);
}

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="top.lucas9.dao.UserMapper">
    <select id="updateUserBySet">
        update user
        <set>
            <if test="null != name">
                name = #{name},
            </if>
            <if test="null != password">
                password = #{password},
            </if>
        </set>
        where id = #{id}
    </select>
</mapper>

test

@Test
public void updateUserBySet() {
    SqlSession sqlSession = null;
    try {
        sqlSession = MybatisUtil.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        userMapper.updateUserBySet(3, "tom", null);
        sqlSession.commit();
    } finally {
        sqlSession.close();
    }
}

10.6 foreach

动态 SQL 的另一个常见使用场景是对集合进行遍历（尤其是在构建 IN 条件语句的时候）。

foreach 元素的功能非常强大，它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。这个元素也不会错误地添加多余的分隔符，看它多智能！

提示：你可以将任何可迭代对象（如 List、Set 等）、Map 对象或者数组对象作为集合参数传递给 foreach。当使用可迭代对象或者数组时，index 是当前迭代的序号，item 的值是本次迭代获取到的元素。当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值。

interface

public interface UserMapper {
    List<User> selectUserByForeach(List nameList);
}

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="top.lucas9.dao.UserMapper">
    <select id="selectUserByForeach" resultType="top.lucas9.entity.User">
        select * from user where name in
        <foreach item="item" index="index" collection="list"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>
</mapper>

test

@Test
public void selectUserByForeach() {
    SqlSession sqlSession = null;
    try {
        sqlSession = MybatisUtil.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<String> nameList = new ArrayList();
        nameList.add("lucas");
        nameList.add("tom");
        nameList.add("map");
        List<User> resultList = userMapper.selectUserByForeach(nameList);
        for (User user : resultList) {
            System.out.println(user);
        }
        sqlSession.commit();
    } finally {
        sqlSession.close();
    }
}

11、缓存

官方文档

勿忘∮心安

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mybatis学习笔记

文章目录Mybatis1、准备Mybatis2、第一个Mybatis程序2.1 创建数据库和表2.2 创建一个普通Maven项目2.3 导入依赖2.4 编写mybatis-config.xml2.5 编写MybatisUtil2.5 创建User实体类2.6 创建UserMapper接口2.7 编写UserMapper.xml2.8 测试3、xml实现CRUD3.1 UserMapper.java3.2 UserMapper.xml3.3 UserMapperTest.java4、注解实现CRUD4.1 m
复制链接

扫一扫