CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255),
    email VARCHAR(255)
);

CREATE TABLE role (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role_name VARCHAR(255)
);

CREATE TABLE user_role (
    user_id INT,
    role_id INT,
    FOREIGN KEY (user_id) REFERENCES user(id),
    FOREIGN KEY (role_id) REFERENCES role(id)
);

<?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_demo"/>
                <property name="username" value="root"/>
                <property name="password" value="password"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="UserMapper.xml"/>
    </mappers>
</configuration>

<?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.example.mapper.UserMapper">
    <select id="selectUserById" resultType="com.example.model.User">
        SELECT * FROM user WHERE id = #{id}
    </select>

    <resultMap id="userRoleMap" type="com.example.model.User">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <collection property="roles" ofType="com.example.model.Role">
            <id column="role_id" property="id"/>
            <result column="role_name" property="roleName"/>
        </collection>
    </resultMap>

    <select id="selectUserWithRoles" resultMap="userRoleMap">
        SELECT u.id, u.username, r.id AS role_id, r.role_name
        FROM user u
        LEFT JOIN user_role ur ON u.id = ur.user_id
        LEFT JOIN role r ON ur.role_id = r.id
        WHERE u.id = #{id}
    </select>
</mapper>

<!-- Mapper XML 配置 -->
<mapper namespace="com.example.mapper.UserMapper">
    <!-- 定義結(jié)果映射 -->
    <resultMap id="BaseResultMap" type="com.example.model.User">
        <id column="id" property="id" />
        <result column="username" property="username" />
        <result column="email" property="email" />
    </resultMap>

    <!-- 使用定義好的結(jié)果映射查詢用戶 -->
    <select id="selectUserById" resultMap="BaseResultMap">
        SELECT id, username, email FROM user WHERE id = #{id}
    </select>
</mapper>

<settings>
    <setting name="lazyLoadingEnabled" value="true"/>
    <setting name="aggressiveLazyLoading" value="false"/>
</settings>

<!-- 開啟當(dāng)前 Mapper 的二級(jí)緩存 -->
<cache eviction="FIFO" flushInterval="60000" size="512" readOnly="true"/>

<!-- Mapper XML 文件中的示例 -->
<mapper namespace="com.example.mapper.UserMapper">
    <!-- 動(dòng)態(tài)生成 SELECT 查詢 -->
    <select id="findUsersByConditions" resultType="User">
        <where>
            <if test="username != null">
                AND username = #{username}
            </if>
            <if test="email != null">
                AND email = #{email}
            </if>
        </where>
    </select>

    <!-- 動(dòng)態(tài)生成 UPDATE 查詢 -->
    <update id="updateUser">
        UPDATE users
        <set>
            <if test="username != null">
                username = #{username},
            </if>
            <if test="email != null">
                email = #{email},
            </if>
        </set>
        WHERE id = #{id}
    </update>

    <!-- 使用 foreach 生成 IN 子句 -->
    <select id="findUsersByIds" resultType="User">
        SELECT * FROM users
        WHERE id IN
        <foreach item="id" collection="ids" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>
</mapper>