常用的高级sql查询

1.根据主键id数组批量修改

   void updateByIdArr(Integer[] idArr);
   
   <update id="updateByIdArr" parameterType="java.util.Arrays" >
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Tue Nov 01 11:14:38 CST 2016.
    -->
    update t_usermanage_adviser
    set status = 0 
    where id in 
    <foreach collection="array" index="index" item="item" open="(" separator="," close=")">
        #{item}
    </foreach>
    
  </update>

 

 2.批量插入 更新sql

（1）. 弊端
如果传入的更新的值为空，会把相应的值更新为空

  int batchInsertOrUpdate(List<TShopAttachment> attachments);
  
  
  <insert id="batchInsertOrUpdate" parameterType="java.util.List">
    insert into t_shop_attachment
    (
      enterprise_id, shop_id, type_id, file_path, uploader, updater, file_name, upload_date,
      update_date, status
    ) values
    <foreach collection="list" item="item" index="index" separator=",">
      (
      #{item.enterprise_id}, #{item.shop_id}, #{item.type_id}, #{item.file_path}, #{item.uploader},
      #{item.updater}, #{item.file_name}, #{item.upload_date},
      #{item.update_date}, #{item.status}
      )
    </foreach>
    ON DUPLICATE KEY UPDATE
    file_path = VALUES(file_path),
    updater = VALUES(updater),
    file_name = VALUES(file_name),
    update_date = VALUES(update_date)
  </insert>

 

3.关联关系association：1对1关联的三种方法

表结构：

 

order:订单表

order_detail 订单明细表

product 商品表

category 商品分类表

梳理图上的4根关系线：
1、1个用户可以有0个或多个订单：[user表]–>[order表]是1对多(0.n)关系
2、1个有效订单会购买1条或多条商品分录：[order表]–>[order_detail表]是1对多(1.n)关系
3、1条商品分录必定对应一个产品详情：[order_detail表]–>[product表]是1对1关系
4、1个商品分类下可能会有0个或多个商品：[category表]–>[product表]是1对多(0.n)关系
5、所有1对多关系，反过来一定是1对1关系。比如[user表]–>[order表]是1对多(0.n)关系，那么反过来[order表]–>[user表]是1对1关系。每个订单都会有与之对应的唯一用户。
要注意，所有1对1关系，反过来却不一定是1对1关系。比如[order_detail表]–>[product表]是1对1关系，反过来[product表]–>[order_detail表]就不是1对1关系。因为同一个产品可能会在多个不同的订单分录中出现（热门商品大家都愿意买嘛）

[order表]–>[user表]是1对1关系，那么就拿这个来练手。1对1关联关系用于对一个表外键的扩展。
现在要在后台系统中按以下字段显示订单列表。

 

有三种方式可以实现：

一、扩展新建POJO对象，不使用association标签

step1.我们已经有一个与表对应的Order类，但是没有用户名称，用户地址，联系电话这三个字段。现在新建一个类OrderExtend，扩充字段：

public class OrderExtend extends Order{
    public OrderExtend() {
        super();
    }
    /*添加用于展示的用户名称，用户地址，联系电话这三个字段*/
    String username;
    String address;
    String cellphone;

    /*下面get和set方法*/
    getter and setter....
}

 

step2.创建接口及Xml

public interface OrderExtendMapper {
    //查询单个订单详情，关联查询用户信息
    public OrderExtend getByOrderno(String orderno) throws Exception;
    //查询订单列表，关联查询用户信息
    public List<OrderExtend> getList() throws Exception;
}

<mapper namespace="twm.mybatisdemo.mapper.OrderExtendMapper">
    <select id="getByOrderno" parameterType="String"
        resultType="twm.mybatisdemo.pojo.OrderExtend">
        SELECT
        `order`.*,`user`.username,`user`.address,`user`.cellphone
        FROM `order` ,`user`
        WHERE `order`.create_userid=`user`.id AND `order`.create_userid=#{id}
    </select>

    <select id="getList" resultType="twm.mybatisdemo.pojo.OrderExtend">
        SELECT
        `order`.*,`user`.username,`user`.address,`user`.cellphone
        FROM `order`
        ,`user`
        WHERE `order`.create_userid=`user`.id
    </select>
