#千锋逆战# Mybatis多表联合

Mybatis多表联合查找

  1. 加入依赖

    • 加入mybatis核心依赖,junit测试,mysql依赖

    • <dependencies>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
    </dependency>

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

  2. XML配置

    • resources目录下新建Mybatis.xml文件

    • 导入db.properties资源文件,如下

      driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3307/shop_mybatis
username=root
password=970809

    • 设置settings,加入自动日志

    • 设置typeAliases,设置别名包,此包下的引用可直接写类名,忽略大小写

    • 设置连接池属性POOLED

    • 在mappers标签下设置对应的映射文件路径

    • <?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"/>

    <!--添加日志功能，STDOUT_LOGGING不需要添加第三方jar包-->
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
    <typeAliases>
        <package name="pojo"/>
    </typeAliases>

    <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="com/mapper/OrderMapper.xml"/>
        <mapper resource="com/mapper/UserMapper.xml"/>
        <mapper resource="com/mapper/DetailMapper.xml"/>
        <mapper resource="com/mapper/ProductMapper.xml"/>
        <mapper resource="com/mapper/TypeMapper.xml"/>
    </mappers>
</configuration>

  3. OrderMapper.xml配置例举

    • namespace和id组合唯一,可自定义

    • 使用select标签进行查询操作,其他操作分别为update,insert,delete

    • 使用resultMap进行联合查询

    • resultMap标签下 如果对应pojo内的属性名(property)和数据库表中的列名相同,可省略不写result标签

    • 使用association标签进行对一的表结构联合查询,使用Collection标签进行对多的表结构联合查询

    • <?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="pojo.OrderMapper">
    <select id="getOrderByOid" resultMap="orderMap">
        select * from orders where oid = #{oid}
  </select>
    
    <resultMap id="orderMap" type="Order">
<!--        <id property="oid" column="oid"/>-->
<!--        <result column="price" property="price"/>-->
<!--        <result column="addr" property="addr"/>-->
<!--        <result column="payType" property="payType"/>-->

        <!--对一,select中填写对应的映射文件的namespace.id-->
        <association property="user" column="uid" select="pojo.UserMapper.getUserByUid"/>

       <!--对多-->
        <collection property="details" column="did" select="pojo.DetailMapper.getDetailByDid" ofType="pojo.Detail"/>
    </resultMap>
</mapper>

  4. 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">
<mapper namespace="pojo.UserMapper">
    <select id="getUserByUid" resultType="User">
    select * from users where uid = #{uid}
  </select>

</mapper>

  5. DetailMapper.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="pojo.DetailMapper">
    <select id="getDetailByDid" resultMap="detailMap">
        select * from details where did = #{did}
  </select>


    <resultMap id="detailMap" type="Detail">
<!--        <id property="did" column="did"/>-->
<!--        <result column="count" property="count"/>-->

        <association property="product" column="pid" select="pojo.ProductMapper.getProductByPid"/>
    </resultMap>

</mapper>

  6. ProductMapper.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="pojo.ProductMapper">
    <select id="getProductByPid" resultMap="productMap">
    select * from products where pid = #{pid}
  </select>

    <resultMap id="productMap" type="Product">

<!--        <id property="pid" column="pid"/>-->
<!--        <result property="name" column="name"/>-->
<!--        <result property="price" column="price"/>-->
<!--        <result property="img" column="img"/>-->

        <association property="type" column="tid" select="pojo.TypeMapper.getTypeByTid"/>
    </resultMap>

</mapper>

  7. TypeMapper.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="pojo.TypeMapper">
    <select id="getTypeByTid" resultType="Type">
    select * from types where tid = #{tid}
  </select>

</mapper>

  8. 测试类

    • 使用junit的@Before注解,setUp在所有方法运行前运行

    • 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.After;
import org.junit.Before;
import org.junit.Test;
import pojo.Order;

import java.io.IOException;

public class TestOrders {
    private SqlSessionFactory sf = null;
    private SqlSession session = null;

    @Before
    public void setUp() {
        try {
            sf = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("Mybatis.xml"));
            session = sf.openSession();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @After
    public void tearDown() {
        if (session != null) {
            session.close();
            session = null;
        }
    }

    @Test
    public void testGetOrderByOid() {
        Order order = session.selectOne("pojo.OrderMapper.getOrderByOid",1);
        System.out.println(order);
    }
}
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容

  • MyBatis一对多和多对一
    在学习MyBatis3的过程中，文档上面一直在强调一个id的东西！在做这个实验的时候，也因为没有理解清楚id含义而...
    杀小贼阅读 990评论 0赞 6
  • Maven管理的Spring + SpringMVC + Mybatis 项目搭建（二、Spr...
    一、配置maven的pom.xml加载jar包 为了后续开发的方便，将SSM框架所有需要的jar包一并加载进来 p...
    docki阅读 2,301评论 1赞 23
  • MarkDown
    1. 简介 1.1 什么是 MyBatis ？ MyBatis 是支持定制化 SQL、存储过程以及高级映射的优秀的...
    笨鸟慢飞阅读 5,694评论 0赞 4
  • 03——mybatis结果反射&使用
    一、结果映射是什么? ResultMap 1.使用场景一 当数据库的列名和实体类的属性名不一致时,无法直接通过re...
    huishao阅读 1,055评论 3赞 3
  • Mybatis
    1、Mybatis支持普通SQL查询、存储一级高级映射的优秀持久层框架 2、Mybatis可以使用简单的XML或注...
    JHMichael阅读 352评论 0赞 1