月度归档： 2025 年 10 月

2025-10-20-JavaEE简单整理

MyBatis 框架全面指南

1. 概述

MyBatis 是一个优秀的持久层框架，它简化了 Java 应用程序与关系型数据库之间的交互。与传统的 JDBC 相比，MyBatis 通过 XML 或注解的方式将 SQL 语句与 Java 对象进行映射，避免了大量样板代码，同时保持了 SQL 的灵活性和可控制性。

核心优势：

  • SQL 与代码分离，便于维护
  • 减少数据转换工作量
  • 保留 SQL 的完整控制权
  • 支持动态 SQL
  • 内置缓存机制
  • 轻量级，学习曲线平缓

2. 安装与配置

2.1 安装方式

传统方式：

  • 将 mybatis-x.x.x.jar 文件置于类路径（classpath）中

Maven 依赖：

<dependency>
  <groupId>org.mybatis</groupId>
  <artifactId>mybatis</artifactId>
  <version>x.x.x</version>
</dependency>

Gradle 依赖：

implementation 'org.mybatis:mybatis:x.x.x'

2.2 版本选择

根据 GitHub Releases 信息，MyBatis 3.5.19 是最新稳定版本（截至知识库更新时间）。需要注意：

  • 3.5.18 是最后一个支持 Java 8 的版本
  • 之后的版本将逐步升级到 Java 11 和 Java 17
  • 新项目应根据 JDK 版本选择合适的 MyBatis 版本

版本选择建议：

  • 评估功能需求、社区支持、性能表现、学习曲线和维护状态
  • 优先选择 LTS (长期支持) 版本
  • 避免使用已停止维护的版本
  • 新项目可考虑较新的稳定版本

3. 核心架构与组件

3.1 核心组件

  • SqlSessionFactoryBuilder：用于创建 SqlSessionFactory，方法作用域
  • SqlSessionFactory：创建 SqlSession 的工厂类，应用作用域
  • SqlSession：执行 SQL 命令的主要接口，请求/方法作用域
  • Mapper 接口：定义数据库操作方法，方法作用域
  • Mapper XML：包含 SQL 语句的配置文件

3.2 工作流程

  1. 通过 SqlSessionFactoryBuilder 创建 SqlSessionFactory
  2. SqlSessionFactory 创建 SqlSession
  3. SqlSession 获取 Mapper 接口实例
  4. 调用 Mapper 方法执行数据库操作
  5. 提交事务并关闭 SqlSession

4. 配置详解

4.1 主配置文件 (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>
  <settings>
    <!– 全局配置参数 –>
    <setting name="mapUnderscoreToCamelCase" value="true"/>
    <setting name="cacheEnabled" value="true"/>
  </settings>
 
  <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>

4.2 从 XML 构建 SqlSessionFactory

String resource = "org/mybatis/example/mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

4.3 无 XML 配置方式

DataSource dataSource = BlogDataSourceFactory.getBlogDataSource();
TransactionFactory transactionFactory = new JdbcTransactionFactory();
Environment environment = new Environment("development", transactionFactory, dataSource);
Configuration configuration = new Configuration(environment);
configuration.addMapper(BlogMapper.class);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration);

5. 基本使用

5.1 获取 SqlSession

try (SqlSession session = sqlSessionFactory.openSession()) {
  // 执行数据库操作
}

5.2 执行查询

方式一：直接使用 SqlSession

Blog blog = (Blog) session.selectOne("org.mybatis.example.BlogMapper.selectBlog", 101);

方式二：通过 Mapper 接口（推荐）

BlogMapper mapper = session.getMapper(BlogMapper.class);
Blog blog = mapper.selectBlog(101);

5.3 执行插入/更新/删除

User user = new User();
user.setName("张三");
user.setEmail("zhangsan@example.com");

int rows = mapper.insertUser(user);
session.commit(); // 提交事务

6. 映射配置

