當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

mybatis教程--映射之一对一查询

發布時間：2025/3/20 编程问答 12 豆豆

生活随笔收集整理的這篇文章主要介紹了 mybatis教程--映射之一对一查询小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

一對一查詢

這里我們是在用戶表，訂單表，訂單詳情表，商品表的基礎上進行講解的。下面我也會把這四張表的po類列出來。

1.1?需求

查詢訂單信息關聯查詢用戶信息

1.2?sql語句

查詢語句：

先確定主查詢表：訂單信息表

再確定關聯查詢表：用戶信息

通過orders關聯查詢用戶使用user_id一個外鍵，只能關聯查詢出一條用戶記錄就可以使用內連接

SELECTorders.*,user.username,user.sexFROMorders,USERWHERE orders.user_id = user.id

1.3?使用resultType實現

1.3.1?創建po類

基礎的單表的 po類：

?user.java

package com.sihai.mybatis.po;import java.io.Serializable; import java.util.Date; import java.util.List;/*** @author sihai*/ public class User implements Serializable {private int id;private String username;// 用戶姓名private String sex;// 性別private Date birthday;// 生日private String address;// 地址//多個訂單private List<Orders> orderlist;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 getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public Date getBirthday() {return birthday;}public void setBirthday(Date birthday) {this.birthday = birthday;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}public List<Orders> getOrderlist() {return orderlist;}public void setOrderlist(List<Orders> orderlist) {this.orderlist = orderlist;}@Overridepublic String toString() {return "User [id=" + id + ", username=" + username + ", sex=" + sex+ ", birthday=" + birthday + ", address=" + address + "]";}}

items.java

package com.sihai.mybatis.po;import java.io.Serializable; import java.util.Date;public class Items implements Serializable {private Integer id;private String name;private Float price;private String pic;private Date createtime;private String detail;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name == null ? null : name.trim();}public Float getPrice() {return price;}public void setPrice(Float price) {this.price = price;}public String getPic() {return pic;}public void setPic(String pic) {this.pic = pic == null ? null : pic.trim();}public Date getCreatetime() {return createtime;}public void setCreatetime(Date createtime) {this.createtime = createtime;}public String getDetail() {return detail;}public void setDetail(String detail) {this.detail = detail == null ? null : detail.trim();}@Overridepublic String toString() {return "Items [id=" + id + ", name=" + name + ", price=" + price+ ", pic=" + pic + ", createtime=" + createtime + ", detail="+ detail + "]";}}

order.java

