Mybatis的高级查询

最新推荐文章于 2024-06-02 12:37:41 发布

quanweiya

最新推荐文章于 2024-06-02 12:37:41 发布

阅读量436

点赞数

分类专栏： Mybatis MySQL数据库文章标签： mybatis mysql java 数据库

本文链接：https://blog.csdn.net/qq_41125149/article/details/106910058

版权

MySQL数据库同时被 2 个专栏收录

13 篇文章 0 订阅

订阅专栏

Mybatis

2 篇文章 0 订阅

订阅专栏

文章目录

有问题加微信：quanweiSpring
1.搭建环境
2.数据库之间的表关系
- 2.1 建表语句
- 2.2数据库表之间的关联
3.高级查询

有问题加微信：quanweiSpring

1.搭建环境

1.1创建项目

创建对应的Java文件，项目名字随便起
在这里插入图片描述

1.2导入jar包

创建对应的lib文件夹存取对应的jar包
在这里插入图片描述

导入对应的jar包
在这里插入图片描述

1.3创建核心配置文件

在src下创建对应的Mybatis-config.xml文件
在这里插入图片描述

创建对应的jdbc.properties主要作用是用来解耦合
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost/day06_db
username=root
password=root
参数看不懂的看MySQL对应的四大配置
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>
    <!--加载外部的配置文件-->
    <properties resource="jdbc.properties"></properties>
    <!--settings-->
    <settings>
        <!--开启驼峰自动映射-->
       <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <!--别名-->
    <typeAliases>
        <package name="cn.itcast.domain"></package>
    </typeAliases>
    <!--mybatis环境的配置
        一个核心配置文件，可以配置多个运行环境，default默认使用哪个运行环境
    -->
    <environments default="development">
        <!--通常我们只需要配置一个就可以了， id是环境的名字 -->
        <environment id="development">
            <!--事务管理器：由JDBC来管理-->
            <!--
                事务管理器type的取值：
                1. JDBC：由JDBC进行事务的管理
                2. MANAGED：事务由容器来管理，后期学习Spring框架的时候，所有的事务由容器管理
            -->
            <transactionManager type="JDBC"/>
            <!--数据源的配置：mybatis自带的连接池-->
            <!--
                数据源：
                1. POOLED：使用mybatis创建的连接池
                2. UNPOOLED：不使用连接池，每次自己创建连接
                3. JNDI：由服务器提供连接池的资源，我们通过JNDI指定的名字去访问服务器中资源。
            -->
            <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>
        <!--加载其它的映射文件 注：注解开发是点号-->
        <!-- <package name="com.itheima.sh.dao"></package>-->
        <!--加载其它的映射文件 注：不是点号-->
        <!--<mapper resource="org/mybatis/example/BlogMapper.xml"/>-->
        <!--
            加载其它的映射文件 xml形式
                包扫描方式加载mapper映射文件,说明：
                1. 要求mapper映射文件，与mapper接口要放在同一个目录
                2. 要求mapper映射文件的名称，与mapper接口的名称要一致
            -->
        <mapper resource="UserMapper.xml"></mapper>

    </mappers>
</configuration>

内容看不懂的看对应的Mybatis的核心配置

1.4创建实体类

整体的包结构：
在这里插入图片描述
查询用过户创建User.java文件

package cn.itcast.domain;

import java.math.BigInteger;

public class User {
    private BigInteger id;
    private String userName;
    private String password;
    private String name;
    private Integer age;
    private Integer sex;
    private Order order;


    public User() {
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", userName='" + userName + '\'' +
                ", password='" + password + '\'' +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", sex=" + sex +
                ", order=" + order +
                '}';
    }

    public BigInteger getId() {
        return id;
    }