6.1 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="org.mybatis.example.BlogMapper">
  <select id="selectBlog" resultType="Blog">
    SELECT * FROM Blog WHERE id = #{id}
  </select>
 
  <insert id="insertUser" parameterType="User">
    INSERT INTO users(name, email) VALUES(#{name}, #{email})
  </insert>
</mapper>

6.2 注解方式

package org.mybatis.example;
public interface BlogMapper {
  @Select("SELECT * FROM blog WHERE id = #{id}")
  Blog selectBlog(int id);
 
  @Insert("INSERT INTO users(name, email) VALUES(#{name}, #{email})")
  @Options(useGeneratedKeys = true, keyProperty = "id")
  int insertUser(User user);
}

注解与 XML 选择建议：

  • 简单语句：使用注解更简洁
  • 复杂语句：XML 提供更好的可读性和维护性
  • 团队可根据情况统一规范

7. 动态 SQL

7.1 常用动态 SQL 元素

if 元素：

<select parameterType="map" resultType="User">
  SELECT * FROM users
  WHERE 1=1
  <if test="name != null">
    AND name = #{name}
  </if>
  <if test="email != null">
    AND email = #{email}
  </if>
</select>

choose/when/otherwise 元素：

<select parameterType="map" resultType="User">
  SELECT * FROM users
  WHERE status = 'ACTIVE'
  <choose>
    <when test="name != null">
      AND name like #{name}
    </when>
    <when test="email != null">
      AND email = #{email}
    </when>
    <otherwise>
      AND 1=1
    </otherwise>
  </choose>
</select>

foreach 元素：

<select parameterType="list" resultType="User">
  SELECT * FROM users
  WHERE id IN
  <foreach item="id" collection="list" open="(" separator="," close=")">
    #{id}
  </foreach>
</select>

7.2 其他动态 SQL 元素

  • trim：自定义前缀、后缀和去除字符
  • where：智能处理 WHERE 子句
  • set：智能处理 UPDATE 语句中的 SET 子句
  • bind：创建变量绑定

8. 关联查询

8.1 一对一关联

<resultMap type="User" id="userWithAddress">
  <id property="id" column="user_id"/>
  <result property="name" column="user_name"/>
  <association property="address" javaType="Address">
    <id property="id" column="address_id"/>
    <result property="street" column="street"/>
    <result property="city" column="city"/>
  </association>
</resultMap>

<select id="selectUserWithAddress" resultMap="userWithAddress">
  SELECT
    u.id as user_id, u.name as user_name,
    a.id as address_id, a.street, a.city
  FROM users u
  LEFT JOIN addresses a ON u.address_id = a.id
  WHERE u.id = #{id}
</select>

8.2 一对多关联

<resultMap type="User" id="userWithOrders">
  <id property="id" column="user_id"/>
  <result property="name" column="user_name"/>
  <collection property="orders" ofType="Order">
    <id property="id" column="order_id"/>
    <result property="orderDate" column="order_date"/>
    <result property="amount" column="amount"/>
  </collection>
</resultMap>

<select id="selectUserWithOrders" resultMap="userWithOrders">
  SELECT
    u.id as user_id, u.name as user_name,
    o.id as order_id, o.order_date, o.amount
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  WHERE u.id = #{id}
</select>

9. 缓存机制

9.1 一级缓存

  • 作用范围：SqlSession 级别（默认开启，不可关闭） — 同一个session
  • 生命周期：随 SqlSession 创建而创建，随 SqlSession 关闭而销毁
  • 触发清空的情况：
    • 执行 INSERT/UPDATE/DELETE 操作
    • 调用 sqlSession.clearCache()
    • 执行事务回滚
    • 配置不同的 Statement ID

@Test
public void findBookById(){
    SqlSession sqlSession= MyBatisUtils.getSqlSession();
    Book book=sqlSession.selectOne( "getBookById",1);
    System.out.println(book);
    Book book1=sqlSession.selectOne( "getBookById",1);
    System.out.println(book1);
    sqlSession.close();
}

// 示例：相同查询在同一个SqlSession中只执行一次
User user1 = sqlSession.selectOne("getUserById", 1); // 执行SQL
User user2 = sqlSession.selectOne("getUserById", 1); // 从缓存获取

2025-10-20 10:45:26,508 [main] DEBUG com.itheima.mapper.BookMapper – Cache Hit Ratio [com.itheima.mapper.BookMapper]: 0.0
2025-10-20 10:45:26,767 [main] DEBUG com.itheima.mapper.BookMapper.getBookById – ==>  Preparing: select * from tb_book where id = ?
2025-10-20 10:45:26,820 [main] DEBUG com.itheima.mapper.BookMapper.getBookById – ==> Parameters: 1(Integer)
2025-10-20 10:45:26,873 [main] DEBUG com.itheima.mapper.BookMapper.getBookById – <==      Total: 1
Book{id=1, name='null', price=99.9, author='小刚'}
2025-10-20 10:45:26,880 [main] DEBUG com.itheima.mapper.BookMapper – Cache Hit Ratio [com.itheima.mapper.BookMapper]: 0.0
Book{id=1, name='null', price=99.9, author='小刚'}

//更新了就不会触发缓存
@Test
public void findBookById1(){
    SqlSession sqlSession= MyBatisUtils.getSqlSession();
    Book book=sqlSession.selectOne( "getBookById",1);
    System.out.println(book);

    Book book2=new Book();
    book2.setId(3);
    book2.setAuthor("Jeek");
    book2.setPrice(0.2);
    sqlSession.update("updateBook",book2);
    sqlSession.commit();
    Book book3=sqlSession.selectOne( "getBookById",3);
    System.out.println(book3);
    sqlSession.close();
}

2025-10-20 10:46:43,588 [main] DEBUG com.itheima.mapper.BookMapper – Cache Hit Ratio [com.itheima.mapper.BookMapper]: 0.0
2025-10-20 10:46:43,834 [main] DEBUG com.itheima.mapper.BookMapper.getBookById – ==>  Preparing: select * from tb_book where id = ?
2025-10-20 10:46:43,891 [main] DEBUG com.itheima.mapper.BookMapper.getBookById – ==> Parameters: 1(Integer)
2025-10-20 10:46:43,947 [main] DEBUG com.itheima.mapper.BookMapper.getBookById – <==      Total: 1
Book{id=1, name='null', price=99.9, author='小刚'}
2025-10-20 10:46:43,955 [main] DEBUG com.itheima.mapper.BookMapper.updateBook – ==>  Preparing: update tb_book set author=?, price=? where id = ?
2025-10-20 10:46:43,960 [main] DEBUG com.itheima.mapper.BookMapper.updateBook – ==> Parameters: Jeek(String), 0.2(Double), 3(Integer)
2025-10-20 10:46:43,961 [main] DEBUG com.itheima.mapper.BookMapper.updateBook – <==    Updates: 1
2025-10-20 10:46:43,963 [main] DEBUG com.itheima.mapper.BookMapper – Cache Hit Ratio [com.itheima.mapper.BookMapper]: 0.0
2025-10-20 10:46:43,963 [main] DEBUG com.itheima.mapper.BookMapper.getBookById – ==>  Preparing: select * from tb_book where id = ?
2025-10-20 10:46:43,963 [main] DEBUG com.itheima.mapper.BookMapper.getBookById – ==> Parameters: 3(Integer)
2025-10-20 10:46:43,964 [main] DEBUG com.itheima.mapper.BookMapper.getBookById – <==      Total: 1
Book{id=3, name='null', price=0.2, author='Jeek'}

9.2 二级缓存

— 不同session

@Test
public void findBookById2(){
    SqlSession sqlSession= MyBatisUtils.getSqlSession();
    Book book=sqlSession.selectOne( "getBookById",1);
    System.out.println(book);
    sqlSession.close();

    SqlSession sqlSession1= MyBatisUtils.getSqlSession();
    Book book1=sqlSession1.selectOne( "getBookById",1);
    System.out.println(book1);
    sqlSession1.close();
}

启用配置：

<settings>
  <setting name="cacheEnabled" value="true"/>
</settings>

Mapper 级别配置：

<cache
  eviction="LRU"                <!– 淘汰策略（默认LRU） –>
  flushInterval="60000"         <!– 刷新间隔（毫秒） –>
  size="512"                    <!– 缓存对象数量 –>
  readOnly="true"/>             <!– 只读模式（性能更优） –>

<?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.itheima.mapper.BookMapper">
    <cache/>
<!–    开启缓存–>
    <select id="getBookById" parameterType="Integer" resultType="Book">
        select *
        from tb_book
        where id = #{id}
    </select>
    <!–    <insert id="" parameterType="">–>
    <!–    </insert>–>
    <update id="updateBook" parameterType="Book">
        update tb_book
        set author=#{author},
            price=#{price}
        where id = #{id}
    </update>
    <!–    <delete id="" parameterType="">–>
    <!–    </delete>–>
</mapper>

2025-10-20 10:42:57,049 [main] DEBUG com.itheima.mapper.BookMapper – Cache Hit Ratio [com.itheima.mapper.BookMapper]: 0.0
2025-10-20 10:42:57,300 [main] DEBUG com.itheima.mapper.BookMapper.getBookById – ==>  Preparing: select * from tb_book where id = ?
2025-10-20 10:42:57,353 [main] DEBUG com.itheima.mapper.BookMapper.getBookById – ==> Parameters: 1(Integer)
2025-10-20 10:42:57,409 [main] DEBUG com.itheima.mapper.BookMapper.getBookById – <==      Total: 1
Book{id=1, name='null', price=99.9, author='小刚'}
2025-10-20 10:42:57,438 [main] DEBUG com.itheima.mapper.BookMapper – Cache Hit Ratio [com.itheima.mapper.BookMapper]: 0.5
Book{id=1, name='null', price=99.9, author='小刚'}

第二次查询命中二级缓存

缓存策略对比：

策略描述适用场景
LRU最近最少使用常规使用
FIFO先进先出固定顺序访问
SOFT软引用内存敏感场景
WEAK弱引用内存极度敏感

10. 与 Spring 集成

10.1 依赖配置

Spring Boot 推荐方式：

<dependency>
  <groupId>org.mybatis.spring.boot</groupId>
  <artifactId>mybatis-spring-boot-starter</artifactId>
  <version>3.0.3</version>
</dependency>

传统 Spring 项目：

<dependency>
  <groupId>org.mybatis</groupId>
  <artifactId>mybatis-spring</artifactId>
  <version>3.0.3</version>
</dependency>

10.2 配置方案

方案一：Spring Boot 自动配置（最简单）

# application.yml
mybatis:
  mapper-locations: classpath*:mapper/**/*.xml
  type-aliases-package: com.example.model
  configuration:
    map-underscore-to-camel-case: true  # 自动驼峰转换

方案二：Java Config 全配置（精细化控制）

@Configuration
public class MyBatisConfig {
 
  @Bean
  @ConfigurationProperties(prefix = "spring.datasource")
  public DataSource dataSource() {
    return DataSourceBuilder.create().build();
  }

  @Bean
  public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
    SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
    factory.setDataSource(dataSource);
    factory.setTypeAliasesPackage("com.example.model");
   
    // 自定义配置
    org.apache.ibatis.session.Configuration config = new Configuration();
    config.setMapUnderscoreToCamelCase(true);
    config.setDefaultFetchSize(100);
    factory.setConfiguration(config);
   
    // 插件配置
    factory.setPlugins(
      new MyBatisInterceptor(),
      new PaginationInterceptor()
    );
   
    return factory.getObject();
  }

  @Bean
  public MapperScannerConfigurer mapperScanner() {
    MapperScannerConfigurer scanner = new MapperScannerConfigurer();
    scanner.setBasePackage("com.example.mapper");
    scanner.setAnnotationClass(Repository.class);
    return scanner;
  }
}

