CN-433 导出数据库数据json或csv

10 files changed, 209 insertions, 30 deletions

diff --git a/galaxy-job-executor/pom.xml b/galaxy-job-executor/pom.xml
index e36fcfb..bd40cb5 100644
--- a/galaxy-job-executor/pom.xml
+++ b/galaxy-job-executor/pom.xml
@@ -85,6 +85,22 @@
             <artifactId>jasypt-spring-boot-starter</artifactId>
             <version>2.1.1</version>
         </dependency>
+        <!-- http://repo1.maven.org/maven2/com/xuxueli/xxl-excel-core/ -->
+        <dependency>
+            <groupId>com.xuxueli</groupId>
+            <artifactId>xxl-excel</artifactId>
+            <version>1.0.0</version>
+        </dependency>
+        <dependency>
+            <groupId>mysql</groupId>
+            <artifactId>mysql-connector-java</artifactId>
+            <version>${mysql-connector-java.version}</version>
+        </dependency>
+        <dependency>
+            <groupId>com.zaxxer</groupId>
+            <artifactId>HikariCP</artifactId>
+            <version>3.4.5</version>
+        </dependency>
     </dependencies>
 
     <build>
diff --git a/galaxy-job-executor/src/main/java/com/mesalab/executor/core/utils/DbUtils.java b/galaxy-job-executor/src/main/java/com/mesalab/executor/core/utils/DBUtils.java
index c4bf46f..456beb5 100644
--- a/galaxy-job-executor/src/main/java/com/mesalab/executor/core/utils/DbUtils.java
+++ b/galaxy-job-executor/src/main/java/com/mesalab/executor/core/utils/DBUtils.java
@@ -9,7 +9,7 @@ import java.sql.SQLException;
 import java.util.List;
 import java.util.Map;
 
-public class DbUtils {
+public class DBUtils {
 
     private Log logger = Log.get();
     private static HikariDataSource hikariDataSource;
diff --git a/galaxy-job-executor/src/main/java/com/mesalab/executor/core/utils/JobUtil.java b/galaxy-job-executor/src/main/java/com/mesalab/executor/core/utils/JobUtil.java
index ffdf7b7..f264898 100644
--- a/galaxy-job-executor/src/main/java/com/mesalab/executor/core/utils/JobUtil.java
+++ b/galaxy-job-executor/src/main/java/com/mesalab/executor/core/utils/JobUtil.java
@@ -61,4 +61,22 @@ public class JobUtil {
             return null;
         }
     }
+    /**
+     * 获取map中第一个非空数据值
+     *
+     * @param <K> Key的类型
+     * @param <V> Value的类型
+     * @param map 数据源
+     * @return 返回的值
+     */
+    public static <K, V> V getFirstNotNull(Map<K, V> map) {
+        V obj = null;
+        for (Map.Entry<K, V> entry : map.entrySet()) {
+            obj = entry.getValue();
+            if (obj != null) {
+                break;
+            }
+        }
+        return obj;
+    }
 }
