當(dāng)前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

mybatis之一对多

發(fā)布時(shí)間：2025/4/16 编程问答 17 豆豆

生活随笔收集整理的這篇文章主要介紹了 mybatis之一对多小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

今天主要話題圍繞這么幾個(gè)方面？

mybatis一對多示例
sql優(yōu)化策略

一、mybatis之一對多

在說一對多之前，順便說一下一對一。

一對一，常見的例子，比如以常見的班級例子來說，一個(gè)班主任只屬于一個(gè)班級(排除某個(gè)班主任能力超群可兼任多個(gè)班級).

例如:

<?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.yc.mybatis.test.classMapper"><select id="getClass" parameterType="int" resultMap="getClassMap">select * from class c, teacher t where c.teacher_id = t.t_id and c.teacher_id=#{id}</select><resultMap type="Classes" id="getClassMap"><id property="id" column="c_id"/> <result property="name" column="c_name"/><association property="teacher" javaType="Teacher"> <id property="id" column="t_id"/><result property="name" column="t_name"/></association></resultMap><select id="getClass1" parameterType="int" resultMap="getClassMap1">select * from class where c_id=#{id}</select><resultMap type="Classes" id="getClassMap1"><id property="id" column="c_id"/> <result property="name" column="c_name"/><association property="teacher" column="teacher_id" select="getTeacher"/> </resultMap><select id="getTeacher" parameterType="int" resultType="Teacher">select t_id id,t_name name from teacher where t_id =#{id}</select> </mapper>

順便對association標(biāo)簽的屬性進(jìn)行解釋:
property:對象屬性名稱
javaType:對象屬性類型
column:所對應(yīng)的外鍵字段名稱

一對多，以我博客為例，比如今天我寫的一個(gè)近期評論的接口就是一個(gè)一對多的體現(xiàn)(一個(gè)評論者可以對應(yīng)多篇文章，相反，多篇文章也能對應(yīng)一個(gè)評論者，從中可以體現(xiàn)一對多，多對一，甚至多對多的關(guān)系)

關(guān)于一對一、一對多或者多對多，可以參考Mybatis 一對一，一對多，多對一，多對多的理解

話不多說，看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.blog.springboot.dao.CommentsDao"><resultMap id="BaseResultMap" type="com.blog.springboot.entity.Comments"><id column="comment_ID" property="commentId" /><result column="comment_post_ID" property="commentPostId" /><result column="comment_author" property="commentAuthor" /><result column="comment_author_email" property="commentAuthorEmail" /><result column="comment_author_url" property="commentAuthorUrl" /><result column="comment_author_IP" property="commentAuthorIp" /><result column="comment_date" property="commentDate" /><result column="comment_date_gmt" property="commentDateGmt" /><result column="comment_content" property="commentContent" /><result column="comment_karma" property="commentKarma" /><result column="comment_approved" property="commentApproved" /><result column="comment_agent" property="commentAgent" /><result column="comment_type" property="commentType" /><result column="comment_parent" property="commentParent" /><result column="user_id" property="userId" /><collection property="posts" ofType="Posts"><result column="post_title" property="postTitle"/></collection></resultMap><sql id="Base_Column_List">comment_ID AS commentId, comment_post_ID AS commentPostId, comment_author AS commentAuthor, comment_author_email AS commentAuthorEmail, comment_author_url AS commentAuthorUrl, comment_author_IP AS commentAuthorIp, comment_date AS commentDate, comment_date_gmt AS commentDateGmt, comment_content AS commentContent, comment_karma AS commentKarma, comment_approved AS commentApproved, comment_agent AS commentAgent, comment_type AS commentType, comment_parent AS commentParent, user_id AS userId</sql><select id="recentComments" resultMap="BaseResultMap">SELECT comments.comment_author,posts.post_title FROM wp_comments AS comments LEFT JOIN wp_posts AS posts ON(comments.comment_post_ID=posts.ID) WHERE comments.comment_approved='0' AND posts.post_status='publish' ORDER BY comments.comment_date_gmt DESC LIMIT 0,5</select></mapper>

