<dataSource type="POOLED">
    <property name="driver" value="${jdbc.driver}"/>
    <property name="url" value="${jdbc.url}"/>
    <property name="username" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>
</dataSource>

<dataSource type="UNPOOLED">
    <!-- 同POOLED的屬性配置 -->
</dataSource>

<dataSource type="JNDI">
    <property name="data_source" value="java:comp/env/jdbc/mybatis_db"/>
</dataSource>

public interface UserMapper {
    // 條件查詢用戶
    List<User> findByWhere(User user);
}

<select id="findByWhere" parameterType="user" resultType="user">
    select * from user
    <where>
        <!-- 當(dāng)username非空且非空字符串時(shí)，添加條件 -->
        <if test="username != null and username != ''">
            and username like #{username}
        </if>
        <!-- 當(dāng)sex非空且非空字符串時(shí)，添加條件 -->
        <if test="sex != null and sex != ''">
            and sex = #{sex}
        </if>
    </where>
</select>

@Test
public void testFindByWhere() {
    User user = new User();
    user.setUsername("%zz%"); // 模糊查詢包含"zz"的用戶名
    user.setSex("m");
    List<User> list = userMapper.findByWhere(user);
    // 遍歷結(jié)果...
}

public class User {
    private List<Integer> ids; // 存儲(chǔ)多個(gè)ID
    // 省略getter、setter
}

List<User> findByIds(User user);

<select id="findByIds" parameterType="user" resultType="user">
    select * from user
    <where>
        <!-- 
            collection：集合屬性名（此處為ids）
            open：SQL片段開頭
            close：SQL片段結(jié)尾
            separator：元素分隔符
            item：遍歷的元素別名
        -->
        <foreach collection="ids" open="id in (" separator="," close=")" item="id">
            #{id}
        </foreach>
    </where>
</select>

@Test
public void testFindByIds() {
    User user = new User();
    List<Integer> ids = new ArrayList<>();
    ids.add(1);
    ids.add(2);
    ids.add(3);
    user.setIds(ids);
    List<User> list = userMapper.findByIds(user); // 查詢ID為1、2、3的用戶
}

<foreach collection="ids" open="id = " separator="or id = " item="id">
    #{id}
</foreach>

<!-- 定義SQL片段 -->
<sql id="userColumns">
    id, username, birthday, sex, address
</sql>
<!-- 引用SQL片段 -->
<select id="findAll" resultType="user">
    select <include refid="userColumns"/> from user
</select>

public class Account implements Serializable {
    private Integer id;
    private Integer uid; // 關(guān)聯(lián)用戶ID
    private Double money;
    // 關(guān)聯(lián)的用戶對(duì)象
    private User user; 
    // 省略getter、setter
}

public class User implements Serializable {
    private Integer id;
    private String username;
    // 關(guān)聯(lián)的賬戶列表
    private List<Account> accounts; 
    // 省略getter、setter
}

public interface AccountMapper {
    List<Account> findAll();
}

<select id="findAll" resultMap="accountMap">
    <!-- 關(guān)聯(lián)查詢賬戶和用戶 -->
    select a.*, u.username, u.address 
    from account a
    left join user u on a.uid = u.id
</select>
<!-- 定義結(jié)果映射 -->
<resultMap id="accountMap" type="account">
    <id property="id" column="id"/>
    <result property="uid" column="uid"/>
    <result property="money" column="money"/>
    <!-- 關(guān)聯(lián)用戶對(duì)象（多對(duì)一） -->
    <association property="user" javaType="user">
        <result property="username" column="username"/>
        <result property="address" column="address"/>
    </association>
</resultMap>

public interface UserMapper {
    // 查詢用戶及關(guān)聯(lián)的賬戶
    List<User> findOneToMany();
}

<select id="findOneToMany" resultMap="userAccountMap">
    select u.*, a.id as aid, a.money 
    from user u
    left join account a on u.id = a.uid
