mybatis框架 之 多表查询

mybatis框架

前言

mybatis框架是一个持久层（数据访问层）的轻量级框架，底层原理是对jdbc的封装。前身是ibatis框架。在与Hibernate框架的选择上，如果是对业务逻辑要求不太复杂的，例如电商类项目，可以选择mybatis框架。如果业务逻辑复杂，对数据库的执行性能要求不高的传统行业项目，可以选择Hibernate框架。

下面贴上使用 mybatis完成的 多表查询。

1.全局配置文件 order-config.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>

    <settings>
        <!--配置驼峰规则-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!--配置结果集的自动映射-->
        <setting name="autoMappingBehavior" value="FULL"/>
    </settings>

    <typeAliases>
        <!--配置实体类的别名，使用时默认是类名，不区分大小写-->
        <package name="com.hehe.pojo"/>
    </typeAliases>

    <!--配置环境-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <!--配置数据源-->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driverClass}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    
    <mappers>
        <!--配置包扫描的方式，mapper映射文件-->
        <package name="com.hehe.mapper"/>
    </mappers>

</configuration>

2.映射文件 OrderMapper.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文件夹中的接口-->
<mapper namespace="com.hehe.mapper.OrderMapper">
    
    <!--selcet标签：查询标签-->
    <select id="findUserAndOrderAndOrderdetailAndItemByOrdernumber" resultMap="manyToMany">
        select * , d.id detail_id
            from tb_item i,tb_order o, tb_orderdetail d,tb_user u
            where o.order_number = #{order_name} and o.user_id = u.id AND
			o.id = d.order_id and d.item_id = i.id;
    </select>

    <!--结果集映射-->
    <resultMap id="manyToMany" type="order">
        <!--主键映射：类—>表中列 -->
        <id column="order_id" property="id"></id>
        
        <!--Order类的 User属性对应一条记录的映射-->
        <association property="user" javaType="user">
            <id column="user_id" property="id"></id>
        </association>
        
        <!--Order类的 List<Orderdetail>集合属性 对应多条记录的映射-->
        <collection property="orderdetails" javaType="list" ofType="orderdetail">
            <id column="detail_id" property="id"></id>
            
            <!--Orderdetail对应一条记录 的映射-->
            <association property="item" javaType="item">
                <id column="item_id" property="id"></id>
            </association>
            
        </collection>
    </resultMap>
    
</mapper>

3.jdbc配置文件 jdbc.properties

jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis
jdbc.username=root
jdbc.password=root

4.mapper接口 OrderMapper.java

package com.hehe.mapper;

import com.hehe.pojo.Order;
import org.apache.ibatis.annotations.Param;

public interface OrderMapper {
    Order findUserAndOrderAndOrderdetailAndItemByOrdernumber(@Param("order_name")String order_name);
}

5.测试类 OrderServiceImpl.java

public class OrderServiceImpl {
    private OrderMapper mapper ;

    @Before
    public void setMapper() throws IOException {
        //1.加载全局配置文件
        InputStream ra = Resources.getResourceAsStream("order-config.xml");

        //2.解析配置文件
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(ra);

        //3.创建sqlSession
        SqlSession sqlSession = factory.openSession(true);

        //4.获得 mapper 对象
        mapper = sqlSession.getMapper(OrderMapper.class);
    }   

    @Test
    public void findUserAndOrderAndOrderdetailAndItemByOrdernumber(){

        Order order = mapper.findUserAndOrderAndOrderdetailAndItemByOrdernumber("20140921001");
        System.out.println("order = " + order);
    }
}

6.pop.xml 依赖

<dependencies>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.1</version>
    </dependency>

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

    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>5.1.7.RELEASE</version>
    </dependency>

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

    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-log4j12</artifactId>
        <version>1.6.3</version>
    </dependency>
</dependencies>

源码已经上传资源