Mybatis多表联合查询，嵌套查询，动态SQL

Mybatis多表联合查询，嵌套查询，动态SQL

Mybatis多表联合查询

一对一

一对一查询：通过一方关联查询出另外一方的关系数据

1. 创建数据库和相关数据表
2. 创建需要关联查询的实体类,里面包含相关的属性

//丈夫查妻子
public class Husband {    
    private Integer id;    
    private String name;   
    private Wife wife;
}

public class Wife {

    private Integer id;
    private String name;
}

3. 编写HusbandMapper接口，为了调用方法

public interface HusbandMapper {

    List<Husband> findByName(String name);
}

4. 编写相关的Sql语句
   • 关联查询的时候需要用resultMap进行自定义标签的映射
   • 非关联单表查询的时候可以直接使用resultType进行查询
   • property表示属性对应的是实体类的字段名称
   • column表示的是自己定义的属性值，与sql语句定义的字段名称相同
   • association和javaType在一对一关联查询的时候使用
   • association里映射的是被关联查询的表和属性值

   <resultMap id="husbandAndWife" type="husband">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <association property="wife" javaType="wife">
            <id property="id" column="wid"/>
            <result property="name" column="wname"/>
        </association>
    </resultMap>
    
    <select id="findByName" parameterType="string" resultMap="husbandAndWife">
        select h.id,h.name,w.id wid, w.name wname from t_husband h,t_wife w 
        where h.id = w.id and h.name = #{name}
    </select>

5. 编写HusbandMapperTest测试类

public class CustomerMapperTest {

    private SqlSessionFactory sqlSessionFactory;

   @Before
   public void init() throws IOException {
       //加载核心配置文件
       InputStream inputStream = Resources.getResourceAsStream("mybatis-config");
       //创建会话工厂
       sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
   }

   @Test
   public void test(){
       //打开会话工厂
       SqlSession sqlSession = sqlSessionFactory.openSession();
       //获取内容
       CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
       //遍历Customer表数据
       List<Customer> ret = mapper.findByName("aa");
       //输出
       System.out.println(ret);
   }

一对多

一个用户可以关联查询出自己的订单信息

1. 创建数据库和相关数据表
2. 创建需要关联查询的实体类,里面包含相关的属性

public class Customer {

    private Integer id;
    private String name;
    
    private List<Order> orders;
}

public class Order {

    private Integer id;
    private String goods;
    private Integer cid;
}

3. 编写CustomerMapper接口，为了调用方法

public interface CustomerMapper {

    List<Customer> findByName(String name);
}

4. 编写相关的Sql语句
   • collection和ofType用于对多查询的场合
   • 左外连接，以左边表为基础和交集取值

<resultMap id="customerAndOrders" type="customer">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <collection property="orders" ofType="order">
        <id property="id" column="id"/>
        <result property="o.goods" column="o.goods"/>
        <result property="o.c_id" column="o.c_id"/>
        </collection>
    </resultMap>

    <select id="findByName" resultMap="customerAndOrders">
        select c.* ,o.id oid,o.goods,o.c_id from t_customer c 
        left join t_order o on c.id = o.c_id where c.name = #{name}
    </select>

5. 编写CustomerMapperTest测试类

public class CustomerMapperTest {

    private SqlSessionFactory sqlSessionFactory;

   @Before
   public void init() throws IOException {
       InputStream inputStream = Resources.getResourceAsStream("mybatis-config");
       sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
   }

   @Test
   public void test(){
       SqlSession sqlSession = sqlSessionFactory.openSession();
       CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
       List<Customer> ret = mapper.findByName("aa");
       System.out.println(ret);
   }

多对多

一个老师对应多个学生，一个学生也对应多个老师

1. 创建数据库和相关数据表
2. 创建需要关联查询的实体类,里面包含相关的属性

public class Teacher {

    private Integer id;
    private String name;

    private List<Student> students;
}

public class Student {

    private Integer id;
    private String name;
}

3. 编写TeacherMapper接口，为了调用方法

public interface TeacherMapper {

    List<Teacher> findByName(String name);
}

4. 编写相关的Sql语句
   • 多对多查询需要借助一个t_s中间表来完成关联

  <resultMap id="teacherAndStudent" type="teacher">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <collection property="students" ofType="student">
            <id property="id" column="sid"/>
            <result property="name" column="sname"/>
        </collection>
    </resultMap>