</mapper>

 

step3.调用

public static void main(String[] args) throws Exception {
        SqlSession session = SqlSessionAssist.getSession();

        OrderExtendMapper ordermapper = session
                .getMapper(OrderExtendMapper.class);
        OrderExtend order = ordermapper.getByOrderno("M201209012578917");
        System.out.println(order.getOrderno() + "," + order.getUsername() + ","
                + order.getAddress() + "," + order.getCellphone());
    }

 

二、（推荐）用sql联合查询，使用association标签

public class Order{

    User user;

    public User getUser() {
        return user;
    }
    public void setUser(User user) {
        this.user = user;
    }
    /*下面get和set方法*/
    getter and setter....
}

 

step2.创建映射器接口及配置Xml 
在twm.mybatisdemo.mapper包下创建 
OrderMapper.java:

public interface OrderMapper {
    //查询单个订单详情，关联查询用户信息
    public Order getByOrderno(String orderno) throws Exception;
    //查询订单列表，关联查询用户信息
    public List<Order> getList() throws Exception;
}

 

OrderMapper.xml:

<mapper namespace="twm.mybatisdemo.mapper.OrderMapper">
    <!-- 定义类型映射 -->
    <resultMap type="Order" id="OrderMap">
        <!-- 订单表属性 -->
        <id column="id" property="id" />
        <result column="orderno" property="orderno" />
        <result column="create_time" property="create_time" />
        <result column="create_userid" property="create_userid" />
        <!-- 关联的用户信息 -->
        <!-- association用于关联查询：
        property指属性，javaType是要映射的对象的类型。 -->
        <association property="user" javaType="User">
            <result column="username" property="username" />
            <result column="address" property="address" />
            <result column="cellphone" property="cellphone" />
        </association>
    </resultMap>

    <select id="getByOrderno" parameterType="String"
        resultMap="OrderMap">
        SELECT
        `order`.*,`user`.username,`user`.address,`user`.cellphone
        FROM `order`
        ,`user`
        WHERE `order`.create_userid=`user`.id AND
        `order`.orderno=#{orderno}
    </select>

    <select id="getList" resultMap="OrderMap">
        SELECT
        `order`.*,`user`.username,`user`.address,`user`.cellphone
        FROM `order`
        ,`user`
        WHERE `order`.create_userid=`user`.id
    </select>
</mapper>

 

step3.调用

public static void main(String[] args) throws Exception {
    SqlSession session = SqlSessionAssist.getSession();

    OrderMapper ordermapper = session.getMapper(OrderMapper.class);
    Order order = ordermapper.getByOrderno("M201209012578917");
    System.out.println(order.getOrderno() + ","
            + order.getUser().getUsername() + ","
            + order.getUser().getAddress() + ","
            + order.getUser().getCellphone());

}

 

什么是延迟加载？如果先查询订单信息即可满足业务要求就不会去查询用户，只有当用到用户信息时再查询用户信息。
对用户信息按需去查询就是延迟加载。
比如上面，只有当调用Order中的getUser方法获取关联的user数据时，才会触发数据库查询user表。

mybatis默认没有开启延迟加载，需要在SqlMapConfig.xml中setting配置。
lazyLoadingEnabled：全局性设置懒加载。如果设为‘false’，则所有相关联的都会被初始化加载。允许值有：true | false。默认值：false
aggressiveLazyLoading：当设置为‘true’的时候，懒加载的对象可能被任何懒属性全部加载。否则，每个属性都按需加载。允许值有：true | false。默认值：true

和第二种方式比，其它都不变。只是DAOImplement层有一些变化，XML文件要调整三处：

第一处：新增一个用户查询语句：

<!-- 新增一个用户查询语句：getUser -->
<select id="getUser" parameterType="int" resultType="User">
    SELECT
    `username`,`address`,`cellphone`
    FROM `user`
    WHERE `id` =#{_parameter}
</select>

 