相關(guān)屬性我就不做多的解釋，關(guān)于MyBatis相關(guān)的教程，除了參考官網(wǎng)之外，還可以參考我的博客系列文章，地址為:https://www.cnblogs.com/youcong/category/1144041.html

關(guān)于ofType還是要說的，如果你的mybatis-config.xml或者是springboot中的application.yml或application.properties沒有配置對應(yīng)的別名，那么請將類的完整路徑填寫上去，假定我沒有做出相關(guān)的配置的話，那么我需要這么寫 ofType=”com.blog.springboot.entity.Posts”。

collection的property要包含在com.blog.springboot.entity.Comments類里面

我貼出我的Comments類，大家可以做一個(gè)參考:

package com.blog.springboot.entity;import java.io.Serializable; import java.util.Date; import java.util.List;import com.baomidou.mybatisplus.activerecord.Model; import com.baomidou.mybatisplus.annotations.TableField; import com.baomidou.mybatisplus.annotations.TableId; import com.baomidou.mybatisplus.annotations.TableName; import com.baomidou.mybatisplus.enums.IdType;/*** <p>* * </p>** @author youcong* @since 2019-02-12*/ @TableName("wp_comments") public class Comments extends Model<Comments> {private static final long serialVersionUID = 1L;@TableId(value = "comment_ID", type = IdType.AUTO)private Long commentId;@TableField("comment_post_ID")private Long commentPostId;@TableField("comment_author")private String commentAuthor;@TableField("comment_author_email")private String commentAuthorEmail;@TableField("comment_author_url")private String commentAuthorUrl;@TableField("comment_author_IP")private String commentAuthorIp;@TableField("comment_date")private Date commentDate;@TableField("comment_date_gmt")private Date commentDateGmt;@TableField("comment_content")private String commentContent;@TableField("comment_karma")private Integer commentKarma;@TableField("comment_approved")private String commentApproved;@TableField("comment_agent")private String commentAgent;@TableField("comment_type")private String commentType;@TableField("comment_parent")private Long commentParent;@TableField("user_id")private Long userId;@TableField(exist=false)private List<Posts> posts;public List<Posts> getPosts() {return posts;}public void setPosts(List<Posts> posts) {this.posts = posts;}public Long getCommentId() {return commentId;}public void setCommentId(Long commentId) {this.commentId = commentId;}public Long getCommentPostId() {return commentPostId;}public void setCommentPostId(Long commentPostId) {this.commentPostId = commentPostId;}public String getCommentAuthor() {return commentAuthor;}public void setCommentAuthor(String commentAuthor) {this.commentAuthor = commentAuthor;}public String getCommentAuthorEmail() {return commentAuthorEmail;}public void setCommentAuthorEmail(String commentAuthorEmail) {this.commentAuthorEmail = commentAuthorEmail;}public String getCommentAuthorUrl() {return commentAuthorUrl;}public void setCommentAuthorUrl(String commentAuthorUrl) {this.commentAuthorUrl = commentAuthorUrl;}public String getCommentAuthorIp() {return commentAuthorIp;}public void setCommentAuthorIp(String commentAuthorIp) {this.commentAuthorIp = commentAuthorIp;}public Date getCommentDate() {return commentDate;}public void setCommentDate(Date commentDate) {this.commentDate = commentDate;}public Date getCommentDateGmt() {return commentDateGmt;}public void setCommentDateGmt(Date commentDateGmt) {this.commentDateGmt = commentDateGmt;}public String getCommentContent() {return commentContent;}public void setCommentContent(String commentContent) {this.commentContent = commentContent;}public Integer getCommentKarma() {return commentKarma;}public void setCommentKarma(Integer commentKarma) {this.commentKarma = commentKarma;}public String getCommentApproved() {return commentApproved;}public void setCommentApproved(String commentApproved) {this.commentApproved = commentApproved;}public String getCommentAgent() {return commentAgent;}public void setCommentAgent(String commentAgent) {this.commentAgent = commentAgent;}public String getCommentType() {return commentType;}public void setCommentType(String commentType) {this.commentType = commentType;}public Long getCommentParent() {return commentParent;}public void setCommentParent(Long commentParent) {this.commentParent = commentParent;}public Long getUserId() {return userId;}public void setUserId(Long userId) {this.userId = userId;}@Overrideprotected Serializable pkVal() {return this.commentId;}@Overridepublic String toString() {return "Comments{" +", commentId=" + commentId +", commentPostId=" + commentPostId +", commentAuthor=" + commentAuthor +", commentAuthorEmail=" + commentAuthorEmail +", commentAuthorUrl=" + commentAuthorUrl +", commentAuthorIp=" + commentAuthorIp +", commentDate=" + commentDate +", commentDateGmt=" + commentDateGmt +", commentContent=" + commentContent +", commentKarma=" + commentKarma +", commentApproved=" + commentApproved +", commentAgent=" + commentAgent +", commentType=" + commentType +", commentParent=" + commentParent +", userId=" + userId +"}";} }

