准备工作:mybatis的环境搭建
一、多对一查询
解决user_Id为某个值的所有orders的信息
对于Users–>Orders 1对多
对于Orders->Users 多对1
1.数据库准备
USE mybatis;
# 创建一个名称为tb_user的表
CREATE TABLE tb_user (
id int(32) PRIMARY KEY AUTO_INCREMENT,
username varchar(32),
address varchar(256)
);
# 插入3条数据
INSERT INTO tb_user VALUES ('1', '小明', '北京');
INSERT INTO tb_user VALUES ('2', '李华', '上海');
INSERT INTO tb_user VALUES ('3', '李刚', '上海');
# 创建一个名称为tb_orders的表
CREATE TABLE tb_orders (
id int(32) PRIMARY KEY AUTO_INCREMENT,
number varchar(32) NOT NULL,
user_id int(32) NOT NULL,
FOREIGN KEY(user_id) REFERENCES tb_user(id)
);
# 插入3条数据
INSERT INTO tb_orders VALUES ('1', '1000011', '1');
INSERT INTO tb_orders VALUES ('2', '1000012', '1');
INSERT INTO tb_orders VALUES ('3', '1000013', '2');
2.实体类
2.1 Orders.java
package com.biem.pojo;
import lombok.*;
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Orders {
private Integer id;
private String number;
private Integer userId;
}
2.2 Users.java
package com.biem.pojo;
import lombok.*;
import java.util.List;
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Users {
private Integer id;
private String username;
private String address;
private List<Orders> ordersList;
}
3.用户配置接口类
3.1 OrdersMapper.java
package com.biem.mapper;
public interface OrdersMapper {
}
3.2 UsersMapper.java
package com.biem.mapper;
public interface UsersMapper {
}
4. 用户配置文件
4.1 OrdersMapper.xml
<?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.biem.mapper.OrdersMapper">
</mapper>
4.2 UsersMapper.xml
<?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.biem.mapper.UsersMapper">
</mapper>
5.解决方法
5.1嵌套查询方式
5.1.1 接口类写入方法
OrdersMapper.java
package com.biem.mapper;
public interface OrdersMapper {
public List<Orders> getOrders1(Integer userId);
}
UsersMapper.java
package com.biem.mapper;
import com.biem.pojo.Users;
public interface UsersMapper {
public Users getUsersById(Integer id);
}
5.1.2 映射文件写入sql语句
OrdersMapper.xml
<!-- public List<Orders> getOrders1(Integer userId);-->
<select id="getOrders1" resultMap="ordersResult1">
select * from tb_orders where user_id = #{userId}
</select>
<resultMap id="ordersResult1" type="orders">
<result column="id" property="id"/>
<result column="number" property="number"/>
<result column="user_id" property="userId"/>
<!--复杂的属性,我们需要单独处理
对象:使用association
集合:collection-->
<association column="id" property="users" javaType="users" select="com.biem.mapper.UsersMapper.getUsersById"/>
</resultMap>
UsersMapper.xml
<!-- public Users getUsersById(Integer id);-->
<select id="getUsersById" resultType="users">
select * from tb_user where id=#{id}
</select>
5.1.3 测试类 OrdersTest.java
package com.biem.test;
import com.biem.mapper.OrdersMapper;
import com.biem.pojo.Orders;
import com.biem.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class OrdersTest {
@Test
public void getOrders1(){
SqlSession session = MybatisUtil.openSession();
OrdersMapper mapper =session.getMapper(OrdersMapper.class);
List<Orders> ordersList = mapper.getOrders1(1);
System.out.println("ordersList = " + ordersList);
session.close();
}
}
5.1.4测试结果
5.2嵌套结果方式
5.2.1 接口类写入方法 OrdersMapper.java
public List<Orders> getOrders1ByNestedResult(Integer userId);
5.2.2 映射文件写入sql语句 OrdersMapper.xml
<!-- public List<Orders> getOrders1ByNestedResult(Integer userId);-->
<resultMap id="ordersResult2" type="orders">
<result column="oid" property="id"/>
<result column="number" property="number"/>
<result column="uid" property="userId"/>
<association property="users" javaType="users">
<!--property是实体类中的字段属性,复杂的属性,使用了javaType描述的是属性的类型,它是一个Teacher对象-->
<result property="id" column="uid"/>
<result property="username" column="username"/>
<result property="address" column="address"/>
</association>
</resultMap>
<select id="getOrders1ByNestedResult" parameterType="Integer" resultMap="ordersResult2" >
select o.id oid, o.number number, o.user_id uid, u.username username, u.address address
from tb_orders o,tb_user u
where o.user_id=u.id and o.user_id =#{userId}
</select>
5.2.3 测试类 OrdersTest.java
@Test
public void getOrders1ByNestedResult(){
SqlSession session = MybatisUtil.openSession();
OrdersMapper mapper =session.getMapper(OrdersMapper.class);
List<Orders> ordersList = mapper.getOrders1ByNestedResult(1);
System.out.println("ordersList = " + ordersList);
session.close();
}
5.2.4测试结果
二、多对多查询
1.数据库准备
USE mybatis;
# 创建一个名称为tb_product的表
CREATE TABLE tb_product (
id INT(32) PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32),
price DOUBLE
);
# 插入3条数据
INSERT INTO tb_product VALUES ('1', 'Java基础入门', '44.5');
INSERT INTO tb_product VALUES ('2', 'Java Web程序开发入门', '38.5');
INSERT INTO tb_product VALUES ('3', 'SSM框架整合实战', '50');
# 创建一个名称为tb_ordersitem 的中间表
CREATE TABLE tb_ordersitem (
id INT(32) PRIMARY KEY AUTO_INCREMENT,
orders_id INT(32),
product_id INT(32),
FOREIGN KEY(orders_id) REFERENCES tb_orders(id),
FOREIGN KEY(product_id) REFERENCES tb_product(id)
);
# 插入3条数据
INSERT INTO tb_ordersitem VALUES ('1', '1', '1');
INSERT INTO tb_ordersitem VALUES ('2', '1', '3');
INSERT INTO tb_ordersitem VALUES ('3', '3', '3');
# 订单表tb_orders由于在上面已经创建,所以这里不用再次创建
2.实体类
2.1 Orders.java
package com.biem.pojo;
import lombok.*;
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Orders {
private Integer id;
private String number;
private Integer userId;
private Users users;
private List<Product> productList;
}
2.2 Product.java
package com.biem.pojo;
import lombok.*;
import java.util.List;
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Product {
private Integer id;
private String name;
private Double price;
private List<Orders> ordersList;
}
3.用户配置接口类
3.1 OrdersMapper.java
package com.biem.mapper;
public interface OrdersMapper {
}
3.2 ProductMapper.java
package com.biem.mapper;
public interface ProductMapper {
}
4. 用户配置文件
4.1 ProductMapper.xml
<?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.biem.mapper.ProductMapper">
</mapper>
4.2 UsersMapper.xml
<?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.biem.mapper.UsersMapper">
</mapper>
5.解决方法
5.1嵌套查询方式
5.1.1 接口类写入方法
OrdersMapper.java
public List<Orders> findOrdersWithProduct(Integer id);
ProductMapper.java
public List<Product> findProductById(Integer id);
5.1.2 映射文件写入sql语句
OrdersMapper.xml
<!-- public List<Orders> findOrdersWithProduct(Integer id);-->
<select id="findOrdersWithProduct" parameterType="Integer" resultMap="OrdersWithProductResult">
select * from tb_orders where id = #{id}
</select>
<resultMap id="OrdersWithProductResult" type="Orders">
<id property="id" column="id"></id>
<result property="number" column="number"></result>
<collection property="productList" column="id" ofType="Product" select="com.biem.mapper.ProductMapper.findProductById">
</collection>
</resultMap>
OrdersMapper.xml
<!--public List<Product> findProductById(Integer id);-->
<select id="findProductById" parameterType="Integer" resultType="Product">
select * from tb_product where id in(
select product_id from tb_ordersitem where orders_id = #{id}
)
</select>
5.1.3 测试类 OrdersTest.java
@Test
public void testFindOrdersWithProduct(){
SqlSession session = MybatisUtil.openSession();
OrdersMapper mapper = session.getMapper(OrdersMapper.class);
List<Orders> ordersList = mapper.findOrdersWithProduct(1);
System.out.println("ordersList = " + ordersList);
}
5.1.4测试结果
5.2嵌套结果方式
5.2.1 接口类写入方法 OrdersMapper.java
public List<Orders> findOrdersWithProductByNestedResult(Integer id);
5.2.2 映射文件写入sql语句 OrdersMapper.xml
<!-- public List<Orders> findOrdersWithProductByNestedResult(Integer id); -->
<select id="findOrdersWithProductByNestedResult" parameterType="Integer" resultMap="OrdersWithProductResult2">
select o.*, p.id as pid, p.name, p.price
from tb_orders o, tb_product p, tb_ordersitem oi
where oi.orders_id=o.id and oi.product_id=p.id and o.id=#{id}
</select>
<resultMap id="OrdersWithProductResult2" type="Orders">
<id property="id" column="id"></id>
<result property="number" column="number"></result>
<collection property="productList" ofType="product">
<id property="id" column="pid"/>
<result property="name" column="name"/>
<result property="price" column="price"/>
</collection>
</resultMap>
5.2.3 测试类 OrdersTest.java
@Test
public void findOrdersWithProductByNestedResult(){
SqlSession session = MybatisUtil.openSession();
OrdersMapper mapper = session.getMapper(OrdersMapper.class);
List<Orders> ordersList = mapper.findOrdersWithProductByNestedResult(1);
System.out.println("ordersList = " + ordersList);
}