diff options
Diffstat (limited to 'UI source code/dns-dev-2.0/dns-system/src/main/resources')
10 files changed, 1480 insertions, 0 deletions
diff --git a/UI source code/dns-dev-2.0/dns-system/src/main/resources/banner.txt b/UI source code/dns-dev-2.0/dns-system/src/main/resources/banner.txt new file mode 100644 index 0000000..d0f401a --- /dev/null +++ b/UI source code/dns-dev-2.0/dns-system/src/main/resources/banner.txt @@ -0,0 +1,8 @@ + _ _ _ + | | | | (_) + ___| |______ __ _ __| |_ __ ___ _ _ __ + / _ | |______/ _` |/ _` | '_ ` _ \| | '_ \ + | __| | | (_| | (_| | | | | | | | | | | + \___|_| \__,_|\__,_|_| |_| |_|_|_| |_| + + :: Spring Boot :: (v2.1.0.RELEASE)
\ No newline at end of file diff --git a/UI source code/dns-dev-2.0/dns-system/src/main/resources/config/application-dev.yml b/UI source code/dns-dev-2.0/dns-system/src/main/resources/config/application-dev.yml new file mode 100644 index 0000000..983bf6d --- /dev/null +++ b/UI source code/dns-dev-2.0/dns-system/src/main/resources/config/application-dev.yml @@ -0,0 +1,118 @@ +#配置数据源 +spring: + datasource: + druid: + db-type: com.alibaba.druid.pool.DruidDataSource + driverClassName: net.sf.log4jdbc.sql.jdbcapi.DriverSpy + url: jdbc:log4jdbc:mysql://${DB_HOST:localhost}:${DB_PORT:3306}/${DB_NAME:eladmin}?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useSSL=false + username: ${DB_USER:root} + password: ${DB_PWD:root} + # 初始连接数 + initial-size: 5 + # 最小连接数 + min-idle: 15 + # 最大连接数 + max-active: 30 + # 超时时间(以秒数为单位) + remove-abandoned-timeout: 180 + # 获取连接超时时间 + max-wait: 3000 + # 连接有效性检测时间 + time-between-eviction-runs-millis: 60000 + # 连接在池中最小生存的时间 + min-evictable-idle-time-millis: 300000 + # 连接在池中最大生存的时间 + max-evictable-idle-time-millis: 900000 + # 指明连接是否被空闲连接回收器(如果有)进行检验.如果检测失败,则连接将被从池中去除 + test-while-idle: true + # 指明是否在从池中取出连接前进行检验,如果检验失败, 则从池中去除连接并尝试取出另一个 + test-on-borrow: true + # 是否在归还到池中前进行检验 + test-on-return: false + # 检测连接是否有效 + validation-query: select 1 + # 配置监控统计 + webStatFilter: + enabled: true + stat-view-servlet: + enabled: true + url-pattern: /druid/* + reset-enable: false + filter: + stat: + enabled: true + # 记录慢SQL + log-slow-sql: true + slow-sql-millis: 1000 + merge-sql: true + wall: + config: + multi-statement-allow: true + +# 登录相关配置 +login: + # 登录缓存 + cache-enable: true + # 是否限制单用户登录 + single-login: false + # 验证码 + login-code: + # 验证码类型配置 查看 LoginProperties 类 + code-type: arithmetic + # 登录图形验证码有效时间/分钟 + expiration: 2 + # 验证码高度 + width: 111 + # 验证码宽度 + height: 36 + # 内容长度 + length: 2 + # 字体名称，为空则使用默认字体 + font-name: + # 字体大小 + font-size: 25 + +#jwt +jwt: + header: Authorization + # 令牌前缀 + token-start-with: Bearer + # 必须使用最少88位的Base64对该令牌进行编码 + base64-secret: ZmQ0ZGI5NjQ0MDQwY2I4MjMxY2Y3ZmI3MjdhN2ZmMjNhODViOTg1ZGE0NTBjMGM4NDA5NzYxMjdjOWMwYWRmZTBlZjlhNGY3ZTg4Y2U3YTE1ODVkZDU5Y2Y3OGYwZWE1NzUzNWQ2YjFjZDc0NGMxZWU2MmQ3MjY1NzJmNTE0MzI= + # 令牌过期时间 此处单位/毫秒 ，默认4小时，可在此网站生成 https://www.convertworld.com/zh-hans/time/milliseconds.html + token-validity-in-seconds: 14400000 + # 在线用户key + online-key: online-token- + # 验证码 + code-key: code-key- + # token 续期检查时间范围（默认30分钟，单位毫秒），在token即将过期的一段时间内用户操作了，则给用户的token续期 + detect: 1800000 + # 续期时间范围，默认1小时，单位毫秒 + renew: 3600000 + +#是否允许生成代码，生产环境设置为false +generator: + enabled: true + +#是否开启 swagger-ui +swagger: + enabled: true + +# IP 本地解析 +ip: + local-parsing: true + +# 文件存储路径 +file: + mac: + path: ~/file/ + avatar: ~/avatar/ + linux: + path: /home/dns/file/ + avatar: /home/dns/avatar/ + windows: + path: C:\dns\file\ + avatar: C:\dns\avatar\ + # 文件大小 /M + maxSize: 100 + avatarMaxSize: 5 diff --git a/UI source code/dns-dev-2.0/dns-system/src/main/resources/config/application-prod.yml b/UI source code/dns-dev-2.0/dns-system/src/main/resources/config/application-prod.yml new file mode 100644 index 0000000..8e1993f --- /dev/null +++ b/UI source code/dns-dev-2.0/dns-system/src/main/resources/config/application-prod.yml @@ -0,0 +1,126 @@ +#配置数据源 +spring: + datasource: + druid: + db-type: com.alibaba.druid.pool.DruidDataSource + driverClassName: net.sf.log4jdbc.sql.jdbcapi.DriverSpy + url: jdbc:log4jdbc:mysql://${DB_HOST:localhost}:${DB_PORT:3306}/${DB_NAME:eladmin}?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useSSL=false + username: ${DB_USER:root} + password: ${DB_PWD:123456} + # 初始连接数 + initial-size: 5 + # 最小连接数 + min-idle: 15 + # 最大连接数 + max-active: 30 + # 获取连接超时时间 + max-wait: 5000 + # 连接有效性检测时间 + time-between-eviction-runs-millis: 60000 + # 连接在池中最小生存的时间 + min-evictable-idle-time-millis: 300000 + # 连接在池中最大生存的时间 + max-evictable-idle-time-millis: 900000 + # 指明连接是否被空闲连接回收器(如果有)进行检验.如果检测失败,则连接将被从池中去除 + test-while-idle: true + # 指明是否在从池中取出连接前进行检验,如果检验失败, 则从池中去除连接并尝试取出另一个 + test-on-borrow: true + # 是否在归还到池中前进行检验 + test-on-return: false + # 检测连接是否有效 + validation-query: select 1 + # 配置监控统计 + webStatFilter: + enabled: true + stat-view-servlet: + enabled: true + # 控制台管理用户名和密码 + url-pattern: /druid/* + reset-enable: false + login-username: admin + login-password: 123456 + filter: + stat: + enabled: true + # 记录慢SQL + log-slow-sql: true + slow-sql-millis: 1000 + merge-sql: true + wall: + config: + multi-statement-allow: true + +# 登录相关配置 +login: + # 登录缓存 + cache-enable: true + # 是否限制单用户登录 + single-login: false + # 验证码 + login-code: + # 验证码类型配置 查看 LoginProperties 类 + code-type: arithmetic + # 登录图形验证码有效时间/分钟 + expiration: 2 + # 验证码高度 + width: 111 + # 验证码宽度 + height: 36 + # 内容长度 + length: 2 + # 字体名称，为空则使用默认字体，如遇到线上乱码，设置其他字体即可 + font-name: + # 字体大小 + font-size: 25 + +#jwt +jwt: + header: Authorization + # 令牌前缀 + token-start-with: Bearer + # 必须使用最少88位的Base64对该令牌进行编码 + base64-secret: ZmQ0ZGI5NjQ0MDQwY2I4MjMxY2Y3ZmI3MjdhN2ZmMjNhODViOTg1ZGE0NTBjMGM4NDA5NzYxMjdjOWMwYWRmZTBlZjlhNGY3ZTg4Y2U3YTE1ODVkZDU5Y2Y3OGYwZWE1NzUzNWQ2YjFjZDc0NGMxZWU2MmQ3MjY1NzJmNTE0MzI= + # 令牌过期时间 此处单位/毫秒 ，默认2小时，可在此网站生成 https://www.convertworld.com/zh-hans/time/milliseconds.html + token-validity-in-seconds: 7200000 + # 在线用户key + online-key: online-token- + # 验证码 + code-key: code-key- + # token 续期检查时间范围（默认30分钟，单位默认毫秒），在token即将过期的一段时间内用户操作了，则给用户的token续期 + detect: 1800000 + # 续期时间范围，默认 1小时，这里单位毫秒 + renew: 3600000 + +# IP 本地解析 +ip: + local-parsing: false + +#是否允许生成代码，生产环境设置为false +generator: + enabled: false + +#如果生产环境要开启swagger，需要配置请求地址 +#springfox: +# documentation: +# swagger: +# v2: +# host: # 接口域名或外网ip + +#是否开启 swagger-ui +swagger: + enabled: false + +# 文件存储路径 +file: + mac: + path: ~/file/ + avatar: ~/avatar/ + linux: + path: /home/dns/file/ + avatar: /home/dns/avatar/ + windows: + path: C:\dns\file\ + avatar: C:\dns\avatar\ + # 文件大小 /M + maxSize: 100 + avatarMaxSize: 5 diff --git a/UI source code/dns-dev-2.0/dns-system/src/main/resources/config/application.yml b/UI source code/dns-dev-2.0/dns-system/src/main/resources/config/application.yml new file mode 100644 index 0000000..a43407b --- /dev/null +++ b/UI source code/dns-dev-2.0/dns-system/src/main/resources/config/application.yml @@ -0,0 +1,97 @@ +server: + port: 8888 + +spring: + freemarker: + check-template-location: false + profiles: + active: dev + jackson: + time-zone: GMT+8 + data: + redis: + repositories: + enabled: false +# pid: +# file: /自行指定位置/dns.pid + + #配置 Jpa + jpa: + hibernate: + ddl-auto: none + open-in-view: true + properties: + hibernate: + dialect: org.hibernate.dialect.MySQL5InnoDBDialect + + redis: + #数据库索引 + database: ${REDIS_DB:0} + host: ${REDIS_HOST:127.0.0.1} + port: ${REDIS_PORT:6379} + password: ${REDIS_PWD:} + #连接超时时间 + timeout: 5000 + +task: + pool: + # 核心线程池大小 + core-pool-size: 10 + # 最大线程数 + max-pool-size: 30 + # 活跃时间 + keep-alive-seconds: 60 + # 队列容量 + queue-capacity: 50 + +#七牛云 +qiniu: + # 文件大小 /M + max-size: 15 + +#邮箱验证码有效时间/秒 +code: + expiration: 300 + +#密码加密传输，前端公钥加密，后端私钥解密 +rsa: + private_key: MIIBUwIBADANBgkqhkiG9w0BAQEFAASCAT0wggE5AgEAAkEA0vfvyTdGJkdbHkB8mp0f3FE0GYP3AYPaJF7jUd1M0XxFSE2ceK3k2kw20YvQ09NJKk+OMjWQl9WitG9pB6tSCQIDAQABAkA2SimBrWC2/wvauBuYqjCFwLvYiRYqZKThUS3MZlebXJiLB+Ue/gUifAAKIg1avttUZsHBHrop4qfJCwAI0+YRAiEA+W3NK/RaXtnRqmoUUkb59zsZUBLpvZgQPfj1MhyHDz0CIQDYhsAhPJ3mgS64NbUZmGWuuNKp5coY2GIj/zYDMJp6vQIgUueLFXv/eZ1ekgz2Oi67MNCk5jeTF2BurZqNLR3MSmUCIFT3Q6uHMtsB9Eha4u7hS31tj1UWE+D+ADzp59MGnoftAiBeHT7gDMuqeJHPL4b+kC+gzV4FGTfhR9q3tTbklZkD2A== + +# 内存用户缓存配置 +user-cache: + # 最小回收数(当缓存数量达到此值时进行回收) + min-evictable-size: 512 + # 最小回收间隔 + min-evictable-interval: 1800000 + # 最小存活时间 (ms) + min-idle-time: 3600000 + + +#mybatis +mybatis-plus: + mapper-locations: classpath*:/mapper/**/*.xml + #实体扫描，多个package用逗号或者分号分隔 + typeAliasesPackage: com.example.modules.*.domain + global-config: + #数据库相关配置 + db-config: + #主键类型 AUTO:"数据库ID自增", INPUT:"用户输入ID", ID_WORKER:"全局唯一ID (数字类型唯一ID)", UUID:"全局唯一ID UUID"; + id-type: AUTO + #字段策略 IGNORED:"忽略判断",NOT_NULL:"非 NULL 判断"),NOT_EMPTY:"非空判断" + field-strategy: NOT_NULL + #驼峰下划线转换 + column-underline: true + logic-delete-value: 1 + logic-not-delete-value: 0 + banner: false + #原生配置 + configuration: + map-underscore-to-camel-case: true + cache-enabled: false + call-setters-on-nulls: true + jdbc-type-for-null: 'null' + +#showSql +logging: + level: + com.example.modules.system.mapper: debug diff --git a/UI source code/dns-dev-2.0/dns-system/src/main/resources/generator.properties b/UI source code/dns-dev-2.0/dns-system/src/main/resources/generator.properties new file mode 100644 index 0000000..2ed9370 --- /dev/null +++ b/UI source code/dns-dev-2.0/dns-system/src/main/resources/generator.properties @@ -0,0 +1,27 @@ +#数据库类型转Java类型 +tinyint=Integer +smallint=Integer +mediumint=Integer +int=Integer +integer=Integer + +bigint=Long + +float=Float + +double=Double + +decimal=BigDecimal + +bit=Boolean + +char=String +varchar=String +tinytext=String +text=String +mediumtext=String +longtext=String + +date=Timestamp +datetime=Timestamp +timestamp=Timestamp
\ No newline at end of file diff --git a/UI source code/dns-dev-2.0/dns-system/src/main/resources/log4jdbc.log4j2.properties b/UI source code/dns-dev-2.0/dns-system/src/main/resources/log4jdbc.log4j2.properties new file mode 100644 index 0000000..302525f --- /dev/null +++ b/UI source code/dns-dev-2.0/dns-system/src/main/resources/log4jdbc.log4j2.properties @@ -0,0 +1,4 @@ +# If you use SLF4J. First, you need to tell log4jdbc-log4j2 that you want to use the SLF4J logger +log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator +log4jdbc.auto.load.popular.drivers=false +log4jdbc.drivers=com.mysql.cj.jdbc.Driver
\ No newline at end of file diff --git a/UI source code/dns-dev-2.0/dns-system/src/main/resources/logback.xml b/UI source code/dns-dev-2.0/dns-system/src/main/resources/logback.xml new file mode 100644 index 0000000..8a399c4 --- /dev/null +++ b/UI source code/dns-dev-2.0/dns-system/src/main/resources/logback.xml @@ -0,0 +1,45 @@ +<?xml version="1.0" encoding="UTF-8"?> +<configuration scan="true" scanPeriod="30 seconds" debug="false"> + <contextName>DiamondV</contextName> + <property name="log.charset" value="utf-8" /> + <property name="log.pattern" value="%contextName- %red(%d{yyyy-MM-dd HH:mm:ss}) %green([%thread]) %highlight(%-5level) %boldMagenta(%logger{36}) - %msg%n" /> + + <!--输出到控制台--> + <appender name="console" class="ch.qos.logback.core.ConsoleAppender"> + <encoder> + <pattern>${log.pattern}</pattern> + <charset>${log.charset}</charset> + </encoder> + </appender> + + <!--普通日志输出到控制台--> + <root level="info"> + <appender-ref ref="console" /> + </root> + + <!--监控sql日志输出,如需监控 Sql 打印，请设置为 INFO --> + <logger name="jdbc.sqlonly" level="INFO" additivity="false"> + <appender-ref ref="console" /> + </logger> + + <logger name="jdbc.resultset" level="ERROR" additivity="false"> + <appender-ref ref="console" /> + </logger> + + <!-- 如想看到表格数据，将OFF改为INFO --> + <logger name="jdbc.resultsettable" level="OFF" additivity="false"> + <appender-ref ref="console" /> + </logger> + + <logger name="jdbc.connection" level="OFF" additivity="false"> + <appender-ref ref="console" /> + </logger> + + <logger name="jdbc.sqltiming" level="OFF" additivity="false"> + <appender-ref ref="console" /> + </logger> + + <logger name="jdbc.audit" level="OFF" additivity="false"> + <appender-ref ref="console" /> + </logger> +</configuration> 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> |