MyBatis复杂映射开发之一对一查询

2022-09-04 22:32:19后端开发 MyBatis评论82字数 4245阅读14分9秒阅读模式

一对一查询需求

用户表和订单表的关系为：一个用户可以有多个订单，一个订单只能从属于一个用户文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-one-query.html

文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-one-query.html

一对一查询需求：查询一个订单，同时查询出该订单所对应的用户文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-one-query.html

对应的sql语句：select * from orders o,user u where o.uid=u.id文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-one-query.html

查询结果如下：文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-one-query.html

id	ordertime	total	uid	id	username	password	birthday
1	2022-03-17 17:15:33	3000	1	1	lucy	123	2022-03-17 17:15:56
2	2022-03-17 17:15:33	4000	1	1	lucy	123	2022-03-17 17:15:56
3	2022-03-17 17:15:33	5000	2	2	tom	123	2022-03-17 17:15:56

需求分析

文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-one-query.html

具体实现

创建User和Order实体

User

文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-one-query.html

/**
* 用户信息
*
* @name: User
* @author: terwer
* @date: 2022-05-08 17:41
*/
class User {
  var id: Int? = null
  var username: String? = null
  override fun toString(): String {
      return "User{" +
              "id=" + id +
              ", username='" + username + '\'' +
              '}'
  }
}

文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-one-query.html

/**
* 用户信息
*
* @name: User
* @author: terwer
* @date: 2022-03-17 17:41
**/
public class User {
  private Integer id;
  private String username;

  public Integer getId() {
      return id;
  }

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

  public String getUsername() {
      return username;
  }

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

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

文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-one-query.html

Order文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-one-query.html

文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-one-query.html

/**
* 订单
*
* @name: Order
* @author: terwer
* @date: 2022-05-08 17:42
*/
class Order {
  var id: Int? = null
  var orderTime: String? = null
  var total: Double? = null

  // 代表当前订单属于哪一个用户
  var user: User? = null
  override fun toString(): String {
      return "Order{" +
              "id=" + id +
              ", orderTime='" + orderTime + '\'' +
              ", total=" + total +
              ", user=" + user +
              '}'
  }
}

文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-one-query.html

/**
* 订单
*
* @name: Order
* @author: terwer
* @date: 2022-03-17 17:42
**/
public class Order {
  private Integer id;
  private String orderTime;
  private Double total;

  // 代表当前订单属于哪一个用户
  private User user;

  public Integer getId() {
      return id;
  }

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

  public String getOrderTime() {
      return orderTime;
  }

  public void setOrderTime(String orderTime) {
      this.orderTime = orderTime;
  }

  public Double getTotal() {
      return total;
  }

  public void setTotal(Double total) {
      this.total = total;
  }

  public User getUser() {
      return user;
  }

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

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

文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-one-query.html

创建OrderMapper接口

文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-one-query.html

/**
 * 订单映射
 *
 * @name: IUserMapper
 * @author: terwer
 * @date: 2022-05-08 17:54
 */
interface OrderMapper {
    /**
     * 查询订单同时查询订单所属用户
     * @return
     */
    fun findOrderAndUser(): List<Order?>?
}

文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-one-query.html

/**
 * 订单映射
 *
 * @name: IUserMapper
 * @author: terwer
 * @date: 2022-03-17 17:54
 **/
public interface OrderMapper {
    /**
     * 查询订单同时查询订单所属用户
     * @return
     */
    public List<Order> findOrderAndUser();
}

文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-one-query.html

配置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="com.terwergreen.mapper.OrderMapper">
    <resultMap id="orderMap" type="com.terwergreen.pojo.Order">
        <result property="id" column="id"></result>
        <result property="orderTime" column="ordertime"></result>
        <result property="total" column="total"></result>

        <association property="user" javaType="com.terwergreen.pojo.User">
            <result property="id" column="uid"></result>
            <result property="username" column="username"></result>
        </association>
    </resultMap>

    <!-- resultMap：手动配置实体属性与表字段的映射关系 -->
    <select id="findOrderAndUser" resultMap="orderMap">
        select * from orders o,user u where o.uid=u.id
    </select>
</mapper>

另外一种配置方法文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-one-query.html

 <mapper namespace="com.terwergreen.mapper.OrderMapper">
    <resultMap id="orderMap" type="com.terwergreen.pojo.Order">
       <result property="id" column="id"></result>
        <result property="orderTime" column="ordertime"></result>
        <result property="total" column="total"></result>

        <result property="user.id" column="uid"></result>
        <result property="user.username" column="username"></result>
    </resultMap>

    <!-- resultMap：手动配置实体属性与表字段的映射关系 -->
    <select id="findOrderAndUser" resultMap="orderMap">
        select * from orders o,user u where o.uid=u.id
    </select>
</mapper>

测试结果

文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-one-query.html

@Test
@Throws(IOException::class)
fun test1() {
    val resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml")
    val sqlSessionFactory = SqlSessionFactoryBuilder().build(resourceAsStream)
    val sqlSession = sqlSessionFactory.openSession()
    val orderMapper = sqlSession.getMapper(OrderMapper::class.java)
    val orderList = orderMapper.findOrderAndUser()
    for (order in orderList) {
        println(order)
    }
}

文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-one-query.html

@Test
public void test1() throws IOException {
    InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

    SqlSession sqlSession = sqlSessionFactory.openSession();
    OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);

    List<Order> orderList = orderMapper.findOrderAndUser();
    for (Order order : orderList) {
        System.out.println(order);
    }
}

文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-one-query.html

文章更新历史
2022/05/08 feat:新增Kotlin支持文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-one-query.html

文章源自浅海拾贝-https://blog.terwergreen.com/mybatis-complex-mapping-development-one-to-one-query.html

扫码加我微信
验证消息请输入：来自你的博客

我的微信公众号
微信扫一扫与我交流吧

mybatis

vite

zookeeper

wordpress

spring-cloud

k8s

electron

javase

MyBatis复杂映射开发之一对一查询

一对一查询需求

需求分析

具体实现

创建User和Order实体

创建OrderMapper接口

配置OrderMapper.xml

测试结果

MyBatis机制介绍与原理

MyBatis-RedisCache源码分析

MyBatis的二级缓存整合redis

MyBatis的二级缓存

MyBatis的一级缓存

MyBatis缓存的概念

MyBatis常用注解及基本增删改查的注解实现

MyBatis的注解实现复杂映射开发

MyBatis复杂映射开发之多对多查询

MyBatis复杂映射开发之一对多查询

发表评论

mybatis

vite

zookeeper

wordpress

spring-cloud

k8s

electron

javase

一对一查询需求

需求分析

具体实现

创建User和Order实体

创建OrderMapper接口

配置OrderMapper.xml

测试结果

发表评论

加入我们

立即登录

找回密码