MyBatis多对多映射查询 Day12 2018-11-30

作者: Ernest_Chou | 来源:发表于2018-11-30 19:03 被阅读0次

7 <collection>一对多查询

<collection>元素的解释:

column 表示当前查询数据库表的列名DEPARTMENT_ID
property 表示返回类型PhoneUserIdAndDepartment属性名department
javaType 表示该属性对于的类型名称，本例是一个ArrayList集合
select 表示执行的查询语句，将查询到的数据封装到property所代表的类型对象当中
ofType 表示集合当中的类型

7.1 基本应用

7.1.1 java bean

同 6.1.1

7.1.2 映射文件

<mapper namespace="com.zhougl.web.dao.WebClassDao">
    <resultMap id="WebClassMap"
        type="com.zhougl.web.beans.WebClass">
        <id column="ID" jdbcType="DECIMAL" property="id" />
        <result column="CODE" jdbcType="VARCHAR" property="code" />
        <result column="NAME" jdbcType="VARCHAR" property="name" />
        <!-- 一对多 -->
        <collection property="students" column="id"
            javaType="ArrayList" ofType="com.zhougl.web.beans.Student"
            select="selectStudentByClassId"
            fetchType="lazy" />
    </resultMap>
    
    <select id="selectStudentByClassId" parameterType="int" resultType="com.zhougl.web.beans.Student">
    select * from STUDENT  where class_id = #{classId,jdbcType=DECIMAL}
  </select>
  <select id="selectClass" parameterType="int" resultMap="WebClassMap">
    select * from WEB_CLASS
  </select>

</mapper>

7.1.3 测试代码

SqlSession session = SqlSessionFactoryUtil.getSession();
        List<WebClass> classes = session.selectList("com.zhougl.web.dao.WebClassDao.selectClass");
        classes.forEach(classe ->{
            System.out.println(classe);
            List<Student> students = classe.getStudents();
            students.forEach(student -> System.out.println(student));
        });
        session.commit();
        session.close();

结果

==>  Preparing: select * from WEB_CLASS 
==> Parameters: 
<==      Total: 2
==>  Preparing: select * from STUDENT where class_id = ? 
==> Parameters: 2(Integer)
<==      Total: 2
WebClass [id=2, code=C002, name=无限流战斗班]
Student [id=2, name=王怡, sex=女, age=24]
Student [id=4, name=王多燕, sex=女, age=26]
==>  Preparing: select * from STUDENT where class_id = ? 
==> Parameters: 1(Integer)
<==      Total: 2
WebClass [id=1, code=C001, name=大乱斗指导班]
Student [id=1, name=王一倩, sex=女, age=22]
Student [id=3, name=王二赞, sex=男, age=28]

7.2 一对多映射

7.2.1 mybatis配置

mybatis-config.xml添加如下配置

<settings>
    <!-- 延迟加载的全局开关 -->
    <setting name="lazyLoadingEnabled" value="true"/>
    <!-- true 使带有延迟加载的属性对象立即加载 ，false-按需加载-->
    <setting name="aggressiveLazyLoading" value="false"/>
</settings>

7.2.2 java bean

同 6.1.1

7.2.3 mapper映射文件

WebClassMapper.xml

<mapper namespace="com.zhougl.web.dao.mapper.WebClassMapper">
    <resultMap id="WebClassMap"
        type="com.zhougl.web.beans.WebClass">
        <id column="ID" jdbcType="DECIMAL" property="id" />
        <result column="CODE" jdbcType="VARCHAR" property="code" />
        <result column="NAME" jdbcType="VARCHAR" property="name" />
        <!-- 一对多 -->
        <collection property="students" column="id"
            javaType="ArrayList" ofType="com.zhougl.web.beans.Student"
            select="com.zhougl.web.dao.mapper.StudentMapper.selectStudentByClassId"
            fetchType="lazy" >
            <id column="ID" jdbcType="DECIMAL" property="id" />
            <result column="NAME" jdbcType="VARCHAR" property="name" />
            <result column="SEX" jdbcType="VARCHAR" property="sex" />
            <result column="AGE" jdbcType="DECIMAL" property="age" />
        </collection>
    </resultMap>

    <sql id="Base_Column_List">
        ID, CODE, NAME
    </sql>
    <select id="selectWebClassById"
        parameterType="int" resultMap="WebClassMap">
        select
        <include refid="Base_Column_List" />
        from WEB_CLASS
        where ID = #{id,jdbcType=DECIMAL}
    </select>

