Mybatis基础 -- 复杂映射

一、一对一查询

  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：运行环境 -->
    <environments default="dev">
        <!-- 一个environment代表一个环境，可以配置多个环境，例如[dev][test][prod]-->
        <environment id="dev">
            <!--当前事务交由JDBC进行管理-->
            <transactionManager type="JDBC"></transactionManager>
            <!-- POOLED表示当前使用mybatis提供的数据库连接池
                 UNPOOLED表示不使用连接池，每次请求都会新建一个数据库连接 -->
            <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的唯一标识-->
<mapper namespace="com.wujun.mapper.OrderMapper">

    <!--手动配置实体属性与表字段的映射关系-->
    <resultMap id="orderMap" type="com.wujun.pojo.Order">
        <result property="id" column="id"></result>
        <result property="orderName" column="order_name"></result>
        <!--配置order实体中的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. 创建两个实体类
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. 创建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. 测试结果
Order(id=1, orderName=订单1, user=User(id=1, username=吴俊1))
Order(id=2, orderName=订单2, user=User(id=2, username=吴俊2))

二、一对多查询

场景：以订单为维度，那么订单和用户的关系是一对一的，但是以用户为维度，那么用户和订单的关系就是一对多。

  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的唯一标识-->
<mapper namespace="com.wujun.mapper.UserMapper">

    <!--手动配置实体属性与表字段的映射关系-->
    <resultMap id="userMap" type="com.wujun.pojo.User">
        <result property="id" column="userId"></result>
        <result property="username" column="username"></result>
        <!--配置user实体中的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目录下新建一个com.wujun.mapper的文件夹，将UserMapper.xml和OrderMapper.xml都放入这个文件夹，这样就可以使用package标签进行映射了
<?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：运行环境 -->
    <environments default="dev">
        <!-- 一个environment代表一个环境，可以配置多个环境，例如[dev][test][prod]-->
        <environment id="dev">
            <!--当前事务交由JDBC进行管理-->
            <transactionManager type="JDBC"></transactionManager>
            <!-- POOLED表示当前使用mybatis提供的数据库连接池
                 UNPOOLED表示不使用连接池，每次请求都会新建一个数据库连接 -->
            <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. 实体类编写
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. 测试结果
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=[])

三、多对多查询

场景：用户 -> 角色
一个用户可以拥有多个角色，一个角色也可以赋予多个用户

  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的唯一标识-->
<mapper namespace="com.wujun.mapper.UserMapper">

    <!--手动配置实体属性与表字段的映射关系-->
    <resultMap id="userMap" type="com.wujun.pojo.User">
        <result property="id" column="userId"></result>
        <result property="username" column="username"></result>
        <!--配置user实体中的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. 测试结果
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=[])
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成，浏览时请结合常识与多方信息审慎甄别。
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

相关阅读更多精彩内容

友情链接更多精彩内容