一对一延迟查询

• 针对的是关联对象， 用户和订单从面相对象的角度来说就是关联对象，当只需要订单数据，尚不需要用户数据的时候，就不应该去查询用户表，啥时候用到用户数据，啥时候查询

原Mybatis查询

pojo

User.java

package com.ahu.pojo;import java.util.Date;
import java.util.List;/*** 一个用户对应多个订单*/
public class User {private int id;private String username;private String sex;private Date birthday;private String address;private List<Orders> ordersList;public int getId() {return id;}public void setId(int id) {this.id = id;}public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public Date getBirthday() {return birthday;}public void setBirthday(Date birthday) {this.birthday = birthday;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}public List<Orders> getOrdersList() {return ordersList;}public void setOrdersList(List<Orders> ordersList) {this.ordersList = ordersList;}@Overridepublic String toString() {return "User{" +"id=" + id +", username='" + username + '\'' +", sex='" + sex + '\'' +", birthday=" + birthday +", address='" + address + '\'' +", ordersList=" + ordersList +'}';}
}

Orders.java

package com.ahu.pojo;import java.util.Date;public class Orders {private Integer id;private Integer userId;private String number;private Date createtime;private String note;private User user;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public Integer getUserId() {return userId;}public void setUserId(Integer userId) {this.userId = userId;}public String getNumber() {return number;}public void setNumber(String number) {this.number = number == null ? null : number.trim();}public Date getCreatetime() {return createtime;}public void setCreatetime(Date createtime) {this.createtime = createtime;}public String getNote() {return note;}public void setNote(String note) {this.note = note == null ? null : note.trim();}public User getUser() {return user;}public void setUser(User user) {this.user = user;}@Overridepublic String toString() {return "Orders{" +"id=" + id +", userId=" + userId +", number='" + number + '\'' +", createtime=" + createtime +", note='" + note + '\'' +", user=" + user +'}';}
}

配置文件

OrdersMapper.java

package com.ahu.mapper;import com.ahu.pojo.Orders;import java.util.List;public interface OrdersMapper {/***一对一查询,订单为基准查询用户* @return*/List<Orders> queryOrdersUser();
}

OrdersMapper.xml

<?xml version="1.0" encoding="utf-8" ?><!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><!--实现延迟加载，拆分SQL语句-->
<mapper namespace="com.ahu.mapper.OrdersMapper"><!--一对一查询,基准是订单表resultMap中写了返回类型，这里没必要写resultType了--><select id="queryOrdersUser" resultMap="queryOrdersUserResultMap">select u.id uid,u.username,u.sex,u.birthday,u.address,o.id,o.user_id,o.number,o.createtime,o.notefrom orders o left outer join user u on u.id = o.user_id;</select><!--手动配置映射,查出来的数据表的列和pojo属性相对应,对应返回类型或者返回函数的泛型--><resultMap id="queryOrdersUserResultMap" type="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"/><!--配置一对一？配置user属性的值association配置对应关系property 属性: 和orders对象的哪个属性关联javaType 属性: 属性数据类型 可以小写?无所谓大小写--><association property="user" javaType="User"><id column="uid" property="id"/><result column="username" property="username"/><result column="sex" property="sex"/><result column="birthday" property="birthday"/><result column="address" property="address"/></association></resultMap>
</mapper>

主配置文件SqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configurationPUBLIC "-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd">
<!--约束文件，位置在mybatis的jar包-->
<configuration><settings><!-- 开启延迟加载--><setting name="lazyLoadingEnabled" value="true" /><!-- 关闭立即加载--><setting name="aggressiveLazyLoading" value="false" /><!-- 设定tostring等方法延迟加载--><setting name="lazyLoadTriggerMethods" value="true" /></settings><!--    别名配置--><typeAliases>
<!--        配置别名，数据类型是pojo对象，简化为user-->
<!--        <typeAlias type="com.ahu.pojo.User" alias="user"></typeAlias>--><!--        定义别名也可以使用包扫描，在xml文件中直接使用类名小写即可--><package name="com.ahu.pojo"/></typeAliases><environments default="development"><environment id="development"><!--transactionManager 事务管理配置type="JDBC"  使用的是最原始的JDBC的事务处理机制type="MANAGERED" 不管理事务--><transactionManager type="JDBC" /><!--dataSource 配置数据源，连接池type="POOLED" 使用连接池MyBatis自带连接池  （type=""UNPOOLED）--><dataSource type="POOLED">
<!--                使用自带的数据库连接池--><!--配置的是，数据库连接四大信息--><property name="driver" value="com.mysql.jdbc.Driver" /><property name="url" value="jdbc:mysql://127.0.0.1:3305/mybatis?characterEncoding=utf8" /><property name="username" value="root" /><property name="password" value="ahulml005" /></dataSource></environment></environments><!--配置的是映射关系  ORM Java对象和数据表--><mappers><!--配置文件不在同一个文件下，填写地址应该注意-->
<!--        <mapper resource="com/ahu/mapper/UserMapper.xml" />--><!--可以使用自动扫描配置,自动扫描xml文件一个数据表对应一个xmlmapper标签的属性 resource="xml路径"package标签的属性 name="包名"--><package name="com.ahu.mapper"/></mappers>
</configuration>    

