Mybatis基礎(chǔ) -- 復(fù)雜映射

一、一對一查詢

  1. 配置文件
// jdbc.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql:///wujun_test
jdbc.username=wujun
jdbc.password=wujun@2020

<!--SqlMapConfig.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="jdbc.properties"></properties>

    <!-- environments：運(yùn)行環(huán)境 -->
    <environments default="dev">
        <!-- 一個(gè)environment代表一個(gè)環(huán)境，可以配置多個(gè)環(huán)境，例如[dev][test][prod]-->
        <environment id="dev">
            <!--當(dāng)前事務(wù)交由JDBC進(jìn)行管理-->
            <transactionManager type="JDBC"></transactionManager>
            <!-- POOLED表示當(dāng)前使用mybatis提供的數(shù)據(jù)庫連接池
                 UNPOOLED表示不使用連接池，每次請求都會新建一個(gè)數(shù)據(jù)庫連接 -->
            <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>
        </environment>
    </environments>

    <!-- 引入映射配置文件 -->
    <mappers>
        <mapper resource="UserMapper.xml"></mapper>
    </mappers>
</configuration>

<!--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">

<!--namespace：名稱空間，與id組成sql的唯一標(biāo)識-->
<mapper namespace="com.wujun.mapper.OrderMapper">

    <!--手動配置實(shí)體屬性與表字段的映射關(guān)系-->
    <resultMap id="orderMap" type="com.wujun.pojo.Order">
        <result property="id" column="id"></result>
        <result property="orderName" column="order_name"></result>
        <!--配置order實(shí)體中的user對象-->
        <association property="user" javaType="com.wujun.pojo.User">
            <result property="id" column="user_id"></result>
            <result property="username" column="username"></result>
        </association>
    </resultMap>

    <select id="findAll" resultMap="orderMap">
        select * from `order` o, `user` u where o.user_id = u.id
    </select>
</mapper>
  1. 創(chuàng)建兩個(gè)實(shí)體類
package com.wujun.pojo;

import lombok.Data;

@Data
public class Order {
    private Integer id;
    private String orderName;
    private User user;
}

package com.wujun.pojo;

import lombok.Data;

@Data
public class User {
    private Integer id;
    private String username;
}
  1. 創(chuàng)建Mapper接口
package com.wujun.mapper;

import com.wujun.pojo.Order;

import java.util.List;

public interface OrderMapper {
    List<Order> findAll();
}
  1. 編寫測試類
package com.wujun.test;

import com.wujun.mapper.OrderMapper;
import com.wujun.pojo.Order;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class MybatisTest {

    @Test
    public void test1() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
        List<Order> all = mapper.findAll();
        for (Order order : all) {
            System.out.println(order);
        }

    }
}
  1. 測試結(jié)果
Order(id=1, orderName=訂單1, user=User(id=1, username=吳俊1))
Order(id=2, orderName=訂單2, user=User(id=2, username=吳俊2))

二、一對多查詢

場景：以訂單為維度，那么訂單和用戶的關(guān)系是一對一的，但是以用戶為維度，那么用戶和訂單的關(guān)系就是一對多。

  1. 用戶配置文件
<?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：名稱空間，與id組成sql的唯一標(biāo)識-->
<mapper namespace="com.wujun.mapper.UserMapper">

    <!--手動配置實(shí)體屬性與表字段的映射關(guān)系-->
    <resultMap id="userMap" type="com.wujun.pojo.User">
        <result property="id" column="userId"></result>
        <result property="username" column="username"></result>
        <!--配置user實(shí)體中的order集合-->
        <collection property="orders" ofType="com.wujun.pojo.Order">
            <result property="id" column="orderId"></result>
            <result property="orderName" column="orderName"></result>
        </collection>
    </resultMap>

    <select id="findAll" resultMap="userMap">
        select u.id as userId, u.username as username, o.id as orderId, o.order_name as orderName
        from `user` u left join `order` o on u.id = o.user_id
    </select>
</mapper>
  1. 貪方便，所以在resources目錄下新建一個(gè)com.wujun.mapper的文件夾，將UserMapper.xml和OrderMapper.xml都放入這個(gè)文件夾，這樣就可以使用package標(biāo)簽進(jìn)行映射了
<?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="jdbc.properties"></properties>

    <!-- environments：運(yùn)行環(huán)境 -->
    <environments default="dev">
        <!-- 一個(gè)environment代表一個(gè)環(huán)境，可以配置多個(gè)環(huán)境，例如[dev][test][prod]-->
        <environment id="dev">
            <!--當(dāng)前事務(wù)交由JDBC進(jìn)行管理-->
            <transactionManager type="JDBC"></transactionManager>
            <!-- POOLED表示當(dāng)前使用mybatis提供的數(shù)據(jù)庫連接池
                 UNPOOLED表示不使用連接池，每次請求都會新建一個(gè)數(shù)據(jù)庫連接 -->
            <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>
        </environment>
    </environments>

    <!-- 引入映射配置文件 -->
    <mappers>
        <package name="com.wujun.mapper"/>
    </mappers>
</configuration>
  1. 實(shí)體類編寫
package com.wujun.pojo;

import lombok.Data;

import java.util.List;

@Data
public class User {
    private Integer id;
    private String username;
    private List<Order> orders;
}
  1. mapper接口編寫
package com.wujun.mapper;

import com.wujun.pojo.User;

import java.util.List;

public interface UserMapper {
    List<User> findAll();
}
  1. 測試類編寫
package com.wujun.test;

import com.wujun.mapper.UserMapper;
import com.wujun.pojo.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class MybatisTest {

    @Test
    public void test1() 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> all = mapper.findAll();
        for (User user : all) {
            System.out.println(user);
        }
    }
}
  1. 測試結(jié)果
User(id=1, username=吳俊1, orders=[Order(id=1, orderName=訂單1), Order(id=3, orderName=訂單3)])
User(id=2, username=吳俊2, orders=[Order(id=2, orderName=訂單2)])
User(id=3, username=吳俊3, orders=[])

三、多對多查詢

場景：用戶 -> 角色
一個(gè)用戶可以擁有多個(gè)角色，一個(gè)角色也可以賦予多個(gè)用戶

  1. 配置文件
<?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：名稱空間，與id組成sql的唯一標(biāo)識-->
<mapper namespace="com.wujun.mapper.UserMapper">

    <!--手動配置實(shí)體屬性與表字段的映射關(guān)系-->
    <resultMap id="userMap" type="com.wujun.pojo.User">
        <result property="id" column="userId"></result>
        <result property="username" column="username"></result>
        <!--配置user實(shí)體中的role集合-->
        <collection property="roles" ofType="com.wujun.pojo.Role">
            <result property="id" column="roleId"></result>
            <result property="roleName" column="roleName"></result>
        </collection>
    </resultMap>

    <select id="findAll" resultMap="userMap">
        select u.id as userId, u.username as username, r.id as roleId, r.role_name as roleName
        from `user` u
        left join `relation_user_role` rur on u.id = rur.user_id
        left join role r on r.id = rur.role_id
    </select>
</mapper>
  1. 測試結(jié)果
User(id=1, username=吳俊1, roles=[Role(id=1, roleName=角色1), Role(id=2, roleName=角色2)])
User(id=2, username=吳俊2, roles=[Role(id=1, roleName=角色1)])
User(id=3, username=吳俊3, roles=[])
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容