mybatis复杂关系应用实例

程序员文章站 2022-07-12 22:37:17

...

1.数据库表结构图

mybatis复杂关系应用实例

2.代码树状图
mybatis复杂关系应用实例

数据表：

user表
mybatis复杂关系应用实例
order表

item表

引入的jar包

mybatis-3.4.5.jar

mysql-connector-java-5.1.30.jar

mybatis-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>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <!--此处是数据库驱动，其他数据库不同于此-->
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <!--端口号为数据库端口号，“mybatis”是数据库名-->
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
                <!--数据库用户名密码-->
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <!--引入映射文件-->
    <mappers>
        <mapper resource="com/java/model/UserMapper.xml"/>
        <mapper resource="com/java/model/OrderMapper.xml"/>
    </mappers>
</configuration>

model包中的类均为javabean，提供构造器，get set方法和 toString方法

User类

package com.java.model;

import java.util.List;

public class User {
    private int id;
    private String username;
    private String password;
    private List<Orders> orders;

    public User() {
    }

    public User(int id, String username, String password, List<Orders> orders) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.orders = orders;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public List<Orders> getOrders() {
        return orders;
    }

    public void setOrders(List<Orders> orders) {
        this.orders = orders;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", orders=" + orders +
                '}';
    }
}

Orders类

package com.java.model;

import java.util.List;

public class Orders {
    private int id;
    private String orderNo;
    private User user;
    private List<Items> items;

    public Orders() {
    }

    public Orders(int id, String orderNo, User user, List<Items> items) {
        this.id = id;
        this.orderNo = orderNo;
        this.user = user;
        this.items = items;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getOrderNo() {
        return orderNo;
    }

    public void setOrderNo(String orderNo) {
        this.orderNo = orderNo;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    public List<Items> getItems() {
        return items;
    }

    public void setItems(List<Items> items) {
        this.items = items;
    }

    @Override
    public String toString() {
        return "Orders{" +
                "id=" + id +
                ", orderNo='" + orderNo + '\'' +
                ", user=" + user +
                ", items=" + items +
                '}';
    }
}

item类

package com.java.model;

public class Items {
    private int id;
    private String itemname;
    private int oid;

    public Items() {
    }

    public Items(int id, String itemname, int oid) {
        this.id = id;
        this.itemname = itemname;
        this.oid = oid;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getItemname() {
        return itemname;
    }

    public void setItemname(String itemname) {
        this.itemname = itemname;
    }

    public int getOid() {
        return oid;
    }

    public void setOid(int oid) {
        this.oid = oid;
    }

    @Override
    public String toString() {
        return "Items{" +
                "id=" + id +
                ", itemname='" + itemname + '\'' +
                ", oid=" + oid +
                '}';
    }
}

model包下的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="user">
  <!--插入操作-->
  <insert id="insert">
    insert into user(username,password) values(#{username},#{password})
  </insert>
  <!--更新操作-->
  <update id="update">
    update user set username = #{username},password = #{password} where id = #{id}
  </update>

  <!-- findById -->
  <!--parameterType为获取值id的类型，返回值涉及order表内容-->
  <select id="selectOne" parameterType="int" resultMap="rs_User">
    select
        u.id uid,
        u.username uusername,
        u.password upassword,
        o.id oid,
        o.orderno oorderno
    from user u left join orders o on u.id = o.uid
        where u.id = #{id}
  </select>

  <!-- 映射一对多关联关系 -->
  <resultMap type="com.java.model.User" id="rs_User">
    <id property="id" column="uid"/>
    <result property="username" column="uusername"/>
    <result property="password" column="upassword"/>
    <collection property="orders" ofType="com.java.model.Orders">
        <id property="id" column="oid"/>
        <result property="orderNo" column="oorderno"/>
    </collection>
  </resultMap>

  <select id="selectAll" resultMap="rs_User">
    select
        u.id uid,
        u.username uusername,
        u.password upassword,
        o.id oid,
        o.orderno oorderno
    from user u left join orders o on u.id = o.uid
  </select>

</mapper>

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="orders">
    <insert id="insert">
        INSERT INTO orders (orderno,uid) VALUES (#{orderNo},#{uid})
    </insert>
    <delete id="delete">
        DELETE FROM orders WHERE id = #{id}
    </delete>
    <update id="update" parameterType="int">
        UPDATE orders SET orderno = #{orderno},uid = #{uid} WHERE id = #{id}
    </update>
    <select id="selectOne" parameterType="int" resultMap="rs_Orders">
        select
        o.id oid,
        o.orderno oorderno,
        o.uid uid,
        u.username uusername,
        u.password upassword,
        i.id iid,
        i.itemname iitemname
    from orders o
        left join user u on o.uid = u.id
        left join items i on o.id = i.oid
    where o.id = #{id}
    </select>
    <resultMap id="rs_Orders" type="com.java.model.Orders">
        <id property="id" column="oid"/>
        <result property="orderNo" column="oorderno"/>
        <association property="user" javaType="com.java.model.User">
            <id property="id" column="uid"/>
            <result property="username" column="uusername"/>
            <result property="password" column="upassword"/>
        </association>
        <collection property="items" ofType="com.java.model.Items">
            <id property="id" column="iid"/>
            <result property="itemName" column="iitemname"/>
        </collection>
    </resultMap>
    <select id="selectAll" resultMap="rs_Orders">
        select
        o.id oid,
        o.orderno oorderno,
        o.uid uid,
        u.username uusername,
        u.password upassword,
        i.id iid,
        i.itemname iitemname
        from orders o
        left join user u on o.uid = u.id
        left join items i on o.id = i.oid
    </select>
</mapper>

test包下为测试类，测试增删查改功能是否有效
例如查询订单：

@Test
    public void selectOne() throws Exception {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();

        Orders order = session.selectOne("orders.selectOne", 1);
        for(Items item : order.getItems()){
            System.out.println(item);
        }
        System.out.println(order);
        session.commit();
        session.close();
    }

上一篇： Mybatis常用的注解开发CRUD&&复杂关系映射（一对一，一对多）&&mybatis 基于注解的二级缓存

下一篇： Arduino流水灯

mybatis复杂关系应用实例

实例讲解iOS应用开发中UIPickerView滚动选择栏的用法

ThinkPHP模板替换与系统常量及应用实例教程

java正则表达式应用的实例代码

kafka应用实例（kafka简单的入门案例）

Struts2中实现web应用的初始化实例详解

高效.NET脏字过滤算法与应用实例

Mybatis逆向生成使用扩展类的实例代码详解

MyBatis动态SQL标签用法实例详解

iOS应用开发中运用设计模式中的组合模式的实例解析

iOS应用运用设计模式中的Strategy策略模式的开发实例