第二处：把原来resultMap的association标签改为

 <association property="user" javaType="User" column="create_userid" select="getUser" /> 

第三处：把getByOrderno和getList查询语句改为普通的select单表查询。

<?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="twm.mybatisdemo.mapper.OrderMapper">
    <!-- 定义类型映射 -->
    <resultMap type="Order" id="OrderMap">
        <!-- 订单表属性 -->
        <id column="id" property="id" />
        <result column="orderno" property="orderno" />
        <result column="create_time" property="create_time" />
        <result column="create_userid" property="create_userid" />
        <!-- 关联的用户信息 -->
        <!-- association用于关联查询： property指属性，javaType是要映射的对象的类型。 -->
        <association property="user" javaType="User" column="create_userid"
            select="getUser" />
    </resultMap>

<!-- 新增一个用户查询：getUser。getUser这一段可以删掉，用user对象的查询方法 -->
<select id="getUser" parameterType="int" resultType="User">
    SELECT
    `username`,`address`,`cellphone`
    FROM `user`
    WHERE `id` =#{_parameter}
</select>

    <select id="getByOrderno" parameterType="String" resultMap="OrderMap">
        SELECT * FROM `order`  WHERE `order`.orderno=#{orderno}
    </select>

    <select id="getList" resultMap="OrderMap">
        SELECT * FROM `order` 
    </select>
</mapper>

 

一切OK了。 
association的几个属性： 
property：指定内部对象属性名 
javaType：内部映射的对象的类型。 
column：要传给select语句的参数，相当于指定外键字段。 
select：指定用户查询语句的ID

 

事实上，大多数业务场景显示的表格，都会用到多个表字段。
如果采用延迟加载，会存在N+1问题。
什么是N+1问题呢？
每一个获取Order内部的User对象，都会进行一次select查询
那么当运行过程中执行Order的getList方法时，SQL首先进行1次查询，查询结果如果有N条订单记录，那么实际在每条订单中显示过程中还要运行一次select用户的查询，共n次。
SQL总共执行了n+1次。相比第二种方法的只进行一次联合查询，这种方式无疑是低效的。
如果业务场景的表格显示字段，并没有跨表，那么可以采用延迟加载方式 

 

<resultMap id="detailMap" type="com.yaoex.usermanage.model.channel.ChannelDetailModel" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="channel_id" property="channel_id" jdbcType="INTEGER" />
    <result column="cust_id" property="cust_id" jdbcType="VARCHAR" />
    <result column="cust_group_id" property="cust_group_id" jdbcType="VARCHAR" />
    
    <association property="custGroup" column="cust_group_id" select="findCustGroup"/>
    <association property="cust" column="cust_id" select="findCust"/>
  </resultMap>

public class ChannelDetailModel extends ChannelDetail {
    
    /**
     * 
     */
    private static final long serialVersionUID = -3176604063524770205L;

    private ChannelCustomer cust;
    
    private CustGroupModel custGroup;
}

  <select id="findCustGroup" resultMap="CustGroupModelMap" parameterType="java.lang.String">
    SELECT id, group_code, group_name FROM t_cust_group WHERE group_code = #{cust_group_id}
  </select>

  <select id="findCust" resultMap="custMap" parameterType="java.lang.String">
    SELECT e.enterprise_id, e.enterprise_name, e.role_type , 
        case e.role_type 
            when 1 then 
                (select a.param_name 
                    from t_usermanage_enterprise_type t, t_usermanage_dict a 
                where t.type_id = a.param_value and t.enterprise_id = e.enterprise_id) 
            else '批发企业' 
        end as customerTypeName
    FROM t_usermanage_enterprise e 
    WHERE e.enterprise_id = #{cust_id}
  </select>

 4.关联关系collection：1对多关联的两种方法

一对多关系 
[order表]–>[order_detail表]是1对多(1.n)关系。1个订单可以由多个订单分录（购买的商品）组成的。1对多关联关系可以理解为一个对象的内部有一个列表。 
在后台系统中我们点击订单列表会进到订单详情页 

有两种方式可以实现：

