Mybatis中的多表查询和动态sql

最新推荐文章于 2024-09-11 17:16:12 发布

Gemstone-Phos

最新推荐文章于 2024-09-11 17:16:12 发布

阅读量910

点赞数

文章标签： java mybatis

本文链接：https://blog.csdn.net/GemstonePhos/article/details/129803893

版权

spring中的mybatis

mybatis配置相关问题-仅在用xml文件配置映射有以下类似问题

将对应mapper.xml文件放在java文件下，需要在pom.xml中配置过滤器，保证xml文件被扫描到,类似格式如下

<!-- 此处是为了方面在java目录中读取xml配置文件  如果不加 默认的只能在resources目录中读取-->
 <resource>
      <directory>src/main/java</directory>
      <includes>
        <include>**/*.xml</include>
        <include>**/*.properties</include>
      </includes>
      <filtering>false</filtering>
    </resource>
    <resource>
      <directory>src/main/resources</directory>
      <includes>
        <include>**/*.xml</include>
        <include>**/*.properties</include>
      </includes>
      <filtering>false</filtering>
    </resource>

而将mapper对应的xml文件放在resource文件夹时，需要在properties或者是 yml 中配置对应的映射路径

//此为在yml或者yaml配置文件中写法，其中mapper是recource目录下存放xml文件的文件夹
mapper-locations: classpath:mapper/*.xml

//此为properties配置文件中的写法
mybatis.mapperLocations=classpath:mapper/*.xml

而在resource下需要创建mapper文件夹，将xml文件放入其下（对应的路径一定要相同，防止扫描不到对应的xml文件导致映射失败）

如果是映射失败，报错如下：Invalid bound statement (not found):

mybatis查询（重要）

多表查询(xml实现-主要)

多表查询思路以及注意事项

多表连接设计实现的过程以及思路:

1.查询sql分析(select语句需要写连接关系- 直接内联/子查询实现，这里只考虑内联不考虑子查询，与注解开发区别出来，在xml中实现内联主要是依靠resultMap中的select语句中，实现方法与注解开发类似，放在下面解释)

2.在对应pojo对象中添加关联关系

3.编写接口方法

4.编写映射文件(或者是直接写注解)

- resultMap 下只需要写对应的<id> 对应：一对一 <association> 下的<id>，

一对多 <collection> 下的<id>

<id> 中有两个常用书写 column 是给当前表的主键(为了防止id注入相同常常在sql语句中指定别名，这样也需要将所有字段列出来) ，property是当前pojo对象与表对应的主键属性名

<collection> 中的注意点 property 中的是指pojo中映射对象的属性名，javaType是指当前类型，ofType是指内部类型。

注意:

collection和association 标签对中，在表与表之间的字段名没有重复时，

不写对应的映射(<id/> <result/>)也可以的，直接让mybatis在生成时自动注入映射(需要写 autoMapping="true")。

这种只适用于多表连接，不适用于子查询。

<collection property="orders" javaType="List" ofType="Order" autoMapping="true">

//property对应的是 属性名
private List<Order> orders;

5.测试(在test中测试每个方法)

图解

一对一

一对多

数据准备：

一对一：

xml实现多表时: 需要在对应的pojo内加入对应的对象来映射

这里的例子为 order - user (1-1)

public class Order {

    private Integer id;
    private String orderNumber;
    //关联User对象
    private User user;
}

xml中一对一映射

<resultMap id="orderUserMap" type="Order" autoMapping="true">
				<!--resultMap的id为 下面sql语句调用的唯一标识  type为主表pojo对象 
  					autoMapping(true)指除了当前指定的映射集，其他字段的映射自动进行
            resultMap中id 标签对 指当前pojo对象以及对应数据库表中的主键字段
  			-->
        
        <id column="oid" property="id"/>
        <!--主表主键 column中 表中需要指定订单别名为oid property中为pojo属性名为id-->


        <association property="user" javaType="User" autoMapping="true">
				<!--association 中的property是指当前order 中的 pojo对象名字，
          	javaType指该类型的名称 
            -->
            <id column="uid" property="id"/>
           <!--从表主键  column用户id别名为uid property为当前user属性名为id-->
        </association>
</resultMap>


<select id="queryOrderAndUserByOrderNumber" resultMap="orderUserMap" >
        select o.id as oid,     //这里指定的别名 与 resultMap中的id 的column属性进行对应
               o.order_number ,

               u.id as uid,     //与column属性进行对应
               u.user_name,
               u.password,
               u.name,
               u.age,
               u.sex
        from tb_order as o
        inner join tb_user as u on o.user_id = u.id
        where o.order_number = #{orderNumber}
    </select>

<association> 标识两个表的一对一关系

一对多：

主表pojo中加入List<?> 子表对象来映射

public class User implements Serializable{
    private Long id;
    // 用户名
    private String userName;
    // 密码
    private String password;
    // 姓名
    private String name;
    // 年龄
    private Integer age;
    //0-女 1-男
    private Integer sex;
    //用户1对多订单
    private List<Order> orders;
}

xml中一对多映射

<resultMap id="userOrder" type="User" autoMapping="true">
			<!--resultMap的id为 下面sql语句调用的唯一标识  type为主表pojo对象(这里为User) 
  					autoMapping(true)指除了当前指定的映射集，其他字段的映射自动进行
            resultMap中id 标签对 指当前pojo对象以及对应数据库表中的主键字段
  			-->
      <id column = "uid" property ="id">
        <!--user 的主键为id，这里为了防止id注入相同为id取了别名  
        		property中对应pojo的对象-->

        <!--Order集合关联映射-->
        <collection property="orders" javaType="List" ofType="Order" autoMapping="true">
					<!--property指当前类中的对应映射的成员变量 javaType指当前类型(一对多中的一般为List)
              ofType为其中包含的类型Order, autoMapping    
          -->
          <!--Order的主键 表中的主键,property中对应Order的主键-->
            <id column="oid" property="id"/>
          
        </collection>
</resultMap>


<!--根据用户id查询用户及其订单数据-->
    <select id="getUserOrders" resultType="User" resultMap="userOrders">
        select
            o.id as oid,    -- 别称定义为oid 为了防止出现结果集输出时id注入相同出错
            o.order_number,
            u.id as uid,       -- 别称定义为uid
            u.user_name,
            u.password,
            u.name,
            u.age,
            u.sex
        from tb_user u
        inner join tb_order o on o.user_id = u.id
        where u.id = #{uid}
    </select>

<collection> 标识两个表一对多的关系

多对多：

多对多本质上是一对一 - 一对多 -的转换，通过一张中间表来进行转换

图解 : 用视图(虚表的概念来帮助理解)

在对应pojo对象中加入对应的对象，还要根据需求来定义

如下需求为 : 根据商品编号查询详细订单信息 (商品1-n 商品订单详细商品订单详细1-1订单详细)

如果需求为根据订单编号查询，方向是相反的 (订单1-n 商品订单详细商品订单详细1-1商品详细)

public class Item { //主表
    private Integer id;
    private String itemName;
    private Float itemPrice;
    private String itemDetail;

    //多对多查询
    // 一个订单信息包含多个订单详细信息
    private List<Orderdetail> detailList;
}

//中间表
public class Orderdetail{
    private Integer id;
    private Double totalPrice;
    private Integer status;

    //多对多查询 一条订单详细中包含一条订单记录
    private Order order;
}

//Item 1-n Orderdetail  
//Orderdetail 1-1 Order
//最后被关联表无需修改

xml实现：

思路：

1.主表 Order (1-n) 映射 Orderdetail (用colloection 标签对)

<!--多对多查询 通过商品编号查询多个商品细节 1个商品细节对应一个订单信息-->
<!--    结果集映射-->
<resultMap id="getDetailByItemId" type="Item" autoMapping="true">
      <!--item的主键(id)以及别名(column)-->
      <id column="itemId" property="id"/>
      <!--一个商品对应多个商品详细-->
      <collection property="detailList" javaType="java.util.List" ofType="Orderdetail" autoMapping="true">
        <!--property为pojo中的对象的名称 -->
        <!--商品详细的主键以及别名 column-主键别名  property-pojo对象中的主键 主键相对应 -->
        <id column="orderDetailId" property="id"/>
        
        <!--一个商品详细表对应一个订单-->
        	<association property="order" javaType="Order" autoMapping="true">
          		<!--property为pojo中的对象的名称 -->
            	<!--订单表的主键以及别名-->
            	<id column="orderId" property="id"/>
        	</association>
      </collection>
</resultMap>

<select id="getDetailByItemId" resultMap="getDetailByItemId" resultType="item">
        select
               tb_item.id as itemId,     -- 取别名 与上面的id内的column对应
               tb_item.item_name,
               tb_item.item_price,
               tb_item.item_detail,
               tb_orderdetail.id as orderDetailId,  -- 取别名 与上面的id内的column对应
               tb_orderdetail.total_price,
               tb_orderdetail.status,
               tb_order.id as orderId,     -- 取别名 与上面的id内的column对应
               tb_order.user_id,
               tb_order.order_number

        from tb_item
                 inner join tb_orderdetail on tb_item.id = tb_orderdetail.item_id
                 inner join tb_order on tb_orderdetail.order_id = tb_order.id
        where tb_item.id = #{itemId};
    </select>

多表查询(注解实现-主要是子查询嵌套的理念)

多表查询注解开发思路

多表连接设计实现的过程:

1.查询sql分析（注解实现，最好的理解是用子查询来理解）

2.在对应pojo对象中添加关联关系(这一步与xml文件内理解一样)

3.编写接口方法（需要额外写 one和many字段的子查询的对应的方法 ,相当于子表）

4.编写结果映射集(或者是直接写注解)

@Results 下,用@Result来指定映射

@Result内常用字段如下： column 指定数据库的列名，property指定pojo对应的属性字段名，

一对一中(等效于<assocation/>标签对)： one = @One(select="" )

一对多中(等效于<colloection/>标签对)：many = @Many(select="" )

@One和@Many中传递的参数都是被关联pojo对象对应Mapper中的方法。

我的理解是，one中传递的是查询出一个对象的值，many是查询出多个对象(集合)的值。

在装配时，将对应pojo对象中的值装配进去，与xml不同的是，纯注解开发在select字段时，是不需要将全部字段列出的，包括多对多，xml需要标明字段的不同(比如id会重名，导致结果集映射时，找不到对应的id)

在实现时，用子查询的方式来理解更为清晰以及好理解，与xml中直接映射不同

数据准备

一对一：

1.需求：根据订单编号查询订单和用户信息

这里从tb_order找tb_user是一对一的

select * from tb_order
where user_id in (select id from tb_user)
and order_number = #{order_number};

2.在pojo对象中写相应的映射

这里在 order中加入user对应的映射对象

public class Order {

    private Integer id;
    private String orderNumber;
    //1-1 关联用户对象
    private User user;
}

3.在接口中写对应的方法

这里因为有子查询的存在，需要在UserMapper中写一个对应的查询,也就是上述查询语句中小括号内的内容

在UserMapper中相当于需要查询id字段的内容，进行以下转换

因为在进行映射时，该从表user的主键id与order的user_id 相对应，是通过user的id来关联order相应字段的

(select id from tb_user) => select * from tb_user where id =#{id}

在UserMapper中

public interface UserMapper {
    //一对一中的字段
    //根据 userId 查询字段 在 order中为user_id
    @Select("select * from tb_user where id = #{id}")
    User selectById(Integer id);
}

在OrderMapper中

public interface OrderMapper {
    //注解开发多表查询
    //1.一对一
    //显示所有订单信息和用户信息

    //定义映射集 - 本质上是子查询的嵌套
    @Select("select * from tb_order where order_number=#{order_number}")
    @Results({
            //与resultMap中类似 column 代表sql表中字段， property代表pojo中字段
            @Result(column = "id" ,property = "id"),
            @Result(
                    property = "user",         //被包含对象的变量名
                    javaType = User.class,     //被包含对象的实际类型
                    column = "user_id",        //根据查询出的Order表中的user_id来查询User表
                    //相当于指定从表的主键 是select语句中的 where后的字段名
                    /**
                     * one, @One 一对一的固定写法
                     * select 属性 : 指定调用 哪个接口 中的哪个方法
                    */
                    one = @One(select ="com.itheima.dao.db6_dao.UserMapper.selectById")
                    //子查询中 另外一张表对主键的查询
                    //select 语句为
                    //select * from tb_order
                    //where user_id in (select id from tb_user);
            )
    })
    List<Order> selectAll(Integer order_number);