也許大家發(fā)現(xiàn)我的mybatis與你們的mybatis不一樣，實(shí)際上我用的是mybatis-plus，mybatis-plus可以說跟mybatis幾乎沒有什么區(qū)別，我多次強(qiáng)調(diào)過，mybatis-plus是mybatis的增強(qiáng)版，意味著mybatis原有的功能，mybatis-plus可以毫無顧忌的拿來即用。

關(guān)于mybatis-plus的學(xué)習(xí)教程，感興趣的朋友可以參考我的這篇博客(包含從入門到使用):https://www.cnblogs.com/youcong/category/1213059.html

sql優(yōu)化策略

sql優(yōu)化的策略有很多，大家可以參考如下:

(1)任何地方都不要使用select?from table_name，請使用具體的字段列表代替”“ ，不要返回用不到的任何字段;
(2)對查詢進(jìn)行優(yōu)化，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在where及order by涉及的列建立索引;
(3)應(yīng)盡量避免在where子句中使用or來連接條件，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描;
(4)應(yīng)盡量避免在where子句中使用!=或<>操作符，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描;
(5)int和not in慎用，否則會(huì)導(dǎo)致全表掃描;
(6)應(yīng)盡量避免在where子句中對字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描;
(7)很多時(shí)候使用exists代替in是一個(gè)好的選擇;
(8)盡量使用數(shù)字型字段，若只含數(shù)值信息的字段設(shè)計(jì)為字符型，這將會(huì)降低查詢和連接的性能，并會(huì)增加存儲(chǔ)開銷,這是因?yàn)橐嬖谔幚聿樵兒瓦B接時(shí)會(huì)逐個(gè)比較字符串職工的每一個(gè)字符，而對于數(shù)字型而言只需要比較一次就夠了;
(9)盡可能使用varchar代替char，因?yàn)槭紫茸冮L字段存儲(chǔ)空間小，可以節(jié)省存儲(chǔ)空間，其次對于查詢來說，在一個(gè)相對較小的字段內(nèi)搜索效率顯然要高些;

當(dāng)然遠(yuǎn)遠(yuǎn)不止這么多，知識的海洋是無窮的，探索的樂趣亦如此。

關(guān)于sql優(yōu)化思路，大家可以參考SQL優(yōu)化思路大全

轉(zhuǎn)載于:https://www.cnblogs.com/youcong/p/10503069.html

總結(jié)

以上是生活随笔為你收集整理的mybatis之一对多的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

MyBatis

上一篇： 20175316 盛茂淞实验一 Jav
下一篇： HTML和CSS在IE7中常见的兼容性问