</select>
<resultMap id="userAccountMap" type="user">
    <id property="id" column="id"/>
    <result property="username" column="username"/>
    <result property="birthday" column="birthday"/>
    <result property="sex" column="sex"/>
    <result property="address" column="address"/>
    <!-- 關(guān)聯(lián)賬戶列表（一對(duì)多） -->
    <collection property="accounts" ofType="account">
        <id property="id" column="aid"/> <!-- 注意別名避免與用戶ID沖突 -->
        <result property="money" column="money"/>
    </collection>
</resultMap>

public class Account implements Serializable {
    private Integer id;
    private Integer uid; // 關(guān)聯(lián)用戶ID
    private Double money;
    // 關(guān)聯(lián)的用戶對(duì)象
    private User user; 
    // 省略getter、setter
}

public class User implements Serializable {
    private Integer id;
    private String username;
    // 關(guān)聯(lián)的賬戶列表
    private List<Account> accounts; 
    // 省略getter、setter
}

public interface RoleDao {
    List<Role> findAll();
}

<select id="findAll" resultMap="roleMap">
    SELECT r.*, u.id as user_id, u.username 
    FROM role r
    JOIN user_role ur ON r.id = ur.RID
    JOIN user u ON u.id = ur.UID
</select>
<resultMap type="role" id="roleMap">
    <id property="id" column="id"/>
    <result property="role_name" column="role_name"/>
    <result property="role_desc" column="role_desc"/>
    <!-- 關(guān)聯(lián)用戶列表（多對(duì)多） -->
    <collection property="users" ofType="user">
        <id property="id" column="user_id"/> <!-- 別名避免與角色I(xiàn)D沖突 -->
        <result property="username" column="username"/>
    </collection>
</resultMap>

@Test
public void testFindAllRoles() {
    List<Role> roles = roleDao.findAll();
    for (Role role : roles) {
        System.out.println("角色：" + role.getRole_name());
        System.out.println("關(guān)聯(lián)用戶：" + role.getUsers());
    }
}

<!-- AccountMapper.xml -->
<resultMap type="Account" id="accountMap">
    <id column="id" property="id"/>
    <result column="uid" property="uid"/>
    <result column="money" property="money"/>
    <!-- 配置延遲加載：通過(guò)select屬性指定關(guān)聯(lián)查詢方法 -->
    <association property="user" javaType="User" 
                 select="com.qcbyjy.mapper.UserMapper.findById" 
                 column="uid">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
    </association>
</resultMap>

<!-- SqlMapConfig.xml -->
<settings>
    <!-- 開啟延遲加載功能 -->
    <setting name="lazyLoadingEnabled" value="true"/>
    <!-- 禁用積極加載（默認(rèn)false，按需加載） -->
    <setting name="aggressiveLazyLoading" value="false"/>
</settings>

@Test
    public void testFindAll1() throws IOException {
        // 先加載主配置文件，加載到輸入流中
        InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        // 創(chuàng)建SqlSessionFactory對(duì)象，創(chuàng)建SqlSession對(duì)象
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
        // 創(chuàng)建SqlSession對(duì)象
        SqlSession session = factory.openSession();
        // 獲取代理對(duì)象
        AccountMapper mapper = session.getMapper(AccountMapper.class);
        // 1. 查詢主對(duì)象（賬戶）
        List<Account> accounts = mapper.findAll();
        System.out.println("===== 主查詢已執(zhí)行 =====");
        // 2. 遍歷賬戶，但不訪問(wèn)關(guān)聯(lián)的用戶
        for (Account account : accounts) {
            System.out.println("賬戶ID：" + account.getId() + "，金額：" + account.getMoney());
        }
        System.out.println("===== 未訪問(wèn)關(guān)聯(lián)對(duì)象 =====");
        // 3. 首次訪問(wèn)關(guān)聯(lián)的用戶
        for (Account account : accounts) {
            System.out.println("===== 開始訪問(wèn)用戶 =====");
            System.out.println("用戶名：" + account.getUser().getUsername()); // 觸發(fā)懶加載
            System.out.println("===== 訪問(wèn)用戶結(jié)束 =====");
        }
        // 關(guān)閉資源
        session.close();
        inputStream.close();
    }