package com.sihai.mybatis.po;import java.io.Serializable; import java.util.Date; import java.util.List;public class Orders implements Serializable {private Integer id;private Integer userId;private String number;private Date createtime;private String note;//關聯用戶信息private User user;//訂單明細private List<Orderdetail> orderdetails;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public Integer getUserId() {return userId;}public void setUserId(Integer userId) {this.userId = userId;}public String getNumber() {return number;}public void setNumber(String number) {this.number = number == null ? null : number.trim();}public Date getCreatetime() {return createtime;}public void setCreatetime(Date createtime) {this.createtime = createtime;}public String getNote() {return note;}public void setNote(String note) {this.note = note == null ? null : note.trim();}public User getUser() {return user;}public void setUser(User user) {this.user = user;}public List<Orderdetail> getOrderdetails() {return orderdetails;}public void setOrderdetails(List<Orderdetail> orderdetails) {this.orderdetails = orderdetails;}}

orderDetails.java

package com.sihai.mybatis.po;import java.io.Serializable;public class Orderdetail implements Serializable {private Integer id;private Integer ordersId;private Integer itemsId;private Integer itemsNum;//商品信息private Items items;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public Integer getOrdersId() {return ordersId;}public void setOrdersId(Integer ordersId) {this.ordersId = ordersId;}public Integer getItemsId() {return itemsId;}public void setItemsId(Integer itemsId) {this.itemsId = itemsId;}public Integer getItemsNum() {return itemsNum;}public void setItemsNum(Integer itemsNum) {this.itemsNum = itemsNum;}public Items getItems() {return items;}public void setItems(Items items) {this.items = items;}@Overridepublic String toString() {return "Orderdetail [id=" + id + ", ordersId=" + ordersId+ ", itemsId=" + itemsId + ", itemsNum=" + itemsNum + "]";}}

1.3.2?一對一查詢映射的pojo

創建pojo包括訂單信息和用戶信息，resultType才可以完成映射。

創建OrderCustom作為自定義pojo，繼承sql查詢列多的po類。

?OrderCustom.java

package com.sihai.mybatis.po; /** * @author sihai */

public class OrderCustom extends Orders {//補充用戶信息private String username;private String sex;private String address;public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}@Overridepublic String toString() {return "OrderCustom [username=" + username + ", sex=" + sex+ ", address=" + address + "]";}}

1.3.3?mapper.xml

定義mapper.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.sihai.mybatis.mapper.OrdersMapperCustom"><resultMap type="orders" id="ordersUserResultMap"><id column="id" property="id"/><result column="user_id" property="userId"/><result column="number" property="number"/><result column="createtime" property="createtime"/><result column="note" property="note"/><association property="user" javaType="user"><id column="user_id" property="id"/><result column="username" property="username"/><result column="sex" property="sex"/></association></resultMap><resultMap type="orders" id="orderCustomLazyLoading"><id column="id" property="id" /><result column="user_id" property="userId" /><result column="number" property="number" /><result column="createtime" property="createtime" /><result column="note" property="note" /><association property="user"select="com.sihai.mybatis.mapper.UserMapper.findUserById" column="user_id"></association></resultMap><select id="findOrderUserList" resultType="orderCustom">SELECT orders.*,user.username,user.sex FROMorders,USER WHERE orders.user_id = user.id </select><select id="findOrderUserListResultMap" resultMap="ordersUserResultMap" >SELECT orders.*,user.username,user.sex FROMorders,USER WHERE orders.user_id = user.id </select><select id="findOrderUserListLazyLoading" resultMap="orderCustomLazyLoading">SELECT orders.*FROMorders</select></mapper>

1.3.4?mapper.java

package com.sihai.mybatis.mapper;import java.util.List;import com.sihai.mybatis.po.OrderCustom; import com.sihai.mybatis.po.Orders; import com.sihai.mybatis.po.User; /** * @author sihai */

public interface OrdersMapperCustom {// 一對一查詢，查詢訂單關聯查詢用戶，使用resultTypepublic List<OrderCustom> findOrderUserList() throws Exception;// 一對一查詢，使用resultMappublic List<Orders> findOrderUserListResultMap() throws Exception;//一對一查詢，延遲加載public List<Orders> findOrderUserListLazyLoading() throws Exception;// 一對多查詢，使用resultMappublic List<Orders> findOrderAndOrderDetails() throws Exception;// 一對多查詢，使用resultMappublic List<User> findUserOrderDetail() throws Exception;}

1.4?使用resultMap實現一對一

1.4.1?resultMap映射思路

resultMap提供一對一關聯查詢的映射和一對多關聯查詢映射，一對一映射思路：將關聯查詢的信息映射到pojo中，如下：

在Orders類中創建一個User屬性，將關聯查詢的信息映射到User屬性中。

private Integer id;private Integer userId;private String number;private Date createtime;private String note;//關聯用戶信息private User user;

1.4.2?mapper.xml

<select id="findOrderUserListResultMap" resultMap="ordersUserResultMap" >SELECT orders.*,user.username,user.sex FROMorders,USER WHERE orders.user_id = user.id </select>

1.4.3?resultMap定義

<resultMap type="orders" id="ordersUserResultMap"><id column="id" property="id"/><result column="user_id" property="userId"/><result column="number" property="number"/><result column="createtime" property="createtime"/><result column="note" property="note"/><association property="user" javaType="user"><id column="user_id" property="id"/><result column="username" property="username"/><result column="sex" property="sex"/></association></resultMap>

1.4.4?mapper.java

// 一對一查詢，使用resultMappublic List<Orders> findOrderUserListResultMap() throws Exception;

1.5 總結

resultType：要自定義pojo 保證sql查詢列和pojo的屬性對應，這種方法相對較簡單，所以應用廣泛。

resultMap：使用association完成一對一映射需要配置一個resultMap，過程有點復雜，如果要實現延遲加載就只能用resultMap實現，如果為了方便對關聯信息進行解析，也可以用association將關聯信息映射到pojo中方便解析。

總結

以上是生活随笔為你收集整理的mybatis教程--映射之一对一查询的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： mybatis教程--原始方式和mapp
下一篇： mybatis教程--一对多查询