幽映每白日，清辉照衣裳。这篇文章主要讲述MyBatis从入门到精通—MyBatis多表查询和注解开发相关的知识，希望能为你提供帮助。
Mybatis多表查询
一对一查询 一对一查询的模型MapperScannerConfigurer用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户
一对一查询的需求：查询一个订单，与此同时查询出该订单所属的用户

文章图片

一对一查询的语句对应的sql语句：

select *from orders o,user u where o.uid=u.id;

查询的结果如下：

文章图片

创建Order和User实体

public class Order private int id; private Date ordertime; private double total; //代表当前订单从属于哪一个客户 private User user; public class User private int id; private String username; private String password; private Date birthday;

创建OrderMapper接口

public interface OrderMapper List< Order> findAll();

配置OrderMapper.xml

< mapper namespace="com.zjq.mapper.OrderMapper"> < resultMap id="orderMap" type="com.zjq.domain.Order"> < result column="uid" property="user.id"> < /result> < result column="username" property="user.username"> < /result> < result column="password" property="user.password"> < /result> < result column="birthday" property="user.birthday"> < /result> < /resultMap> < select id="findAll" resultMap="orderMap"> select * from orders o,user u where o.uid=u.id < /select> < /mapper>

其中还可以配置如下：

< resultMap id="orderMap" type="com.zjq.domain.Order"> < result property="id" column="id"> < /result> < result property="ordertime" column="ordertime"> < /result> < result property="total" column="total"> < /result> < association property="user" javaType="com.zjq.domain.User"> < result column="uid" property="id"> < /result> < result column="username" property="username"> < /result> < result column="password" property="password"> < /result> < result column="birthday" property="birthday"> < /result> < /association> < /resultMap>

测试结果

OrderMapper mapper = sqlSession.getMapper(OrderMapper.class); List< Order> all = mapper.findAll(); for(Order order : all) System.out.println(order);

输出：

11:29:37,682 DEBUG JdbcTransaction:137 - Opening JDBC Connection 11:29:38,178 DEBUG PooledDataSource:406 - Created connection 1858609436. 11:29:38,179 DEBUG JdbcTransaction:101 - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@6ec8211c] 11:29:38,182 DEBUG findAll:159 - ==> Preparing: SELECT *,o.id oid FROM orders o,USER u WHERE o.uid=u.id 11:29:38,305 DEBUG findAll:159 - ==> Parameters: 11:29:38,351 DEBUG findAll:159 - < ==Total: 3 11:29:38,357 DEBUG JdbcTransaction:123 - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@6ec8211c] Orderid=1, ordertime=Sun Dec 12 00:00:00 GMT+08:00 2021, total=3000.0, user=Userid=1, username=zjq, password=abc, birthday=Wed Jul 28 00:00:00 GMT+08:00 2021, roleList=null Orderid=2, ordertime=Sun Dec 12 00:00:00 GMT+08:00 2021, total=4000.0, user=Userid=1, username=zjq, password=abc, birthday=Wed Jul 28 00:00:00 GMT+08:00 2021, roleList=null Orderid=3, ordertime=Sun Dec 12 00:00:00 GMT+08:00 2021, total=5000.0, user=Userid=2, username=jack, password=123456, birthday=Fri Aug 20 00:00:00 GMT+08:00 2021, roleList=null

一对多查询 一对多查询的模型用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户
一对多查询的需求：查询一个用户，与此同时查询出该用户具有的订单

文章图片

一对多查询的语句对应的sql语句：

select *,o.id oid from user u left join orders o on u.id=o.uid;

查询的结果如下：

文章图片

修改User实体

public class Order private int id; private Date ordertime; private double total; //代表当前订单从属于哪一个客户 private User user; public class User private int id; private String username; private String password; private Date birthday; //代表当前用户具备哪些订单 private List< Order> orderList;

创建UserMapper接口

public interface UserMapper List< User> findAll();

配置UserMapper.xml