理解上是从子表的查询开始理解，比较合理，子查询也可以很好理解。

但是mybatis在执行时，是从正向执行的。

这里测试的时候没有加入额外的约束条件，是查询所有的订单和用户信息，

从订单出发的查询所有用户信息，相当于一个左外连接(订单在左，用户在右)。

select * from tb_order
where user_id in (select id from tb_user);

以下是mybatis的执行过程：

mybaits首先准备 select * from tb_order这个预编译语句，然后发现当前没有参数后，直接执行，查询出结果；

通过结果集映射发现当前还有一个属性需要映射，通过one得到这个映射调用的子查询方法，如下

然后通过column指定的user_id来获得当前参数的值，从数据库中查出对应的数据，再将数据装到当前对象中，这里子查询对象为映射集指定的javaType=User.class，以及在主表中的属性名为user，这些都是@Result中指定的，这里因为结果不是一条，所以同样的操作执行了多次。

一对多：

1.需求：通过用户名查询用户名和订单信息

tb_user(1-n)tb_order

select * from tb_user
where id in (select user_id from tb_order)
and user_name =#{user_name};

2.pojo对象中加入相应映射

public class User implements Serializable{
    private Long id;
    // 用户名
    private String userName;
    // 密码
    private String password;
    // 姓名
    private String name;
    // 年龄
    private Integer age;
    //0-女 1-男
    private Integer sex;
    //1-n
    //关联一对多的对象
    private List<Order> orders;
}

