一、一对多

举例：一个人，对应多辆车

1.1 实体类

1.1.1 PerSon类

package com.woniuxy.entity;
import java.util.List;
import lombok.Data;
@Data
public class Person {
    private int pid;
    private String pname;
    private List<Car> cars;
}

1.1.2 Car类

package com.woniuxy.entity;
import lombok.Data;
@Data
public class Car {
    private int cid;
    private String cname;
}

1.2 接口

PersonMapper.java 接口

package com.woniuxy.mapper;
import com.woniuxy.entity.Person;
public interface PersonMapper {
    //连表查询
    public Person findPersonByPid(int pid);
    // n+1查询
    public Person findPersonByPid2(int pid);
}

1.3 XML文件

PersonMapper.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.woniuxy.mapper.PersonMapper">
    <!-- 连表查询 -->
    <select id="findPersonByPid" resultMap="personMap">
        select * from person
        p,car c where p.pid=c.pid and p.pid=#{pid}
    </select>
    <resultMap type="Person" id="personMap">
        <id column="pid" property="pid" />
        <result column="pname" property="pname" />
        <!-- 多个 -->
        <collection property="cars" ofType="Car">
            <id column="cid" property="cid" />
            <result column="cname" property="cname" />
        </collection>
    </resultMap>
    <!-- n+1 -->
    <select id="findPersonByPid2" resultMap="personMap2">
        select * from person where pid=#{pid}
    </select>
    <resultMap type="Person" id="personMap2">
        <id column="pid" property="pid" />
        <collection property="cars" column="pid" select="findCarByPid"></collection>
    </resultMap>
    <select id="findCarByPid" resultType="Car">
        select * from car where pid=#{pid}
    </select>
</mapper>

注意：xml文件中主要不同的地方在于，一对一，映射另一张表关系的时候使用的是，而1对多使用的是，多对多同理

1.4 测试类

OneToMoreTest 测试类

package com.woniuxy.test;

import java.io.IOException;
import java.io.InputStream;

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 com.woniuxy.mapper.PersonMapper;

public class OneToMoreTest {

    public static void main(String[] args) throws IOException {

        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");

        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);

        SqlSession session = factory.openSession();

        PersonMapper personMapper = session.getMapper(PersonMapper.class);
//        System.out.println(personMapper.findPersonByPid(1001));
        System.out.println(personMapper.findPersonByPid2(1001));
    }

}

二、多对多

多对多与一对多主要不同的地方在于有一张中间表，其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.woniuxy.mapper.ClazzMapper">
    <select id="findClazzByCid" resultMap="clazzMap">
        select * from clazz where cid = #{cid}
    </select>

    <resultMap type="Clazz" id="clazzMap">
        <id column="cid" property="cid"/>
        <collection property="teachers" column="cid" select="findTeacherByCid"></collection>
    </resultMap>

    <select id="findTeacherByCid" resultType="Teacher">
        select * from teacher t,clazz_teacher ct where t.tid=ct.tid and ct.cid=#{cid}
    </select>
</mapper>

逻辑：先通过id查其中一张表(clazz)表，再联查中间表(clazz_teacher)和另外一张表(teacher)，用第一张表的id和中间表的id，建立关系，并且用第二张表和中间表的id建立关系，例如：

select * from teacher t,clazz_teacher ct where t.tid=ct.tid and ct.cid=#{cid}

三、动态SQL

3.1 if拼接

<!-- 假设有这样一个需求：用户可以输入，选择多个查询条件 这些条件有:
          图书名字（模糊）、分类、作者（模糊）三个作为查询条件
          如果一个查询条件都没有，则查询出所有的商品，有指定查询条件就作为查询条件查询出对应的数据
          if    用来判断条件是否成立，如果成立则将其中的SQL语句拼接到SQL的后边
   -->
    <select id="findIf" resultType="Goods">
        select * from mall_goods
       <!-- where 可以根据其中的条件是否成立自动决定是否添加where关键字，
           而且还能够自动去除多余的and 和 or 
       -->
           <where>
               <if test="name!=null and name.length()!=0">
                   and name like #{name}
               </if>
               <if test="categoryid!=0">
                   and categoryid = #{categoryid}
               </if>
               <if test="author!=null and author.length()!=0">
                   and author like #{author}
               </if>
           </where>
    </select>

3.2 foreach循环批量操作

<!-- 批量删除数据 -->
    <deldete id="delGoodsByIds">
        delete from mall_goods where id in
        <foreach item="id" collection="list" open="(" separator="," close=")">
            #{id}
        </foreach>
    </deldete>

Java笔记

35 - Mybatis - 03