一、（推荐）用sql联合查询，使用collection标签

sql查询语句是这样的：

SELECT
    `order`.*,`user`.username,`user`.address,`user`.cellphone,
    `order_detail`.productname,`order_detail`.price,`order_detail`.num
    FROM `order`
    ,`user`,`order_detail` 
    WHERE `order`.create_userid=`user`.id AND
    `order_detail`.`order_id`=`order`.id AND 
    `order`.orderno='M201209012578917'

step1.我们已经有一个与表对应的Order类，但是没有分录列表字段。所以在Order类里新增List<OrderDetail> orderdetailList属性：

public class Order {
    Integer id;
    String orderno;
    Integer create_time;
    Integer create_userid;
    User user;

    /* 添加分录列表 */
    List<OrderDetail> orderdetailList;

    /*下面get和set方法*/
    getter and setter....
}

step2.创建接口及Xml 
在twm.mybatisdemo.mapper包下的OrderMapper.xml和OrderMapper.java的基础上改进。 
OrderMapper.java里添加方法:

 //查询单个订单详情，关联查询用户信息以及订单明细 2 public List<Order> getDetailByOrderno(String orderno) throws Exception; 

OrderMapper.xml里添加一个resultMap和一个select:

<resultMap type="Order" id="Contain_DetailList_OrderMap" extends="OrderMap">
    <!-- order中订单信息字段及关联的用户字段，和上面的OrderMap映射一样，这里只是扩充订单明细，extends继承上面的即可 -->
    <!-- 配置关联订单明细信息 -->
    <collection property="orderdetailList" ofType="OrderDetail">
        <id column="id" property="id" />
        <result column="productname" property="productname" />
        <result column="num" property="num" />
        <result column="price" property="price" />
    </collection>
</resultMap>

<select id="getDetailByOrderno" parameterType="String" resultMap="Contain_DetailList_OrderMap">
    SELECT
    `order`.*,`user`.username,`user`.address,`user`.cellphone,
    `order_detail`.productname,`order_detail`.price,`order_detail`.num
    FROM `order`
    ,`user`,`order_detail` 
    WHERE `order`.create_userid=`user`.id AND
    `order_detail`.`order_id`=`order`.id AND 
    `order`.orderno=#{orderno}
</select>

step3.调用

public static void main(String[] args) throws Exception {
        SqlSession session = SqlSessionAssist.getSession();

        OrderMapper ordermapper = session.getMapper(OrderMapper.class);
        Order order = ordermapper.getDetailByOrderno("M201209012578917");
        System.out.println(order.getOrderno() + ","
                + order.getUser().getUsername() + ","
                + order.getUser().getAddress() + ","
                + order.getUser().getCellphone());
        List<OrderDetail> detailList=order.getOrderdetailList();
        for (OrderDetail orderDetail : detailList) {
            System.out.println(orderDetail.getProductname() + ","
                    + orderDetail.getNum() + ","
                    + orderDetail.getPrice());
        }
    }

结果：

M201209012578917,高天仪,北冥山庄,18911110000 iphone 8 plus,3,5700.0

擦~怎么只有一条明细呢?

看mybatis的日志

DEBUG [main] - ==>  Preparing: SELECT 
  order.*,user.username,user.address,user.cellphone, 
  order_detail.productname,order_detail.price,order_detail.num 
  FROM order ,user,order_detail WHERE 
  order.create_userid=user.id AND 
  order_detail.order_id=order.id AND order.orderno=?  DEBUG 
  [main] - ==> Parameters: M201209012578917(String) DEBUG [main] - <== 
  Total: 2

SQL查询出来确实是2条啊。
上网搜了一下，发现问题出在<id column="id" property="id" />
如果两表联查，主表和明细表的主键都是id的话，明细表的多条只能查询出来第一条。
解决办法：在SQL中加上order_detail.id as detail_id，定义一个别名<id column="detail_id" property="id" />
再把<id column="id" property="id" />改为<id column="detail_id" property="id" />