</mapper>

7.2.4 mapper接口

WebClassMapper.xml

public interface WebClassMapper {
  
    WebClass selectWebClassById(int i);
}

7.2.5 测试类

public class OneToManyTest {

    public static void main(String[] args) {
        SqlSession session = SqlSessionFactoryUtil.getSession();
        OneToManyTest test = new OneToManyTest();
        //测试一对多
        test.testOneToMany(session);
        
        //测试多对一
        //test.testManyToOne(session);
        session.commit();
        session.close();

    }
    //测试一对多，查询班级（一）级联查询学生（多）
    public void testOneToMany(SqlSession session) {
        WebClassMapper mapper = session.getMapper(WebClassMapper.class);
        WebClass webClass = mapper.selectWebClassById(1);
        System.out.println(webClass.getId()+" "+webClass.getCode()+" "+webClass.getName());
        System.out.println(webClass.toString());
        List<Student> students = webClass.getStudents();
        for (Student student : students) {
            System.out.println(student.toString());
        }
    }

}

==>  Preparing: select ID, CODE, NAME from WEB_CLASS where ID = ? 
==> Parameters: 1(Integer)
<==      Total: 1
1 C001 大乱斗指导班
==>  Preparing: select ID as studentId, NAME as studentName, SEX, AGE, CLASS_ID from STUDENT where CLASS_ID = ? 
==> Parameters: 1(Integer)
<==      Total: 4
WebClass [id=1, code=C001, name=大乱斗指导班]
Student [id=1, name=王一倩, sex=女, age=22]
Student [id=2, name=王怡, sex=女, age=24]
Student [id=3, name=王二赞, sex=男, age=28]
Student [id=4, name=王多燕, sex=女, age=26]

7.3 多对一映射

7.3.1 java bean

同 6.1.1

7.3.2 mapper配置

StudentMapper.xml

<mapper namespace="com.zhougl.web.dao.mapper.StudentMapper">
  <resultMap id="StudentMap" type="com.zhougl.web.beans.Student">
    <id column="studentId" jdbcType="DECIMAL" property="id" />
    <result column="studentName" jdbcType="VARCHAR" property="name" />
    <result column="SEX" jdbcType="VARCHAR" property="sex" />
    <result column="AGE" jdbcType="DECIMAL" property="age" />
    <!-- 多对一 -->
    <association property="webClass" javaType="com.zhougl.web.beans.WebClass">
        <id column="ID" jdbcType="DECIMAL" property="id" />
        <result column="CODE" jdbcType="VARCHAR" property="code" />
        <result column="NAME" jdbcType="VARCHAR" property="name" />
    </association>
  </resultMap>
 
  <sql id="Base_Column_List">
    ID as studentId, NAME as studentName, SEX, AGE, CLASS_ID
  </sql>
  <sql id="student_List">
    ${student}.ID as studentId, ${student}.NAME as studentName, ${student}.SEX, ${student}.AGE, ${student}.CLASS_ID
  </sql>
  <sql id="Web_Class_Column_List">
        ${webClass}.ID , ${webClass}.CODE, ${webClass}.NAME 
  </sql>
 <!-- 多表连接 -->
 <!-- 查出来的列同名时需要使用别名区分 -->
  <select id="selectStudentById" parameterType="int" resultMap="StudentMap">
    select 
    <include refid="student_List" >
        <property name="student" value="s"/>
    </include>,
    <include refid="Web_Class_Column_List" >
        <property name="webClass" value="c"/>
    </include>
    from STUDENT s,WEB_CLASS c
    where s.class_id=c.id and s.ID = #{id,jdbcType=DECIMAL}
  </select>
  <select id="selectStudentByClassId" parameterType="int" resultMap="StudentMap">
    select 
    <include refid="Base_Column_List" />
    from STUDENT
    where CLASS_ID = #{classId,jdbcType=DECIMAL}
  </select>
 