业务代码

package com.ahu.test;import com.ahu.mapper.OrdersMapper;
import com.ahu.pojo.Orders;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;import java.io.IOException;
import java.io.InputStream;
import java.util.List;public class MainTest {private SqlSessionFactory sqlSessionFactory;@Beforepublic void before() throws IOException {InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();sqlSessionFactory = sqlSessionFactoryBuilder.build(is);}/*** SQ查询了用户和订单信息，但是运行时期只需要订单数据* 没有用户数据，对用户数据的查询就是浪费资源* 延迟查询：需要的时候就查，不需要就不查** 方法：拆分* SQL语句*/@Testpublic void testQueryOrdersUser(){SqlSession sqlSession = sqlSessionFactory.openSession();OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);List<Orders> orders = ordersMapper.queryOrdersUser();for (Orders order : orders) {System.out.println(order);}sqlSession.close();}
}

一对一延迟查询

修改OrdersMapper.xml配置

 * SQ查询了用户和订单信息，但是运行时期只需要订单数据* 没有用户数据，对用户数据的查询就是浪费资源* 延迟查询：需要的时候就查，不需要就不查* 方法：拆分SQL语句

<?xml version="1.0" encoding="utf-8" ?><!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><!--实现延迟加载，拆分SQL语句-->
<mapper namespace="com.ahu.mapper.OrdersMapper"><!--一对一查询,基准是订单表resultMap中写了返回类型，这里没必要写resultType了拆分sqlselect u.id uid,u.username,u.sex,u.birthday,u.address,o.id,o.user_id,o.number,o.createtime,o.notefrom orders o left outer join user u on u.id = o.user_id;--><select id="queryOrdersUser" resultMap="queryOrdersUserResultMap">select o.id,o.user_id,o.number,o.createtime,o.notefrom orders o;</select><!--手动配置映射,查出来的数据表的列和pojo属性相对应,对应返回类型或者返回函数的泛型--><resultMap id="queryOrdersUserResultMap" type="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"/><!--配置一对一？配置user属性的值association配置对应关系property 属性: 和orders对象的哪个属性关联javaType 属性: 属性数据类型 可以小写?无所谓大小写column:使用哪个列作为条件进行二次查询select:封装的第二次查询的SQL语句--><!--因为单独查询，所以有没必要映射--><!--foreach标签的应用场景是函数传进来的参数--><association property="user" javaType="User" column="user_id" select="queryUserByUserId">
<!--            <id column="uid" property="id"/>-->
<!--            <result column="username" property="username"/>-->
<!--            <result column="sex" property="sex"/>-->
<!--            <result column="birthday" property="birthday"/>-->
<!--            <result column="address" property="address"/>--></association></resultMap><select id="queryUserByUserId" parameterType="Integer" resultType="User">select u.id,u.username,u.sex,u.birthday,u.addressfrom user u where u.id = #{id}</select>
</mapper>

分情况查询