千万不要试图删了Contain_DetailList_OrderMap里的<id column="id" property="id" />这句。这样没有主键，系统不会distinct去重记录的。

二、（不推荐）不使用sql联合查询，通过collection的延迟加载来实现 
在上面的基础上修改： 
step1.添加select片段，根据订单id，查询所有订单明细记录

<select id="SelectOrderDetail" parameterType="int" resultType="OrderDetail">
    select * from `order_detail` where order_id=#{id}
</select>

step2.修改collection 
collection的几个属性： 
property：指定内部对象属性名 
javaType：内部映射的对象的类型。 
column：要传给select语句的参数，相当于指定外键字段。 
select：指定用户查询语句的ID

<resultMap type="Order" id="Contain_DetailList_OrderMap" extends="OrderMap">
    <!-- order中订单信息字段及关联的用户字段，和上面的OrderMap映射一样，这里只是扩充订单明细，extends继承上面的即可 -->
    <!-- 配置关联订单明细信息 -->
    <collection property="orderdetailList" select="SelectOrderDetail" column="id" />
</resultMap>

step3.修改getDetailByOrderno的select片段，删除联查order_detail相关的部分

<select id="getDetailByOrderno" parameterType="String" resultMap="Contain_DetailList_OrderMap">
    SELECT `order`.*,`user`.username,`user`.address,`user`.cellphone    
    FROM `order` ,`user`
    WHERE `order`.create_userid=`user`.id and
    `order`.orderno=#{orderno}
</select>

运行结果：

DEBUG [main] - ==>  Preparing: SELECT 
  order.*,user.username,user.address,user.cellphone FROM order 
  ,user WHERE order.create_userid=user.id and order.orderno=?  
  DEBUG [main] - ==> Parameters: M201209012578917(String) DEBUG [main] - 
  ====>  Preparing: select * from order_detail where order_id=?  DEBUG [main] - ====> Parameters: 1(Integer) DEBUG [main] - <====      Total: 
  2 DEBUG [main] - <==      Total: 1 
  M201209012578917,高天仪,北冥山庄,18911110000 iphone 8 plus,3,5700.0 
  华为G7,2,2700.0

可以看到存在N+1问题，只不过这种场景下N永远是1。 
原来用一个联合查询可以解决的，现在需要用两次查询。

  <resultMap id="ChannelDetailResultCustMap" type="com.yaoex.usermanage.model.channel.ChannelProduct" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="seller_id" property="seller_id" jdbcType="VARCHAR" />
    <result column="product_id" property="product_id" jdbcType="VARCHAR" />
    <result column="channel_type" property="channel_type" jdbcType="INTEGER" />
    <result column="channel_status" property="channel_status" jdbcType="INTEGER" />
    <result column="create_user" property="create_user" jdbcType="VARCHAR" />
    <result column="create_time" property="create_time" jdbcType="TIMESTAMP" />
    <result column="update_user" property="update_user" jdbcType="VARCHAR" />
    <result column="update_time" property="update_time" jdbcType="TIMESTAMP" />
    
    <result column="spu_code" property="spu_code" jdbcType="VARCHAR" />
    <result column="product_code" property="product_code" jdbcType="VARCHAR" />
    <result column="product_name" property="product_name" jdbcType="VARCHAR" />
    <result column="short_name" property="short_name" jdbcType="VARCHAR" />
    <result column="factory_name" property="factory_name" jdbcType="VARCHAR" />
    <result column="spec" property="spec" jdbcType="VARCHAR" />
    <result column="drugform" property="drugform" jdbcType="VARCHAR" />
      <!--javatype指定的是user对象的属性的类型（例如id，canBuyCust），而oftype指定的是映射到list集合属性中pojo的类型（本例指的是ChannelCustomer类型）-->
    <collection property="canBuyCust" ofType="com.yaoex.usermanage.model.channel.ChannelCustomer" column="{id=id,canBuyFlag=can_buy}" select="findCanBuyCustWithCust"/>
    <collection property="canNotBuyCust" ofType="com.yaoex.usermanage.model.channel.ChannelCustomer" column="{id=id,canBuyFlag=cannot_buy}" select="findCanBuyCustWithCust"/>
  </resultMap>

 