<!-- UserMapper.xml -->
<resultMap type="User" id="userMap">
    <id column="id" property="id"/>
    <result column="username" property="username"/>
    <!-- 配置延遲加載：集合屬性 -->
    <collection property="accounts" ofType="Account" 
                select="com.qcbyjy.mapper.AccountMapper.findByUid" 
                column="id">
        <id column="id" property="id"/>
        <result column="money" property="money"/>
    </collection>
</resultMap>

List<User> users = userMapper.findAll();
for (User user : users) {
    // 調(diào)用getAccounts()時(shí)觸發(fā)延遲查詢
    System.out.println(user.getAccounts()); 
}

    @Test
    public void testFindAllq() throws Exception {
        // 調(diào)用方法
        List<User> list = mapper.findAll();
        for (User user : list) {
            System.out.println(user.getUsername());
            System.out.println(user.getAccounts());
            System.out.println("==============");
        }
    }

@Test
public void testFirstLevelCache() {
    try (SqlSession session = sqlSessionFactory.openSession()) {
        UserMapper mapper = session.getMapper(UserMapper.class);
        // 第一次查詢：觸發(fā)SQL
        User user1 = mapper.findById(1);
        // 第二次查詢：命中緩存
        User user2 = mapper.findById(1);
        System.out.println(user1 == user2); // 輸出true（同一對(duì)象）
    }
}

// BaseExecutor.java
@Override
public <E> List<E> query(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler) throws SQLException {
    BoundSql boundSql = ms.getBoundSql(parameter);
    // 1. 創(chuàng)建緩存Key
    CacheKey key = createCacheKey(ms, parameter, rowBounds, boundSql);
    // 2. 查詢一級(jí)緩存
    return query(ms, parameter, rowBounds, resultHandler, key, boundSql);
}
@Override
public <E> List<E> query(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, CacheKey key, BoundSql boundSql) throws SQLException {
    // 從本地緩存中獲取
    List<E> list = localCache.getObject(key);
    if (list != null) {
        // 緩存命中
        handleLocallyCachedOutputParameters(ms, key, parameter, boundSql);
        return list;
    } else {
        // 緩存未命中，查詢數(shù)據(jù)庫(kù)
        return queryFromDatabase(ms, parameter, rowBounds, resultHandler, key, boundSql);
    }
}

try (SqlSession session = sqlSessionFactory.openSession()) {
    UserMapper mapper = session.getMapper(UserMapper.class);
    // 第一次查詢，從數(shù)據(jù)庫(kù)加載，存入一級(jí)緩存
    User user1 = mapper.getUserById(1);  
    // 第二次查詢，命中一級(jí)緩存，直接返回 user1 的引用
    User user2 = mapper.getUserById(1);  
    System.out.println(user1 == user2); // 輸出 true，是同一個(gè)對(duì)象實(shí)例
}

// 開啟二級(jí)緩存后，不同 SqlSession 測(cè)試
try (SqlSession session1 = sqlSessionFactory.openSession()) {
    UserMapper mapper1 = session1.getMapper(UserMapper.class);
    User user1 = mapper1.getUserById(1); 
    session1.commit(); // 提交后，數(shù)據(jù)可能同步到二級(jí)緩存（取決于配置）
}
try (SqlSession session2 = sqlSessionFactory.openSession()) {
    UserMapper mapper2 = session2.getMapper(UserMapper.class);
    User user2 = mapper2.getUserById(1); // 命中二級(jí)緩存，反序列化生成新對(duì)象
    System.out.println(user1 == user2); // 輸出 false，是不同對(duì)象實(shí)例
}