3.在接口中写对应的方法

这里再OrderMapper中写：根据user_id查询到多个Order的方法

(select user_id from tb_order) => select * from tb_order where user_id = #{id}

OrderMapper中的方法

public interface OrderMapper{
    //2.一对多     user (1-n) orders
    //select 语句
    //select * from tb_user
    //where id in (select user_id from tb_order)
    //这里实现的是：select user_id from tb_order
    //所以对应子查询查询出来的应该是对应的user_id的列 ，用于在result中映射
    @Select("select * from tb_order where user_id = #{id}")
    //预编译的id只是一个传入的参数 不是order的主键值
    List<Order> selectByUserId(Integer id);
}

UserMapper中的方法

//2.一对多
    @Select("Select * from tb_user where user_name=#{user_name}")
    //映射集
    @Results({
            //column指定表中的字段名  property 指定pojo对象中的对应的字段名
            @Result(column = "id", property = "id"),
            @Result(
                    column = "id",                    //根据查询出的user表的id字段去查询order表
                    //select * from tb_user           //这里指的是where后的id
                    //where id in (select user_id from tb_order)
                    javaType = List.class,            //java中的对象的类型
                    property = "orders",              //pojo关联对象中的对象名
                    /**
                     * many = @Many,一对多的固定写法，
                     * select属性，指定调用哪一个接口中的哪个查询方法
                    */
                    many = @Many(select = "com.itheima.dao.db6_dao.OrderMapper.selectByUserId")
                    //select语句
                    //select * from tb_user
                    //where id in (select user_id from tb_order)
            )
    })
    List<User> selectAll(String user_name);

