SpringBoot2.X之旅，Mybatis+Durid+Mysql实践，mybatis自定义sql语句和多表关联查询（Web Project）

一、基础

本文demo是在SpringBoot2.X之旅，Mybatis+Durid+Mysql实践，mybatis generator代码生成器实现简单CURD和Druid Monitor（Web Project）基础上编写的，这篇文章仅作为本人笔记。

二、自定义sql：

1、在OrderInfoMapper接口添加方法：

/**
     * 根据UserId查找订单
     * @param userId
     * @return
     */
    List<OrderInfo> selectByUserId(Integer userId);

2、在OrderInfoMapper.xml添加映射sql语句：

<!--根据UserId查找订单-->
  <select id="selectByUserId" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Sat Mar 16 10:50:50 CST 2019.
    -->
    select
    <include refid="Base_Column_List" />
    from order_info
    where user_id = #{userId}
  </select>

3、测试：

@Test
    public void selectByUserId() {
        List<OrderInfo> orderInfos = orderInfoMapper.selectByUserId(2);
        System.out.println(orderInfos);
    }

4、结果：

三、表关联查询：

1、新建dto，接收查询结果：

package com.cobra.mybatisdemo.dto;

import java.math.BigDecimal;

/**
 * @Author: Baron
 * @Description:
 * @Date: Created in 2019/3/16 19:01
 */
public class OrderDTO {

    private Integer orderId;

    private String orderDesc;

    private BigDecimal orderFee;

    private String username;

    private String email;

    public Integer getOrderId() {
        return orderId;
    }

    public void setOrderId(Integer orderId) {
        this.orderId = orderId;
    }

    public String getOrderDesc() {
        return orderDesc;
    }

    public void setOrderDesc(String orderDesc) {
        this.orderDesc = orderDesc;
    }

    public BigDecimal getOrderFee() {
        return orderFee;
    }

    public void setOrderFee(BigDecimal orderFee) {
        this.orderFee = orderFee;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    @Override
    public String toString() {
        return "OrderDTO{" +
                "orderId=" + orderId +
                ", orderDesc='" + orderDesc + '\'' +
                ", orderFee=" + orderFee +
                ", username='" + username + '\'' +
                ", email='" + email + '\'' +
                '}';
    }

}

2、在OrderInfoMapper.xml添加映射设结果集：

<resultMap id="OrderDTO" type="com.cobra.mybatisdemo.dto.OrderDTO">
    <id column="order_id" jdbcType="INTEGER" property="orderId"/>
    <result column="order_desc" jdbcType="VARCHAR" property="orderDesc"/>
    <result column="order_fee" jdbcType="DECIMAL" property="orderFee"/>
    <result column="username" jdbcType="VARCHAR" property="username"/>
    <result column="email" jdbcType="VARCHAR" property="email"/>
  </resultMap>

3、添加映射sql语句：

<select id="selectOrderDTOListByUserId" parameterType="java.lang.Integer" resultMap="OrderDTO">
    <!--
      通过userId查询得到OrderDTO结果集
    -->
    SELECT
    o.order_id as order_id,
    o.order_desc as order_desc,
    o.order_fee as order_fee,
    u.username as username,
    u.email as email
    FROM order_info o, user_info u
    WHERE o.user_id=u.user_id
    AND u.user_id= #{userId}
    order by o.order_fee desc
  </select>

4、测试：

@Test
    public void selectOrderDTOListByUserId() {
        List<OrderDTO> orderDTOList = orderInfoMapper.selectOrderDTOListByUserId(2);
        System.out.println(orderDTOList);
    }

5、结果：

 

附github地址：https://github.com/yaobaron/mybatisdemo