不查询uesr信息时

    @Testpublic void testQueryOrdersUser(){SqlSession sqlSession = sqlSessionFactory.openSession();OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);List<Orders> orders = ordersMapper.queryOrdersUser();for (Orders order : orders) {
//            System.out.println(order);System.out.println(order.getId() + "::" + order.getUserId()+ "::" + order.getNumber());}sqlSession.close();}

查询user信息时

    @Testpublic void testQueryOrdersUser(){SqlSession sqlSession = sqlSessionFactory.openSession();OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);List<Orders> orders = ordersMapper.queryOrdersUser();for (Orders order : orders) {
//            System.out.println(order);System.out.println(order.getId() + "::" + order.getUserId()+ "::" + order.getNumber() + "::" + order.getUser());}sqlSession.close();}

一对多查询

原Mybatis查询

• 用户和订单之间是一对多的关系
• 只输出用户信息的情况会造成浪费资源

pojo

同一对一的User和Order

配置文件

UserMapper.xml

<?xml version="1.0" encoding="utf-8" ?><!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><!--mapper标签属性namespace和接口名相同-->
<mapper namespace="com.ahu.mapper.UserMapper"><select id="queryUser" resultType="user">select <include refid="userColumn"/> from user</select><!--多条件查询标签where拼接查询的条件（自动检查，不需要加1=1,Mys）根据pojo中的对象决定查询的条件(username和sex)标签if对pojo的属性进行判断属性test判断属性是否为空--><!--直接用属性名就可以如username，如果使用user.username则user是内置的属性对象，空串用''表示SQL语句会自动加where,也会自动删除第一个and--><select id="queryByWhere" resultType="user" parameterType="user">select * from user<where><if test="username != null and username != ''">and username like #{username}</if><if test="sex != null and sex != ''">and sex = #{sex}</if></where></select><!--批量删除参数是集合使用迭代标签foreach,标签属性parameterType,参数的数据类型,写的是集合的泛型迭代标签 foreach  遍历集合，固定的集合list属性： collection 遍历容器集合属性： open SQL开始的符号属性： close SQL结束符号属性： separator SQL语句参数分隔符属性  item 遍历的容器的元素--><delete id="deleteUserByList" parameterType="list">delete from user where id in<foreach collection="list" open="(" close=")" separator="," item="id">#{id}
--             取出集合元素</foreach></delete><!--传入数组collection属性填array--><delete id="deleteUserByArray" parameterType="int[]">delete from user where id in<foreach collection="array" open="(" close=")" separator="," item="id">#{id}</foreach></delete><select id="queryUserOrders" resultMap="queryUserOrdersResultMap">select u.id uid,u.username,u.sex,u.birthday,u.address,o.id,o.user_id,o.number,o.createtime,o.notefrom user u left outer join orders o on u.id = o.user_id;</select><!--        type返回类型--><resultMap id="queryUserOrdersResultMap" type="User"><id column="uid" property="id"/><result column="username" property="username"/><result column="sex" property="sex"/><result column="birthday" property="birthday"/><result column="address" property="address"/><!--配置user对象的属性，不是单一的对象，是集合容器collection配置一对多属性property配置pojo对象的属性属性ofTyte指定集合泛型--><collection property="ordersList" ofType="Orders"><id column="id" property="id"/><id column="user_id" property="userId"/><id column="number" property="number"/><id column="createtime" property="createtime"/><id column="note" property="note"/></collection></resultMap><sql id="userColumn">id,username,sex,birthday,address</sql>
</mapper>

业务代码

package com.ahu.test;import com.ahu.mapper.OrdersMapper;
import com.ahu.mapper.UserMapper;
import com.ahu.pojo.Orders;
import com.ahu.pojo.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;import java.io.IOException;
import java.io.InputStream;
import java.util.List;public class MainTest {private SqlSessionFactory sqlSessionFactory;@Beforepublic void before() throws IOException {InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();sqlSessionFactory = sqlSessionFactoryBuilder.build(is);}/*** SQ查询了用户和订单信息，但是运行时期只需要订单数据* 没有用户数据，对用户数据的查询就是浪费资源* 延迟查询：需要的时候就查，不需要就不查** 方法：拆分SQL语句*/@Testpublic void testQueryOrdersUser(){SqlSession sqlSession = sqlSessionFactory.openSession();OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);List<Orders> orders = ordersMapper.queryOrdersUser();for (Orders order : orders) {
//            System.out.println(order);System.out.println(order.getId() + "::" + order.getUserId()+ "::" + order.getNumber());}sqlSession.close();}/*** 一对多查询，以用户表为基准，不过和上例相同，Orders内置为User属性，查询出来Orders的属性user为空，不能套娃*/@Testpublic void testQueryUserOrders(){SqlSession sqlSession = sqlSessionFactory.openSession();UserMapper userMapper = sqlSession.getMapper(UserMapper.class);List<User> users = userMapper.queryUserOrders();for (User user : users) {System.out.println(user);}sqlSession.close();}
}

多对多延迟查询

修改UserMapper.xml的配置（最下方一对多的配置）

 可以传入重命名的列名uid（防止重名）使用子标签select的第二次子查询也是可以使用映射的ofType传入泛型

<?xml version="1.0" encoding="utf-8" ?><!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><!--mapper标签属性namespace和接口名相同-->
<mapper namespace="com.ahu.mapper.UserMapper"><select id="queryUser" resultType="user">select <include refid="userColumn"/> from user</select><!--多条件查询标签where拼接查询的条件（自动检查，不需要加1=1,Mys）根据pojo中的对象决定查询的条件(username和sex)标签if对pojo的属性进行判断属性test判断属性是否为空--><!--直接用属性名就可以如username，如果使用user.username则user是内置的属性对象，空串用''表示SQL语句会自动加where,也会自动删除第一个and--><select id="queryByWhere" resultType="user" parameterType="user">select * from user<where><if test="username != null and username != ''">and username like #{username}</if><if test="sex != null and sex != ''">and sex = #{sex}</if></where></select><!--批量删除参数是集合使用迭代标签foreach,标签属性parameterType,参数的数据类型,写的是集合的泛型迭代标签 foreach  遍历集合，固定的集合list属性： collection 遍历容器集合属性： open SQL开始的符号属性： close SQL结束符号属性： separator SQL语句参数分隔符属性  item 遍历的容器的元素--><delete id="deleteUserByList" parameterType="list">delete from user where id in<foreach collection="list" open="(" close=")" separator="," item="id">#{id}
--             取出集合元素</foreach></delete><!--传入数组collection属性填array--><delete id="deleteUserByArray" parameterType="int[]">delete from user where id in<foreach collection="array" open="(" close=")" separator="," item="id">#{id}</foreach></delete><!--拆分sql语句select u.id uid,u.username,u.sex,u.birthday,u.address,o.id,o.user_id,o.number,o.createtime,o.notefrom user u left outer join orders o on u.id = o.user_id;--><select id="queryUserOrders" resultMap="queryUserOrdersResultMap">select u.id uid,u.username,u.sex,u.birthday,u.addressfrom user u;</select><!--        type返回类型--><resultMap id="queryUserOrdersResultMap" type="User"><id column="uid" property="id"/><result column="username" property="username"/><result column="sex" property="sex"/><result column="birthday" property="birthday"/><result column="address" property="address"/><!--配置user对象的属性，不是单一的对象，是集合容器collection配置一对多属性property配置pojo对象的属性属性ofTyte指定集合泛型--><!--            可以传入重命名的列名uid（防止重名）使用子标签select的第二次子查询也是可以使用映射的ofType传入泛型--><collection property="ordersList" ofType="Orders" column="uid" select="queryOrdersByUserId"><id column="id" property="id"/><id column="user_id" property="userId"/><id column="number" property="number"/><id column="createtime" property="createtime"/><id column="note" property="note"/></collection></resultMap><select id="queryOrdersByUserId" parameterType="Integer" resultType="Orders">select o.id,o.user_id,o.number,o.createtime,o.notefrom orders o where o.user_id = #{id};</select><sql id="userColumn">id,username,sex,birthday,address</sql>
</mapper>

业务代码

不查询订单信息就不会出现

    /*** 一对多查询，以用户表为基准，不过和上例相同，Orders内置为User属性，查询出来Orders的属性user为空，不能套娃*/@Testpublic void testQueryUserOrders(){SqlSession sqlSession = sqlSessionFactory.openSession();UserMapper userMapper = sqlSession.getMapper(UserMapper.class);List<User> users = userMapper.queryUserOrders();for (User user : users) {System.out.println(user.getUsername());}sqlSession.close();}

查询了订单信息就会调用

    /*** 一对多查询，以用户表为基准，不过和上例相同，Orders内置为User属性，查询出来Orders的属性user为空，不能套娃*/@Testpublic void testQueryUserOrders(){SqlSession sqlSession = sqlSessionFactory.openSession();UserMapper userMapper = sqlSession.getMapper(UserMapper.class);List<User> users = userMapper.queryUserOrders();for (User user : users) {System.out.println(user.getUsername() + "::" + user.getOrdersList());}sqlSession.close();}