sql

最新推荐文章于 2024-06-27 18:08:19 发布

王梦_wm

最新推荐文章于 2024-06-27 18:08:19 发布

阅读量178

点赞数

分类专栏：技术

技术专栏收录该内容

33 篇文章 0 订阅

订阅专栏

1 Mybatis中使用Collection

<insert id="insertWxpublishs">
    insert into wxpublish(wxpublishno,edidate) values
    <foreach collection="list" index="index" item="item" open="" close="" separator=",">
       (#{item.wxpublishno,jdbcType=VARCHAR}, #{item.edidate,jdbcType=TIMESTAMP})
      </foreach>
</insert>

Mybatis中使用Collection元素进行一对多级联查询:

https://blog.csdn.net/hero_cheng/article/details/53540703

2 起别名as不是一定要写的

<select id="selectWxOrderPickingInfoByfhid" resultMap="WxOrderPicking_BaseResultMap"
        parameterType="java.lang.Integer">
    select wxorders.ordercode ordercode,wxorders.mdmc mdmc，wxorderdetail.cm cm,spmx.mxcode mxcode,fbspbatch.batchname spmc,
    wxorderbatch.batchid batchid,
    wxorderbatch.batchcode as fbbatchcode,
    (case when realbatchid is null then wxorderbatch.batchcode else  spbatch.batchcode end) batchcode,
    (case when realbatchid is null then (case when fbspbatch.pkgtype=0 then fbbatchkw.kw else mxkw.kw end)  else batchkw.kw end) kw
    FROM wxorderbatch
    left join wxorders on (wxorders.id = wxorderbatch.orderid)
    left join wxorderdetail on (wxorderbatch.id = wxorderdetail.batchorderid)
    left join spbatchdetail on (wxorderdetail.spbatchdetailid = spbatchdetail.id )
    left join spmx on (spbatchdetail.mxid = spmx.id )
    left join (
    select mxid,kw=stuff((select ','+kw from spmxkw where mxid=t.mxid for xml path('')),1,1,'')
    from spmxkw t group by mxid
    ) as mxkw on (spmx.id = mxkw.mxid)
    left join (
    select fbbatchcode,batchcm,realbatchid=stuff((select ','+CAST(id AS varchar(50)) from spbatch
    where (fbbatchcode=sb.fbbatchcode) and (batchcm=sb.batchcm) for xml path('')),1,1,'')
    from spbatch sb where LEN(fbbatchcode) >0 and LEN(batchcm) >0
    group by fbbatchcode,batchcm
    ) tempspbatch  on tempspbatch.fbbatchcode = wxorderbatch.batchcode and tempspbatch.batchcm=wxorderdetail.cm
    left join spbatch on tempspbatch.realbatchid = spbatch.id
    left join spbatch fbspbatch on wxorderbatch.batchid = fbspbatch.id
    LEFT JOIN
    (
    select batchid,kw=stuff((select ','+ kw from (select batchid,kw from sppackage where status=2 group by batchid,kw
    ) pp where batchid=t.batchid for xml path('')),1,1,'')
    from (select batchid,kw from sppackage where status=2 group by batchid,kw) t group by batchid
    ) as batchkw
    ON batchkw.batchid = tempspbatch.realbatchid
    LEFT JOIN (
    select batchid,dq,kw=stuff((select ','+ kw from (select batchid,dq,kw from sppackage where status=2 group by batchid,dq,kw) pp where batchid=t.batchid and dq=t.dq  for xml path('')),1,1,'')
    from (select batchid,dq,kw from sppackage where status=2 group by batchid,dq,kw) t group by batchid,dq
    ) as fbbatchkw
    ON fbbatchkw.batchid = wxorderbatch.batchid and fbbatchkw.dq=wxorderdetail.cm
    where wxorderbatch.fhid = #{fhid,jdbcType=INTEGER}
</select>

好了，现在开始具体说一下用法：

①stuff：
1、作用
stuff(param1, startIndex, length, param2)
将param1中自startIndex(SQL中都是从1开始，而非0)起，删除length个字符，然后用param2替换删掉的字符。

2、参数
param1
一个字符数据表达式。param1可以是常量、变量，也可以是字符列或二进制数据列。
startIndex
一个整数值，指定删除和插入的开始位置。如果 startIndex或 length 为负，则返回空字符串。如果startIndex比param1长，则返回空字符串。startIndex可以是 bigint 类型。
length
一个整数，指定要删除的字符数。如果 length 比param1长，则最多删除到param1 中的最后一个字符。length 可以是 bigint 类型。

3、返回类型
如果param1是受支持的字符数据类型，则返回字符数据。如果param1是一个受支持的 binary 数据类型，则返回二进制数据。
4、备注
如果结果值大于返回类型支持的最大值，则产生错误。

eg：

[sql]view plain copy
select STUFF('abcdefg',1,0,'1234')       --结果为'1234abcdefg'  
select STUFF('abcdefg',1,1,'1234')       --结果为'1234bcdefg'  
select STUFF('abcdefg',2,1,'1234')       --结果为'a1234cdefg'  
select STUFF('abcdefg',2,2,'1234')       --结果为'a1234defg'  

通过以上4个小例子，应该能明白stuff的用法了。

②for xml path:

for xml path有的人可能知道有的人可能不知道，其实它就是将查询结果集以XML形式展现，有了它我们可以简化我们的查询语句实现一些以前可能需要借助函数活存储过程来完成的工作。那么以一个实例为主.

我们还是通过列子引入：

假设有个表存放着学生的选课情况（stu_courses）：

接下来我们来看应用FOR XML PATH的查询结果语句如下：

[sql]view plain copy
select stu_name,stu_course from stu_courses for xml path;  

结果如下：

[html]view plain copy
<row>  
  <stu_name>张三</stu_name>  
  <stu_course>数学</stu_course>  
</row>  
<row>  
  <stu_name>张三</stu_name>  
  <stu_course>语文</stu_course>  
</row>  
<row>  
  <stu_name>张三</stu_name>  
  <stu_course>英语</stu_course>  
</row>  
<row>  
  <stu_name>李四</stu_name>  
  <stu_course>数学</stu_course>  
</row>  
<row>  
  <stu_name>李四</stu_name>  
  <stu_course>语文</stu_course>  
</row>  

由此可以看出 FOR XML PATH 可以将查询结果根据行输出成XML各式！而且我们还可以改变XML行节点的名称，代码如下：

[sql]view plain copy
select stu_name,stu_course from stu_courses for xml path('course');  

看显示结果，原来的行节点<row> 变成了我们在PATH后面括号()中自定义的名称<course>：

[html]view plain copy
<course>  
  <stu_name>张三</stu_name>  
  <stu_course>数学</stu_course>  
</course>  
<course>  
  <stu_name>张三</stu_name>  
  <stu_course>语文</stu_course>  
</course>  
<course>  
  <stu_name>张三</stu_name>  
  <stu_course>英语</stu_course>  
</course>  
<course>  
  <stu_name>李四</stu_name>  
  <stu_course>数学</stu_course>  
</course>  
<course>  
  <stu_name>李四</stu_name>  
  <stu_course>语文</stu_course>  
</course>  

其实我们还可以改变列节点，还记的给列起别名的关键字AS吗？就是用它!代码如下：

[sql]view plain copy
select stu_name as MyName,stu_course as MyCourse from stu_courses for xml path('course');  

显示结果：

[html]view plain copy
<course>  
  <MyName>张三</MyName>  
  <MyCourse>数学</MyCourse>  
</course>  
<course>  
  <MyName>张三</MyName>  
  <MyCourse>语文</MyCourse>  
</course>  
<course>  
  <MyName>张三</MyName>  
  <MyCourse>英语</MyCourse>  
</course>  
<course>  
  <MyName>李四</MyName>  
  <MyCourse>数学</MyCourse>  
</course>  
<course>  
  <MyName>李四</MyName>  
  <MyCourse>语文</MyCourse>  
</course>  

我们还可以构建我们喜欢的输出方式，看代码：

[sql]view plain copy
select '['+stu_name+','+stu_course+']' from stu_courses for xml path('');  

显示结果：

[plain]view plain copy
[张三,数学][张三,语文][张三,英语][李四,数学][李四,语文] 

3. association

<association property="cgdan" column="cgid"
             select="com.xileku.dao.CgdanMapper.selectByPrimaryKey"></association>

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

这个不需要新建扩展类了。在Order类中，新增一个User类型的属性，将查询出来的用户相关数据通过association标签映射到user。 association专门用来建立1对1关联关系。其中 property：指定对象的属性名 javaType：指定要映射的对象的类型。

step1.在Order类中，新增一个User类型的属性

public class OrderExtend extends Order{
    /*添加用于展示的用户名称，用户地址，联系电话这三个字段*/
    String username;
    String address;
    String cellphone;
    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>

三、不用sql联合查询，通过association的延迟加载来实现

什么是延迟加载？如果先查询订单信息即可满足业务要求就不会去查询用户，只有当用到用户信息时再查询用户信息。
对用户信息按需去查询就是延迟加载。
比如上面，只有当调用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

getUser用户查询这一段语句也可以省略，因为之前在twm.mybatisdemo.mapper.UserMapper(UserMapper.xml)中创建过一个selectById查询。所以这里可以删掉getUser那一段查询，把association改一下：

<association property="user" javaType="User" column="create_userid" select="twm.mybatisdemo.mapper.UserMapper.selectById" />

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

本文博客地址： http://blog.csdn.net/soonfly/article/details/63688288 （转载请注明出处）