方案三：XML 传统配置（兼容旧项目）

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
  <property name="dataSource" ref="dataSource"/>
  <property name="configLocation" value="classpath:mybatis-config.xml"/>
</bean>

<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
  <property name="basePackage" value="com.example.mapper"/>
</bean>

10.3 事务管理

@Configuration
@EnableTransactionManagement
public class TransactionConfig {
 
  @Bean
  public PlatformTransactionManager transactionManager(DataSource dataSource) {
    return new DataSourceTransactionManager(dataSource);
  }
 
  // 可定义事务模板
  @Bean
  public TransactionTemplate transactionTemplate(PlatformTransactionManager manager) {
    return new TransactionTemplate(manager);
  }
}

10.4 使用规范

Mapper 接口示例：

@Repository
public interface UserMapper {
  @Select("SELECT * FROM users WHERE id = #{id}")
  User selectById(@Param("id") int id);
 
  @Options(useGeneratedKeys = true, keyProperty = "id")
  @Insert("INSERT INTO users(name) VALUES(#{name})")
  int insert(User user);
}

Service 层示例：

@Service
@Transactional(readOnly = true)
public class UserService {
 
  private final UserMapper userMapper;
 
  @Autowired
  public UserService(UserMapper userMapper) {
    this.userMapper = userMapper;
  }
 