mybaits内的执行过程，与一对一的例子一样，在测试时，用的直接查询所有数据，

这里不难看出，多表映射集中result 中的 column指的是当前where 后的字段名，根据查询出的id去查询order表中user_id= 当前id的值

select * from tb_user
where id in (select user_id from tb_order)

mybatis的大致执行流程如下

一对多与一对一关系的区别就在于，在子查询那个Mapper对应方法时，返回的是多个数据，用List接收，pojo中也是;而一对一中直接用对应对象，和 pojo中一样映射。

多对多：

1.需求：通过订单编号查询订单详细以及商品详细

order (1-n) orderdetail (1-1) item

sql语句如下

select * from tb_order
where id in (select order_id from tb_orderdetail 
             where item_id in(select id from tb_item))
where order_number = #{order_number};

2.pojo对象中加入相应映射

public class Order {
    private Integer id;
    private String orderNumber;
    //n-n 关联订单信息
    //order(1-n) orderdetail 
    //一个订单有多个订单详细
    private List<Orderdetail> detailList;
}

public class Orderdetail { 
    private Integer id;
    private Double totalPrice;
    private Integer status;
    
    //多对多中的一对一
    //一个订单详细对应一个Item对象
    private Item item;
}

3.在接口中写对应的方法

建议书写顺序，从最内层的子查询写，这里一共两个子查询

