mybatis五表联合操作

最新推荐文章于 2023-09-08 00:06:32 发布

三亩地两头牛一壶老酒

最新推荐文章于 2023-09-08 00:06:32 发布

阅读量324

点赞数

文章标签：数据库 mybatis

本文链接：https://blog.csdn.net/weixin_45491031/article/details/104809271

版权

一，建立表结构
1，users 用户表
2，orders 订单表
3, details 订单详细信息表
4,products 产品表
5,types 产品类别表
注：五表的逻辑关系图
在这里插入图片描述
1，pom文件添加junit，mysql， mybatis（3.4.4）

2，设置mybatis的配置文件

<?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节点
        里面有配置信息 分别是环境和映射
        　其中环境里有datasource，里面有我们熟悉的连接数据库的四个字符串
-->
<configuration>

    <!--
        引入db的配置文件信息，后面用到的四个连接字符串就可以直接使用 ${}的方式来动态引入
    -->
    <properties resource="db.properties" />

    <!--
        给当前mybatis项目添加日志功能，该STDOUT_LOGGING值的好处是不用添加第三方jar包就可以有日志的输出
    -->
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

    <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="${user}"/>
                <property name="password" value="${pass}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/qfedu/mapper/OrderMapper.xml"/>
        <mapper resource="com/qfedu/mapper/UserMapper.xml"/>
        <mapper resource="com/qfedu/mapper/DetailMapper.xml"/>
        <mapper resource="com/qfedu/mapper/ProductMapper.xml"/>
        <mapper resource="com/qfedu/mapper/TypeMapper.xml"/>
    </mappers>
</configuration>

3，db.properties数据库的配置文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mall
user=root
pass=12345678

4，设计pojo类

5，设置映射文件mapper
1，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文件都将有一个自己的映射的namespace，
    每个方法对应自己的sql语句，每个sql语句对应有一个id
    整个项目中所有的namespace.id必须是唯一的
-->
<mapper namespace="com.qfstu.pojo.OrderMapper">

    <select id="getOrderByOid" resultMap="orderMap">
        select * from orders where oid = #{oid}
    </select>

    <resultMap id="orderMap" type="com.qfstu.pojo.Order">

        <!--
            id代表主键，分别设置列和属性的对应关系
        -->
        <id property="oid" column="oid" ></id>

        <!--
            result代表普通字段的映射，分别指定列与属性的对应

            如果字段名和属性名一致，可以省略

            属性名叫做payType，字段名pay_type;
            <result column="pay_type" property="payType" />
        -->
        <result column="price" property="price" />
        <result column="addr" property="addr" />
        <result column="payType" property="payType" />

        <!--
            association关联，只要是"对一"的关系都可以使用association，代表关联
            property代表Order类中的属性名u
            column代表Orders表中的uid字段
            select代表要使用该查询完成两表的联合查询得出user对象
        -->
        <association property="u" column="uid" select="com.qfstu.pojo.UserMapper.getUserByUid"></association>

        <collection property="details" column="oid" select="com.qfstu.pojo.DetailMapper.getDetailsByOid" ofType="com.qfstu.pojo.Detail"/>

    </resultMap>
</mapper>

2，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，
    每个方法对应自己的sql语句，每个sql语句对应有一个id
    整个项目中所有的namespace.id必须是唯一的
-->
<mapper namespace="com.qfstu.pojo.UserMapper">

    <select id="getUserByUid" resultType="com.qfstu.pojo.User">
        select * from users where uid = #{uid}
    </select>
</mapper>

3，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，
    每个方法对应自己的sql语句，每个sql语句对应有一个id
    整个项目中所有的namespace.id必须是唯一的
-->
<mapper namespace="com.qfstu.pojo.DetailMapper">

    <select id="getDetailsByOid" resultMap="detailMap">
        select * from details where oid = #{oid}
    </select>

    <resultMap id="detailMap" type="com.qfstu.pojo.Detail">
        <id column="did" property="did"></id>

        <result property="count" column="count" />

        <association property="pro" column="pid" select="com.qfstu.pojo.ProductMapper.getProductByPid" />
    </resultMap>

</mapper>

4，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，
    每个方法对应自己的sql语句，每个sql语句对应有一个id
    整个项目中所有的namespace.id必须是唯一的
-->
<mapper namespace="com.qfstu.pojo.ProductMapper">

    <select id="getProductByPid" resultMap="productMap">
        select * from products where pid = #{uid}
    </select>

    <resultMap id="productMap" type="com.qfstu.pojo.Product">
        <id column="pid" property="pid"></id>

        <association property="t" column="tid" select="com.qfstu.pojo.TypeMapper.getTypesByTid" />
    </resultMap>

</mapper>

5，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，
    每个方法对应自己的sql语句，每个sql语句对应有一个id
    整个项目中所有的namespace.id必须是唯一的
-->
<mapper namespace="com.qfstu.pojo.TypeMapper">

    <select id="getTypesByTid" resultType="com.qfstu.pojo.Types">
        select * from types where tid = #{tid}
    </select>