diff --git a/galaxy-job-executor/src/main/java/com/mesalab/executor/jobhandler/DataExtractJob.java b/galaxy-job-executor/src/main/java/com/mesalab/executor/jobhandler/DataExtractJob.java
index 2cf34ec..26ced93 100644
--- a/galaxy-job-executor/src/main/java/com/mesalab/executor/jobhandler/DataExtractJob.java
+++ b/galaxy-job-executor/src/main/java/com/mesalab/executor/jobhandler/DataExtractJob.java
@@ -37,21 +37,21 @@ public class DataExtractJob {
 
     /**
      * {
-     * 	"source": {
-     * 		"type": "file",
-     * 		"path": "/",
-     * 		"fileType": "json,xlsx",
-     * 		"source_table": "ioc"
-     *        },
-     * 	"transform": {"type": "websketch"},
-     * 	"sink": {
-     * 		"type": "mariadb",
-     * 		"ip": "192.168.44.*",
-     * 		"database": "web_skecth",
-     * 		"table": "ioc_malware",
-     * 		"username": "root",
-     * 		"pin": ""
-     *    }
+     * "source": {
+     * "type": "file",
+     * "path": "/",
+     * "fileType": "json,xlsx",
+     * "source_table": "ioc"
+     * },
+     * "transform": {"type": "websketch"},
+     * "sink": {
+     * "type": "mariadb",
+     * "ip": "192.168.44.*",
+     * "database": "web_skecth",
+     * "table": "ioc_malware",
+     * "username": "root",
+     * "pin": ""
+     * }
      * }
      *
      * @param params
@@ -70,8 +70,12 @@ public class DataExtractJob {
             Map<String, String> sinkParams = paramsMap.get("sink");
 
             Map<String, List> sourceResult = dataSourceService.adapt(sourceParams);
-            List<Map> transformResult = dataTransformService.adapt(transformParams, sourceResult);
-            dataSinkService.adapt(transformResult, sinkParams);
+            if (ObjectUtil.isNotEmpty(transformParams)) {
+                Map<String, List> transformResult = dataTransformService.adapt(transformParams, sourceResult);
+                dataSinkService.adapt(transformResult, sinkParams);
+            } else {
+                dataSinkService.adapt(sourceResult, sinkParams);
+            }
             return ReturnT.SUCCESS;
         } catch (Exception e) {
             e.printStackTrace();
@@ -98,7 +102,7 @@ public class DataExtractJob {
             XxlJobLogger.log("sink is empty ! ");
             return null;
         }
-        if (ObjectUtil.isEmpty(param.get("transform"))) {
+        if (ObjectUtil.isNull(param.get("transform"))) {
             XxlJobLogger.log("transform is empty ! ");
             return null;
         }
diff --git a/galaxy-job-executor/src/main/java/com/mesalab/executor/pojo/DBParam.java b/galaxy-job-executor/src/main/java/com/mesalab/executor/pojo/DBParam.java
new file mode 100644
index 0000000..ee0378f
--- /dev/null
+++ b/galaxy-job-executor/src/main/java/com/mesalab/executor/pojo/DBParam.java
@@ -0,0 +1,27 @@
+package com.mesalab.executor.pojo;
+
+import lombok.AllArgsConstructor;
+import lombok.Builder;
+import lombok.Data;
+import lombok.NoArgsConstructor;
+
+/**
+ * @description:
+ * @author: zhq
+ * @create: 2022-04-02
+ **/
+@Data
+@Builder
+@NoArgsConstructor
+@AllArgsConstructor
+public class DBParam {
+
+    private String type;
+    private String ip;
+    private String database;
+    private String table;
+    private String username;
+    private String pin;
+    private String sql;
+
+}
diff --git a/galaxy-job-executor/src/main/java/com/mesalab/executor/pojo/IocMalware.java b/galaxy-job-executor/src/main/java/com/mesalab/executor/pojo/IocMalware.java
new file mode 100644
index 0000000..632edcc
--- /dev/null
+++ b/galaxy-job-executor/src/main/java/com/mesalab/executor/pojo/IocMalware.java
@@ -0,0 +1,31 @@
+package com.mesalab.executor.pojo;
+
+import lombok.Data;
+
+/**
+ * @description:
+ * @author: zhq
+ * @create: 2022-03-28
+ **/
+@Data
+public class IocMalware {
+
+    public Integer id;//	唯一性id
+    public String ioc_value;//	ioc值
+    public String ioc_type;//	ioc类别
+    public String threat_type;//	威胁类别
+    public String malware_name;//	malware名称
+    public String malware_alias;//	malware别名
+    public String mitre_attack_description;//	mitre_attack获取的malware描述
+    public String mitre_attack_platforms;//	mitre_attack获取的malware平台
+    public String mitre_attack_techniques;//	mitre_attack获取的malware技术
+    public String mitre_attack_groups;//	mitre_attack获取的malware对应组
+    public String confidence_level;//	置信度
+    public String reference;//	参考地址
+    public String source;//	信息来源
+    public String first_report_time;//	首次报告时间
+    public String last_seen_time;//	最后出现时间
+    public Long create_time;//	初次创建时间
+    public Long update_time;//	最近更新时间
+
+}
diff --git a/galaxy-job-executor/src/main/java/com/mesalab/executor/service/DataSinkService.java b/galaxy-job-executor/src/main/java/com/mesalab/executor/service/DataSinkService.java
index 0c3a72b..d5516db 100644
--- a/galaxy-job-executor/src/main/java/com/mesalab/executor/service/DataSinkService.java
+++ b/galaxy-job-executor/src/main/java/com/mesalab/executor/service/DataSinkService.java
@@ -1,11 +1,15 @@
 package com.mesalab.executor.service;
 
+import cn.hutool.core.io.file.FileWriter;
 import cn.hutool.core.util.StrUtil;
 import cn.hutool.log.Log;
-import com.mesalab.executor.core.utils.DbUtils;
+import com.mesalab.executor.core.utils.DBUtils;
+import com.mesalab.executor.core.utils.JobUtil;
+import com.zdjizhi.utils.DateUtils;
 import org.springframework.stereotype.Service;
 import org.springframework.transaction.annotation.Transactional;
 
+import java.io.File;
 import java.sql.Connection;
 import java.sql.PreparedStatement;
 import java.sql.SQLException;
@@ -21,12 +25,29 @@ import java.util.Map;
 public class DataSinkService {
     private Log logger = Log.get();
 
-
-    public void adapt(List<Map> transformResult, Map<String, String> sinkParams) throws Exception {
+    public void adapt(Map<String, List> transformResult, Map<String, String> sinkParams) throws Exception {
         String type = sinkParams.get("type");
         //1. 文件类型 2. 选择处理工具 3. 转为格式数据
         if ("mariadb".equals(type) || "mysql".equals(type)) {
-            mysqlSink(transformResult, sinkParams);
+            mysqlSink(JobUtil.getFirstNotNull(transformResult), sinkParams);
+        } else if ("file".equals(type)) {
+            fileSink(JobUtil.getFirstNotNull(transformResult), sinkParams);
+        }
+    }
+
+    public void fileSink(List<Map> transformResult, Map<String, String> sinkParams) throws Exception {
+        String fileType = sinkParams.get("fileType");
+        String path = sinkParams.get("path");
+        String prefix = sinkParams.get("prefix");
+        String date = DateUtils.getCurrentDate("yyyyMMdd");
+        String filePath = StrUtil.concat(false, path, prefix, "_", date);
+        if ("json".equals(fileType)) {
+            FileWriter fileWriter = new FileWriter(filePath.concat(".json"));
+            File file = fileWriter.writeLines(transformResult);
+            logger.info("download json file to path {}", file.getAbsolutePath());
+        } else if ("csv".equals(fileType)) {
+            File file = JobUtil.createCsvFile(transformResult, filePath.concat(".json"));
+            logger.info("download csv file to path {}", file.getAbsolutePath());
         }
     }
 
@@ -43,16 +64,15 @@ public class DataSinkService {
     @Transactional(rollbackFor = Exception.class)
     public void exec(List<Map> transformResult, Map<String, String> sinkParams) throws Exception {
         Connection conn = null;
-        DbUtils dbUtils = null;
+        DBUtils dbUtils = null;
         PreparedStatement pst = null;
         try {
             String tableName = sinkParams.get("table");
-            dbUtils = new DbUtils();
+            dbUtils = new DBUtils();
             //创建数据库连接库对象
             conn = dbUtils.getDBConn(sinkParams);
             String sql = dbUtils.getInsertSql(transformResult, tableName);
 
-
             long start = System.currentTimeMillis();
             conn.setAutoCommit(false);
             pst = conn.prepareStatement(sql);
diff --git a/galaxy-job-executor/src/main/java/com/mesalab/executor/service/DataSourceService.java b/galaxy-job-executor/src/main/java/com/mesalab/executor/service/DataSourceService.java
index 242c8e8..7ddf60c 100644
--- a/galaxy-job-executor/src/main/java/com/mesalab/executor/service/DataSourceService.java
+++ b/galaxy-job-executor/src/main/java/com/mesalab/executor/service/DataSourceService.java
@@ -1,17 +1,27 @@
 package com.mesalab.executor.service;
 
+import cn.hutool.core.bean.BeanUtil;
 import cn.hutool.core.io.FileUtil;
 import cn.hutool.core.io.IoUtil;
 import cn.hutool.core.io.file.FileReader;
+import cn.hutool.core.map.MapUtil;
 import cn.hutool.core.util.StrUtil;
+import cn.hutool.db.DbUtil;
+import cn.hutool.db.Entity;
+import cn.hutool.db.handler.EntityListHandler;
+import cn.hutool.db.sql.SqlExecutor;
 import cn.hutool.json.JSONUtil;
+import cn.hutool.log.Log;
 import cn.hutool.poi.excel.ExcelReader;
 import cn.hutool.poi.excel.ExcelUtil;
+import com.mesalab.executor.core.utils.DBUtils;
+import com.mesalab.executor.pojo.DBParam;
 import org.apache.commons.io.filefilter.FileFilterUtils;
 import org.springframework.stereotype.Service;
 
 import java.io.File;
 import java.nio.file.Paths;
+import java.sql.Connection;
 import java.util.HashMap;
 import java.util.List;
 import java.util.Map;
@@ -25,15 +35,31 @@ import java.util.stream.Collectors;
 @Service
 public class DataSourceService {
 
-    public Map<String, List> adapt(Map<String, String> sourceParams) throws Exception{
+    private Log logger = Log.get();
+
+    public Map<String, List> adapt(Map<String, String> sourceParams) throws Exception {
         String type = sourceParams.get("type");
         //1. 文件类型 2. 选择处理工具 3. 转为格式数据
         if ("file".equals(type)) {
             return fileSource(sourceParams);
+        } else if ("mysql".equals(type) || "mariadb".equals(type)) {
+            return MapUtil.of("mysql", mysqlSource(sourceParams));
         }
         return null;
     }
 
+    private List<Map> mysqlSource(Map<String, String> sourceParams) throws Exception {
+        DBParam dbParam = BeanUtil.fillBeanWithMap(sourceParams, new DBParam(), false);
+        DBUtils dbUtils = new DBUtils();
+        Connection conn = dbUtils.getDBConn(sourceParams);
+        String sql = dbParam.getSql();
+        List<Entity> entityList = SqlExecutor.query(conn, sql, new EntityListHandler());
+        logger.info("query result size {}", entityList.size());
+        List<Map> collect = entityList.stream().map(x -> BeanUtil.beanToMap(x)).collect(Collectors.toList());
+        DbUtil.close(conn);
+        return collect;
+    }
+
     /**
      * v1: 支持 json ,xlsx
      * <p>
diff --git a/galaxy-job-executor/src/main/java/com/mesalab/executor/service/DataTransformService.java b/galaxy-job-executor/src/main/java/com/mesalab/executor/service/DataTransformService.java
index 5fb51f0..cc2b92d 100644
--- a/galaxy-job-executor/src/main/java/com/mesalab/executor/service/DataTransformService.java
+++ b/galaxy-job-executor/src/main/java/com/mesalab/executor/service/DataTransformService.java
@@ -1,5 +1,6 @@
 package com.mesalab.executor.service;
 
+import cn.hutool.core.map.MapUtil;
 import cn.hutool.core.util.StrUtil;
 import cn.hutool.json.JSONNull;
 import cn.hutool.json.JSONUtil;
@@ -30,11 +31,10 @@ public class DataTransformService {
      * @param params
      * @return
      */
-    public List<Map> adapt(Map<String, String> params, Map<String, List> sourceResult) throws Exception {
+    public Map<String, List> adapt(Map<String, String> params, Map<String, List> sourceResult) throws Exception {
         if ("websketch".equals(params.get("type"))) {
-            return webSketch(sourceResult);
+            return MapUtil.of("mysql", webSketch(sourceResult));
         }
-
         return null;
     }
 
diff --git a/galaxy-job-executor/src/test/java/com/mesalab/executor/test/DataExtractTest.java b/galaxy-job-executor/src/test/java/com/mesalab/executor/test/DataExtractTest.java
index ab9fd72..234c5bc 100644
--- a/galaxy-job-executor/src/test/java/com/mesalab/executor/test/DataExtractTest.java
+++ b/galaxy-job-executor/src/test/java/com/mesalab/executor/test/DataExtractTest.java
@@ -6,11 +6,15 @@ import cn.hutool.core.io.file.FileReader;
 import cn.hutool.core.io.file.FileWriter;
 import cn.hutool.core.map.CaseInsensitiveMap;
 import cn.hutool.core.util.ObjectUtil;
+import cn.hutool.core.util.StrUtil;
 import cn.hutool.json.JSONUtil;
+import cn.hutool.log.Log;
 import cn.hutool.poi.excel.ExcelReader;
 import cn.hutool.poi.excel.ExcelUtil;
+import com.mesalab.executor.core.utils.JobUtil;
 import com.zaxxer.hikari.HikariConfig;
 import com.zaxxer.hikari.HikariDataSource;
+import com.zdjizhi.utils.DateUtils;
 import org.apache.commons.io.filefilter.FileFilterUtils;
 import org.junit.Test;
 import org.junit.runner.RunWith;
@@ -189,4 +193,37 @@ public class DataExtractTest {
 
     }
 
+    private Log logger = Log.get();
+
+    @Test
+    public void fileTest(){
+        HashMap<Object, Object> map = new HashMap<>();
+        HashMap<Object, Object> map1 = new HashMap<>();
+        HashMap<Object, Object> map2 = new HashMap<>();
+        map.put("a","b");
+        map1.put("b","b");
+        map2.put("c","b");
+        map.put("d","b");
+        List<Map> transformResult = new ArrayList();
+        transformResult.add(map);
+
+        String fileType = "csv";
+        String path = "D:\\test\\test\\1\\";
+        String prefix = "test";
+        String date = DateUtils.getCurrentDate("yyyyMMdd");
+        String filePath = StrUtil.concat(false, path, prefix, "-", date);
+        System.err.println("################# "+filePath);
+        if ("json".equals(fileType)) {
+            FileWriter fileWriter = new FileWriter(filePath+".json");
+            for (Map o : transformResult) {
+                String s = JSONUtil.toJsonStr(o);
+                File file = fileWriter.write(s,false);
+            }
+            logger.info("download file to path {}", filePath);
+        } else if ("csv".equals(fileType)) {
+            File file = JobUtil.createCsvFile(transformResult, StrUtil.concat(false, filePath, ".csv"));
+            logger.info("download file to path {}", file.getAbsolutePath());
+        }
+    }
+
 }
\ No newline at end of file