    public void setId(BigInteger 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 String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Integer getSex() {
        return sex;
    }

    public void setSex(Integer sex) {
        this.sex = sex;
    }

    public Order getOrder() {
        return order;
    }

    public void setOrder(Order order) {
        this.order = order;
    }
}

1.5创建接口

接口中一般为对数据库查询的业务方法，这里不牵涉复杂的业务

package cn.itcast.dao;

import cn.itcast.domain.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface UserMapper {
        List<User> findAllUsers();

        Map<String, User> dindUserByIdOnMap(@Param("id")String id);
        @MapKey("id")
        Map<String, User> findUsersByMap();


        User findUserByIdMap(@Param("id")Integer id);

       /* 【需求】：查询男性用户，如果输入了用户名，按用户名模糊查询,如果没有输入用户名，就查询所有男性用户**。*/

        List<User> findUserBySexOrName(@Param("sex") Integer sex, @Param("name") String name);

        User findUserAndOrder(@Param("orderNumber") String orderNumber);


        List<User> findUserNameOrSex(@Param("sex") Integer sex,@Param("name") String name);

        /*根据用户名或者住址查询所有男性用户:
        如果输入了用户名则按照用户名模糊查找，
        否则就按照住址查找，两个条件只能成立一个，
        如果都不输入就查找用户名为“孙悟空”的用户。*/
        List<User> findAllUserBysexOrName(@Param("sex") Integer sex,@Param("name") String name);



}

1.6创建Mapper映射文件

<?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 指定接口的类全名
-->
<mapper namespace="cn.itcast.dao.UserMapper">
    <!--
    查询语句
    id: 接口中方法的名字
    resultType：返回的实体类的类型，类全名
    -->
    <select id="findAllUsers" resultType="cn.itcast.domain.User">
        select * from tb_user
    </select>

    <select id="dindUserByIdOnMap" resultType="map">
        select * from tb_user where id=#{id}
    </select>
    <select id="findUsersByMap" resultType="map">
        select * from tb_user
    </select>


    <resultMap id="findUserByIdMap1" type="User" autoMapping="true">
        <id property="id" column="id"></id>

    </resultMap>
    <select id="findUserByIdMap" resultMap="findUserByIdMap1">
        select * from tb_user where id=#{id}
    </select>

    <resultMap id="findUserBySexOrName" type="user" autoMapping="true">
        <id column="id" property="id"></id>
        <result property="userName" column="user_name"></result>
    </resultMap>

    <select id="findUserBySexOrName" resultMap="findUserBySexOrName">
        select * from tb_user where sex=#{sex}
            <if test="name !=null">
                  and name like '%${name}%'
            </if>
    </select>


    <resultMap id="findUserAndOrder" type="User" autoMapping="true">
        <id column="id" property="id" ></id>
        <association property="order"  javaType="order" autoMapping="true">
            <id property="id" column="id"></id>
        </association>
    </resultMap>
    <select id="findUserAndOrder" resultMap="findUserAndOrder">
       SELECT
	o.user_id,
	o.order_number,
	o.id,
	u.id,
	u.user_name,
	u.`password`,
	u.`name`,
	u.age,
	u.sex
FROM
	tb_order AS o
	LEFT JOIN tb_user AS u ON o.user_id = u.id
WHERE
	o.order_number =${orderNumber};
    </select>

    <resultMap id="findUserNameOrSex" type="user">
        <id column="id" property="id"></id>
        <result property="userName" column="user_name"></result>
    </resultMap>

    <select id="findUserNameOrSex" resultMap="findUserNameOrSex">
        select * from tb_user
        where sex=#{sex}
        <if test="name !=null ">
            and name like "%${name}%"
        </if>
    </select>


    <resultMap id="findAllUserBysexOrName" type="user">
        <id column="id" property="id"></id>
        <result property="userName" column="user_name"></result>
    </resultMap>
    <select id="findAllUserBysexOrName" resultMap="findAllUserBysexOrName">
        select * from tb_user
        where
        <choose>
            <when test="sex!=null">
                sex=#{sex}
            </when>
            <when test="name!=null">
                name like "%${name}%"
            </when>
            <otherwise>
                1=1
            </otherwise>
        </choose>
    </select>
</mapper>

1.7创建对应的工具包

提取Mybatis工具包的作用是为了简化开发，减少代码量

package cn.itcast.util;

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 java.io.IOException;
import java.io.InputStream;

public class MybatisUtils {

