mysql复杂查询语句⽤例_Mybatis复杂查询（SQL配置+注解）⽂章内容输出来源：拉勾教育Java⾼薪训练营前⾔：我们在针对单表查询的时候，⾮常简单，但是如果是两个数据库表，三个数据表连接的话要怎么处理呢
1、基本环境准备
UTF-8
UTF-8
1.8
1.8
1.8
mybatis
3.4.5
mysql
mysql-connector-java
5.1.6
runtime
junit
junit
4.12
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ordertime` varchar(255) DEFAULT NULL,
`total` double DEFAULT NULL,
`uid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ------------------------------ Records of orders-- ----------------------------INSERT INTO `orders` VALUES ('1', '2019-12-12',
'3000', '1');
INSERT INTO `orders` VALUES ('2', '2019-12-12', '4000', '1');
INSERT INTO `orders` VALUES ('3', '2019-12-12', '5000', '2');
-- ------------------------------ Table structure for sys_role-- ----------------------------DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`rolename` varchar(255) DEFAULT NULL,
`roleDesc` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
android开发心得体会) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ------------------------------ Records of sys_role-- ----------------------------INSERT INTO `sys_role` VALUES ('1', 'CTO', 'CTO');
INSERT INTO `sys_role` VALUES ('2', 'CEO', 'CEO');
-- ------------------------------ Table structure for sys_user_role-- ----------------------------DROP TABLE IF EXISTS `sys_user_role`;
CREATE TABLE `sys_user_role` (
`userid` int(11) NOT NULL,
`roleid` int(11) NOT NULL,
PRIMARY KEY (`userid`,`roleid`),
KEY `roleid` (`roleid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ------------------------------ Records of sys_user_role-- ----------------------------INSERT INTO `sys_user_role` VALUES ('1', '1');
INSERT INTO `sys_user_role` VALUES ('2', '1');
INSERT INTO `sys_user_role` VALUES ('1', '2');
INSERT INTO `sys_user_role` VALUES ('2', '2');
-- ------------------------------ Table structure for user-- ----------------------------DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`password` varchar(50) DEFAULT NULL,
`birthday` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ------------------------------ Records of user-- ----------------------------INSERT INTO `user` VALUES ('1', 'lucy', '123', '2019-12-12');
INSERT INTO `user` VALUES ('2', 'tom','123', '2019-12-12');
2、使⽤xml配置⽂件
2.1 【SQL配置 ⼀对⼀查询】
业务场景: 订单表，⽤户表，⼀个订单只属于⼀个⽤户
需求：查询⼀个订单，与此同时查询出该订单所属的⽤户信息 订单表【orders】：
⽤户表【user】：
需求的查询语句为：
select * from orders o,user u where o.uid=u.id;
结果为：
可以看到，sql语句查询的结果是两张表关联的结果，我们在mybatis应该如何查询呢？
(2.1.1) 创建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;
}
(2.1.2) 创建OerderMapper接⼝
public interface IOrderMapper {
public List findOrderAndUser();
}
(2.1.3) 配置l 【⽅式⼀ 使⽤association】
-////DTD Mapper 3.0//EN""/dtd/mybatis-3-mapper.dtd"> select * from orders o,user u where o.uid=u.id
(2.1.4) 配置l 【⽅式⼆ 使⽤对象.字段名】
-////DTD Mapper 3.0//EN""/dtd/mybatis-3-mapper.dtd"> select * from orders o,user u where o.uid=u.id
(2.1.5) 新建⼀个测试类
public class MyBatisTest {
@Test
public void test001() throws IOException {
InputStream inputStream = ResourceAsStream("l"); SqlSessionFactory build = new SqlSessionFactoryBuilder().build(inputStream); SqlSession sqlSession = build.openSession();
IOrderMapper mapper = Mapper(IOrderMapper.class);
List orderAndUser = mapper.findOrderAndUser();
for (Order order : orderAndUser) {
System.out.println(order);
伦勃朗老妇人的肖像android培训讲师简历}
}
}
运⾏的结果为：
整体的结构图如下所⽰：⼀对⼀项⽬⽬录结构
2.2 【SQL配置 ⼀对多查询】
业务场景：订单表，⽤户表，⼀个⽤户有多个订单
mysql语句多表查询需求：查询所有⽤户，并且查询出⽤户的所属订单
需求的查询语句为：
select *,o.id oid from user u left join orders o on u.id=o.uid;
-- 使⽤左外连接查⽤户的订单数据-- 可以看到mary有2条订单信息-- 李四有1⼀条订单信息(2.2.1) 修改实体User,Order
public class User {
private Integer id;
private String username;
//⽤户拥有多个Order private List orderList;
}
discuz论坛纯文字模板public class Order {
private Integer id;
private String orderTime;
private Double total;
private User user;
}
(2.2.2) 创建IUserMapper接⼝
public interface IUserMapper{
public List findAll();
}
(2.2.3) 创建l
-
////DTD Mapper 3.0//EN""/dtd/mybatis-3-mapper.dtd">
select *,o.id oid from user u left join orders o on u.id=o.uid
(2.2.4) 创建测试类
@Test
public void test002() throws IOException {
InputStream inputStream = ResourceAsStream("l"); SqlSessionFactory build = new SqlSessionFactoryBuilder().build(inputStream); SqlSession sqlSession = build.openSession();
IUserMapper mapper = Mapper(IUserMapper.class);
List all = mapper.findAll();
for (User user : all) {
System.out.println(user);
}
}
(2.2.5) 测试结果：
2.3 【SQL配置 多对多查询】
业务场景：⼀个⽤户有多个⾓⾊，⼀个⾓⾊属于多个⽤户
需求：查询所有⽤户，以及⽤户对应的⾓⾊列表
⾓⾊表【sys_role】
⽤户⾓⾊表【sys_user_role】
(2.3.1) 修改⽤户实体：
public class User {
private Integer id;
jscript script文件可以删除吗private String username;
private List roleList;
}
public class Role {
private Integer id;
private String roleName;
private String roleDesc;
}
(2.3.2) 编写IUserMapper接⼝
public interface IUserMapper{
public List findUserAndRoles();
}
(2.3.3) 编写l

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。