(三)Mybatis的多对多查询(有mapper.xml)+注解开发(无mapper.xml)

news/2025/2/19 3:24:06/

一、Mybatis的多对多关系的mapper映射

(一个用户具有多个角色,一个角色可以被多个用户拥有)

1. 建表sql语句

CREATE TABLE `sys_role` (`id` bigint(20) NOT NULL AUTO_INCREMENT,`roleName` varchar(50) DEFAULT NULL,`roleDesc` varchar(50) DEFAULT NULL,PRIMARY KEY (`id`)
);/*Data for the table `sys_role` */insert  into `sys_role`(`id`,`roleName`,`roleDesc`) values (1,'院长','负责全面工作'),(2,'研究员','课程研发工作'),(3,'讲师','授课工作'),(4,'助教','协助解决学生的问题');/*Table structure for table `sys_user` */DROP TABLE IF EXISTS `sys_user`;CREATE TABLE `sys_user` (`id` bigint(20) NOT NULL AUTO_INCREMENT,`username` varchar(50) DEFAULT NULL,`email` varchar(50) DEFAULT NULL,`password` varchar(80) DEFAULT NULL,`phoneNum` varchar(20) DEFAULT NULL,PRIMARY KEY (`id`)
) ;/*Data for the table `sys_user` */insert  into `sys_user`(`id`,`username`,`email`,`password`,`phoneNum`) values (1,'zhangsan','zhangsan@itcast.cn','123','13888888888'),(2,'lisi','lisi@itcast.cn','123','13999999999'),(3,'wangwu','wangwu@itcast.cn','123','18599999999');/*Table structure for table `sys_user_role` */DROP TABLE IF EXISTS `sys_user_role`;CREATE TABLE `sys_user_role` (`userId` bigint(20) NOT NULL,`roleId` bigint(20) NOT NULL,PRIMARY KEY (`userId`,`roleId`),KEY `roleId` (`roleId`),CONSTRAINT `sys_user_role_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `sys_user` (`id`),CONSTRAINT `sys_user_role_ibfk_2` FOREIGN KEY (`roleId`) REFERENCES `sys_role` (`id`)
);/*Data for the table `sys_user_role` */insert  into `sys_user_role`(`userId`,`roleId`) values (1,1),(1,2),(2,2),(2,3);

2. 构建相应的User类和Role类

package com.bean;import java.util.List;public class User {private Long id;private String username;private String email;private String password;private String phoneNum;private List<Role> roles;public Long getId() {return id;}public void setId(Long id) {this.id = id;}public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public String getEmail() {return email;}public void setEmail(String email) {this.email = email;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}public String getPhoneNum() {return phoneNum;}public void setPhoneNum(String phoneNum) {this.phoneNum = phoneNum;}public List<Role> getRoles() {return roles;}public void setRoles(List<Role> roles) {this.roles = roles;}@Overridepublic String toString() {return "User{" +"id=" + id +", username='" + username + '\'' +", email='" + email + '\'' +", password='" + password + '\'' +", phoneNum='" + phoneNum + '\'' +", rolist='" + roles + '\'' +'}';}
}
package com.bean;
public class Role {private Long id;private String roleName;private String roleDesc;public Long getId() {return id;}public void setId(Long id) {this.id = id;}public String getRoleName() {return roleName;}public void setRoleName(String roleName) {this.roleName = roleName;}public String getRoleDesc() {return roleDesc;}public void setRoleDesc(String roleDesc) {this.roleDesc = roleDesc;}@Overridepublic String toString() {return "Role{" +"id=" + id +", roleName='" + roleName + '\'' +", roleDesc='" + roleDesc + '\'' +'}';}
}

3. 编写UserMapper接口

package com.mapper;import com.bean.User;import java.io.IOException;
import java.util.List;public interface UserMapper {List<User> findUserAndRoleAll() throws IOException;}

4. 编写UserMapper.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mapper.UserMapper"><resultMap id="UserRoleMap" type="user"><!--user的信息--><id column="userId" property="id"></id><result column="username" property="username"></result><result column="email" property="email"></result><result column="password" property="password"></result><result column="phoneNum" property="phoneNum"></result><!--内部的role信息--><collection property="roles" ofType="role"><id column="roleId" property="id"></id><result column="roleName" property="roleName"></result><result column="roleDesc" property="roleDesc"></result></collection></resultMap><select id="findUserAndRoleAll" resultMap="UserRoleMap">SELECT * FROM sys_user u,sys_user_role ur,sys_role r WHERE u.id=ur.userId and ur.roleId=r.id</select></mapper>

5. 编写测试类

package com;import com.bean.User;
import com.mapper.UserMapper;
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 org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class TestDemo {@Testpublic void test() throws IOException{InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);SqlSession session = sessionFactory.openSession(true);UserMapper userMapper = session.getMapper(UserMapper.class);List<User> all = userMapper.findUserAndRoleAll();for (User user : all) {System.out.println(user);}}}

在这里插入图片描述

二、Mybatis的多对多关系的注解开发

  • 常用注解:@Result(实现结果集封装),@Results(与Result一起使用,封装多个结果集),@One(实现一对一结果集封装),@Many(实现一对多结果集封装)

1. 删除UserMapper.xml文件,修改SqlMapConfig.xml文件如下

   <!-- 指定映射关系 --><mappers><!--指定接口所在的包--><package name="com.mapper"></package></mappers>

2. 创建UserMapper.java和RoleMapper.java接口

package com.mapper;
import com.bean.User;
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import java.io.IOException;
import java.util.List;
public interface UserMapper {@Select("select * from sys_user")@Results({@Result(id = true,column = "id",property = "id"),@Result(id = true,column = "username",property = "username"),@Result(id = true,column = "email",property = "email"),@Result(id = true,column = "password",property = "password"),@Result(id = true,column = "phoneNum",property = "phoneNum"),@Result(javaType = List.class,//要封装的实体类型property = "roles",//要封装的属性名称column = "id",//根据sys_user的id字段查询sys_user_role的对应的记录many = @Many(select = "com.mapper.RoleMapper.findByUserId")//查询哪个接口的方法获得对应数据)})List<User> findUserAndRoleAll() throws IOException;
}
package com.mapper;
import com.bean.Role;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface RoleMapper {@Select("select * from sys_user_role ur,sys_role r where ur.roleId = r.id and ur.userId = #{uid}")List<Role> findByUserId(int uid);
}

3. 编写测试代码

package com;
import com.bean.User;
import com.mapper.UserMapper;
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 org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;public class TestDemo {private UserMapper userMapper;@Beforepublic void before() throws IOException{InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);SqlSession session = sessionFactory.openSession(true);userMapper = session.getMapper(UserMapper.class);}@Testpublic void test() throws IOException{List<User> all = userMapper.findUserAndRoleAll();for (User user : all) {System.out.println(user);}}}

4. 测试结果如下

在这里插入图片描述


http://www.ppmy.cn/news/792272.html

相关文章

健康 真好

不得不感叹一声&#xff0c;健康 真好。 从上次写博文到现在过了半个月吧&#xff0c;我这半个月过的我天昏地暗日月无光&#xff0c;我记得我月初考试前一天就发烧&#xff0c;喝了药半夜出了汗退烧还和同学晚上看欧洲杯决赛&#xff0c;或许那会就已经埋下了伏笔。之后又是上…

健康管理系统

健康管理的思路和实践最初出现在美国。如同其他学科和行业一样,健康管理的兴起也是由于市场的需要和人类知识的积累。随着社会的发展,健康越来越成为社会公众关注的焦点和热点,也越来越成为幸福指数的关键指标。现代人要应付快节奏的学习、工作和生活,要面临越来越多的竞争和…

更便捷的使用MyBatis--->MyBatisPlus

什么是MyBatisPlus&#xff1f; 为了简化crud代码而产生的一款增强工具 只做增强不做改变。 最大的好处就是能简化开发的代码 性能方面也是非常高效的。总而言之 只要我们使用MyBatis的话 我们用MyBatisplus只会更加方便 因为他把我们平常做的重复化的工作删除了。 所以 我们掌…

mybatis-plus的generator配置

下载myBatisX插件,用mapper和xml方法的跳转 <!DOCTYPE generatorConfigurationPUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN""http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd"><generatorConfiguration>…

Mybatis | 06 Mybatis多表查询**

Mybatis | 06 Mybatis多表查询 Mybatis多表查询1. 四种表的关系1.1 一对多关系1.2 多对一关系1.3 一对一关系1.4 多对多关系 2. 示例&#xff1a;用户和账户的查询2.1 相关准备2.1.1 数据库2.1.2 代码2.1.2.1 用户和账户实体类2.1.2.2 用户和账户的DAO层接口2.1.2.3 配置文件2.…

Mybatis generator和mybatis-plus

一 Mybatis generator的实现 实现方法一&#xff1a; 依赖配置文件运行实现类 1.依赖 <!-- MyBatis 生成器 --><dependency><groupId>org.mybatis.generator</groupId><artifactId>mybatis-generator-core</artifactId><version>1.…

乐高幻影忍者推出十周年复刻经典套装;炸鸡大师Popeyes开设首家南京旗舰店 | 美通企业日报...

今日看点&#xff1a;乐高幻影忍者系列诞生十周年&#xff0c;推出复刻经典套装与时尚联名系列。炸鸡大师Popeyes正式宣布入驻南京。麦德龙中国发布2020可持续发展报告。Cytiva向龙沙集团交付模块化生物工厂。拜耳以医药创新突破推动处方药业务转型。锐思华创携裸眼AR亮相国际消…

mybatis/mybatisplus 一对多查询

1.Controller 定义 需要传入的参数 OntologyEntity paramnew OntologyEntity(); param.setName(name); Page<OntologyEntity> resultPagenew Page<>(pageNumber,pageSize); // 查询分页数据&#xff1a;mybatisplus // param&#xff1a;为查询需要传入的参数可以…