< mapper namespace="com.zjq.mapper.UserMapper"> < resultMap id="userMap" type="com.zjq.domain.User"> < result column="id" property="id"> < /result> < result column="username" property="username"> < /result> < result column="password" property="password"> < /result> < result column="birthday" property="birthday"> < /result> < collection property="orderList" ofType="com.zjq.domain.Order"> < result column="oid" property="id"> < /result> < result column="ordertime" property="ordertime"> < /result> < result column="total" property="total"> < /result> < /collection> < /resultMap> < select id="findAll" resultMap="userMap"> select *,o.id oid from user u left join orders o on u.id=o.uid < /select> < /mapper>

测试结果

UserMapper mapper = sqlSession.getMapper(UserMapper.class); List< User> all = mapper.findAll(); for(User user : all) System.out.println(user.getUsername()); List< Order> orderList = user.getOrderList(); for(Order order : orderList) System.out.println(order); System.out.println("----------------------------------");

输出：

11:38:28,060 DEBUG findAll:159 - ==> Preparing: SELECT *,o.id oid FROM USER u,orders o WHERE u.id=o.uid 11:38:28,179 DEBUG findAll:159 - ==> Parameters: 11:38:28,254 DEBUG findAll:159 - < ==Total: 3 Userid=1, username=zjq, password=abc, birthday=Wed Jul 28 00:00:00 GMT+08:00 2021, roleList=null Userid=2, username=jack, password=123456, birthday=Fri Aug 20 00:00:00 GMT+08:00 2021, roleList=null 11:38:28,265 DEBUG JdbcTransaction:123 - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@6ec8211c] 11:38:28,265 DEBUG JdbcTransaction:91 - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@6ec8211c] 11:38:28,265 DEBUG PooledDataSource:363 - Returned connection 1858609436 to pool.

多对多查询 多对多查询的模型用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用
多对多查询的需求：查询用户同时查询出该用户的所有角色

文章图片

多对多查询的语句对应的sql语句：

select u.*,r.*,r.id rid from user u left join sys_user_role ur on u.id=ur.useridinner join sys_role r on ur.roleid=r.id;

查询的结果如下：

文章图片

创建Role实体，修改User实体

public class User private int id; private String username; private String password; private Date birthday; //代表当前用户具备哪些订单 private List< Order> orderList; //代表当前用户具备哪些角色 private List< Role> roleList; public class Role private int id; private String rolename;

添加UserMapper接口方法

List< User> findAllUserAndRole();

配置UserMapper.xml

< resultMap id="userRoleMap" type="com.zjq.domain.User"> < result column="id" property="id"> < /result> < result column="username" property="username"> < /result> < result column="password" property="password"> < /result> < result column="birthday" property="birthday"> < /result> < collection property="roleList" ofType="com.zjq.domain.Role"> < result column="rid" property="id"> < /result> < result column="rolename" property="rolename"> < /result> < /collection> < /resultMap> < select id="findAllUserAndRole" resultMap="userRoleMap"> select u.*,r.*,r.id rid from user u left join sys_user_role ur on u.id=ur.userid inner join sys_role r on ur.roleid=r.id; < /select>

测试结果

UserMapper mapper = sqlSession.getMapper(UserMapper.class); List< User> all = mapper.findAllUserAndRole(); for(User user : all) System.out.println(user.getUsername()); List< Role> roleList = user.getRoleList(); for(Role role : roleList) System.out.println(role); System.out.println("----------------------------------");

11:45:51,443 DEBUG findUserAndRoleAll:159 - ==> Preparing: SELECT * FROM USER u,sys_user_role ur,sys_role r WHERE u.id=ur.userId AND ur.roleId=r.id 11:45:51,572 DEBUG findUserAndRoleAll:159 - ==> Parameters: Userid=1, username=zjq, password=abc, birthday=Wed Jul 28 00:00:00 GMT+08:00 2021, roleList=[Roleid=1, roleName=CTO, roleDesc=CTO, Roleid=2, roleName=COO, roleDesc=COO] Userid=2, username=jack, password=123456, birthday=Fri Aug 20 00:00:00 GMT+08:00 2021, roleList=[Roleid=1, roleName=CTO, roleDesc=CTO, Roleid=2, roleName=COO, roleDesc=COO] 11:45:51,610 DEBUG findUserAndRoleAll:159 - < ==Total: 4 11:45:51,618 DEBUG JdbcTransaction:123 - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@6ec8211c] 11:45:51,619 DEBUG JdbcTransaction:91 - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@6ec8211c] 11:45:51,619 DEBUG PooledDataSource:363 - Returned connection 1858609436 to pool.

