upload UI source codeHEADmain

3 files changed, 1055 insertions, 0 deletions

diff --git a/UI source code/dns-dev-2.0/dns-system/src/main/resources/mapper/dns/DnsDao.xml b/UI source code/dns-dev-2.0/dns-system/src/main/resources/mapper/dns/DnsDao.xml
new file mode 100644
index 0000000..37d8a3b
--- /dev/null
+++ b/UI source code/dns-dev-2.0/dns-system/src/main/resources/mapper/dns/DnsDao.xml
@@ -0,0 +1,577 @@
+<?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.example.modules.dns.mapper.DnsDohDao">
+
+    <resultMap id="dohMap" type="com.example.modules.dns.domain.Result">
+        <id property="id" column="id"/>
+        <id property="ip" column="ip"/>
+        <id property="host" column="host"/>
+        <id property="port" column="port"/>
+        <result property="path" column="path"/>
+        <result property="method" column="method"/>
+        <result property="connectType" column="connect_type"/>
+        <result property="statusCode" column="status_code"/>
+        <result property="repHeader" column="rep_header"/>
+        <result property="repBody" column="rep_body"/>
+        <result property="timestamp" column="timestamp"/>
+        <result property="component" column="component"/>
+        <result property="rounds" column="rounds"/>
+        <association property="ipInformation" columnPrefix="ii_"
+                     javaType="com.example.modules.dns.domain.IpInformation">
+            <result property="id" column="id"/>
+            <result property="ip" column="ip"/>
+            <result property="country" column="country"/>
+            <result property="province" column="province"/>
+            <result property="city" column="city"/>
+            <result property="district" column="district"/>
+            <result property="provider" column="provider"/>
+            <result property="isp" column="isp"/>
+            <result property="asnumber" column="asnumber"/>
+            <result property="timestamp" column="timestamp"/>
+            <result property="zipcode" column="zipcode"/>
+            <result property="timezone" column="timezone"/>
+        </association>
+
+        <collection property="pathList" columnPrefix="da2_" ofType="String">
+            <result property="pathList" column="path"/>
+        </collection>
+
+        <collection property="componentList" columnPrefix="da2_" ofType="String">
+            <result property="component" column="component"/>
+        </collection>
+        <collection property="ipCert" columnPrefix="ic_" ofType="com.example.modules.dns.domain.IpCert">
+            <id property="id" column="id"/>
+            <result property="ip" column="ip"/>
+            <result property="port" column="port"/>
+            <result property="certificate" column="certificate"/>
+            <result property="ca" column="ca"/>
+            <result property="timestamp" column="timestamp"/>
+        </collection>
+
+        <collection property="vulnerability" columnPrefix="v_" ofType="com.example.modules.dns.domain.Vulnerability">
+            <id property="id" column="id"/>
+            <result property="component" column="component"/>
+            <result property="vulnerability" column="vulnerability"/>
+            <result property="timestamp" column="timestamp"/>
+        </collection>
+
+    </resultMap>
+
+    <select id="queryDohPage" resultMap="dohMap">
+        SELECT da.id,da.ip,da.`host`,da.`port`,da.rounds,da.`timestamp`
+        ,GROUP_CONCAT(concat(da.method,'',da.path)) path
+        ,GROUP_CONCAT(distinct da.component) component
+        ,ii.country ii_country,ii.provider ii_provider,ii.city ii_city ,ii.asnumber ii_asnumber,ii.isp ii_isp,
+        ii.province ii_province,ii.zipcode ii_zipcode,ii.district ii_district,
+        v.`vulnerability` v_vulnerability
+        FROM (SELECT da.* FROM doh_attribute da
+        <where>
+            <if test="params.ip != null and params.ip != ''">
+                da.ip = #{params.ip}
+            </if>
+            <if test="params.port != null and params.port != ''">
+                AND da.`port` = #{params.port}
+            </if>
+        </where>
+        ) da
+        LEFT JOIN ip_information ii ON da.ip=ii.ip
+        LEFT JOIN `vulnerability` v ON v.`component` = da.`component`
+
+        GROUP BY da.ip,da.port,da.host,da.rounds
+        order by da.`timestamp` desc
+        LIMIT #{params.n},#{params.m}
+    </select>
+
+    <select id="dohProvinceCount" resultType="map">
+        SELECT
+        COUNT(distinct da.ip) count,ii.`province`
+        FROM (SELECT DISTINCT ip from doh_attribute
+        <where>
+            <if test="params.ip != null and params.ip != ''">
+                ip = #{params.ip}
+            </if>
+            <if test="params.port != null and params.port != ''">
+                and port = #{params.port}
+            </if>
+        </where>
+        ) da
+        JOIN `ip_information` ii
+        ON da.ip = ii.ip AND ii.province IS NOT NULL AND ii.province !=''
+        GROUP BY ii.province
+    </select>
+
+    <select id="dohChinaMapCount" resultType="map">
+        SELECT ii.`province`,
+               COUNT(distinct da.ip) count
+        FROM `doh_attribute` da
+                 JOIN `ip_information` ii
+                      ON da.ip = ii.ip
+        where ii.country = '中国'
+          and da.`rounds` = (select MAX(rounds) FROM doh_attribute)
+          and ii.province IS NOT NULL
+          AND ii.province != ''
+        GROUP BY ii.province
+
+        order by count desc
+    </select>
+
+    <select id="dohCountryCount" resultType="map">
+        SELECT ii.`country`,
+               COUNT(distinct da.ip) count
+        FROM `doh_attribute` da
+                 JOIN `ip_information` ii
+                      ON da.ip = ii.ip
+        where da.`rounds` = (SELECT DISTINCT MAX(rounds) FROM doh_attribute)
+        GROUP BY ii.country
+        order by count desc
+    </select>
+
+    <select id="dohAndDo53ProvinceCount" resultType="map">
+        SELECT province,SUM(count) count FROM (
+        SELECT
+        ii.`province`,COUNT(distinct da.ip) count,da.ip
+        FROM `doh_attribute` da
+        JOIN `ip_information` ii
+        ON da.ip = ii.ip
+        where ii.province IS NOT NULL AND ii.province !=''
+        <if test="params.ip != null and params.ip != ''">
+            and da.ip = #{params.ip}
+        </if>
+        GROUP BY ii.province
+
+        UNION ALL
+
+        SELECT
+        ii.`province`,COUNT(distinct s.ip) COUNT, s.ip
+        FROM `scan_result` s
+        JOIN `ip_information` ii
+        ON s.ip = ii.ip
+        where ii.province IS NOT NULL AND ii.province !=''
+        <if test="params.ip != null and params.ip != ''">
+            and s.ip = #{params.ip}
+        </if>
+        GROUP BY ii.province
+        ) a1
+
+        GROUP BY a1.province
+    </select>
+
+    <select id="dohProviderCount" resultType="map">
+        SELECT
+        COUNT(ip_attr.ip) count,ii.`provider`
+        from
+        (SELECT DISTINCT ip from doh_attribute
+        <where>
+            <if test="params.ip != null and params.ip != ''">
+                ip = #{params.ip}
+            </if>
+            <if test="params.port != null and params.port != ''">
+                and port = #{params.port}
+            </if>
+        </where>
+        ) ip_attr
+        JOIN `ip_information` ii on ip_attr.ip = ii.ip AND ii.provider IS NOT NULL AND ii.provider !=''
+
+        GROUP BY ii.provider
+    </select>
+
+    <select id="dohAndDo53ProviderCount" resultType="map">
+
+        SELECT provider,SUM(COUNT) count FROM(
+        SELECT
+        ii.`provider`,COUNT(da.ip) COUNT,da.ip
+        FROM (SELECT id, ip from doh_attribute
+        <where>
+            <if test="params.ip != null and params.ip != ''">
+                ip = #{params.ip}
+            </if>
+        </where>
+        ) da
+        JOIN `ip_information` ii ON da.ip = ii.ip
+        GROUP BY ii.provider
+
+        UNION ALL
+        SELECT
+        ii.`provider`,COUNT(distinct s.ip) COUNT,s.ip
+        FROM `scan_result` s
+        JOIN `ip_information` ii ON s.ip = ii.ip
+        GROUP BY ii.provider
+        ) a1
+        <where>
+            <if test="params.ip != null and params.ip != ''">
+                a1.ip = #{params.ip}
+            </if>
+        </where>
+        GROUP BY a1.provider
+        HAVING provider IS NOT NULL AND provider !=''
+    </select>
+
+    <select id="dohComponentCount" resultType="map">
+        SELECT
+        da.component,COUNT(distinct da.ip) count
+        FROM (SELECT DISTINCT ip,component from doh_attribute
+        where
+        component IS NOT NULL AND component !=''
+        <if test="params.ip != null and params.ip != ''">
+            and ip = #{params.ip}
+        </if>
+        <if test="params.port != null and params.port != ''">
+            and port = #{params.port}
+        </if>
+        ) da
+        GROUP BY da.component
+
+    </select>
+
+    <select id="dohAndDo53ComponentCount" resultType="map">
+        SELECT component,SUM(COUNT) count FROM (
+        SELECT
+        da.component,COUNT(distinct da.ip) COUNT,da.ip
+        FROM `doh_attribute` da
+        <where>
+
+        <if test="params.ip != null and params.ip != ''">
+             da.ip = #{params.ip}
+        </if>
+
+        </where>
+        GROUP BY da.component,da.ip
+
+        UNION ALL
+
+        SELECT
+        s.component,COUNT(distinct s.ip) COUNT ,s.ip
+        FROM `scan_result` s
+        GROUP BY s.component,s.ip) a1
+        where component IS NOT NULL AND component !=''
+        <if test="params.ip != null and params.ip != ''">
+            and a1.ip = #{params.ip}
+        </if>
+
+        GROUP BY a1.component
+
+    </select>
+
+    <select id="dohServiceCategoryCount" resultType="map">
+        <!--SELECT IFNULL(SUM(count),0) count FROM (
+        SELECT da.ip,COUNT(distinct da.ip) count FROM `doh_attribute` da
+        <where>
+            <if test="params.ip != null and params.ip != ''">
+                and da.ip = #{params.ip}
+            </if>
+            <if test="params.port != null and params.port != ''">
+                and da.port = #{params.port}
+            </if>
+        </where>
+        GROUP BY ip) a1-->
+        SELECT count(1) count from (
+        SELECT distinct da.ip from `doh_attribute` da
+        <where>
+            <if test="params.ip != null and params.ip != ''">
+                da.ip = #{params.ip}
+            </if>
+            <if test="params.port != null and params.port != ''">
+                and da.port = #{params.port}
+            </if>
+        </where>
+        ) a
+
+    </select>
+
+    <select id="dohResultTotalCount" resultType="int">
+        SELECT COUNT(1) count FROM (
+        SELECT ip,PORT,HOST,rounds
+        FROM `doh_attribute` da
+        <where>
+            <if test="params.ip != null and params.ip != ''">
+                da.ip = #{params.ip}
+            </if>
+            <if test="params.port != null and params.port != ''">
+                and da.port = #{params.port}
+            </if>
+        </where>
+        GROUP BY ip,PORT,HOST,rounds
+        ) a1
+    </select>
+
+    <select id="dohVulnerabilityCount" resultType="map">
+        SELECT vulnerability,COUNT(distinct ip) count FROM (
+        SELECT da.ip,da.port,da.host,da.`component`
+        ,v.`vulnerability`
+        FROM `doh_attribute` da
+        JOIN `vulnerability` v ON v.`component` = da.`component` AND vulnerability IS NOT NULL AND vulnerability != ''
+        <where>
+            <if test="params.ip != null and params.ip != ''">
+                da.ip = #{params.ip}
+            </if>
+            <if test="params.port != null and params.port != ''">
+                and da.port = #{params.port}
+            </if>
+        </where>
+        ) a1
+
+        GROUP BY a1.vulnerability
+    </select>
+
+    <select id="dohAndDo53VulnerabilityCount" resultType="map">
+        SELECT vulnerability,COUNT(distinct ip) count FROM (
+        SELECT da.ip
+        ,v.`vulnerability`
+        FROM `doh_attribute` da
+        JOIN `vulnerability` v ON v.`component` = da.`component`
+        where vulnerability IS NOT NULL AND vulnerability !=''
+        <if test="params.ip != null and params.ip != ''">
+            and ip = #{params.ip}
+        </if>
+
+        UNION ALL
+
+        SELECT s.ip
+        ,v.`vulnerability`
+        FROM `scan_result` s
+        JOIN `vulnerability` v ON v.`component` = s.`component`
+        where vulnerability IS NOT NULL AND vulnerability !=''
+        <if test="params.ip != null and params.ip != ''">
+            and ip = #{params.ip}
+        </if>
+        ) a1
+
+        GROUP BY a1.vulnerability
+    </select>
+
+
+    <select id="dohPortCount" resultType="map">
+        SELECT COUNT(ip) count,`port` FROM
+        (SELECT DISTINCT ip,port from doh_attribute
+        where `port` is not null AND `port` != ''
+        <if test="params.ip != null and params.ip != ''">
+            and ip = #{params.ip}
+        </if>
+        <if test="params.port != null and params.port != ''">
+            and port = #{params.port}
+        </if>
+        ) da
+        GROUP BY port
+    </select>
+
+
+    <select id="dohIpCount" resultType="int">
+        SELECT COUNT(distinct da.ip) FROM doh_attribute da
+        <where>
+            <if test="params.ip != null and params.ip != ''">
+                da.ip = #{params.ip}
+            </if>
+        </where>
+    </select>
+    <select id="getDohRepBody" resultMap="dohMap">
+        select da.rep_body, da.rep_header
+        from doh_attribute da
+        where da.ip = #{ip}
+          AND da.port = #{port}
+          AND da.host = #{host}
+          AND da.rounds = #{rounds}
+    </select>
+
+    <select id="selectDohInfoByUnion" resultMap="dohMap">
+        SELECT da.*,
+        GROUP_CONCAT(concat(da.method, '', da.path)) path,
+        ii.country ii_country,
+        ii.provider ii_provider,
+        ii.city ii_city,
+        ii.asnumber ii_asnumber,
+        ii.isp ii_isp,
+        ii.province ii_province,
+        ii.zipcode ii_zipcode,
+        ii.district ii_district,
+        v.`vulnerability` v_vulnerability
+        from (select da.ip
+            from (select count(1), d.ip from doh_attribute d
+            <where>
+                <if test="params.ip != null and params.ip != ''">
+                    d.ip = #{params.ip}
+                </if>
+                <if test="params.port != null and params.port != ''">
+                    and d.`port` = #{params.port}
+                </if>
+            </where>
+            group by d.ip) da
+            JOIN scan_result s on da.ip = s.ip) dip
+        join doh_attribute da on da.ip = dip.ip
+        left join ip_information ii on ii.ip = dip.ip
+        left join vulnerability v on v.component = da.component
+
+        group by da.ip, da.host, da.`port`, da.rounds
+        order by da.`timestamp` desc
+        LIMIT #{params.offset},#{params.limit}
+    </select>
+
+    <select id="selectDohCountByUnion" resultType="integer">
+        select count(1)
+        from (SELECT da.id, da.ip, da.host, da.`port`, da.rounds
+        from (select da.ip
+        from (select count(1), d.ip from doh_attribute d
+        <where>
+            <if test="params.ip != null and params.ip != ''">
+                d.ip = #{params.ip}
+            </if>
+            <if test="params.port != null and params.port != ''">
+                and d.`port` = #{params.port}
+            </if>
+        </where>
+        group by d.ip) da
+        JOIN scan_result s on da.ip = s.ip
+        ) dip
+        join doh_attribute da on da.ip = dip.ip
+        group by da.ip, da.host, da.`port`, da.rounds) total
+
+    </select>
+
+    <select id="countDohPortUnion" resultType="java.util.Map">
+
+        select count(distinct dh.ip) count ,dh.`port`
+        from (select distinct ip,`port` from doh_attribute
+        <where>
+            <if test="params.ip != null and params.ip != ''">
+                ip = #{params.ip}
+            </if>
+            <if test="params.port != null and params.port != ''">
+                and port = #{params.port}
+            </if>
+        </where>
+        ) dh
+        join scan_result sc on sc.ip = dh.ip
+        group by dh.`port`
+        having dh.`port` is not null AND dh.`port` != ''
+
+        UNION ALL
+
+        select count(distinct sc.ip) count,'53' as `port`
+        from (select distinct ip,`port` from doh_attribute da
+        <where>
+            <if test="params.ip != null and params.ip != ''">
+                da.ip = #{params.ip}
+            </if>
+        </where>
+        ) dh
+        join scan_result sc on sc.ip = dh.ip
+
+    </select>
+
+    <select id="getDohAndDo53ProvinceCount" resultType="java.util.Map">
+        SELECT
+        ii.`province`,COUNT(distinct s.ip) COUNT
+        FROM ( select sc.id,sc.ip from `scan_result` sc
+        <where>
+            <if test="params.ip != null and params.ip != ''">
+                sc.ip = #{ip}
+            </if>
+        </where>
+        )s
+        join `doh_attribute` da on da.ip = s.ip
+        JOIN `ip_information` ii
+        ON s.ip = ii.ip
+        <where>
+            <if test="params.port != null and params.port != ''">
+                da.`port` = #{params.port}
+            </if>
+        </where>
+        GROUP BY ii.province
+        having ii.province IS NOT NULL AND ii.province !=''
+    </select>
+
+    <select id="getDohAndDo53ProviderCount" resultType="java.util.Map">
+        SELECT
+        ii.`provider`,COUNT(distinct s.ip) COUNT
+        FROM ( select sc.id,sc.ip from `scan_result` sc
+        <where>
+            <if test="params.ip != null and params.ip != ''">
+                sc.ip = #{ip}
+            </if>
+        </where>
+        )s
+        join `doh_attribute` da on da.ip = s.ip
+        JOIN `ip_information` ii
+        ON s.ip = ii.ip
+        <where>
+            <if test="params.port != null and params.port != ''">
+                da.`port` = #{params.port}
+            </if>
+        </where>
+        GROUP BY ii.provider
+        having ii.provider IS NOT NULL AND ii.provider !=''
+    </select>
+
+    <select id="getDohAndDo53ComponentCount" resultType="java.util.Map">
+        SELECT
+        da.component component,count(distinct da.ip) count
+        FROM ( select count(1),d.ip,d.component from doh_attribute d
+        where d.component IS NOT NULL AND d.component !=''
+        <if test="params.ip != null and params.ip != ''">
+           AND d.ip = #{ip}
+        </if>
+        ) da
+        JOIN scan_result s on da.ip = s.ip
+        <where>
+            <if test="params.port != null and params.port != ''">
+                da.`port` = #{params.port}
+            </if>
+        </where>
+        GROUP BY da.component
+        having da.component IS NOT NULL AND da.component !=''
+    </select>
+
+    <select id="getDohServiceCategoryCount" resultType="java.util.Map">
+        SELECT count(DISTINCT da.ip)
+        from (select count(1),d.ip from doh_attribute d
+        <where>
+            <if test="params.ip != null and params.ip != ''">
+                d.ip = #{params.ip}
+            </if>
+            <if test="params.port != null and params.port != ''">
+                and d.`port` = #{params.port}
+            </if>
+        </where>
+        group by d.ip) da
+        join scan_result sc on da.ip = sc.ip
+    </select>
+    <select id="getDohAndDo53VulnerabilityCount" resultType="java.util.Map">
+        SELECT vulnerability, COUNT(distinct ip) count
+        FROM (
+        select da.ip, dv.vulnerability
+        from (select count(1), d.ip, d.component
+        from doh_attribute d
+        where d.component is not null and d.component != ''
+        <if test="params.ip != null and params.ip != ''">
+            and d.ip = #{params.ip}
+        </if>
+        <if test="params.port != null and params.port != ''">
+            and d.`port` = #{params.port}
+        </if>
+        group by d.ip, d.component) da
+        join scan_result sc on da.ip = sc.ip
+        join (select distinct v.vulnerability, d.component, d.ip from doh_attribute d
+        JOIN `vulnerability` v ON v.`component` = d.`component`) dv
+        on da.ip = dv.ip
+        where dv.vulnerability is not null and dv.vulnerability != ''
+
+        union all
+
+        select s.ip, dv.vulnerability
+        from (select count(1), d.ip from doh_attribute d
+        group by d.ip
+        ) da
+        join scan_result s on da.ip = s.ip
+        join (select distinct v.vulnerability, sc.component, sc.ip
+        from scan_result sc
+        JOIN `vulnerability` v ON v.`component` = sc.`component`) dv
+        on s.ip = dv.ip
+        where dv.vulnerability is not null and dv.vulnerability != ''
+        <if test="params.ip != null and params.ip != ''">
+            and s.ip = #{params.ip}
+        </if>
+        ) a1
+        GROUP BY a1.vulnerability
+    </select>
+
+</mapper>
diff --git a/UI source code/dns-dev-2.0/dns-system/src/main/resources/mapper/dns/DnsDo53Dao.xml b/UI source code/dns-dev-2.0/dns-system/src/main/resources/mapper/dns/DnsDo53Dao.xml
new file mode 100644
index 0000000..849fc64
--- /dev/null
+++ b/UI source code/dns-dev-2.0/dns-system/src/main/resources/mapper/dns/DnsDo53Dao.xml
@@ -0,0 +1,400 @@
+<?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.example.modules.dns.mapper.DnsDo53Dao">
+
+    <resultMap id="do53Map" type="com.example.modules.dns.domain.Result">
+        <id property="id" column="id"/>
+        <result property="ip" column="ip"/>
+        <result property="flags" column="flags"/>
+        <result property="opcode" column="opcode"/>
+        <result property="qr" column="qr"/>
+        <result property="aa" column="aa"/>
+        <result property="ra" column="ra"/>
+        <result property="rcode" column="rcode"/>
+        <result property="queryName" column="queryName"/>
+        <result property="queryResponse" column="query_response"/>
+        <result property="component" column="component"/>
+        <result property="scanType" column="scanType"/>
+        <result property="timestamp" column="time"/>
+        <result property="dnsType" column="dnsType"/>
+        <result property="epoch" column="epoch"/>
+
+        <association property="ipInformation" columnPrefix="ii_"
+                     javaType="com.example.modules.dns.domain.IpInformation">
+            <result property="id" column="id"/>
+            <result property="ip" column="ip"/>
+            <result property="country" column="country"/>
+            <result property="province" column="province"/>
+            <result property="city" column="city"/>
+            <result property="district" column="district"/>
+            <result property="provider" column="provider"/>
+            <result property="isp" column="isp"/>
+            <result property="asnumber" column="asnumber"/>
+            <result property="timestamp" column="timestamp"/>
+            <result property="zipcode" column="zipcode"/>
+            <result property="timezone" column="timezone"/>
+        </association>
+        <collection property="componentList" ofType="String">
+            <result property="component" column="component"/>
+        </collection>
+
+        <collection property="vulnerability" columnPrefix="v_" ofType="com.example.modules.dns.domain.Vulnerability">
+            <id property="id" column="id"/>
+            <result property="component" column="component"/>
+            <result property="vulnerability" column="vulnerability"/>
+            <result property="timestamp" column="timestamp"/>
+        </collection>
+
+
+    </resultMap>
+
+    <select id="queryDo53Page" resultMap="do53Map">
+    SELECT s.*,
+        ii.country ii_country,ii.provider ii_provider,
+        ii.city ii_city ,ii.asnumber ii_asnumber,ii.isp ii_isp,
+        ii.province ii_province,ii.zipcode ii_zipcode,ii.district ii_district,
+        dt.type dnsType,v.`vulnerability` v_vulnerability
+        FROM (select s.* from scan_result s
+        <where>
+            <if test="params.ip != null and params.ip != ''">
+                s.ip = #{params.ip}
+            </if>
+        </where>
+        order by s.time desc
+        LIMIT #{params.n},#{params.m}
+        ) s
+        LEFT JOIN ip_information ii ON ii.ip = s.ip
+        LEFT JOIN dns_type dt ON dt.ip = s.ip AND dt.`epoch` = s.`epoch`
+        LEFT JOIN `vulnerability` v ON v.`component` = s.`component`
+
+
+    </select>
+
+    <select id="do53IpCount" resultType="int">
+        SELECT COUNT(distinct ip) FROM `scan_result`
+        <where>
+            <if test="params.ip != null and params.ip != ''">
+                ip = #{params.ip}
+            </if>
+        </where>
+
+    </select>
+
+    <select id="do53ServiceCategoryCount" resultType="map">
+        SELECT TYPE,COUNT(distinct ip) count FROM (
+        SELECT s.ip,dt.type FROM `scan_result` s
+        JOIN `dns_type` dt ON s.ip=dt.ip
+        where type IS NOT NULL AND type !=''
+        <if test="params.ip != null and params.ip != ''">
+            and s.ip = #{params.ip}
+        </if>
+        <if test="params.dnsType != null and params.dnsType != ''">
+            and dt.type = #{params.dnsType}
+        </if>
+        ) a1
+        GROUP BY a1.type
+    </select>
+
+    <select id="do53ProvinceCount" resultType="map">
+        SELECT
+        ii.`province`,COUNT(distinct s.ip) count
+        FROM `scan_result` s
+        JOIN `ip_information` ii
+        ON s.ip = ii.ip AND ii.province IS NOT NULL AND ii.province !=''
+        <where>
+            <if test="params.ip != null and params.ip != ''">
+                and s.ip = #{params.ip}
+            </if>
+        </where>
+        GROUP BY ii.province
+    </select>
+
+    <select id="do53CountryCount" resultType="map">
+        SELECT ii.`country`,
+               COUNT(s.ip) count
+        FROM `scan_result` s
+                 JOIN `ip_information` ii
+                      ON s.ip = ii.ip
+        GROUP BY ii.country
+    </select>
+
+    <select id="do53CountryMapCount" resultType="map">
+        SELECT ii.`country`,
+               COUNT(ii.`country`) count,
+               dt.type
+        FROM `dns_type` dt
+                 JOIN `ip_information` ii ON dt.ip = ii.ip
+        WHERE dt.epoch = (SELECT MAX(epoch) FROM `dns_type`)
+        GROUP BY ii.`country`, dt.`type`
+        order by count desc
+    </select>
+
+    <select id="do53WorldMapCount" resultType="map">
+        SELECT ii.`country`,
+               COUNT(ii.`country`) count
+        FROM `dns_type` dt
+                 JOIN `ip_information` ii ON dt.ip = ii.ip
+        WHERE dt.epoch = (SELECT MAX(epoch) FROM `dns_type`)
+        GROUP BY ii.`country`
+        order by count desc
+    </select>
+
+    <select id="do53ChinaMapCount" resultType="map">
+        SELECT ii.`province`,
+               COUNT(ii.`province`) count
+        FROM `dns_type` dt
+                 JOIN `ip_information` ii ON dt.ip = ii.ip
+        WHERE ii.country = '中国'
+          and dt.epoch = (SELECT MAX(epoch) FROM `dns_type`)
+        GROUP BY ii.`province`
+        order by count desc
+    </select>
+
+    <select id="do53ProviderCount" resultType="map">
+        SELECT
+        ii.`provider`,COUNT(s.ip) count
+        FROM `scan_result` s
+        JOIN `ip_information` ii
+        ON s.ip = ii.ip AND provider IS NOT NULL AND provider !=''
+        <where>
+            <if test="params.ip != null and params.ip != ''">
+                s.ip = #{params.ip}
+            </if>
+        </where>
+        GROUP BY ii.provider
+    </select>
+
+
+    <select id="do53ComponentCount" resultType="map">
+        SELECT
+        s.component,COUNT(distinct s.ip) count
+        FROM `scan_result` s
+        where
+        component IS NOT NULL AND component !=''
+        <if test="params.ip != null and params.ip != ''">
+            and s.ip = #{params.ip}
+        </if>
+        GROUP BY s.component
+    </select>
+
+    <select id="do53VulnerabilityCount" resultType="map">
+
+        SELECT vulnerability,COUNT(ip) count FROM (
+        SELECT s.ip
+        ,v.`vulnerability`
+        FROM `scan_result` s
+        JOIN `vulnerability` v ON v.`component` = s.`component`
+        where vulnerability IS NOT NULL AND vulnerability !=''
+        <if test="params.ip != null and params.ip != ''">
+            and s.ip = #{params.ip}
+        </if>
+        ) a1
+        GROUP BY a1.vulnerability
+    </select>
+
+    <select id="pageCount" resultType="java.lang.Integer">
+        select count(1) from scan_result s
+        <where>
+            <if test="params.ip != null and params.ip != ''">
+                s.ip = #{params.ip}
+            </if>
+        </where>
+    </select>
+
+    <select id="queryOpenRdns" resultType="result">
+        select sc.*,
+        dt.type dnsType,
+        ii.country ii_country,ii.provider ii_provider,ii.city ii_city ,ii.asnumber ii_asnumber,ii.isp ii_isp,
+        ii.province ii_province,ii.zipcode ii_zipcode,ii.district ii_district,
+        v.`vulnerability` v_vulnerability
+        from
+        (
+        select sc.* from
+        scan_result sc join (select ip,epoch from dns_type where type = 1) dt on dt.ip = sc.ip and dt.epoch = sc.epoch
+        <where>
+            <if test="params.ip != null and params.ip != ''">
+                sc.ip = #{params.ip}
+            </if>
+        </where>
+        order by sc.epoch desc,sc.`time` desc LIMIT #{params.offset},#{params.limit}
+        ) sc
+
+        left join ip_information ii on sc.ip = ii.ip
+        left join vulnerability v on sc.component = v.component
+    </select>
+
+    <select id="getCountByDnsType" resultType="java.lang.Integer">
+        select count(1) from
+        (select id,ip,epoch from scan_result sr
+        <where>
+            <if test="params.ip != null and params.ip != ''">
+                sr.ip = #{params.ip}
+            </if>
+        </where>
+        ) sc
+        join ( select d.id,d.ip,d.epoch from dns_type d where d.type = #{dnsType}) dt
+        on dt.ip = sc.ip AND dt.epoch = sc.epoch
+
+
+    </select>
+
+    <select id="getIndependentIpNum" resultType="java.lang.Integer">
+        select count(distinct sc.ip)
+        from ( select id,ip,epoch from scan_result sr
+        <where>
+            <if test="params.ip != null and params.ip != ''">
+                ip = #{params.ip}
+            </if>
+        </where>
+        ) sc
+        join (select ip, epoch from dns_type d where d.type = #{dnsType})
+        dt on dt.ip = sc.ip and dt.epoch = sc.epoch
+
+    </select>
+
+    <select id="selectScanResultCountByUnion" resultType="java.lang.Integer">
+        select count(1) count
+        from (
+              select da.ip
+                 from (select count(1), d.ip from doh_attribute d
+                <where>
+                    <if test="params.ip != null and params.ip != ''">
+                        d.ip = #{params.ip}
+                    </if>
+                    <if test="params.port != null and params.port != ''">
+                        and d.`port` = #{params.port}
+                    </if>
+                </where>
+              group by d.ip) da
+              JOIN scan_result s on da.ip = s.ip
+             ) dip
+       join scan_result sc on sc.ip = dip.ip
+    </select>
+
+    <select id="selectScanResultByUnion" resultMap="do53Map">
+    SELECT sc.*,
+        ii.country ii_country, ii.provider ii_provider,
+        ii.city ii_city, ii.asnumber ii_asnumber, ii.isp ii_isp,
+        ii.province ii_province,ii.zipcode ii_zipcode,
+        ii.district ii_district,
+        dt.type dnsType, v.`vulnerability` v_vulnerability
+        from (
+        select da.ip
+            from (select count(1), d.ip from doh_attribute d
+            <where>
+                <if test="params.ip != null and params.ip != ''">
+                    d.ip = #{params.ip}
+                </if>
+                <if test="params.port != null and params.port != ''">
+                    and d.`port` = #{params.port}
+                </if>
+            </where>
+            group by d.ip) da
+            JOIN scan_result s on da.ip = s.ip
+        ) dip
+
+        join scan_result sc on sc.ip = dip.ip
+        left join ip_information ii on ii.ip = dip.ip
+        left join vulnerability v on v.component = sc.component
+        left join dns_type dt ON dt.ip = sc.ip AND dt.`epoch` = sc.`epoch`
+        order by sc.`time` desc
+        LIMIT #{params.offset},#{params.limit}
+    </select>
+
+    <select id="getDohAndDo53SrvCategoryCount" resultType="java.util.Map">
+        SELECT dt.type, COUNT(distinct da.ip) count
+        FROM (select count(1),d.ip from doh_attribute d
+        <where>
+            <if test="params.ip != null and params.ip != ''">
+                d.ip = #{params.ip}
+            </if>
+            <if test="params.port != null and params.port != ''">
+                and d.`port` = #{params.port}
+            </if>
+        </where>
+        group by d.ip
+        ) da
+        JOIN scan_result s on da.ip = s.ip
+        JOIN `dns_type` dt ON da.ip = dt.ip and dt.type > 0
+
+        GROUP BY dt.type
+    </select>
+
+    <select id="getDo53ProvinceCountByDnsType" resultType="java.util.Map">
+        select s.`province`,COUNT(distinct s.ip) count  from
+             (SELECT  s.ip,ii.province   FROM `scan_result` s
+                    JOIN `ip_information` ii  ON s.ip = ii.ip
+                    where ii.province IS NOT NULL AND ii.province !=''
+                        <if test="params.ip != null and params.ip != ''">
+                        AND s.ip = #{params.ip}
+                        </if>
+                 ) s
+            join dns_type dt on s.ip = dt.ip
+        where dt.type = #{dnsType}
+        GROUP BY s.province
+    </select>
+
+    <select id="getDo53ProviderCountByDnsType" resultType="java.util.Map">
+        SELECT
+        s.`provider`,COUNT(distinct s.ip) count
+        FROM (  SELECT ii.`provider`,s.ip
+        FROM `scan_result` s
+        JOIN `ip_information` ii
+        ON s.ip = ii.ip AND provider IS NOT NULL AND provider !=''
+        <where>
+            <if test="params.ip != null and params.ip != ''">
+                s.ip = #{params.ip}
+            </if>
+        </where>
+        ) s
+        JOIN dns_type dt on dt.ip = s.ip
+        <if test="dnsType != null and dnsType != ''">
+            AND dt.type = #{dnsType}
+        </if>
+        GROUP BY s.provider
+    </select>
+
+    <select id="getDo53ComponentCountByDnsType" resultType="java.util.Map">
+        select s.component,COUNT(distinct s.ip) count
+        from (
+            SELECT s.component, s.ip  FROM `scan_result` s
+        where
+        component IS NOT NULL AND component !=''
+        <if test="params.ip != null and params.ip != ''">
+            and s.ip = #{params.ip}
+        </if>
+        ) s
+        JOIN (SELECT ip from dns_type where type = #{dnsType}) dt on s.ip = dt.ip
+        GROUP BY s.component
+    </select>
+
+    <select id="getDo53SrvCategoryCountByDnsType" resultType="java.lang.Integer">
+        SELECT COUNT(a1.ip) count FROM (
+        SELECT s.ip,dt.type FROM `scan_result` s
+        JOIN `dns_type` dt ON s.ip=dt.ip
+        where dt.type = #{dnsType}
+        <if test="params.ip != null and params.ip != ''">
+            and s.ip = #{params.ip}
+        </if>
+        ) a1
+        GROUP BY a1.type
+    </select>
+
+    <select id="getDo53VulnerabCountByDnsType" resultType="java.util.Map">
+        SELECT vulnerability,COUNT(a1.ip) count FROM (
+        SELECT s.ip
+        ,v.`vulnerability`
+        FROM `scan_result` s
+        JOIN `vulnerability` v ON v.`component` = s.`component`
+        where vulnerability IS NOT NULL AND vulnerability !=''
+        <if test="params.ip != null and params.ip != ''">
+            and s.ip = #{params.ip}
+        </if>
+        ) a1
+        JOIN dns_type dt on a1.ip = dt.ip and dt.type = #{dnsType}
+        GROUP BY a1.vulnerability
+    </select>
+
+
+</mapper>
diff --git a/UI source code/dns-dev-2.0/dns-system/src/main/resources/mapper/dns/IpInformationDao.xml b/UI source code/dns-dev-2.0/dns-system/src/main/resources/mapper/dns/IpInformationDao.xml
new file mode 100644
index 0000000..f4a0047
--- /dev/null
+++ b/UI source code/dns-dev-2.0/dns-system/src/main/resources/mapper/dns/IpInformationDao.xml
@@ -0,0 +1,78 @@
+<?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.example.modules.dns.mapper.IpInformationDao">
+
+    <resultMap id="ipMap" type="com.example.modules.dns.domain.IpInformation">
+        <id property="id" column="id"/>
+        <id property="ip" column="ip"/>
+        <result property="country" column="country"/>
+        <result property="province" column="province"/>
+        <result property="city" column="city"/>
+        <result property="district" column="district"/>
+        <result property="provider" column="provider"/>
+        <result property="isp" column="isp"/>
+        <result property="asnumber" column="asnumber"/>
+        <result property="timestamp" column="timestamp"/>
+        <result property="zipcode" column="zipcode"/>
+        <result property="timezone" column="timezone"/>
+
+        <association property="dnsType" columnPrefix="dt_" javaType="com.example.modules.dns.domain.DnsType">
+            <id property="id" column="id"/>
+            <id property="ip" column="ip"/>
+            <result property="type" column="type"/>
+        </association>
+
+        <collection property="ipCert" columnPrefix="ic_" ofType="com.example.modules.dns.domain.IpCert">
+            <id property="id" column="id"/>
+            <id property="ip" column="ip"/>
+            <result property="port" column="port"/>
+            <result property="certificate" column="certificate"/>
+            <result property="ca" column="ca"/>
+            <result property="timestamp" column="timestamp"/>
+        </collection>
+
+        <collection property="dohAttribute" columnPrefix="da_" ofType="com.example.modules.dns.domain.DohAttribute">
+            <id property="ip" column="ip"/>
+            <result property="host" column="host"/>
+            <result property="port" column="port"/>
+            <result property="path" column="path"/>
+            <result property="method" column="method"/>
+            <result property="connectType" column="connect_type"/>
+            <result property="statusCode" column="status_code"/>
+            <result property="repHeader" column="rep_header"/>
+            <result property="repBody" column="rep_body"/>
+            <result property="timestamp" column="timestamp"/>
+            <result property="component" column="component"/>
+        </collection>
+
+    </resultMap>
+
+
+
+    <select id="queryIpPage" resultMap="ipMap">
+        SELECT ii.*
+--         ,ic.certificate ic_certificate ,ic.ca ic_ca
+        ,da.host da_host , da.rep_body da_rep_body , da.status_code da_status_code ,da.component da_component,da.path da_path
+        FROM `ip_information`  ii
+--         LEFT JOIN `ip_cert` ic ON ii.ip = ic.ip
+        left join doh_attribute da on ii.ip = da.ip
+
+        <where>
+            <if test="params.ip != null and params.ip != ''">
+                and ii.ip = #{params.ip}
+            </if>
+        </where>
+
+    </select>
+    <select id="getIpInfoByFwd" resultType="ipInformation">
+        select ii.* from ip_information ii
+        join (SELECT fd.upstream from forward_dns fd where fd.forwarder = #{ip}
+            <if test="epoch != '' and epoch != null ">
+                and fd.epoch = #{epoch}
+            </if>
+            ) fw
+            on fw.upstream = ii.ip
+    </select>
+
+</mapper>