</mapper>

7.3.3 mapper接口

StudentMapper.java

public interface StudentMapper {
   
    Student selectStudentById(int id);
    List<Student> selectStudentByClassId(int classId);

}

7.3.4 测试类

public class OneToManyTest {
    //测试多对一，查询学生（多）级联查询班级（一）
    public void testManyToOne(SqlSession session) {
        StudentMapper studentMapper = session.getMapper(StudentMapper.class);
        Student student = studentMapper.selectStudentById(1);
        System.out.println(student);
        System.out.println(student.getWebClass().toString());
    }

结果

==>  Preparing: select s.ID as studentId, s.NAME as studentName, s.SEX, s.AGE, s.CLASS_ID , c.ID , c.CODE, c.NAME from STUDENT s,WEB_CLASS c where s.class_id=c.id and s.ID = ? 
==> Parameters: 1(Integer)
<==      Total: 1
Student [id=1, name=王一倩, sex=女, age=22]
WebClass [id=1, code=C001, name=大乱斗指导班]

7.4 多对多映射

7.4.1 java bean

public class WebOrder {
    private BigDecimal id;
    private String code;
    private BigDecimal total;
    private BigDecimal userId;
    //订单和用户是多对一关系
    private WebUser user;
    //订单和商品是多对多关系
    private List<WebArticle> articles;
}

public class WebUser {
    private BigDecimal id;
    private String username;
    private String loginname;
    private String password;
    private String phone;
    private String address;
    
    //用户和订单是一对多关系
    private List<WebOrder> orders;
}

public class WebArticle {
    private BigDecimal id;
    private String name;
    private BigDecimal price;
    private String remark;
}

7.4.2 mapper配置

WebOrderMapper.xml

<mapper namespace="com.zhougl.web.dao.mapper.WebOrderMapper">
  <resultMap id="BaseResultMap" type="com.zhougl.web.beans.WebOrder">
    <id column="oId" jdbcType="DECIMAL" property="id" />
    <result column="CODE" jdbcType="VARCHAR" property="code" />
    <result column="TOTAL" jdbcType="DECIMAL" property="total" />
    <!-- 多对一关联 -->
    <association property="user" javaType="com.zhougl.web.beans.WebUser">
         <id column="ID" jdbcType="DECIMAL" property="id" />
        <result column="USERNAME" jdbcType="VARCHAR" property="username" />
        <result column="LOGINNAME" jdbcType="VARCHAR" property="loginname" />
        <result column="PASSWORD" jdbcType="VARCHAR" property="password" />
        <result column="PHONE" jdbcType="VARCHAR" property="phone" />
        <result column="ADDRESS" jdbcType="VARCHAR" property="address" />
    </association>
    <!-- 多对多关联 -->
    <collection property="articles" javaType="ArrayList"
        column="oId" ofType="com.zhougl.web.beans.WebArticle"
        select="com.zhougl.web.dao.mapper.WebArticleMapper.selectArticleByOrderId"
        fetchType="lazy">
        <id column="ID" jdbcType="DECIMAL" property="id" />
        <result column="NAME" jdbcType="VARCHAR" property="name" />
        <result column="PRICE" jdbcType="DECIMAL" property="price" />
        <result column="REMARK" jdbcType="VARCHAR" property="remark" />
    </collection>
  </resultMap>
    <!-- 有同名列，需要使用别名 -->
  <select id="selectOrderById" parameterType="int" resultMap="BaseResultMap">
    select 
    o.ID as oId, o.CODE, o.TOTAL, u.*
    from WEB_ORDER o,WEB_USER u
    where o.user_id = u.id and o.ID = #{id,jdbcType=DECIMAL}
  </select>
  <select id="selectOrderByUserId" parameterType="int" resultType="com.zhougl.web.beans.WebOrder">
    select * from WEB_ORDER where user_id = #{userId,jdbcType=DECIMAL}
  </select>
</mapper>

<mapper namespace="com.zhougl.web.dao.mapper.WebUserMapper">
  <resultMap id="BaseResultMap" type="com.zhougl.web.beans.WebUser">
    <id column="ID" jdbcType="DECIMAL" property="id" />
    <result column="USERNAME" jdbcType="VARCHAR" property="username" />
    <result column="LOGINNAME" jdbcType="VARCHAR" property="loginname" />
    <result column="PASSWORD" jdbcType="VARCHAR" property="password" />
    <result column="PHONE" jdbcType="VARCHAR" property="phone" />
    <result column="ADDRESS" jdbcType="VARCHAR" property="address" />
    <!-- 一对多关联 -->
    <collection property="orders" javaType="ArrayList" 
        ofType="com.zhougl.web.beans.WebOrder" 
        column="id" select="com.zhougl.web.dao.mapper.WebOrderMapper.selectOrderByUserId" 
        fetchType="lazy">
        <id column="ID" jdbcType="DECIMAL" property="id" />
        <result column="CODE" jdbcType="VARCHAR" property="code" />
        <result column="TOTAL" jdbcType="DECIMAL" property="total" />
    </collection>
  </resultMap>
 
