前言 创建数据库表(user和message
),并插入数据:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 CREATE TABLE `user` (`id` varchar (64 ) NOT NULL ,`name` varchar (64 ) NULL ,PRIMARY KEY (`id` ) ); CREATE TABLE `info` (`id` varchar (64 ) NOT NULL ,`uid` varchar (64 ) NULL ,`msg` varchar (255 ) NULL ,PRIMARY KEY (`id` ) ); INSERT INTO `user` (`id` , `name` ) VALUES ('1001' , 'ahzoo' );INSERT INTO `user` (`id` , `name` ) VALUES ('1002' , 'ouo' );INSERT INTO `info` (`id` , `uid` , `msg` ) VALUES ('2001' , '1001' , '测试信息' );INSERT INTO `info` (`id` , `uid` , `msg` ) VALUES ('2002' , '1001' , '第二条信息' );
再创建两个数据库表(role和user_role
),并插入数据,用于多对多操作:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 CREATE TABLE `role` (`id` varchar (64 ) NOT NULL ,`name` varchar (64 ) NULL ,PRIMARY KEY (`id` ) ); CREATE TABLE `user_role` (`user_id` varchar (64 ) NOT NULL ,`role_id` varchar (64 ) NOT NULL ); INSERT INTO `role` (`id` , `name` ) VALUES ('3001' , '用户' );INSERT INTO `role` (`id` , `name` ) VALUES ('3002' , '管理员' );INSERT INTO `user_role` (`user_id` , `role_id` ) VALUES ('1001' , '3001' );INSERT INTO `user_role` (`user_id` , `role_id` ) VALUES ('1001' , '3002' );INSERT INTO `user_role` (`user_id` , `role_id` ) VALUES ('1002' , '3002' );
创建一个springboot项目,并生成增删改查,快速开始。 项目结构: 依赖:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 <dependencies > <dependency > <groupId > mysql</groupId > <artifactId > mysql-connector-java</artifactId > </dependency > <dependency > <groupId > org.mybatis.spring.boot</groupId > <artifactId > mybatis-spring-boot-starter</artifactId > <version > 2.2.2</version > </dependency > <dependency > <groupId > org.springframework.boot</groupId > <artifactId > spring-boot-starter</artifactId > </dependency > <dependency > <groupId > org.projectlombok</groupId > <artifactId > lombok</artifactId > <optional > true</optional > </dependency > <dependency > <groupId > org.springframework.boot</groupId > <artifactId > spring-boot-starter-test</artifactId > <scope > test</scope > </dependency > </dependencies >
一对一 一条信息对应一个发送者,查询信息的发送者。 在Info
实体类中增加User
对象,这样在对user和info进行联查时,将User
对象一起返回:entity/Info
1 2 3 4 5 6 7 8 9 import lombok.Data;@Data public class Info { private String id; private String uid; private String msg; private User user; }
映射文件:mapper/InfoDao.xml
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 <?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.example.demo.mapper.InfoMapper" > <select id ="selectListToMap" resultType ="Map" > SELECT u.id,u.name,i.id,i.uid,i.msg FROM user u,info i WHERE u.id=i.uid </select > <resultMap id ="BaseResultMap" type ="com.example.demo.entity.Info" > <id property ="id" column ="id" jdbcType ="VARCHAR" /> <result property ="uid" column ="uid" jdbcType ="VARCHAR" /> <result property ="msg" column ="msg" jdbcType ="VARCHAR" /> <association property ="user" javaType ="com.example.demo.entity.User" > <id property ="id" column ="user_id" jdbcType ="VARCHAR" /> <result property ="name" column ="name" jdbcType ="VARCHAR" /> </association > </resultMap > <select id ="selectListByMap" resultMap ="BaseResultMap" > SELECT u.id AS user_id,u.name,i.id,i.uid,i.msg FROM user u,info i WHERE u.id=i.uid </select > </mapper >
持久层:mapper/InfoMapper
1 2 3 4 5 6 7 8 9 10 import com.example.demo.entity.Info;import org.apache.ibatis.annotations.Mapper;import java.util.List;import java.util.Map;@Mapper public interface InfoMapper { List<Map<String,String>> selectListToMap(); List<Info> selectListByMap () ; }
测试: 查询目标信息对应的用户信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 import com.example.demo.entity.Info;import com.example.demo.mapper.InfoMapper;import org.junit.jupiter.api.Test;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.context.SpringBootTest;import java.util.List;import java.util.Map;@SpringBootTest class InfoTests { @Autowired InfoMapper infoMapper; @Test public void toGetMapResult () { List<Map<String, String>> infos = infoMapper.selectListToMap(); infos.forEach(System.out::println); } @Test public void toGetInfoResult () { List<Info> infos = infoMapper.selectListByMap(); infos.forEach(System.out::println); } }
一对多 一个用户可以发送多条信息,查询用户下的所有信息。 在User
实体类中增加Info的List
对象,用于返回查询到的多个Info
对象:entity/User
1 2 3 4 5 6 7 8 9 10 import lombok.Data;import java.util.List;@Data public class User { private String id; private String name; private List<Info> infos; }
持久层:mapper/UserMapper
1 2 3 4 5 6 7 import com.example.demo.entity.User;import org.apache.ibatis.annotations.Mapper;import java.util.List;@Mapper public interface UserMapper { List<User> findAllByMap () ; }
映射文件:mapper/UserMapper.xml
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 <?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.example.demo.mapper.UserMapper" > <resultMap id ="BaseResultMap" type ="com.example.demo.entity.User" > <id property ="id" column ="id" jdbcType ="VARCHAR" /> <result property ="name" column ="name" jdbcType ="VARCHAR" /> <collection property ="infos" ofType ="com.example.demo.entity.Info" > <result property ="id" column ="info_id" jdbcType ="VARCHAR" /> <result property ="uid" column ="uid" jdbcType ="VARCHAR" /> <result property ="msg" column ="msg" jdbcType ="VARCHAR" /> </collection > </resultMap > <select id ="findAllByMap" resultMap ="BaseResultMap" > SELECT u.id,u.name,i.id AS info_id,i.uid,i.msg FROM user u LEFT OUTER JOIN info i ON u.id = i.uid; </select > </mapper >
测试:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 import com.example.demo.entity.User;import com.example.demo.mapper.UserMapper;import org.junit.jupiter.api.Test;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.context.SpringBootTest;import java.util.List;@SpringBootTest public class UserTests { @Autowired UserMapper userMapper; @Test public void toGetAll () { List<User> allByMap = userMapper.findAllByMap(); allByMap.forEach(System.out::println); } }
可以看到所有用户下的所有信息都被查询到
多对多 一个用户可以有多个角色,一个角色可以属于多个用户,查询用户对应的角色,及角色对应的用户。 在User
实体类中增加Role的List
对象,用于返回查询到的多个Role
对象:entity/User
1 2 3 4 5 6 7 8 9 import lombok.Data;import java.util.List;@Data public class User { private String id; private String name; private List<Role> roles; }
同理:entity/Role
1 2 3 4 5 6 7 8 9 10 import lombok.Data;import java.util.List;@Data public class Role { private String id; private String name; private List<User> users; }
持久层:mapper/RoleMapper
1 2 3 4 5 6 7 import com.example.demo.entity.Role;import org.apache.ibatis.annotations.Mapper;import java.util.List;@Mapper public interface RoleMapper { List<Role> getAllUser () ; }
mapper/UserMapper
1 2 3 4 5 6 7 import com.example.demo.entity.User;import org.apache.ibatis.annotations.Mapper;import java.util.List;@Mapper public interface UserMapper { List<User> getAllRole () ; }
映射文件:mapper/RoleMapper
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 <resultMap id ="roleMap" type ="com.example.demo.entity.Role" > <id property ="id" column ="id" jdbcType ="VARCHAR" /> <result property ="name" column ="name" jdbcType ="VARCHAR" /> <collection property ="users" ofType ="com.example.demo.entity.User" > <result property ="id" column ="role_id" jdbcType ="VARCHAR" /> <result property ="name" column ="name" jdbcType ="VARCHAR" /> </collection > </resultMap > <select id ="getAllUser" resultMap ="roleMap" > SELECT u.id,u.name,r.id AS role_id,r.name FROM user u LEFT OUTER JOIN user_role ur ON u.id = ur.user_id LEFT OUTER JOIN role r ON r.id = ur.role_id </select >
mapper/UserMapper
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 <resultMap id ="userMap" type ="com.example.demo.entity.User" > <id property ="id" column ="id" jdbcType ="VARCHAR" /> <result property ="name" column ="name" jdbcType ="VARCHAR" /> <collection property ="roles" ofType ="com.example.demo.entity.Role" > <result property ="id" column ="role_id" jdbcType ="VARCHAR" /> <result property ="name" column ="name" jdbcType ="VARCHAR" /> </collection > </resultMap > <select id ="getAllRole" resultMap ="userMap" > SELECT u.id,u.name,r.id AS role_id,r.name FROM user u LEFT OUTER JOIN user_role ur ON u.id = ur.user_id LEFT OUTER JOIN role r ON r.id = ur.role_id </select >
测试:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 @Autowired RoleMapper roleMapper; @Autowired UserMapper userMapper; @Test public void toGetAllUser () { List<Role> allUser = roleMapper.getAllUser(); allUser.forEach(System.out::println); } @Test public void toGetAllRole () { List<User> allRole = userMapper.getAllRole(); allRole.forEach(System.out::println); }
**