select * from tb_order
where id in (select order_id from tb_orderdetail 
             where item_id in(select id from tb_item))

-- 第一个子查询
(select id from tb_item)

先写第一个子查询方法对应的接口方法ItemMapper的方法

//内部的 item和orderdetail是一对一的
public interface ItemMapper{
	@Select("select * from tb_item where id = #{id}")
	Item selectByItemId(Integer id);
}

第二个子查询

select * from tb_order
where id in (select order_id from tb_orderdetail 
             where item_id in(select id from tb_item))

-- 第二个子查询 这里不考虑内部的子查询 
(select order_id from tb_orderdetail 
             where item_id in(select id from tb_item))

第二个子查询对应的接口方法OrderDetailMapper

public interface OrderDetailMapper{
    @Select("select * from tb_orderdetail where order_id= #{order_id}")
	@Results({
        @Result(
            column = "item_id", //column字段是表中的字段 这里多为where后的字段
            //查询完表后的 item_id对应的字段
            javaType = Item.class,
            property = "item", //orderdetail 这个pojo中的对应的字段名
            one = @One(select="com.itheima.dao.db6_dao.ItemMapper.selectByItemId")
        )
    })
    List<Orderdetail> OrderdetailToItem(Integer order_id);
}

最后一个查询对应的接口方法

public interface OrderMapper{
    //3.多对多      order(1-n) orderdetail (1-1) item
    //select * from tb_order
    //where id in (select order_id from tb_orderdetail
    //                             where item_id in (select id from tb_item));
    //select写出来后, pojo 对象是正向映射, resultMap中的方法是逆向书写
    //从最后一个子查询往前写,层层调用

    //最后一步 多对多


    //结果如下：
    //Order{id=1, orderNumber='20140921001', user=null,
    //          detailList=[Orderdetail [id=1, totalPrice=5288.0, status=1],
    //                      Orderdetail [id=2, totalPrice=6288.0, status=1]]}
    //Order{id=2, orderNumber='20140921002', user=null,
    //          detailList=[Orderdetail [id=3, totalPrice=6288.0, status=1]]}
    //Order{id=3, orderNumber='20140921003', user=null,
    //          detailList=[Orderdetail [id=4, totalPrice=5288.0, status=1]]}
    @Select("select * from tb_order")
    @Results({
            //column中指的是表中的字段   property指的是pojo对象中的对应字段名
            @Result(
                    column = "id",
                    //根据查询出来的order表中的id 与 子查询中的结果进行对比
                    javaType = List.class,
                    property = "detailList",
                    //pojo中的对应的名称
                    many = @Many(select = "com.itheima.dao.db6_dao.OrderDetailMapper.OrderdetailToItem")
                    //对应的方法
            )
    })
    List<Order> selectAllOrderAndItem();
}

多表查询(对比以及混用思路)

注解开发

1.简单的增删改查

2.在xml中写映射resultMap结果集，然后用@ResultMap来映射结果集感觉也是很好用的办法

3.需要实现子查询效果时

xml开发

1.多表查询，映射方便，清晰明了

2.可以实现动态sql，如果用注解开发的话，要多写一个ProviderUtils类调用方法。

动态sql(xml实现)

where

where常与if一起连用，防止所有条件为空出现多余的where和and

