文章目录 Mybatis多表查询一对一查询一对一查询的模型一对一查询的语句创建Order和User实体创建OrderMapper接口配置OrderMapper.xml测试结果 一对多查询一对多查询的模型一对多查询的语句修改Us
用户表和订单表的关系为,一个用户有多个订单,一个订单只从属于一个用户
一对一查询的需求:查询一个订单,与此同时查询出该订单所属的用户
对应的sql语句:
SELECT *,o.id oid FROM orders o,USER u WHERE o.uid=u.id;
查询的结果如下:
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;}
public interface OrderMapper { List<Order> findAll();}
<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 *,o.id oid 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);}
输出:
用户表和订单表的关系为,一个用户有多个订单,一个订单只从属于一个用户
一对多查询的需求:查询一个用户,与此同时查询出该用户具有的订单
对应的sql语句:
select *,o.id oid from user u left join orders o on u.id=o.uid;
查询的结果如下:
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;}
public interface UserMapper { List<User> findAll();}
<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("----------------------------------");}
输出:
15:00:24,330 DEBUG findAll:159 - ==> Preparing: select u.id uid,u.username ,o.id oid,o.ordertime,o.total from user u left join orders o on u.id=o.uid 15:00:24,360 DEBUG findAll:159 - ==> Parameters: 15:00:24,393 DEBUG findAll:159 - <== Total: 5User{id=1, username='admin', password='null', birthday=null, roleList=null, orderList=[Order{id=1, ordertime=Sun Apr 16 14:23:50 CST 2023, total=2000.0, user=null}]}User{id=2, username='共饮一杯无666', password='null', birthday=null, roleList=null, orderList=[Order{id=2, ordertime=Sun Apr 16 14:23:50 CST 2023, total=8888.0, user=null}]}User{id=3, username='共饮一杯无', password='null', birthday=null, roleList=null, orderList=[Order{id=3, ordertime=Sun Apr 16 14:23:50 CST 2023, total=3699.0, user=null}]}User{id=4, username='共饮一杯无', password='null', birthday=null, roleList=null, orderList=[]}User{id=5, username='共饮一杯无', password='null', birthday=null, roleList=null, orderList=[]}
用户表和角色表的关系为,一个用户有多个角色,一个角色被多个用户使用
多对多查询的需求:查询用户同时查询出该用户的所有角色
对应的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;
查询的结果如下:
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;}
List<User> findAllUserAndRole();
<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: User{id=1, username='zjq', password='abc', birthday=Wed Jul 28 00:00:00 GMT+08:00 2021, roleList=[Role{id=1, roleName='CTO', roleDesc='CTO'}, Role{id=2, roleName='COO', roleDesc='COO'}]}User{id=2, username='jack', password='123456', birthday=Fri Aug 20 00:00:00 GMT+08:00 2021, roleList=[Role{id=1, roleName='CTO', roleDesc='CTO'}, Role{id=2, roleName='COO', roleDesc='COO'}]}11:45:51,610 DEBUG findUserAndRoleAll:159 - <== Total: 411: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也可以使用注解开发方式,这样我们就可以减少编写Mapper
映射文件了。我们先围绕一些基本的CRUD来学习,再学习复杂映射多表操作。
@Insert
:实现新增
@Update
:实现更新
@Delete
:实现删除
@Select
:实现查询
@Result
:实现结果集封装
@Results
:可以与@Result 一起使用,封装多个结果集
@One
:实现一对一结果集封装
@Many
:实现一对多结果集封装
我们完成简单的user表的增删改查的操作
private UserMapper userMapper;@Beforepublic 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);}@Testpublic void testAdd() { User user = new User(); user.setUsername("测试数据"); user.setPassword("123"); user.setBirthday(new Date()); userMapper.add(user);}@Testpublic void testUpdate() throws IOException { User user = new User(); user.setId(16); user.setUsername("测试数据修改"); user.setPassword("abc"); user.setBirthday(new Date()); userMapper.update(user);}@Testpublic void testDelete() throws IOException { userMapper.delete(16);}@Testpublic void testFindById() throws IOException { User user = userMapper.findById(1); System.out.println(user);}@Testpublic 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>
实现复杂关系映射之前我们可以在映射文件中通过配置来实现,使用注解开发后,我们可以使用@Results注解,@Result注解,@One注解,@Many注解组合完成复杂关系的配置
用户表和订单表的关系为,一个用户有多个订单,一个订单只从属于一个用户
一对一查询的需求:查询一个订单,与此同时查询出该订单所属的用户
对应的sql语句:
select * from orders;select * from user where id=查询出订单的uid;
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;}
public interface OrderMapper { List<Order> findAll();}
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); }
@Testpublic 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;
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;}
List<User> findAllUserAndOrder();
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
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;}
List<User> findAllUserAndRole();
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("----------------------------------");}
本文内容到此结束了,
如有收获欢迎点赞👍收藏💖关注✔️,您的鼓励是我最大的动力。
如有错误❌疑问💬欢迎各位指出。
主页:共饮一杯无的博客汇总👨💻保持热爱,奔赴下一场山海。🏃🏃🏃
来源地址:https://blog.csdn.net/qq_35427589/article/details/130464966
--结束END--
本文标题: MyBatis多表查询和注解开发
本文链接: https://www.lsjlt.com/news/375077.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-04-03
2024-04-03
2024-04-01
2024-01-21
2024-01-21
2024-01-21
2024-01-21
2023-12-23
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0