Mybatis的注解开发
MyBatis的常用注解这几年来注解开发越来越流行，Mybatis也可以使用注解开发方式，这样我们就可以减少编写Mapper
映射文件了。我们先围绕一些基本的CRUD来学习，再学习复杂映射多表操作。
@Insert：实现新增
@Update：实现更新
@Delete：实现删除
@Select：实现查询
@Result：实现结果集封装
@Results：可以与@Result一起使用，封装多个结果集
@One：实现一对一结果集封装
@Many：实现一对多结果集封装
MyBatis的增删改查我们完成简单的user表的增删改查的操作

private UserMapper userMapper; @Before public void before() throws IOException InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(true); userMapper = sqlSession.getMapper(UserMapper.class); @Test public void testAdd() User user = new User(); user.setUsername("测试数据"); user.setPassword("123"); user.setBirthday(new Date()); userMapper.add(user); @Test public void testUpdate() throws IOException User user = new User(); user.setId(16); user.setUsername("测试数据修改"); user.setPassword("abc"); user.setBirthday(new Date()); userMapper.update(user); @Test public void testDelete() throws IOException userMapper.delete(16); @Test public void testFindById() throws IOException User user = userMapper.findById(1); System.out.println(user); @Test public void testFindAll() throws IOException List< User> all = userMapper.findAll(); for(User user : all) System.out.println(user);

修改MyBatis的核心配置文件，我们使用了注解替代的映射文件，所以我们只需要加载使用了注解的Mapper接口即可

< mappers> < !--扫描使用注解的类--> < mapper class="com.zjq.mapper.UserMapper"> < /mapper> < /mappers>

或者指定扫描包含映射关系的接口所在的包也可以

< mappers> < !--扫描使用注解的类所在的包--> < package name="com.zjq.mapper"> < /package> < /mappers>

MyBatis的注解实现复杂映射开发实现复杂关系映射之前我们可以在映射文件中通过配置来实现，使用注解开发后，我们可以使用@Results注解，@Result注解，@One注解，@Many注解组合完成复杂关系的配置

文章图片


文章图片

一对一查询 一对一查询的模型用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户
一对一查询的需求：查询一个订单，与此同时查询出该订单所属的用户

文章图片

一对一查询的语句对应的sql语句：

select * from orders; select * from user where id=查询出订单的uid;

创建Order和User实体

public class Order private int id; private Date ordertime; private double total; //代表当前订单从属于哪一个客户 private User user; public class User private int id; private String username; private String password; private Date birthday;

创建OrderMapper接口

public interface OrderMapper List< Order> findAll();

使用注解配置Mapper

public interface OrderMapper @Select("select * from orders") @Results( @Result(id=true,property = "id",column = "id"), @Result(property = "ordertime",column = "ordertime"), @Result(property = "total",column = "total"), @Result(property = "user",column = "uid", javaType = User.class, one = @One(select = "com.zjq.mapper.UserMapper.findById")) ) List< Order> findAll();

public interface UserMapper @Select("select * from user where id=#id") User findById(int id);

测试结果

@Test public void testSelectOrderAndUser() List< Order> all = orderMapper.findAll(); for(Order order : all) System.out.println(order);

一对多查询 一对多查询的模型用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户
一对多查询的需求：查询一个用户，与此同时查询出该用户具有的订单

文章图片

一对多查询的语句对应的sql语句：

select * from user; select * from orders where uid=查询出用户的id;

修改User实体

public class Order private int id; private Date ordertime; private double total; //代表当前订单从属于哪一个客户 private User user; public class User private int id; private String username; private String password; private Date birthday; //代表当前用户具备哪些订单 private List< Order> orderList;

创建UserMapper接口

List< User> findAllUserAndOrder();

使用注解配置Mapper

public interface UserMapper @Select("select * from user") @Results( @Result(id = true,property = "id",column = "id"), @Result(property = "username",column = "username"), @Result(property = "password",column = "password"), @Result(property = "birthday",column = "birthday"), @Result(property = "orderList",column = "id", javaType = List.class, many = @Many(select = "com.zjq.mapper.OrderMapper.findByUid")) ) List< User> findAllUserAndOrder(); public interface OrderMapper @Select("select * from orders where uid=#uid") List< Order> findByUid(int uid);

