多对一
1.环境配置
Orders.java添加属性User
package com.biem.pojo;
import lombok.*;
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Orders {
private Integer id;
private String number;
private String userId;
private Users users;
}
嵌套查询方式
UsersMapper.java 添加
package com.biem.mapper;
import com.biem.pojo.Users;
public interface UsersMapper {
public Users findUserById(int id);
public Users findUserByIdWithNestedResults(Integer id);
public Users getUsersById(Integer id);
}
OrdersMapper.java 添加
package com.biem.mapper;
import com.biem.pojo.Orders;
import java.util.List;
public interface OrdersMapper {
public List<Orders> selectOrders(Integer uid);
public List<Orders> getOrders1(Integer userId);
}
UsersMappers.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">
<resultMap id="usersWithOrders1" type="Users">
<id property="id" column="id"></id>
<result property="username" column="username"/>
<result property="address" column="address"/>
<collection property="ordersList" javaType="ArrayList" ofType="orders" select="com.biem.mapper.OrdersMapper.selectOrders" column="id"/>
</resultMap>
<select id="findUserById" parameterType="Integer" resultMap="usersWithOrders1">
select * from tb_user where id = #{id}
</select>
<select id="findUserByIdWithNestedResults" parameterType="Integer" resultMap="UsersWithOrdersResult" >
select u.*, o.id as orders_id , o.number, o.user_id
from tb_user u, tb_orders o
where u.id = o.user_id and u.id=#{id}
</select>
<resultMap id="UsersWithOrdersResult" type="Users">
<id property="id" column="id"></id>
<result property="username" column="username"/>
<result property="address" column="address"/>
<collection property="ordersList" ofType="Orders">
<id property="id" column="orders_id"></id>
<result property="number" column="number"/>
<result property="userId" column="user_id"/>
</collection>
</resultMap>
<select id="getUsersById" resultType="users">
select * from tb_user where `id` = #{id}
</select>
</mapper>
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">
<select id="selectOrders" parameterType="Integer" resultType="Orders">
select * from tb_orders where user_id=#{userId}
</select>
<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"/>
<association column="id" property="users" javaType="users" select="com.biem.mapper.UsersMapper.getUsersById"/>
</resultMap>
</mapper>
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 testGetOrders1(){
SqlSession session = MyBatisUtil.openSession();
OrdersMapper mapper = session.getMapper(OrdersMapper.class);
List<Orders> ordersList = mapper.getOrders1(1);
System.out.println("ordersList = " + ordersList);
}
}
测试结果
嵌套结果方式
OrderMapper.java 添加
package com.biem.mapper;
import com.biem.pojo.Orders;
import java.util.List;
public interface OrdersMapper {
public List<Orders> selectOrders(Integer uid);
public List<Orders> getOrders1(Integer userId);
public List<Orders> getOrders1ByNestedResult(Integer userId);
}
OrderMapper.xml添加
<!-- public List<Orders> getOrders1ByNestedResult(Integer userId); -->
<select id="getOrders1ByNestedResult" resultMap="ordersResult2">
<!-- id为接口中实现的方法名,resultMap为返回值类型,由于返回值包含一个Teacher对象,所以不能用resultType-->
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>
<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>
OrderTest.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 testGetOrders1(){
SqlSession session = MyBatisUtil.openSession();
OrdersMapper mapper = session.getMapper(OrdersMapper.class);
List<Orders> ordersList = mapper.getOrders1(1);
System.out.println("ordersList = " + ordersList);
}
@Test
public void testGetOrders2(){
SqlSession session = MyBatisUtil.openSession();
OrdersMapper mapper = session.getMapper(OrdersMapper.class);
List<Orders> ordersList = mapper.getOrders1ByNestedResult(1);
System.out.println("ordersList = " + ordersList);
}
}
测试结果