  @Transactional
  public User createUser(String name) {
    User user = new User();
    user.setName(name);
    userMapper.insert(user);
    return userMapper.selectById(user.getId());
  }
}

10.5 高级功能

动态数据源配置：

@Bean
@Primary
public DataSource dynamicDataSource() {
  DynamicDataSource ds = new DynamicDataSource();
  ds.setDefaultTargetDataSource(primaryDataSource());
  ds.setTargetDataSources(Map.of(
    "master", primaryDataSource(),
    "slave", secondaryDataSource()
  ));
  return ds;
}

11. 最佳实践

11.1 代码规范

  1. 使用 Mapper 接口：比直接使用 SqlSession 更类型安全，更易维护
  2. 命名空间规范：使用 Java 包命名规范，如 com.example.mapper.UserMapper
  3. SQL 语句命名：使用动词+名词形式，如 selectUserById
  4. 参数命名：使用 @Param 注解明确参数名称

11.2 性能优化

  1. 合理使用缓存：
  • 读多写少的场景使用二级缓存
  • 注意缓存一致性问题
  1. 批量操作：
  • 使用 ExecutorType.BATCH 提高批量操作性能
  • 避免一次性处理过多数据
  1. SQL 优化：
  • 避免 SELECT *，只查询需要的字段
  • 合理使用索引
  • 分页查询使用合理的分页方式

