Mapper课01

1.POJO对象类

1.1 User

private int id;
private String username; // 用户姓名
private String sex;      // 性别
private Date birthday;   // 生日
private String address;  // 地址

//用户创建的订单列表
private List<Orders> ordersList;

1.2 Orders

private Integer id;
private Integer userId;
private String number;
private Date createtime;
private String note;
    
//用户信息
private User user;
    
//订单明细
private List<Orderdetail> orderdetails;

1.3 Orderdetail

private Integer id;
private Integer ordersId;
private Integer itemsId;
private Integer itemsNum;
    
//明细对应的商品信息
private Items items;  // 不用生成getter&setter方法

1.4 Items

private Integer id;
private String name;
private Float price;
private String pic;
private Date createtime;
private String detail;

2.Mapper.xml文件

模块设计

一对多查询

<!-- 订单及订单明细 -->
	<resultMap type="com.app.mybatis.po.Orders" id="OrdersAndOrderDetailResultMap" extends="OrdersUserResultMap">
		<id column="id" property="id"/>
		<result column="user_id" property="userId"/>
		<result column="number" property="number"/>
		<result column="createtime" property="createtime"/>
		<result column="note" property="note"/>
		
		<association property="user" javaType="com.app.mybatis.po.User">
			<id column="user_id" property="id"/>
			<result column="username" property="username"/>
			<result column="sex" property="sex"/>
			<result column="address" property="address"/>
		</association>
		
		<collection property="orderdetails" ofType="com.app.mybatis.po.Orderdetail">
			<id column="orderdetail_id" property="id" />
			<result column="items_id" property="itemsId"/>
		 	<result column="items_num" property="itemsNum"/>
		 	<result column="orders_id" property="ordersId"/>
		</collection>
	</resultMap>

<!-- 订单及订单明细 -->
	<select id="findOrdersAndOrderDetailResultMap" resultMap="OrdersAndOrderDetailResultMap">
		SELECT 
		  orders.*,
		  USER.username,
		  USER.sex,
		  USER.address,
		  orderdetail.id orderdetail_id,
		  orderdetail.items_id,
		  orderdetail.items_num,
		  orderdetail.orders_id
		FROM
		  orders,
		  USER,
		  orderdetail
		WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id
	</select>

多对多查询

<!-- 查询用户及购买的商品 -->
	<resultMap type="com.app.mybatis.po.User" id="UserAndItemsResultMap">
		<!-- 用户信息 -->
		<id column="user_id" property="id" />
		<result column="sex" property="sex" />
		<result column="address" property="address" />
		
		<!-- 订单信息 -->
		<collection property="ordersList" ofType="com.app.mybatis.po.Orders">
			<id column="id" property="id" />
			<result column="user_id" property="userId" />
			<result column="number" property="number" />
			<result column="createtime" property="createtime" />
			<result column="note" property="note" />
			
			<!-- 订单明细 -->
			<collection property="orderdetails" ofType="com.app.mybatis.po.Orderdetail">
				<id column="orderdetail_id" property="id" />
				<result column="items_id" property="itemsId" />
				<result column="items_num" property="itemsNum" />
				<result column="orders_id" property="ordersId" />
				
				<!-- 商品信息 -->
				<association property="items" javaType="com.app.mybatis.po.Items">
					<id column="items_id" property="id" />
					<result column="items_name" property="name" />
					<result column="items_detail" property="detail" />
					<result column="items_price" property="price" />
				</association>
			</collection>
		</collection>
		
	</resultMap>

<!-- 查询用户及购买的商品 -->
	<select id="findUserAndItemsResultMap" resultMap="UserAndItemsResultMap">
		SELECT
		    ORDERS.*,
		    USER.username,
		    USER.sex,
		    USER.address,
		    ORDERDETAIL.id orderdetail_id,
		    ORDERDETAIL.items_id,
		    ORDERDETAIL.items_num,
		    ORDERDETAIL.orders_id,
		    ITEMS.name items_name,
		    ITEMS.detail items_detail,
		    ITEMS.price items_price
		FROM
		    ORDERS,
		    USER,
		    ORDERDETAIL,
		    ITEMS
		WHERE
		     ORDERS.user_id = user.id AND ORDERDETAIL.orders_id = orders.id
	</select>

延迟加载

<!-- 延迟加载resultMap -->
	<resultMap type="com.app.mybatis.po.Orders" id="OrdersUserLazyLoadingResultMap">
		<id column="id" property="id" />
		<result column="user_id" property="userId" />
		<result column="number" property="number" />
		<result column="createtime" property="createtime" />
		<result column="note" property="note" />
		<!-- 实现对用户信息进行延迟加载 -->
		<association property="user" javaType="com.app.mybatis.po.User" 
		select="com.app.mybatis.mapper.UserMapper.findUserById" column="user_id">
			
		</association>
		
	</resultMap>
	
	<!-- 查询订单关联查询用户，用户信息需要延迟加载 -->
	<select id="findOrdersUserLazyLoading" resultMap="OrdersUserLazyLoadingResultMap">
		SELECT * FROM ORDERS
	</select>

SqlMapConfig.xml中配置

<span style="white-space:pre">	</span><settings>
		<setting name="lazyLoadingEnabled" value="true" />
		<setting name="aggressiveLazyLoading" value="false" />
	</settings>

3.Mapper.java接口类

public interface OrdersMapperCustom

// 查询订单（关联用户）及订单明细
public List<Orders> findOrdersAndOrderDetailResultMap() throws Exception;

// 查询用户及购买的商品
public List<User> findUserAndItemsResultMap() throws Exception;

// 延迟加载
public List<Orders> findOrdersUserLazyLoading() throws Exception;

4.实现接口类--单元测试

@Test
	public void testFindOrdersAndOrderDetailResultMap() throws Exception {

		SqlSession sqlSession = sqlSessionFactory.openSession();
		// 创建代理对象
		OrdersMapperCustom ordersMapperCustom = sqlSession
				.getMapper(OrdersMapperCustom.class);

		// 调用maper的方法
		List<Orders> list = ordersMapperCustom.findOrdersAndOrderDetailResultMap();

		System.out.println(list);

		sqlSession.close();
	}

查询用户及购买的商品

List<User> list = ordersMapperCustom.findUserAndItemsResultMap();

延迟加载

	// 延迟加载
	@Test
	public void testFindOrdersUserLazyLoading() throws Exception {

		SqlSession sqlSession = sqlSessionFactory.openSession();
		// 创建代理对象
		OrdersMapperCustom ordersMapperCustom = sqlSession
				.getMapper(OrdersMapperCustom.class);

		// 调用maper的方法
		List<Orders> list = ordersMapperCustom.findOrdersUserLazyLoading();

		for (Orders orders:list) {
			User user = orders.getUser();
			System.out.println(user);
		}
	
	}

一级缓存测试

	// 一级缓存测试
	@Test
	public void testCache1() throws Exception{
		SqlSession sqlSession = sqlSessionFactory.openSession();
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		
		User user1 = userMapper.findUserById(1);
		System.out.println(user1);
		
/*		user1.setUsername("测试用户2");
		userMapper.updateUser(user1);
		sqlSession.commit();
		*/
		User user2 = userMapper.findUserById(1);
		System.out.println(user2);
		
		sqlSession.close();
	}

5

6