    private static SqlSessionFactory sqlSessionFactory = null;
    static{

        try {
            SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
            InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
            sqlSessionFactory = sqlSessionFactoryBuilder.build(resourceAsStream);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    public static SqlSession getSqlSession() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        return sqlSession;
    }
    public static void release(SqlSession sqlSession, InputStream inputStream ) {
        if (sqlSession != null) {
            sqlSession.close();
        }

        try {
            inputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }



}

1.8创建测试类（确保项目跑通）

测是查询表中的所有数据


package cn.itcast.test;

import cn.itcast.dao.UserMapper;
import cn.itcast.domain.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 java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class UserFindAllDemo {
    public static void main(String[] args) throws Exception {
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory build = sqlSessionFactoryBuilder.build(resourceAsStream);
        SqlSession sqlSession = build.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> allUsers = mapper.findAllUsers();
        for (User allUser : allUsers) {

            System.out.println("allUser = " + allUser);
        }


    }
}

2.数据库之间的表关系

2.1 建表语句

/*
 Navicat Premium Data Transfer

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 50727
 Source Host           : localhost:3306
 Source Schema         : day06_db

 Target Server Type    : MySQL
 Target Server Version : 50727
 File Encoding         : 65001

 Date: 22/06/2020 21:31:38
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for tb_item
-- ----------------------------
DROP TABLE IF EXISTS `tb_item`;
CREATE TABLE `tb_item`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商品名称',
  `item_price` float(6, 1) NOT NULL COMMENT '商品价格',
  `item_detail` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '商品描述',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_item
-- ----------------------------
INSERT INTO `tb_item` VALUES (1, 'iPhone 6', 5288.0, '苹果公司新发布的手机产品。');
INSERT INTO `tb_item` VALUES (2, 'iPhone 6 plus', 6288.0, '苹果公司发布的新大屏手机。');

-- ----------------------------
-- Table structure for tb_order
-- ----------------------------
DROP TABLE IF EXISTS `tb_order`;
CREATE TABLE `tb_order`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `order_number` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单号',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `FK_orders_1`(`user_id`) USING BTREE,
  CONSTRAINT `FK_orders_1` FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_order
-- ----------------------------
INSERT INTO `tb_order` VALUES (1, 1, '20140921001');
INSERT INTO `tb_order` VALUES (2, 2, '20140921002');
INSERT INTO `tb_order` VALUES (3, 1, '20140921003');

-- ----------------------------
-- Table structure for tb_orderdetail
-- ----------------------------
DROP TABLE IF EXISTS `tb_orderdetail`;
CREATE TABLE `tb_orderdetail`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(32) NULL DEFAULT NULL COMMENT '订单号',
  `item_id` int(32) NULL DEFAULT NULL COMMENT '商品id',
  `total_price` double(20, 0) NULL DEFAULT NULL COMMENT '商品总价',
  `status` int(11) NULL DEFAULT NULL COMMENT '状态',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `FK_orderdetail_1`(`order_id`) USING BTREE,
  INDEX `FK_orderdetail_2`(`item_id`) USING BTREE,
  CONSTRAINT `FK_orderdetail_1` FOREIGN KEY (`order_id`) REFERENCES `tb_order` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_orderdetail_2` FOREIGN KEY (`item_id`) REFERENCES `tb_item` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_orderdetail
-- ----------------------------
INSERT INTO `tb_orderdetail` VALUES (1, 1, 1, 5288, 1);
INSERT INTO `tb_orderdetail` VALUES (2, 1, 2, 6288, 1);
INSERT INTO `tb_orderdetail` VALUES (3, 2, 2, 6288, 1);
INSERT INTO `tb_orderdetail` VALUES (4, 3, 1, 5288, 1);

-- ----------------------------
-- Table structure for tb_user
-- ----------------------------
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名',
  `password` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '密码',
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `age` int(10) NULL DEFAULT NULL COMMENT '年龄',
  `sex` int(11) NULL DEFAULT NULL COMMENT '0-女 1-男',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `username`(`user_name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_user
-- ----------------------------
INSERT INTO `tb_user` VALUES (1, 'zhangsan', '123456', '张三', 30, 1);
INSERT INTO `tb_user` VALUES (2, 'lisi', '123456', '李四', 21, 0);
INSERT INTO `tb_user` VALUES (3, 'wangwu', '123456', '王五', 22, 1);
INSERT INTO `tb_user` VALUES (4, 'zhangwei', '123456', '张伟', 20, 1);
INSERT INTO `tb_user` VALUES (5, 'lina', '123456', '李娜', 28, 0);
INSERT INTO `tb_user` VALUES (6, '蔡徐坤', '123', '小菜', 18, 1);

SET FOREIGN_KEY_CHECKS = 1;

2.2数据库表之间的关联

在这里插入图片描述

3.高级查询

3.1一对一查询

需求：通过订单编号20140921003查询出订单信息，并查询出下单人信息

一对一的思想：
一个订单只能属于一个人

SQL语句分析：
SELECT
o.user_id,
o.order_number,
o.id,
u.id,
u.user_name,
u.password,
u.name,
u.age,
u.sex
FROM
tb_order AS o
LEFT JOIN tb_user AS u ON o.user_id = u.id
WHERE
o.order_number IN ( 20140921003 );

在domain实体类中创建对应的Order订单的实体类

package cn.itcast.domain;

import java.math.BigInteger;

public class Order {
    private  Integer id;
    private BigInteger userId;
    private String orderNumber;

    public Order() {
    }

    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", userId=" + userId +
                ", orderNumber='" + orderNumber + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

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

    public BigInteger getUserId() {
        return userId;
    }

    public void setUserId(BigInteger userId) {
        this.userId = userId;
    }

    public String getOrderNumber() {
        return orderNumber;
    }

    public void setOrderNumber(String orderNumber) {
        this.orderNumber = orderNumber;
    }
}

在User实体类中添加关联：
private Order order;
重新写对应的getSet方法，toString方法
同样在order表中，添加User的关联

创建OrderMapper接口
package cn.itcast.dao;

import cn.itcast.domain.Order;
import org.apache.ibatis.annotations.Param;

public interface OrderMapper {
Order queryOrderAndUser(@Param(“OrderNumber”) String OrderNumber);
}

Order 的实体类，保存商品对应的信息

package cn.itcast.domain;

import java.math.BigInteger;

public class Order {
    private  Integer id;
    private BigInteger userId;
    private String orderNumber;
    private User user;

    public Order() {
    }

    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", userId=" + userId +
                ", orderNumber='" + orderNumber + '\'' +
                ", user=" + user +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public User getUser() {
        return user;
    }

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

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

    public BigInteger getUserId() {
        return userId;
    }

    public void setUserId(BigInteger userId) {
        this.userId = userId;
    }

    public String getOrderNumber() {
        return orderNumber;
    }

    public void setOrderNumber(String orderNumber) {
        this.orderNumber = orderNumber;
    }
}

对应的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">
<!--
映射文件
namespace 指定接口的类全名
-->
<mapper namespace="cn.itcast.dao.OrderMapper">
    <!--
    查询语句
    id: 接口中方法的名字
    resultType：返回的实体类的类型，类全名
    -->

    <resultMap id="queryOrderAndUser" type="Order" autoMapping="true">
        <id column="id" property="id"></id>
        <association property="user" javaType="User" autoMapping="true">
            <id column="id" property="id"></id>
        </association>
    </resultMap>
    <select id="queryOrderAndUser" resultMap="queryOrderAndUser">
           SELECT
            *
        FROM
            tb_order tbo
            INNER JOIN tb_user tbu ON tbo.user_id = tbu.id
        WHERE
            tbo.order_number = #{OrderNumber}
    </select>
</mapper>

对应的测试类：
java文件：

package cn.itcast.test;

import cn.itcast.dao.OrderMapper;
import cn.itcast.domain.Order;
import cn.itcast.util.MybatisUtils;
import org.apache.ibatis.session.SqlSession;

public class Demo02 {
    public static void main(String[] args) {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
       Order order =  mapper.queryOrderAndUser("20140921003");
        System.out.println("order = " + order);
    }
}

3.2 一对多多表查询

需求：查询id为1的用户及其订单信息

对应的SQL语句：

select * from tb_user tbu inner join tb_order tbo on tbu.id = tbo.user_id where tbu.id=1;

对应的OrderMapper接口

package cn.itcast.dao;

import cn.itcast.domain.Order;
import cn.itcast.domain.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface OrderMapper {
    Order findOneToOne(@Param("id") String id);
    User oneTomany(@Param("id") Long id);
}

user实体类中添加的信息，用于一对多的关联
在这里插入图片描述

OrderMapper的配置文件


<?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 指定接口的类全名
-->
<mapper namespace="cn.itcast.dao.OrderMapper">
    <!--
    查询语句
    id: 接口中方法的名字
    resultType：返回的实体类的类型，类全名
    -->
    <resultMap id="OneToOne" type="cn.itcast.domain.Order" autoMapping="true">
        <id column="id" property="id"></id>
        <association property="user" javaType="User" autoMapping="true">
            <id column="id" property="id"></id>
        </association>
    </resultMap>
    <select id="findOneToOne" resultMap="OneToOne">
        select * from tb_order as tbo  inner join tb_user as tbu on tbo.user_id = tbu.id
where order_number = #{id}
    </select>





    <resultMap id="oneToMany" type="User" autoMapping="true">
        <id property="id" column="uid"></id>
        <collection property="orders" javaType="list" ofType="Order" autoMapping="true">
            <id column="oid" property="id"></id>
        </collection>
    </resultMap>
    <select id="oneTomany" resultMap="oneToMany">
           SELECT
            tbu.id as uid,
            tbu.user_name,
            tbu.password,
            tbu.name,
            tbu.age,
            tbu.sex,
            tbo.id as oid,
            tbo.order_number
        FROM
            tb_user AS tbu
            INNER JOIN tb_order AS tbo ON tbu.id = tbo.user_id
        WHERE
            tbu.id = #{id}
    </select>
</mapper>

对应的测试类

package cn.itcast.text;

import cn.itcast.dao.OrderMapper;
import cn.itcast.domain.User;
import cn.itcast.util.MybatisUtils;
import org.apache.ibatis.session.SqlSession;

public class OneToMany{
    public static void main(String[] args){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
        User user = mapper.oneTomany(1L);
        System.out.println(user);
    }
}

3.3多对多

查询订单号为20140921001的订单的详情信息即查询订单信息+订单中的商品信息；

对应的OrderMapper接口方法：
Order findOrderAndItem(@Param(“orderNumber”) String orderNumber);

对应的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">
<!--
映射文件
namespace 指定接口的类全名
-->
<mapper namespace="cn.itcast.dao.OrderMapper">
    <!--
    查询语句
    id: 接口中方法的名字
    resultType：返回的实体类的类型，类全名
    -->
    <resultMap id="OneToOne" type="cn.itcast.domain.Order" autoMapping="true">
        <id column="id" property="id"></id>
        <association property="user" javaType="User" autoMapping="true">
            <id column="id" property="id"></id>
        </association>
    </resultMap>
    <select id="findOneToOne" resultMap="OneToOne">
        select * from tb_order as tbo  inner join tb_user as tbu on tbo.user_id = tbu.id
where order_number = #{id}
    </select>





    <resultMap id="oneToMany" type="User" autoMapping="true">
        <id property="id" column="uid"></id>
        <collection property="orders" javaType="list" ofType="Order" autoMapping="true">
            <id column="oid" property="id"></id>
        </collection>
    </resultMap>
    <select id="oneTomany" resultMap="oneToMany">
           SELECT
            tbu.id as uid,
            tbu.user_name,
            tbu.password,
            tbu.name,
            tbu.age,
            tbu.sex,
            tbo.id as oid,
            tbo.order_number
        FROM
            tb_user AS tbu
            INNER JOIN tb_order AS tbo ON tbu.id = tbo.user_id
        WHERE
            tbu.id = #{id}
    </select>

    <resultMap id="manyToMany" type="Order" autoMapping="true">
        <id property="id" column="oid"></id>
        <collection property="orderDetails" javaType="list" ofType="OrderDetail" autoMapping="true">
            <id column="did" property="id"></id>
            <association property="item" javaType="Item" autoMapping="true">
                <id column="id" property="id"></id>
            </association>
        </collection>
    </resultMap>
    <select id="findOrderAndItem" resultMap="manyToMany">
        SELECT
            tbo.id as oid,
            tbo.order_number,
            detail.id as did,
            detail.order_id,
            detail.item_id,
            detail.total_price,
            detail.status,
            item.*
        FROM
            tb_order AS tbo
            INNER JOIN tb_orderdetail AS detail ON tbo.id = detail.order_id
            INNER JOIN tb_item AS item ON detail.item_id = item.id
        WHERE
            tbo.order_number = #{orderNumber}
    </select>
</mapper>

对应的测试类：


package cn.itcast.text;

import cn.itcast.dao.OrderMapper;
import cn.itcast.domain.Order;
import cn.itcast.util.MybatisUtils;
import org.apache.ibatis.session.SqlSession;

public class ManyToMany {
    public static void main(String[] args) {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        OrderMapper sqlSessionMapper = sqlSession.getMapper(OrderMapper.class);
        Order orderAndItem = sqlSessionMapper.findOrderAndItem("20140921001");

        System.out.println("orderAndItem = " + orderAndItem);

    }
}

注意主要了解对应的执行流程，掌握对应的执行流程，才会明白内部的原理

quanweiya

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mybatis的高级查询

文章目录1.搭建环境1.1创建项目1.2导入jar包1.3创建核心配置文件1.4创建实体类1.5创建接口1.6创建Mapper映射文件1.7创建对应的工具包1.8创建测试类（确保项目跑通）2.数据库之间的表关系2.1 建表语句2.2数据库表之间的关联3.高级查询3.1一对一查询3.2 一对多多表查询1.搭建环境1.1创建项目创建对应的Java文件，项目名字随便起1.2导入jar包创建对应的lib文件夹存取对应的jar包导入对应的jar包1.3创建核心配置文件在src下创建对应的Myba
复制链接

扫一扫

专栏目录