11.3 常见问题解决方案

  1. N+1 查询问题：
  • 使用 JOIN 一次性查询
  • 使用 @Select 注解的 fetchType 属性
  • 使用 LazyLoadingEnabled 配置
  1. 驼峰命名转换：
  • 启用 mapUnderscoreToCamelCase 配置
  • 确保数据库字段使用下划线命名法
  1. 事务管理：
  • 明确事务边界
  • 避免在事务方法中调用非事务方法
  • 合理设置事务超时时间

12. 版本更新要点

MyBatis 3.5.18-3.5.19 主要更新

  • 3.5.19：修复了 3.5.17 中引入的回归问题
  • 3.5.18：
    • 修复 问题
    • 忽略空的 xnode
    • 共享表达式验证器
    • 优化 mapper 构建器
    • 构建系统更新：支持 Java 17 构建

重要提示：3.5.18 是最后一个支持 Java 8 的版本，后续版本将逐步升级到 Java 11 和 Java 17。

13. 总结

MyBatis 作为一款轻量级持久层框架，提供了 SQL 与代码分离、自动映射、动态 SQL 和缓存机制等核心功能，同时保持了对 SQL 的完全控制权。它既不像传统 JDBC 那样繁琐，也不像全自动 ORM 框架那样失去对 SQL 的控制，是一种平衡性极佳的解决方案。

使用建议：

  • 新项目优先考虑 MyBatis 与 Spring Boot 的整合方案
  • 根据项目复杂度选择 XML 或注解方式
  • 重视命名规范和代码结构
  • 合理使用缓存提高性能
  • 遵循作用域和生命周期的最佳实践

通过合理使用 MyBatis，可以显著提高开发效率，同时保证应用程序的性能和可维护性。

resources/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="cacheEnabled" value="true"/>
    </settings>
    <typeAliases>
        <package name="com.itheima.pojo"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${mysql.driver}"/>
                <property name="url" value="${mysql.url}"/>
                <property name="username" value="${mysql.username}"/>
                <property name="password" value="${mysql.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mapper/UserMapper.xml"/>
        <mapper resource="mapper/StudentMapper.xml"/>
        <mapper resource="mapper/CustomerMapper.xml"/>
        <mapper resource="mapper/PersonMapper.xml"/>
        <mapper resource="mapper/IdCardMapper.xml"/>
        <mapper resource="mapper/UsersMapper.xml"/>
        <mapper resource="mapper/OrdersMapper.xml"/>
        <mapper resource="mapper/ProductMapper.xml"/>
        <mapper resource="mapper/BookMapper.xml"/>
    </mappers>