<!--查询用户名和地址 ，可以允许任意一个条件为空-->
    <select id="queryByUserNameAndAddress" resultType="User">
        select * from user
        <where>
            <if test="userName != null and userName.trim()!=''">
                username = #{userName}
            </if>
            <if test="address != null and address.trim()!=''">
                and address = #{address}
            </if>
        </where>
    </select>

if

<!--查询男性用户，如果输入了用户名，按用户名模糊查询,
    如果没有输入用户名，就查询所有男性用户。-->
  <select id="queryLikeUserName" resultType="user">
        select * from user where sex='男'
        <if test="userName!=null and userName.trim()!= ''">
            and username like concat('%',#{userName},'%')
        </if>
  </select>

chose-when-otherwise

chose-when-otherwise相当于java中的case 条件选择

    <select id="queryByUserNameOrAddress" resultType="User">
        select * from user where sex='男'
        <choose>
            <when test="userName!=null and userName.trim()!=''">
            and username like concat('%',#{userName},'%')
            </when>
            <when test="address!=null and address.trim()!=''">
            and address  = #{address}
            </when>
            <otherwise>
            and username = '孙悟空'
            </otherwise>
        </choose>
    </select>

set

在修改数据时，希望用户修改值时可以仅仅修改一个，其他的条件可以不修改。

如果不用动态sql会导致用户没有输入修改的字段为空，更新到数据库时这个字段也会为空

<update id="updateSelectiveUser">
        update user
        <set>
            <if test="username!=null and username.trim()!=''">
                username = #{username},
            </if>
            <if test="birthday!=null">
                birthday = #{birthday},
            </if>
            <if test="sex !=null and sex.trim()!=''">
                sex = #{sex},
            </if>
            <if test="address != null and address.trim()!= ''">
                address = #{address},
            </if>
        </set>
        where id = #{id}
    </update>

foreach

希望用户可以进行批量添加和批量删除的操作

<!--    案例：
        按照id值是1，2，3来查询用户数据；
        select * from user where id in (1,2,3);
        编译后为
        select * from user where id in ( ? , ? , ? )
        -->
    <select id="queryByIds" resultType="User">
        select * from user where id in
        <foreach
                collection="arrIds"
                item="ID"
                separator=","
                open="("
                close=")">
        	#{ID}
        </foreach>
    </select>

动态sql(Provider工具类实现-注解@SelectProvider)

注解

@SelectProvider ：type = "调用的工具类的class对象"，method = "工具类中的对象名"

注意：用该种方法开发时，注意Mapper接口中方法的参数要与工具类中的参数一致

Mapper接口中方法写法

@SelectProvider(type = ProviderUtils.class,method = "queryUserBySexOrUsernameSQL2")
List<User> queryUserBySexOrUsernameSQL2(@Param("sex") String sex,@Param("username") String username);

ProviderUtil中的工具类

public class ProviderUtils {
//方式3 使用mybatis提供的一个对象 SQL
    public String queryUserBySexOrUsernameSQL2(@Param("sex") String sex,@Param("username") String username){
        //创建SQL对象
        SQL sql = new SQL();
        //链式编程，每个方法返回值都是sql对象
        sql.SELECT("*").FROM("user").WHERE("sex = #{sex}");
        //判断用户是否为空 不为空就继续链式编程，即继续拼接
        if (username != null && !"".equals(username)){
            sql.WHERE("username like concat('%',#{username},'%')");
        }

        //select * from user
        //转换为字符串并返回
        return sql.toString();
    }

	//也可以实现批量删除
	//批量删除
    public String delete(@RequestBody List<Integer> ids){
        SQL sql = new SQL();
        sql.DELETE_FROM("emp");
        //预编译结果达成 delete from emp where id in (?,?,?)
        StringJoiner sj = new StringJoiner(",","(",")");
        if (ids.size() > 0 && ids !=null){
            for (Integer id : ids) {
                sj.add(id.toString());
            }
            sql.WHERE("id in"+sj.toString());
        }
        return sql.toString();
    }
    
}

总结：理论上生成的sql语句满足动态就可以，这种写法更接近于java中的语法思路，只要条件满足就可以直接添加，原理是返回一个预编译的sql语句