写了史上遇到最复杂的sql 在Mybatis中实现不同条件对应sql的变化

最新推荐文章于 2023-04-11 16:33:36 发布
最新推荐文章于 2023-04-11 16:33:36 发布
阅读量564 收藏
点赞数
分类专栏： mybatis
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_32157851/article/details/102627570
版权

    由于使用mysql进行开发，然后进行分页的时候，可以使用select found_rows();来获取上一条sql执行的查询总数，但是这个需要和SQL_CALC_FOUND_ROWS count( * ) AS howmany,  这个一起使用才可以，并且这两个sql不能在Mybatis中的不同的两个  <select></>标签中配合使用，只能在一个select中，所以，有了下面的 <select id="getResult" resultMap="BaseResultMap1,BaseResultMap2">  </>这种形式。然后在 dao中通过List<List<?>> 来得到值，并且通过 list.get(0)获取对象列表，通过lsit.get(1).get(0) 获取总行数。

 

 

<?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="AlarmsGroupView">

    <resultMap id="BaseResultMap" type="com.qzt.model.entity.AlarmsGroupView">
        <id column="group_id" property="groupId" jdbcType="VARCHAR" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="howmany" property="howMany" jdbcType="INTEGER" />
        <result column="max_risk" property="maxRisk" jdbcType="INTEGER" />
    </resultMap>

    <resultMap id="count" type="java.lang.Integer">
        <id column="count" />
    </resultMap>

    <resultMap id="SensorMap" type="com.qzt.model.entity.Sensor">
        <id column="id" property="id" jdbcType="VARCHAR" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="ip" property="ip" jdbcType="VARCHAR" />
    </resultMap>

    <resultMap id="LableMap" type="com.qzt.model.entity.Lable">
        <id column="id" property="id" jdbcType="VARCHAR" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="type" property="type" jdbcType="VARCHAR" />
        <result column="ctx" property="ctx" jdbcType="VARCHAR" />
    </resultMap>

    <resultMap id="IntentMap" type="com.qzt.model.entity.Intent">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="Base_Column_List">
        <if test="alarmsNameDate != null and alarmsNameDate != ''">
            ,date (alarm_event.timestamp ) as day
        </if>
        <if test="alarmsNameSdstDate != null and alarmsNameSdstDate != ''">
            ,inet6_ntoa( alarm_event.src_ip ) AS src_ip
            ,inet6_ntoa( alarm_event.dst_ip ) AS dst_ip
            ,date (alarm_event.timestamp ) as day
        </if>
    </sql>

    <sql id="Base_Column_List_select">
        <if test="alarmsNameDate != null and alarmsNameDate != ''">
            ,convert_tz(a.timestamp,'+00:00','+8:00' ) as timestamp
        </if>
        <if test="alarmsNameSdstDate != null and alarmsNameSdstDate != ''">
        ,a.src_ip
        ,a.dst_ip
        ,convert_tz(a.timestamp,'+00:00','+8:00' ) as timestamp
        </if>
    </sql>

    <sql id="Base_Column_List_table">
        <if test="assetGroup != null and assetGroup != ''">
            left join alarm_hosts ah on  a.backlog_id = ah.id_alarm
        </if>
        <if test="sensor != null and sensor != ''">
            ,event e , backlog_event be
        </if>
        <if test="label != null and label != ''">
            ,component_tags tg
        </if>
        <if test="show1 != null and show1 != ''">
            ,alarm_groups g
        </if>
    </sql>


    <sql id="Base_Column_List_similar_table">
        <if test="assetGroup != null and assetGroup != ''">
            left join alarm_hosts ah on  a.backlog_id = ah.id_alarm
        </if>
        <if test="show1 != null and show1 != ''">
            ,alarm_groups g
        </if>
    </sql>


    <sql id="Base_Column_List_group">
        <if test="alarmsNameGroup != null and alarmsNameGroup != ''">
            name
        </if>
        <if test="alarmsNameDate != null and alarmsNameDate != ''">
            name,day
        </if>
        <if test="alarmsNameSdstDate != null and alarmsNameSdstDate != ''">
            name, src_ip, dst_ip, day
        </if>
    </sql>
    <sql id="Base_Column_List_order">
        <if test="alarmsNameGroup != null and alarmsNameGroup != ''">
            name
        </if>
        <if test="alarmsNameDate != null and alarmsNameDate != ''">
            alarm_event.timestamp
        </if>
        <if test="alarmsNameSdstDate != null and alarmsNameSdstDate != ''">
            alarm_event.timestamp 
        </if>
        <if test="sensor != null and sensor != ''">
            AND e.sensor_id=unhex(#{sensor}) AND be.event_id=e.id AND be.backlog_id=a.backlog_id
        </if>
    </sql>
    <sql id="page_sql">
        <if test="pageSize!=null and offsetNum!=null">
            limit #{pageSize} offset #{offsetNum}
        </if>
    </sql>

    <select id="getAlarmsGrupView" resultMap="BaseResultMap,count" parameterType="java.util.Map">

        
      SELECT SQL_CALC_FOUND_ROWS count( * ) AS howmany,
        sha1( alarm_event.name ) AS group_id,
        alarm_event.name AS name,
        max(alarm_event.risk) as max_risk
        <include refid="Base_Column_List"/>

        FROM (
        (SELECT DISTINCT a.backlog_id, <![CDATA[ IF(a.plugin_id<>1505,IFNULL(p.name, ''),IFNULL(CONCAT(ki.name,' &mdash; ',ca.name,' &mdash; ',ta.subcategory), IFNULL(p.name, ''))) AS name, ]]>  a.risk
        <include refid="Base_Column_List_select"/>
         FROM alarm a
        LEFT JOIN plugin_sid p ON a.plugin_id = p.plugin_id AND a.plugin_sid = p.sid
        LEFT JOIN ( alarm_taxonomy ta LEFT JOIN alarm_kingdoms ki ON ta.kingdom=ki.id LEFT JOIN alarm_categories ca ON ta.category=ca.id ) ON a.plugin_sid=ta.sid AND a.corr_engine_ctx=ta.engine_id
        <include refid="Base_Column_List_table"/>
          ,backlog b
        WHERE 1=1
         AND a.status='open'
         AND a.backlog_id=b.id AND <![CDATA[b.timestamp<>'1970-01-01 00:00:00']]>
         <if test="alarmName != null and alarmName != ''">
             AND (p.name LIKE '%${alarmName}%' or ki.name LIKE '%${alarmName}%' or ca.name LIKE '%${alarmName}%' or ta.subcategory LIKE '%${alarmName}%')
         </if>

        <if test="sourceIpaddr != null and sourceIpaddr != ''">
           and  inet6_aton(#{sourceIpaddr}) = a.src_ip
        </if>
        <if test="destIPaddr != null and destIPaddr != ''">
            and  inet6_aton(#{destIPaddr}) = a.dst_ip
        </if>
        <if test="assetGroup != null and assetGroup != ''">

            AND ah.id_host in (SELECT host_id FROM host_group_reference WHERE host_group_id=UNHEX(#{assetGroup}))
        </if>
        <if test="startTime != null and startTime != ''">
            AND convert_tz(a.timestamp,'+00:00','+8:00') <![CDATA[ >= ]]> #{startTime}
        </if>
        <if test="endTime != null and endTime != ''">
            AND convert_tz(a.timestamp,'+00:00','+8:00') <![CDATA[ <= ]]> #{endTime}
        </if>
        <if test="sensor != null and sensor != ''">
            AND e.sensor_id=unhex(#{sensor}) AND be.event_id=e.id AND be.backlog_id=a.backlog_id
        </if>
        <if test="intent != null and intent  != ''">
            and ta.kingdom = ${intent}
        </if>
        <if test="directiveId != null and directiveId != ''">
            and b.directive_id = ${directiveId}
        </if>
        <if test="greaterThanEqual != null and greaterThanEqual != ''">
            AND a.backlog_id in (select backlog_id from (SELECT count(*) as total_events,backlog_event.backlog_id FROM backlog_event, event
            WHERE event.id = backlog_event.event_id AND event.plugin_id  <![CDATA[ <>  ]]>  1505 GROUP BY backlog_id having total_events  <![CDATA[ >= ]]> ${eventNum}) as bk)
        </if>
        <if test="lessThanEqual != null and lessThanEqual != ''">
            AND a.backlog_id in (select backlog_id from (SELECT count(*) as total_events,backlog_event.backlog_id FROM backlog_event, event
            WHERE event.id = backlog_event.event_id AND event.plugin_id  <![CDATA[ <>  ]]>    1505 GROUP BY backlog_id having total_events  <![CDATA[ <= ]]> ${eventNum}) as bk)
        </if>
        <if test="label != null and label != ''">
            AND a.backlog_id=tg.id_component AND tg.id_tag=UNHEX(#{label})
        </if>
        <if test="show1 != null and show1 != ''">
            <![CDATA[
            AND CONCAT( sha1( IFNULL(CONCAT(ki.name,' &mdash; ',ca.name,' &mdash; ',ta.subcategory), IFNULL(p.name, '')) ),
            CONCAT( 'S', hex(a.src_ip), 'D', hex( a.dst_ip), DATE( convert_tz(a.timestamp,'+00:00','+8:00') ) ) )=g.group_id AND g.owner='admin'
        ]]>
        </if>
        <if test="show2 != null and show2 != ''">

            <![CDATA[
            AND CONCAT( sha1( IFNULL(CONCAT(ki.name,' &mdash; ',ca.name,' &mdash; ',ta.subcategory), IFNULL(p.name, '')) ),
            CONCAT( 'S', hex(a.src_ip), 'D', hex( a.dst_ip), DATE( convert_tz(a.timestamp,'+00:00','+8:00') ) ) )
            NOT IN(Select group_id FROM alarm_groups WHERE owner IS NOT NULL AND owner<>'')
            ]]>
        </if>
        <if test="show3 != null and show3 != ''">
        <![CDATA[
            AND CONCAT( sha1( IFNULL(CONCAT(ki.name,' &mdash; ',ca.name,' &mdash; ',ta.subcategory), IFNULL(p.name, '')) ),
            CONCAT( 'S', hex(a.src_ip), 'D', hex( a.dst_ip), DATE( convert_tz(a.timestamp,'+00:00','+8:00') ) ) )
            NOT IN(Select group_id FROM alarm_groups WHERE owner<>'admin' AND owner IS NOT NULL AND owner<>'')

            ]]>
        </if>
        )
        ) as alarm_event
        GROUP BY <include refid="Base_Column_List_group"/>
        <if test="riskLevel != nulll and riskLevel != ''">
            having max_risk <![CDATA[ >= ]]> ${riskLevel}
        </if>
        ORDER BY <include refid="Base_Column_List_order"/> DESC
         
        <include refid="page_sql"/>;
        select found_rows() as count ;

    </select>

    <select id="getAlarmsGrupViewBySimilar" resultMap="BaseResultMap,count" parameterType="java.util.Map">

        SELECT SQL_CALC_FOUND_ROWS distinct a.similar AS group_id, <![CDATA[ IF(a.plugin_id<>1505,p.name,IFNULL(CONCAT(ki.name,' &mdash; ',ca.name,' &mdash; ',ta.subcategory), p.name)) AS name, ]]>
        max(a.risk) as max_risk,
         count(distinct a.similar) as howmany
        FROM alarm a
        LEFT JOIN plugin_sid p ON a.plugin_id = p.plugin_id AND a.plugin_sid = p.sid
        LEFT JOIN ( alarm_taxonomy ta LEFT JOIN alarm_kingdoms ki ON ta.kingdom=ki.id LEFT JOIN alarm_categories ca ON ta.category=ca.id ) ON a.plugin_sid=ta.sid
        AND a.corr_engine_ctx=ta.engine_id
        <include refid="Base_Column_List_similar_table"/>
        ,backlog b
        WHERE 1=1
        AND a.status='open'
        AND a.backlog_id=b.id AND  <![CDATA[  b.timestamp<>'1970-01-01 00:00:00' ]]>

        <if test="assetGroup != null and assetGroup != ''">

            AND ah.id_host in (SELECT host_id FROM host_group_reference WHERE host_group_id=UNHEX(#{assetGroup}))
        </if>
        <if test="sensor != null and sensor != ''">
            AND e.sensor_id=unhex(#{sensor}) AND be.event_id=e.id AND be.backlog_id=a.backlog_id
        </if>
        <if test="intent != null and intent  != ''">
            and ta.kingdom = ${intent}
        </if>
        <if test="directiveId != null and directiveId != ''">
            and b.directive_id = ${directiveId}
        </if>
        <if test="show1 != null and show1 != ''">
            <![CDATA[
           AND a.similar=g.group_id AND g.owner='admin'
        ]]>
        </if>
        <if test="show2 != null and show2 != ''">

            <![CDATA[
           AND a.similar NOT IN(Select group_id FROM alarm_groups WHERE owner IS NOT NULL AND owner<>'')
            ]]>
        </if>
        <if test="show3 != null and show3 != ''">
            <![CDATA[
           AND a.similar NOT IN(Select group_id FROM alarm_groups WHERE owner<>'admin' AND owner IS NOT NULL AND owner<>'')

            ]]>
        </if>
        GROUP BY a.similar
        ORDER BY a.timestamp DESC

        <include refid="page_sql"/>;
        select found_rows() as count ;

    </select>

    <select id="getSensor" parameterType="java.util.Map" resultMap="SensorMap">
            SELECT sensor.name, HEX(sensor.id) AS s_id, INET6_NTOA(sensor.ip) AS sensor_ipd, sensor_properties.*
            FROM sensor, sensor_properties
            WHERE sensor.id = sensor_properties.sensor_id
            <if test="sensorName != null and sensorName != ''">
                and sensor.name= #{sensorName} and inet6_aton(#{sensorIp}) = sensor.ip
            </if>
            AND sensor.name != '(null)'  ORDER BY name
    </select>

    <select id="getLabel" parameterType="java.util.Map" resultMap="LableMap">
        SELECT DISTINCT  name,HEX(id) AS id, HEX(ctx) AS ctx FROM tag  WHERE 1 = 1 AND type = 'alarm'
        <if test="label != null and label != ''">
            and name=#{label}
        </if>
    </select>

    <select id="getIntent" resultMap="IntentMap">
        select * from alarm_kingdoms
    </select>

</mapper>

下面是dao中的代码： 

    public List<List<?>> getAlarmsGrupView(Map params){
        List<List<?>> list = new ArrayList<>();

        try {
            list = sqlSessionTemplate2.selectList("AlarmsGroupView.getAlarmsGrupView", params);
        } catch (Exception e) {
            log.error("error",e);
        }

        return list;
    }

 

非衣鲲化
关注
专栏目录
Spring Boot入门(14)：使用Mybatis-Plus轻松实现高效自定义SQL操作！
**My Coding Family**
08-20 3万+
如何使用Mybatis-Plus执行自定义SQL，一文教会你。
Spring Boot入门(20)：使用Mybatis-plus实现Spring Boot多数据源配置，轻松实现数据库读写分离！
**My Coding Family**
08-22 1万+
如何实现多数据源的配置及项目使用，此文教会你，超级简单，开箱即用。
Mybatis---复杂sql应用
qq_52208113的博客
04-04 306
1、多对一处理 编写实体类 package com.ss.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; @Data @NoArgsConstructor @AllArgsConstructor public class Student { private int id; private String name; private
MyBatis动态SQL（认真看看，以后写SQL就容易多了）
yuandengta的博客
08-21 272
mybatis最新源码和注释 MyBatis 令人喜欢的一大特性就是动态 SQL。在使用 JDBC 的过程中， 根据条件进行 SQL 的拼接是很麻烦且很容易出错的。MyBatis 动态 SQL 的出现， 解决了这个麻烦。 MyBatis通过 OGNL 来进行动态 SQL 的使用的。 目前， 动态 SQL 支持以下几种标签 1 数据准备 为了后面的演示， 创建了一个 Maven 项目 mybatis-dynamic, 创建了对应的数据库和表 DROPTABLEIFEXI...
05 My Batis 复杂映射
蟑螂恶霸的博客
01-20 1682
1 动态SQL拼接 2 XML文件映射HelloWord MyBatis提供了注解定义SQL与Mapper接口的映射关系，还提供了利用XML文件映射SQL与接口方法的关系。相对于注解方式，XML文件的更加灵活强大，可以实现复杂的动态SQL拼接，以及复杂结果的映射功能。 首先利用XML文件实现一个HelloWorld查询，体验以下基本的XML文件映射功能。在DemoMapper接口上定义一个test方法，此时方法上不用注解声明SQL： package cn.tedu.mapper; import org.a
Springboot2+Quartz+MybatisPlus+easymock
公众号 Java4ye
01-15 794
S10那天, 我往DB里插入了两百多万数据
Mybatis---动态SQL(拼接sql语句根据不同的条件生成不同的SQL语句进行复杂查询)
AJ_007的博客
03-11 3722
1.动态SQL 1.1 什么是动态SQL 动态SQL就是指根据不同的条件生成不同的SQL语句 1.2 环境搭建 1.数据库表 -- auto-generated definition create table blog ( id varchar(50) not null comment '博客id', title varchar(100) not nul...
史上最全Mybatis面试题
olol666888的博客
04-06 1635
史上最全Mybatis面试题 1、什么是Mybatis？ （1）Mybatis是一个半ORM（对象关系映射）框架，它内部封装了JDBC，开发时只需要关注SQL语句本身，不需要花费精力去处理加载驱动、创建连接、创建statement等繁杂的过程。程序员直接编写原生态sql，可以严格控制sql执行性能，灵活度高。 （2）MyBatis 可以使用 XML 或注解来配置和映射原生信息，将 POJO映射成数...
Spring Boot入门(10)：Mybatis之XML映射文件中如何使用特殊符号？揭秘＜、＜=等符号的正确使用方法！
热门推荐
**My Coding Family**
08-21 2万+
Mybatis之XML映射文件中如何使用特殊符号？揭秘＜、＜=等符号的正确使用方法
史上最全MyBatis面试题及答案
liyanfang0310的博客
06-21 1012
1、什么是MyBatis？ MyBatis是一个可以自定义SQL、存储过程和高级映射的持久层框架。 2、讲下MyBatis的缓存 MyBatis的缓存分为一级缓存和二级缓存,一级缓存放在session里面,默认就有,二级缓存放在它的命名空间里,默认是不打开的,使用二级缓存属性类需要实现Serializable序列化接口(可用来保存对象的状态),可在它的映射文件中配置 3、Mybatis是如何进行分页的？分页插件的原理是什么？ Mybatis使用RowBounds对象进行分页，也可以直接编写sql实现分页，
mybatis关联一对多查询时，FOUND_ROWS（）方法输出错误
SimpleCow的博客
07-13 2526
情况 上周五在敲代码的时候，发现一个列表的总数输出有问题，不是1就是0，根本不是我想要的数。 使用的是下面的这两个 SQL_CALC_FOUND_ROWS FOUND_ROWS() 原因 我是将查询列和查询总数分为两个方法进行调用的，先进行列查询，然后再使用FOUND_ROWS()方法进行总数查询。 这就导致了，我没有连续执行包含“SQL_CALC_FOUND_ROWS”和FOUND_ROWS()这两个，中间相隔了若干个关联查询的sql。 经过测试，无论是否书写“SQL_CALC_FOUND
mybatis一次执行多条语句报错问题
qq_36375934的博客
03-24 1290
分页查询，同时返回总记录数时使用如下查询： select SQL_CALC_FOUND_ROWS * from user limit 0,10; SELECT FOUND_ROWS() as count; 出现报错信息，且报错信息是第二个语句。 ### The error may involve defaultParameterMap ### The error occurred while setting parameters com.mysql.jdbc.exceptions.jdbc4.
【采坑】mybatis分页同时获取总记录数，报错java.sql.SQLException: Column 'count' not found.
weixin_41786574的博客
03-06 771
我的xml代码 <?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="com.homew...
JavaEE实训项目：Mybatis实现多条件查询
reasonable_LX的博客
02-15 3661
（1）多条件查询： 当用户输入的学生姓名不为空时，则只根据学生姓名进行学生信息的查询 当用户输入的学生姓名为空而学生专业不为空时，则只根据学生专业进行学生信息查询 当用户输入的学生姓名和专业都为空，则要求查询出所有学号不为空的学生信息 （2）查询出id值小于5的学生信息
mybatis的left join多条件操作
人生到处知何似，应似飞鸿踏雪泥。
08-26 9032
一. 背景 小熙在修改一个bug的时候，想直接在左连接后面加上条件，但是深思后发现在不同情况下却有数据不一致的问题。 二. 过程 小熙随机创建了 两张表一张学生表和另一张班级表 （1） SELECT * FROM student; （2）SELECT * FROM classes; 以学生表为左表对班级表进行 left join： SELECT * FROM student s LEF...
mysql数据库中 convert_mysql数据库中CONVERT_TZ()函数起什么作用呢？
weixin_29407885的博客
01-19 1218
摘要：下文讲述mysql数据库中CONVERT_TZ()函数功能说明，如下所示；CONVERT_TZ()函数功能将一个日期时间数据，从一个时区转换为另一个时区CONVERT_TZ()函数语法:CONVERT_TZ(datetime,from_tz,to_tz)----------参数说明------datetime:待进行时区转换的时间日期数据from_tz:当前时区to_tz:转换后时区----...
2.6 MyBatis中的复杂SQL
最新发布
蟑螂恶霸的博客
04-11 596
说明: 用户传递了一个user对象, 要求根据user中不为null的属性查询数据. 例子1: User {name:“张三”} Sql: select * from demo_user where name = “张三”例子2: User {name:“张三”, age: 18} Sql: select * from demo_user where name = “张三” and age=18 1.1.3 问题说明 User对象中的数据可能为null.但是如果sql不做处理,则将会把null当做参数.导致
七、Mybatis动态SQL
DOONDO的技术窝
02-16 490
动态SQL:根据不同的条件需要执行不同的 SQL 命令.称为动态 SQL  MyBatis 中动态 SQL 在 mapper.xml 中添加逻辑判断等.  If 使用： 接口Mapper方法: /** * 动态SQL where的使用 */ List&lt;Log&gt; selectByWhereAccInOrAccOut(@Param("accIn")String accI...
Mybatis中根据不同条件执行不同sql之test 条件判断
weixin_43804366的博客
12-01 1万+
Mybatis中根据不同条件执行不同sql之test 条件判断 近期开发过程中，想在mybatis中根据不同条件执行不同的sql，想到了用 &amp;amp;lt;if test=&amp;quot;quoteFlag != null and quoteFlag !='' &amp;quot;&amp;amp;gt; sql 语句 &amp;amp;lt;if test=&amp;quot;quoteFlag != null and quoteFl
SpringMVC4+MyBatis+SQL Server2014数据库读写分离实战
"SpringMVC4+MyBatis+SQL Server2014实现数据库读写分离" 在现代Web应用程序开发中，数据库读写分离是一种常见的优化策略，它能够提高系统的并发性能和稳定性。本篇内容将详细介绍如何利用SpringMVC4、MyBatis和SQL...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值