测试结果

List< User> all = userMapper.findAllUserAndOrder(); for(User user : all) System.out.println(user.getUsername()); List< Order> orderList = user.getOrderList(); for(Order order : orderList) System.out.println(order); System.out.println("-----------------------------");

多对多查询 多对多查询的模型用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用
多对多查询的需求：查询用户同时查询出该用户的所有角色

文章图片

多对多查询的语句对应的sql语句：

select * from user; select * from role r,user_role ur where r.id=ur.role_id and ur.user_id=用户的id

创建Role实体，修改User实体

public class User private int id; private String username; private String password; private Date birthday; //代表当前用户具备哪些订单 private List< Order> orderList; //代表当前用户具备哪些角色 private List< Role> roleList; public class Role private int id; private String rolename;

添加UserMapper接口方法

List< User> findAllUserAndRole();

使用注解配置Mapper

public interface UserMapper @Select("select * from user") @Results( @Result(id = true,property = "id",column = "id"), @Result(property = "username",column = "username"), @Result(property = "password",column = "password"), @Result(property = "birthday",column = "birthday"), @Result(property = "roleList",column = "id", javaType = List.class, many = @Many(select = "com.zjq.mapper.RoleMapper.findByUid")) ) List< User> findAllUserAndRole(); public interface RoleMapper @Select("select * from role r,user_role ur where r.id=ur.role_id and ur.user_id=#uid") List< Role> findByUid(int uid);

测试结果

UserMapper mapper = sqlSession.getMapper(UserMapper.class); List< User> all = mapper.findAllUserAndRole(); for(User user : all) System.out.println(user.getUsername()); List< Role> roleList = user.getRoleList(); for(Role role : roleList) System.out.println(role); System.out.println("----------------------------------");

SSM框架整合
1.1 原始方式整合 1.准备工作
文章图片

2.创建Maven工程【MyBatis从入门到精通—MyBatis多表查询和注解开发】
文章图片

3.导入Maven坐标参考：素材/配置文件/pom.xml文件
4.编写实体类

public class Account private int id; private String name; private double money; //省略getter和setter方法

5.编写Mapper接口

public interface AccountMapper //保存账户数据 void save(Account account); //查询账户数据 List< Account> findAll();

6.编写Service接口

public interface AccountService void save(Account account); //保存账户数据 List< Account> findAll(); //查询账户数据

7.编写Service接口实现

@Service("accountService") public class AccountServiceImpl implements AccountService public void save(Account account) SqlSession sqlSession = MyBatisUtils.openSession(); AccountMapper accountMapper = sqlSession.getMapper(AccountMapper.class); accountMapper.save(account); sqlSession.commit(); sqlSession.close(); public List< Account> findAll() SqlSession sqlSession = MyBatisUtils.openSession(); AccountMapper accountMapper = sqlSession.getMapper(AccountMapper.class); return accountMapper.findAll();

8.编写Controller

@Controller public class AccountController @Autowired private AccountService accountService; @RequestMapping("/save") @ResponseBody public String save(Account account) accountService.save(account); return "save success"; @RequestMapping("/findAll") public ModelAndView findAll() ModelAndView modelAndView = new ModelAndView(); modelAndView.setViewName("accountList"); modelAndView.addObject("accountList",accountService.findAll()); return modelAndView;

9.编写添加页面

< %@ page contentType="text/html; charset=UTF-8" language="java" %> < html> < head> < title> Title< /title> < /head> < body> 保存账户信息表单 < form action="$pageContext.request.contextPath/save.action" method="post"> 用户名称< input type="text" name="name"> < br/> 账户金额< input type="text" name="money"> < br/> < input type="submit" value="https://www.songbingjia.com/android/保存"> < br/> < /form> < /body> < /html>

10.编写列表页面

< table border="1"> < tr> < th> 账户id< /th> < th> 账户名称< /th> < th> 账户金额< /th> < /tr> < c:forEach items="$accountList" var="account"> < tr> < td> $account.id< /td> < td> $account.name< /td> < td> $account.money< /td> < /tr> < /c:forEach> < /table>