</mapper>

6，TestOrder.java

package com.qfstu.test;

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

import java.io.IOException;


/**
 * Created by 86150 on 2020/3/10.
 */
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(true);
        }catch (IOException e){
            e.printStackTrace();
        }
    }

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


    @Test
    public void testGetOrderByOid(){
        Order order = session.selectOne("com.qfstu.pojo.OrderMapper.getOrderByOid", "f8fb1b8a62db11eaa2c13d7cb7217e15");
        System.out.println(order);
    }
    
}

运行结果

Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1791868405.
==>  Preparing: select * from orders where oid = ? 
==> Parameters: f8fb1b8a62db11eaa2c13d7cb7217e15(String)
<==    Columns: oid, price, addr, payType, uid
<==        Row: f8fb1b8a62db11eaa2c13d7cb7217e15, 30998, beijingxisanqi, zhibubao, 1
====>  Preparing: select * from users where uid = ? 
====> Parameters: 1(Integer)
<====    Columns: uid, name, pass, phone
<====        Row: 1, wukong, 888888, 13488071376
<====      Total: 1
====>  Preparing: select * from details where oid = ? 
====> Parameters: f8fb1b8a62db11eaa2c13d7cb7217e15(String)
<====    Columns: did, count, pid, oid
<====        Row: 63ba6caa62dc11eaa2c13d7cb7217e15, 2, 06d7903f62da11eaa2c13d7cb7217e15, f8fb1b8a62db11eaa2c13d7cb7217e15
======>  Preparing: select * from products where pid = ? 
======> Parameters: 06d7903f62da11eaa2c13d7cb7217e15(String)
<======    Columns: pid, name, img, price, tid
<======        Row: 06d7903f62da11eaa2c13d7cb7217e15, iphone x, iphone.jpg, 9999, d96cac00628111eaa2c13d7cb7217e15
========>  Preparing: select * from types where tid = ? 
========> Parameters: d96cac00628111eaa2c13d7cb7217e15(String)
<========    Columns: tid, name
<========        Row: d96cac00628111eaa2c13d7cb7217e15, digit
<========      Total: 1
<======      Total: 1
<====        Row: ade6d49962dc11eaa2c13d7cb7217e15, 1, 778619ce62da11eaa2c13d7cb7217e15, f8fb1b8a62db11eaa2c13d7cb7217e15
======>  Preparing: select * from products where pid = ? 
======> Parameters: 778619ce62da11eaa2c13d7cb7217e15(String)
<======    Columns: pid, name, img, price, tid
<======        Row: 778619ce62da11eaa2c13d7cb7217e15, yagao, heiren.jpg, 99, d090dec4628111eaa2c13d7cb7217e15
========>  Preparing: select * from types where tid = ? 
========> Parameters: d090dec4628111eaa2c13d7cb7217e15(String)
<========    Columns: tid, name
<========        Row: d090dec4628111eaa2c13d7cb7217e15, house
<========      Total: 1
<======      Total: 1
<====        Row: b7fa3ada62dc11eaa2c13d7cb7217e15, 1, aa0f109e62d911eaa2c13d7cb7217e15, f8fb1b8a62db11eaa2c13d7cb7217e15
======>  Preparing: select * from products where pid = ? 
======> Parameters: aa0f109e62d911eaa2c13d7cb7217e15(String)
<======    Columns: pid, name, img, price, tid
<======        Row: aa0f109e62d911eaa2c13d7cb7217e15, mac pro, mac.jpg, 21999, d96cac00628111eaa2c13d7cb7217e15
<======      Total: 1
<====      Total: 3
<==      Total: 1
Order{oid='f8fb1b8a62db11eaa2c13d7cb7217e15', price=30998.0, addr='beijingxisanqi', payType='zhibubao', u=User{uid=1, name='wukong', pass='888888', phone='13488071376'}, details=[Detail{did='63ba6caa62dc11eaa2c13d7cb7217e15', count=2, pro=Product{pid='06d7903f62da11eaa2c13d7cb7217e15', name='iphone x', img='iphone.jpg', price=9999.0, t=Types{tid='d96cac00628111eaa2c13d7cb7217e15', name='digit'}}}, Detail{did='ade6d49962dc11eaa2c13d7cb7217e15', count=1, pro=Product{pid='778619ce62da11eaa2c13d7cb7217e15', name='yagao', img='heiren.jpg', price=99.0, t=Types{tid='d090dec4628111eaa2c13d7cb7217e15', name='house'}}}, Detail{did='b7fa3ada62dc11eaa2c13d7cb7217e15', count=1, pro=Product{pid='aa0f109e62d911eaa2c13d7cb7217e15', name='mac pro', img='mac.jpg', price=21999.0, t=Types{tid='d96cac00628111eaa2c13d7cb7217e15', name='digit'}}}]}
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@6acdbdf5]
Returned connection 1791868405 to pool.

Process finished with exit code 0