  <sql id="Base_Column_List">
    ID, USERNAME, LOGINNAME, PASSWORD, PHONE, ADDRESS
  </sql>
 
  <select id="selectUserById" parameterType="int" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from WEB_USER
    where ID = #{id,jdbcType=DECIMAL}
  </select>
  
</mapper>

<mapper namespace="com.zhougl.web.dao.mapper.WebArticleMapper">
  <resultMap id="BaseResultMap" type="com.zhougl.web.beans.WebArticle">
    <id column="ID" jdbcType="DECIMAL" property="id" />
    <result column="NAME" jdbcType="VARCHAR" property="name" />
    <result column="PRICE" jdbcType="DECIMAL" property="price" />
    <result column="REMARK" jdbcType="VARCHAR" property="remark" />
  </resultMap>
  
  <sql id="Base_Column_List">
    ID, NAME, PRICE, REMARK
  </sql>
  
  <select id="selectArticleByOrderId" parameterType="int" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from WEB_ARTICLE where id in(
        select article_id from WEB_ITEM where order_id =#{orderId,jdbcType=DECIMAL}
    )
  </select>
  
</mapper>

7.4.3 mapper接口

public interface WebOrderMapper {
    List<WebOrder> selectOrderByUserId(int userId);
    WebOrder selectOrderById(int id); 
}
public interface WebUserMapper {
    WebUser selectUserById(int id);
}

7.4.4 测试类


public class ManyToManyTest {

    public static void main(String[] args) {
        SqlSession session = SqlSessionFactoryUtil.getSession();
        ManyToManyTest test = new ManyToManyTest();
        //test.testOneToMany(session);
        test.testManyToMany(session);
        session.commit();
        session.close();
    }
    public void testOneToMany(SqlSession session) {
        WebUserMapper userMapper = session.getMapper(WebUserMapper.class);
        WebUser user = userMapper.selectUserById(1);
        System.out.println(user.getUsername()+" "+user.getLoginname()+" "+user.getPhone()+" "+user.getAddress());
        List<WebOrder> orders = user.getOrders();
        for (WebOrder webOrder : orders) {
            System.out.println(webOrder.toString());
        }
    }
    public void testManyToMany(SqlSession session) {
        WebOrderMapper orderMapper = session.getMapper(WebOrderMapper.class);
        WebOrder order = orderMapper.selectOrderById(1);
        System.out.println(order.getCode()+" "+order.getTotal());
        WebUser user = order.getUser();
        System.out.println(user.toString());
        List<WebArticle> articles = order.getArticles();
        for (WebArticle webArticle : articles) {
            System.out.println(webArticle.toString());
        }
    }
}

MyBatis多对多映射查询 Day12 2018-11-30

7 <collection>一对多查询

7.1 基本应用

7.1.1 java bean

7.1.2 映射文件

7.1.3 测试代码

7.2 一对多映射

7.2.1 mybatis配置

7.2.2 java bean

7.2.3 mapper映射文件

7.2.4 mapper接口

7.2.5 测试类

7.3 多对一映射

7.3.1 java bean

7.3.3 mapper接口

7.3.4 测试类

7.4 多对多映射

7.4.1 java bean

7.4.2 mapper配置

7.4.3 mapper接口

7.4.4 测试类

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读