11.编写相应配置文件(文件参考目录：素材/配置文件)?Spring配置文件：applicationContext.xml
?SprngMVC配置文件：spring-mvc.xml
?MyBatis映射文件：AccountMapper.xml
?MyBatis核心文件：sqlMapConfig.xml
?数据库连接信息文件：jdbc.properties
?Web.xml文件：web.xml
?日志文件：[log4j.xml](
12.测试添加账户
文章图片

13.测试账户列表
文章图片

1.2 Spring整合MyBatis 1.整合思路
文章图片

2.将SqlSessionFactory配置到Spring容器中

< !--加载jdbc.properties--> < context:property-placeholder location="classpath:jdbc.properties"/> < !--配置数据源--> < bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> < property name="driverClass" value="https://www.songbingjia.com/android/$jdbc.driver"/> < property name="jdbcUrl" value="https://www.songbingjia.com/android/$jdbc.url"/> < property name="user" value="https://www.songbingjia.com/android/$jdbc.username"/> < property name="password" value="https://www.songbingjia.com/android/$jdbc.password"/> < /bean> < !--配置MyBatis的SqlSessionFactory--> < bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> < property name="dataSource" ref="dataSource"/> < property name="configLocation" value="https://www.songbingjia.com/android/classpath:sqlMapConfig.xml"/> < /bean>

3.扫描Mapper，让Spring容器产生Mapper实现类

< !--配置Mapper扫描--> < bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> < property name="basePackage" value="https://www.songbingjia.com/android/com.zjq.mapper"/> < /bean>

4.配置声明式事务控制

< !--配置声明式事务控制--> < bean id="transacionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> < property name="dataSource" ref="dataSource"/> < /bean> < tx:advice id="txAdvice" transaction-manager="transacionManager"> < tx:attributes> < tx:method name="*"/> < /tx:attributes> < /tx:advice> < aop:config> < aop:pointcut id="txPointcut" expression="execution(* com.zjq.service.impl.*.*(..))"/> < aop:advisor advice-ref="txAdvice" pointcut-ref="txPointcut"/> < /aop:config>

5.修改Service实现类代码

@Service("accountService") public class AccountServiceImpl implements AccountService @Autowired private AccountMapper accountMapper; public void save(Account account) accountMapper.save(account); public List< Account> findAll() return accountMapper.findAll();

文章图片

推荐阅读

• 

  什么是职场？
• 

  百日练，一百天读一百本书，打卡第9天，《你只是看上去很努力》35分钟，理解50%，2000/分钟
• 

  空调主机跟遥控器显示温度不同步是怎么回事 新科柜机空调不制冷调节温度显示不同步
• 

  等闲识得东风面中的东风是什么意思 等闲识得东风面释义
• 

  redis 集合的底层实现 redis集合存bean
• 

  Java中关于Collections集合工具类的详细介绍
• 

  福州贵安水世界好玩吗 2023福州贵安水世界门票多少钱一张
• 

  蜗居背景音乐歌词蜗居背景音乐歌词翻译
• 

  甘肃所有大学名单 甘肃省大学排名
• 

  雷殿生名字打分99分 雷王姓取名
• 

  python|python 的生产者和消费者模式
• 

  quiet怎么读 quiet怎么造句
• 

  龙眼肉罐头做法 新鲜的龙眼肉怎么做罐头
• 

  海藻面膜敷多长时间好
• 

  mysql my.cnf配置模板参考
• 

  苹果扣费怎么退 iPhone扣费怎么退款
• 

  你认为孤独的根源是什么？
• 

  减肥期间,有哪些做起来简单又好吃的减肥食谱？
• 

  创维55寸4k智能电视多少钱一台 哪个电视牌子好
• 

  二甲双胍|糖尿病天天吃二甲双胍，请花5分钟弄清这6个注意事项，很重要

• 025.封装请求和返回参数
• 024.统一的后端返回值（CommonResp）
• 数据湖（十六）（Structured Streaming实时写入Iceberg）
• 023.电子书列表查询接口的开发
• clickhouse 20.x 三分片两副本部署与本地表的压力测试
• 005.Maven参数配置
• 问题 K: 最勇敢的机器人(并查集＋背包)
• Prime Distance(区间筛)
• 004.maven概念模型图