    <select id="findByName" resultMap="teacherAndStudent">
        select t.id,t.name,s.id sid,s.name sname from t_teacher t
            left join t_s ts on t.id = ts.t_id
            left join t_student s on ts.s_id = s.id
            where t.name = #{name}
    </select>

5. 编写TeacherMapperTest测试类

public class TeacherMapperTest {

    private SqlSessionFactory sqlSessionFactory;

    @Before
    public void init() throws IOException {
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    }

    @Test
    public void test(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        List<Teacher> ret = mapper.findByName("唐丹");
        System.out.println(ret);
    }
}

Mybatis的嵌套查询

嵌套查询是将原来多表查询中的联合查询语句拆成单个表的查询，再使用MyBatis的语法嵌套在一起

嵌套查询使用时，先查询a表的信息，然后依赖a和b表的外键约束，利用in()，再次查询b表对应到a表上的信息。该方式可以改为饿汉式，内存使用较小，但需要多次访问数据库而导致消耗时间多。

一对一嵌套查询

查询一个订单，同时查询出该订单所属的用户

	<select id="findById" resultType="customer">
        select * from t_customer where id = #{id}
    </select>

    <resultMap id="orderAndCustomer2" type="order">
        <id property="id" column="id"/>
        <result property="goods" column="goods"/>
        <result property="cid" column="cid"/>
        <association property="customer"
                     javaType="customer"
                     column="c_id"
                     select="findById"/>
    </resultMap>

    <select id="findByGoods2" resultMap="orderAndCustomer2">
        select * from t_order where goods = #{goods}
    </select>

一对多嵌套查询

查询一个用户，与此同时查询该用户具有的订单

	<resultMap id="baseMap" type="order">
        <id property="id" column="id"/>
        <result property="goods" column="goods"/>
        <result property="cid" column="c_id"/>
    </resultMap>

    <select id="findByCid" resultMap="baseMap">
        select * from t_order where c_id = #{cid}
    </select>

    <resultMap id="customerAndOrders2" type="customer">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <collection property="orders"
                    ofType="order"
                    column="id"
                    select="findByCid"/>

    </resultMap>

    <select id="findByName2" resultMap="customerAndOrders2">
        select * from t_customer where name = #{name}
    </select>

动态Sql查询

<insert id="save" parameterType="girl" keyProperty="id" useGeneratedKeys="true">
        insert into girl values (null, #{name},#{age},#{address})
    </insert>

    <sql id="selectAll">
        select * from girl
    </sql>

    <select id="findById" parameterType="int" resultType="girl">
        <include refid="selectAll"/> where id = #{id}
    </select>

    <select id="findAll" resultType="girl">
        <include refid="selectAll"/>
    </select>

Where标签

• ****标签相当于 where 1=1

• 如果没有条件，就不会拼接where关键字

• where标签可以忽略我们成立条件前面的and或者or关键字

    <select id="findByGirl" resultType="girl">
        select * from girl
        <where>
            <if test="name != null and name != ''">
                and name = #{name}
            </if>
            <if test="age != null and age != ''">
                and age = #{age}
            </if>
            <if test="address != null and address != ''">
                and address = #{address}
            </if>
        </where>
    </select>

    <select id="findByIds" resultType="girl">
        select * from girl
        <where>
            <foreach collection="list" open="id in (" item="id" separator="," close=")">
                #{id}
            </foreach>
        </where>
    </select>

Set标签

• set标签在更新的时候，自动加上set关键字，然后会去掉最后一个条件的逗号

 <update id="update" parameterType="girl">
        update girl
        <set>
            <if test="name != null and name != ''">
                name = #{name},
            </if>
            <if test="age != null and age != ''">
                age = #{age},
            </if>
            <if test="address != null and address != ''">
                address = #{address}
            </if>
        </set>
        where id = #{id}
	</update>

Foreach标签

• 标签用来对数据进行循环遍历
  • collection：代表遍历的集合元素
  • open：代表语句开始的部分
  • close：代表语句结束的部分
  • item：代表遍历集合的每一个元素，生成的变量名
  • sperator：代表分隔符

 <select id="findByIds" resultType="girl">
        select * from girl
        <where>
            <foreach collection="list" open="id in (" item="id" separator="," close=")">
                #{id}
            </foreach>
        </where>
    </select>