public class ChannelProduct extends Channel {
    /**
     * 
     */
    private static final long serialVersionUID = 1595115735063349179L;

    private String spu_code;
    
    private String product_code;
    
    private String product_name;
    
    private String short_name;
    
    private String factory_name;
    
    private String spec;
    
    private String drugform;
    
    private Integer canNotBuyCustNum;
    
    private List<ChannelCustomer> canBuyCust;
    
    private List<ChannelCustomer> canNotBuyCust;
}

  <!-- 按客户添加的渠道    查看客户-->
  <select id="findCanBuyCustWithCust" resultMap="customerResultMap" parameterType="map">
    SELECT t.cust_id, t.cust_group_id, e.enterprise_id, e.enterprise_name, e.role_type,
        case e.role_type 
            when 1 then 
                (select a.param_name 
                    from t_usermanage_enterprise_type t, t_usermanage_dict a 
                where t.type_id = a.param_value and t.enterprise_id = e.enterprise_id) 
            else '批发企业' 
        end as customerTypeName 
    FROM t_channel_detail t left join t_usermanage_enterprise e on e.enterprise_id = t.cust_id

    WHERE t.channel_id = #{id} and t.can_buy_flag = #{canBuyFlag} and t.is_condition = 0 

  </select>

 5、批量更新sql:

（1）根据一个字段批量更新：List<String> channelIdSet

        Map<String,Object> params = new HashMap<>();
        params.put("channelIdSet", channelIdSet);
        this.baseDao.update("com.yaoex.usermanage.persistence.interfaces.channel.ChannelMapper.batchDeleteChannel", params);

    <update id="batchDeleteChannel" parameterType="map">
        update t_channel
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="is_delete = case" suffix="end,">
                <foreach collection="channelIdSet" item="item">
                    when id=#{item} then 1
                </foreach>
            </trim>
        </trim>
        <where>
            and id in
            <foreach collection="channelIdSet" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </where>
    </update>

 （2）根据多个字段同时更新：Set<ProductUniqueBean> productUniqueBeans

    @Override
    public void batchDeleteChannelApply(Set<ProductUniqueBean> productUniqueBeans) {
        Map<String,Object> params = new HashMap<>();
        params.put("productUniqueBeans", productUniqueBeans);
        this.baseDao.update("com.yaoex.usermanage.persistence.interfaces.channelapply.ChannelApplyMapper.batchDeleteChannelApply", params);
    }

    <update id="batchDeleteChannelApply" parameterType="map">
        update t_channel_apply
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="is_delete = case" suffix="end,">
                <foreach collection="productUniqueBeans" item="item">
                    when spu_code=#{item.spuCode} and seller_code=#{item.sellerCode} then 1
                </foreach>
            </trim>
        </trim>
        <where>
            and (spu_code,seller_code) in
            <foreach collection="productUniqueBeans" item="item" open="(" separator="," close=")">
                (#{item.spuCode},#{item.sellerCode})
            </foreach>
        </where>
    </update>

 

 

6、统计数量方法

SELECT IFNULL(SUM(if(d.role_type = 1,2,0)),0) roleType from t_usermanage_enterprise_dft d ;

SELECT IFNULL(SUM(d.role_type = 1),0) roleType from t_usermanage_enterprise_dft d;

SELECT COUNT(*) from t_usermanage_enterprise_dft d WHERE d.role_type = 1;

 

7、时间范围搜索

（1）

AND STR_TO_DATE(#{now},"%Y-%m-%d") >= STR_TO_DATE(t2.start_time,"%Y-%m-%d")
AND STR_TO_DATE(t2.end_time,"%Y-%m-%d") >= STR_TO_DATE(#{now},"%Y-%m-%d")

 （2）

      <if test="startTime != null and startTime != ''">
            <![CDATA[  and updateTime >=  #{startTime} ]]>
      </if>
      <if test="endTime != null and endTime != ''">
            <![CDATA[  and updateTime <=  #{endTime} ]]>
      </if>