</configuration>

resources/mapper/BookMapper.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.itheima.mapper.BookMapper">
    <cache/>
<!–    开启缓存–>
    <select id="getBookById" parameterType="Integer" resultType="Book">
        select *
        from tb_book
        where id = #{id}
    </select>
    <!–    <insert id="" parameterType="">–>
    <!–    </insert>–>
    <update id="updateBook" parameterType="Book">
        update tb_book
        set author=#{author},
            price=#{price}
        where id = #{id}
    </update>
    <!–    <delete id="" parameterType="">–>
    <!–    </delete>–>
</mapper>

resources/mapper/CustomerMapper.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.itheima.mapper.CustomerMapper">
    <select id="findCustomerById" parameterType="com.itheima.pojo.Customer" resultType="com.itheima.pojo.Customer">
        select * from t_customer where 1=1
        <if test="username!=null and username !="">
            and username like concat('%',#{username},'%')
        </if>
        <if test="jobs!=null and jobs!="">
            and jobs=#{jobs}
        </if>
    </select>
    <select id="findCustomerByNameOrJobs" parameterType="com.itheima.pojo.Customer"
            resultType="com.itheima.pojo.Customer">
        select * from t_customer where 1=1
        <choose>
            <when test="username!=null and username != "">
                and username like concat('%',#{username},'%')
            </when>
            <when test="jobs!=null and jobs != "">
                and jobs like concat('%',#{username},'%')
            </when>
            <otherwise>
                and phone is not null
            </otherwise>
        </choose>
    </select>
    <select id="findCustomerByNameOrJobs2" parameterType="com.itheima.pojo.Customer"
            resultType="com.itheima.pojo.Customer">
        select * from t_customer
        <trim prefix="where" prefixOverrides="and">
            <if test="username!=null and username != "">
                and username like concat('%',#{username},'%')
            </if>
            <if test="jobs!=null and jobs != "">
                and jobs=#{jobs}
            </if>
        </trim>
    </select>
    <select id="findCustomerByNameAndJobs" parameterType="com.itheima.pojo.Customer"
            resultType="com.itheima.pojo.Customer">
        select * from t_customer
        <where>
            <if test="username!=null and username !="">
                and username like concat('%',#{username},'%')
            </if>
            <if test="jobs!=null and jobs!="">
                and jobs=#{jobs}
            </if>
        </where>
    </select>
    <select id="findCustomerByArray" resultType="com.itheima.pojo.Customer">
        SELECT * FROM t_customer
        <where>
            <if test="array != null and array.length > 0">
                id IN
                <foreach item="id" collection="array" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>
<!–    <select id="findCustomerByArray" resultType="com.itheima.pojo.Customer">–>
<!–        SELECT * FROM t_customer–>
<!–        <where>–>
<!–            <if test="array != null and array.length > 0">–>
<!–                id IN–>
<!–                <foreach item="id" collection="array" open="(" separator="," close=")">–>
<!–                    #{id}–>
<!–                </foreach>–>
<!–            </if>–>
<!–        </where>–>
<!–    </select>–>
    <select id="findByList" parameterType="java.util.List" resultType="com.itheima.pojo.Customer">
        select * from t_customer where id in
        <foreach collection="list" item="id" index="index" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>
    <select id="findByMap" parameterType="java.util.Map" resultType="com.itheima.pojo.Customer">
        select * from t_customer where id in
        <foreach collection="list" item="roleMap" index="index" open="(" separator="," close=")">
            #{roleMap}
        </foreach>
    </select>
    <!–    <insert id="" parameterType="">–>
    <!–    </insert>–>
    <update id="updateCustomerBySet" parameterType="com.itheima.pojo.Customer">
        update t_customer
        <set>
            <if test="username!=null and username !="">
                username=#{username},
            </if>
            <if test="jobs!=null and jobs !="">
                jobs=#{jobs},
            </if>
            <if test="phone!=null and phone !="">
                phone=#{phone},
            </if>
        </set>
        where id=#{id}
    </update>
    <!–    <delete id="" parameterType="">–>
    <!–    </delete>–>
</mapper>

resources/mapper/OrdersMapper.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.itheima.mapper.OrdersMapper">
    <select id="getOrdersByUserId" parameterType="Integer" resultMap="ordersWithProductResult">
        select *
        from tb_orders
        where  id = #{id}
    </select>
    <resultMap id="ordersWithProductResult" type="Orders">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="sex" column="sex"/>
        <collection property="productList" ofType="Product"
                    select="com.itheima.mapper.ProductMapper.getProductById"
                    column="id"/>
    </resultMap>
<!–    <insert id="" parameterType="">–>
<!–    </insert>–>
<!–    <update id="" parameterType="">–>
<!–    </update>–>
<!–    <delete id="" parameterType="">–>
<!–    </delete>–>
</mapper>

resources/mapper/UsersMapper.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.itheima.mapper.UsersMapper">

    <!– 用户订单查询 –>
    <select id="getUserWithOrders" parameterType="Integer" resultMap="userOrderResultMap">
        SELECT
            u.id,
            u.username,
            u.address,
            o.id AS order_id,
            o.number
        FROM tb_user u
                 LEFT JOIN tb_orders o ON u.id = o.user_id
        WHERE u.id = #{id}
    </select>

    <!– 用户与订单映射关系 –>
    <resultMap id="userOrderResultMap" type="com.itheima.pojo.Users">
        <!– 主键映射（必须放在最前面） –>
        <id property="id" column="id"/>

        <!– 普通字段映射 –>
        <result property="name" column="name"/>
        <result property="address" column="address"/>

        <!– 订单集合映射（关键修复：正确闭合标签） –>
        <collection
                property="orders"
                javaType="java.util.ArrayList"
                ofType="com.itheima.pojo.Orders">
            <!– 订单主键 –>
            <id property="id" column="order_id"/>
            <!– 订单其他字段 –>
            <result property="number" column="number"/>
        </collection>
    </resultMap>

    <!– 身份证与人员映射（修复顺序问题） –>
    <resultMap id="IdCardWithPersonResult" type="com.itheima.pojo.IdCard">
        <!– 主键必须放在最前面 –>
        <id property="id" column="id"/>

        <!– 普通字段 –>
        <result property="code" column="code"/>

        <!– 关联对象 –>
        <association
                property="person"
                javaType="com.itheima.pojo.Person">
            <id property="id" column="person_id"/>
            <result property="name" column="person_name"/>
            <result property="age" column="person_age"/>
        </association>
    </resultMap>

</mapper>

resources/db.properties

mysql.driver=com.mysql.cj.jdbc.Driver
mysql.url=jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
mysql.username=root
mysql.password=123456

resources/log4j.properties

log4j.rootLogger=DEBUG, Console

log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p %c – %m%n

log4j.logger.java.sql.Result=INFO
log4j.logger.org.apache=INFO
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

src/test/java/MybatisTest.java

import com.itheima.pojo.*;
import com.itheima.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MybatisTest {
    @Test
    public void testFindCustomerById(){
        SqlSession sqlSession= MyBatisUtils.getSqlSession();
        Customer customer=new Customer();

        List<Customer> list =sqlSession.selectList("findCustomerById",customer);
        for(Customer c:list){
            System.out.println(c);
        }
        sqlSession.close();
    }
    @Test
    public void testfindCustomerByNameOrJobs(){
        SqlSession sqlSession= MyBatisUtils.getSqlSession();
        Customer customer=new Customer();

        List<Customer> list =sqlSession.selectList("findCustomerByNameOrJobs2",customer);
        for(Customer c:list){
            System.out.println(c);
        }
        sqlSession.close();
    }
    @Test
    public void testfindCustomerByArray(){
        SqlSession sqlSession= MyBatisUtils.getSqlSession();
        int[] ids=new int[]{1,2};
        List<Customer> list =sqlSession.selectList("findCustomerByArray",ids);
        for(Customer c:list){
            System.out.println(c);
        }
        sqlSession.close();
    }
    @Test
    public void testfindCustomerByList(){
        SqlSession sqlSession= MyBatisUtils.getSqlSession();
        List<Integer> ids=new ArrayList<>();
        ids.add(2);
        ids.add(3);
        List<Customer> list =sqlSession.selectList("findByList",ids);
        for(Customer c:list){
            System.out.println(c);
        }
        sqlSession.close();
    }
    @Test
    public void testfindCustomerByMap(){
        SqlSession sqlSession= MyBatisUtils.getSqlSession();
        List<Integer> ids=new ArrayList<>();
        ids.add(2);
        ids.add(3);
        Map<String,Object> map=new HashMap<String,Object>();
        map.put("id",ids);
        map.put("jobs","teacher");
        List<Customer> list =sqlSession.selectList("findByMap",map);
        for(Customer c:list){
            System.out.println(c);
        }
        sqlSession.close();
    }
    @Test
    public void updateCustomerById(){
        SqlSession sqlSession= MyBatisUtils.getSqlSession();
        Customer customer=new Customer();
        customer.setId(1);
        customer.setUsername("marry");
        int count=sqlSession.update("updateCustomerBySet",customer);
        if(count>0){
            System.out.println("Success");
        }else {
            System.out.println("Fail");
        }
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void findPersonById(){
        SqlSession sqlSession= MyBatisUtils.getSqlSession();
        Person person=sqlSession.selectOne( "getPersonById2",1);
        System.out.println(person);
        sqlSession.close();
    }
    @Test
    public void findUsersById(){
        SqlSession sqlSession= MyBatisUtils.getSqlSession();
        Users users=sqlSession.selectOne( "getUserWithOrders",1);
        System.out.println(users);
        sqlSession.close();
    }
    @Test
    public void getProductById(){
        SqlSession sqlSession= MyBatisUtils.getSqlSession();
        Orders orders=sqlSession.selectOne( "getOrdersByUserId",1);
        System.out.println(orders);
        sqlSession.close();
    }
    @Test
    public void findBookById(){
        SqlSession sqlSession= MyBatisUtils.getSqlSession();
        Book book=sqlSession.selectOne( "getBookById",1);
        System.out.println(book);
        Book book1=sqlSession.selectOne( "getBookById",1);
        System.out.println(book1);
        sqlSession.close();
    }
    @Test
    public void findBookById1(){
        SqlSession sqlSession= MyBatisUtils.getSqlSession();
        Book book=sqlSession.selectOne( "getBookById",1);
        System.out.println(book);

        Book book2=new Book();
        book2.setId(3);
        book2.setAuthor("Jeek");
        book2.setPrice(0.2);
        sqlSession.update("updateBook",book2);
        sqlSession.commit();
        Book book3=sqlSession.selectOne( "getBookById",3);
        System.out.println(book3);
        sqlSession.close();
    }
    @Test
    public void findBookById2(){
        SqlSession sqlSession= MyBatisUtils.getSqlSession();
        Book book=sqlSession.selectOne( "getBookById",1);
        System.out.println(book);
        sqlSession.close();

        SqlSession sqlSession1= MyBatisUtils.getSqlSession();
        Book book1=sqlSession1.selectOne( "getBookById",1);
        System.out.println(book1);
        sqlSession1.close();
    }
    @Test
    public void findBookById3(){
        SqlSession sqlSession= MyBatisUtils.getSqlSession();
        SqlSession sqlSession1= MyBatisUtils.getSqlSession();
        SqlSession sqlSession2= MyBatisUtils.getSqlSession();
        Book book=sqlSession.selectOne( "getBookById",1);
        System.out.println(book);
        Book book3=sqlSession1.selectOne( "getBookById",1);
        System.out.println(book3);
        sqlSession.close();
        Book book2=new Book();
        book2.setId(3);
        book2.setAuthor("Jeek");
        book2.setPrice(0.2);
        sqlSession.update("updateBook",book2);
        sqlSession.commit();
        Book book1=sqlSession2.selectOne( "getBookById",1);
        System.out.println(book1);
        sqlSession1.close();
    }
}