背景:公司疫情填报系统。公司有A、B两类员工,每日需要填写疫情相关的信息。这批数据存在mysql之中,个人填报信息每天最多存1条记录,多次提交的数据会将最新记录保留。其中主要涉及三张表,一张员工信息表、一张在公司的疫情填报表,一张不在公司的疫情填报表。两张表中关联字段user_id。
整体的处理思路:kafka -> flink -> redis
flink中清洗思路:kafka数据:MySQL的操作日志。主要有INSERT和DELETE对三张表的操作类型;三张表中员工表作为中间表,进行两两关联;再将关联的流在根据异常标准过滤出来,最后在分成A、B两种类型写入到Redis中。
整体的思路:JSON流 -> INSERT流 和 DELETE流(分流)
INSERT流 -> 三张表的字符流 (过滤) -> 将字符流转换为对象流
员工对象流作为中间流,两两关联 -> 变成两条字符流(关联)
两条字符流 -> 两条异常信息流(过滤) -> 变成四条字符流(根据A、B类型过滤) -> 字符流转换成对象流
四条对象流 -> Redis
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<dependencies>
<!-- https://mvnrepository.com/artifact/org.apache.flink/flink-scala -->
<dependency>
<groupId>org.apache.flink</groupId>
<artifactId>flink-scala_2.11</artifactId>
<version>1.10.1</version>
</dependency>
<dependency>
<groupId>org.apache.flink</groupId>
<artifactId>flink-streaming-scala_2.11</artifactId>
<version>1.10.1</version>
</dependency>
<dependency>
<groupId>org.apache.flink</groupId>
<artifactId>flink-clients_2.11</artifactId>
<version>1.10.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.flink/flink-connector-kafka-0.11 -->
<dependency>
<groupId>org.apache.flink</groupId>
<artifactId>flink-connector-kafka-0.11_2.11</artifactId>
<version>1.10.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>6.0.6</version>
</dependency>
<!-- 解析json字符串 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.62</version>
</dependency>
<!-- redis的依赖 -->
<dependency>
<groupId>org.apache.bahir</groupId>
<artifactId>flink-connector-redis_2.11</artifactId>
<version>1.0</version>
</dependency>
<!-- redis -->
<dependency>
<groupId>redis.clients</groupId>
<artifactId>jedis</artifactId>
<version>2.9.0</version>
</dependency>
</dependencies>
<build>
<plugins>
<!-- 该插件用于将Scala代码编译成class文件 -->
<plugin>
<groupId>net.alchim31.maven</groupId>
<artifactId>scala-maven-plugin</artifactId>
<version>3.2.2</version>
<executions>
<execution>
<!-- 声明绑定到maven的compile阶段 -->
<goals>
<goal>compile</goal>
<goal>testCompile</goal>
</goals>
</execution>
</executions>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-assembly-plugin</artifactId>
<version>3.0.0</version>
<configuration>
<descriptorRefs>
<descriptorRef>jar-with-dependencies</descriptorRef>
</descriptorRefs>
</configuration>
<executions>
<execution>
<id>make-assembly</id>
<phase>package</phase>
<goals>
<goal>single</goal>
</goals>
</execution>
</executions>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>8</source>
<target>8</target>
</configuration>
</plugin>
</plugins>
</build>
</project>
import com.alibaba.fastjson.JSON; import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; import com.hailiang.epidemic.beans.*; import com.mysql.cj.jdbc.PreparedStatement; import org.apache.flink.api.common.functions.FilterFunction; import org.apache.flink.api.common.functions.JoinFunction; import org.apache.flink.api.common.functions.MapFunction; import org.apache.flink.api.common.restartstrategy.RestartStrategies; import org.apache.flink.configuration.Configuration; import org.apache.flink.streaming.api.TimeCharacteristic; import org.apache.flink.streaming.api.collector.selector.OutputSelector; import org.apache.flink.streaming.api.datastream.DataStream; import org.apache.flink.streaming.api.datastream.DataStreamSource; import org.apache.flink.streaming.api.datastream.SingleOutputStreamOperator; import org.apache.flink.streaming.api.datastream.SplitStream; import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment; import org.apache.flink.streaming.api.functions.TimestampAssigner; import org.apache.flink.streaming.api.functions.sink.RichSinkFunction; import org.apache.flink.streaming.api.functions.timestamps.BoundedOutOfOrdernessTimestampExtractor; import org.apache.flink.streaming.api.windowing.assigners.TumblingProcessingTimeWindows; import org.apache.flink.streaming.api.windowing.time.Time; import org.apache.flink.streaming.connectors.kafka.FlinkKafkaConsumer011; import org.apache.flink.streaming.connectors.redis.RedisSink; import org.apache.flink.streaming.connectors.redis.common.config.FlinkJedisPoolConfig; import org.apache.flink.streaming.connectors.redis.common.mapper.RedisCommand; import org.apache.flink.streaming.connectors.redis.common.mapper.RedisCommandDescription; import org.apache.flink.streaming.connectors.redis.common.mapper.RedisMapper; import org.apache.flink.streaming.util.serialization.SimpleStringSchema; import java.sql.Connection; import java.sql.DriverManager; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.*; public class EpidemicAbnormal { public static void main(String[] args) throws Exception { StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment(); // 时间语义 env.setStreamTimeCharacteristic(TimeCharacteristic.EventTime); // 重启策略 故障后重启三次,每次间隔10秒 // env.setRestartStrategy(RestartStrategies.fixedDelayRestart( // 3,new Long(10*1000) // )); // 失败率重启 env.setRestartStrategy(RestartStrategies .failureRateRestart(3, org.apache.flink.api.common.time.Time.days(1), org.apache.flink.api.common.time.Time.minutes(1))); // kafka连接配置文件 Properties properties = new Properties(); properties.setProperty("bootstrap.servers", ""); properties.setProperty("group.id", "epidemicflink"); properties.setProperty("key.deserializer", "org.apache.kafka.common.serialization.StringDeserializer"); properties.setProperty("value.deserializer", "org.apache.kafka.common.serialization.StringDeserializer"); properties.setProperty("auto.offset.reset", "latest"); // redis配置 FlinkJedisPoolConfig config = new FlinkJedisPoolConfig.Builder() .setHost("") .setPort(6379) .build(); // 原始流:所有表的操作都在内 DataStreamSource<String> inputStream = env.addSource(new FlinkKafkaConsumer011<String>("epidemic_db_1", new SimpleStringSchema(), properties)).setParallelism(1); // 分流 SplitStream<String> splitStream = inputStream.split(new MyOutputSelector()); // 过滤:三张表 // eceb_epidemic_user => 对象 // eceb_epidemic_before => 对象 // eceb_epidemic_after => 对象 SingleOutputStreamOperator<String> userStream = splitStream.select("insertStream").filter(new UserFilterFunction()); SingleOutputStreamOperator<epidemicUser> epidemicUserStream = userStream.map(new UserMapperFunction()) .assignTimestampsAndWatermarks(new BoundedOutOfOrdernessTimestampExtractor<epidemicUser>(Time.milliseconds(500)) { @Override public long extractTimestamp(epidemicUser element) { String[] times = element.getUpdate_time().split(" "); String[] day = times[0].split("-"); String[] seconds = times[1].split(":"); long time = Date.UTC(new Integer(day[0]), new Integer(day[1]), new Integer(day[2]), new Integer(seconds[0]), new Integer(seconds[1]), new Integer(seconds[2])); return time; } }).setParallelism(1); // epidemicUserStream.print("epidemicUserStream"); SingleOutputStreamOperator<String> BeforeStream = splitStream.select("insertStream").filter(new BeforeFilterFunction()); SingleOutputStreamOperator<epidemicBefore> epidemicBeforeStream = BeforeStream.map(new BeforeMapperFunction()) .assignTimestampsAndWatermarks(new BoundedOutOfOrdernessTimestampExtractor<epidemicBefore>(Time.milliseconds(500)) { @Override public long extractTimestamp(epidemicBefore element) { // String dateTmp = element.getUpdate_time().replace("/-/g", "/"); String[] times = element.getUpdate_time().split(" "); String[] day = times[0].split("-"); String[] seconds = times[1].split(":"); long time = Date.UTC(new Integer(day[0]), new Integer(day[1]), new Integer(day[2]), new Integer(seconds[0]), new Integer(seconds[1]), new Integer(seconds[2])); return time; } }).setParallelism(1); // epidemicBeforeStream.print("epidemicBeforeStream"); SingleOutputStreamOperator<String> AfterStream = splitStream.select("insertStream").filter(new AfterFilterFunction()); SingleOutputStreamOperator<epidemicAfter> epidemicAfterStream = AfterStream.map(new AfterMapperFunction()) .keyBy("user_id") .assignTimestampsAndWatermarks(new BoundedOutOfOrdernessTimestampExtractor<epidemicAfter>(Time.milliseconds(500)) { @Override public long extractTimestamp(epidemicAfter element) { String[] times = element.getUpdate_time().split(" "); String[] day = times[0].split("-"); String[] seconds = times[1].split(":"); long time = Date.UTC(new Integer(day[0]), new Integer(day[1]), new Integer(day[2]), new Integer(seconds[0]), new Integer(seconds[1]), new Integer(seconds[2])); return time; } }).setParallelism(1); // epidemicAfterStream.print("epidemicAfterStream"); // eceb_epidemic_user + eceb_epidemic_before DataStream<String> beforeUserStream = epidemicUserStream.join(epidemicBeforeStream) .where(i1 -> i1.getUser_id()) .equalTo(i2 -> i2.getUser_id()) .window(TumblingProcessingTimeWindows.of(Time.seconds(3))) .apply(new UserJoinBefore()); // eceb_epidemic_user + eceb_epidemic_after DataStream<String> afterUserStream = epidemicUserStream.join(epidemicAfterStream) .where(i1 -> i1.getUser_id()) .equalTo(i2 -> i2.getUser_id()) .window(TumblingProcessingTimeWindows.of(Time.seconds(3))) .apply(new UserJoinAfter()); // 分流:eceb_epidemic_user + eceb_epidemic_before => student + teacher SplitStream<String> beforeAllStream = beforeUserStream.split(new beforeAllStream()); // 分流:eceb_epidemic_user + eceb_epidemic_after => student + teacher SplitStream<String> afterAllStream = afterUserStream.split(new afterAllStream()); //学生异常信息流 // eceb_epidemic_user + eceb_epidemic_before => student的操作流 DataStream<beforeAll> studentBeforeStream = beforeAllStream.select("studentBeforeStream").map(new beforeAllMapper()); // eceb_epidemic_user + eceb_epidemic_after => student 的操作流 DataStream<afterAll> studentAfterStream = afterAllStream.select("studentAfterStream").map(new afterAllMapper()); //教师、职工异常信息流 // eceb_epidemic_user + eceb_epidemic_before => teacher的操作流 DataStream<beforeAll> teacherBeforeStream = beforeAllStream.select("teacherBeforeStream").map(new beforeAllMapper()); // eceb_epidemic_user + eceb_epidemic_after => teacher的操作流 DataStream<afterAll> teacherAfterStream = afterAllStream.select("teacherAfterStream").map(new afterAllMapper()); // 2021-02-02 寒假学生填报所用到的表 SingleOutputStreamOperator<beforeAll> beforeStudentAbnormal = studentBeforeStream.filter(new beforeStudentFilter()); SingleOutputStreamOperator<afterAll> afterStudentAbnormal = studentAfterStream.filter(new afterStudentFilter()); // 2021-02-02 寒假教师、职工填报的表 SingleOutputStreamOperator<afterAll> afterTeacherAbnormal = teacherAfterStream.filter(new afterTeacherFilter()); SingleOutputStreamOperator<beforeAll> beforeTeacherAbnormal = teacherBeforeStream.filter(new beforeTeacherFilter()); // Sink阶段 //写入所有学生信息 // studentBeforeStream.addSink(new RedisSink<>(config,new RedisBeforeStudent())).setParallelism(1); //写入教师所有信息 // teacherAfterStream.addSink(new RedisSink<>(config,new RedisAfterTeacher())).setParallelism(1); //教职工异常信息写入mysql // afterTeacherAbnormal.addSink(new MyJDBCSink()); // beforeStudentAbnormal.print("beforestudent0301"); // afterTeacherAbnormal.print("afterTeacherAbnormal"); beforeStudentAbnormal.addSink(new beforeStudentMySQL()).setParallelism(1); afterStudentAbnormal.addSink(new afterStudentMySQL()).setParallelism(1); afterTeacherAbnormal.addSink(new afterStaffMYSQL()).setParallelism(1); beforeTeacherAbnormal.addSink(new beforeStaffMySQL()).setParallelism(1); env.execute(); } public static class MyOutputSelector implements OutputSelector<String> { @Override public Iterable<String> select(String value) { return (value.contains("INSERT")) ? Collections.singletonList("insertStream") : Collections.singletonList("deleteStream"); } } public static class UserFilterFunction implements FilterFunction<String> { @Override public boolean filter(String value) throws Exception { return value.contains("eceb_epidemic_user"); } } public static class BeforeFilterFunction implements FilterFunction<String> { @Override public boolean filter(String value) throws Exception { return value.contains("eceb_epidemic_report_before"); } } public static class AfterFilterFunction implements FilterFunction<String> { @Override public boolean filter(String value) throws Exception { return value.contains("eceb_epidemic_report_after"); } } public static class UserMapperFunction implements MapFunction<String, epidemicUser> { @Override public epidemicUser map(String value) throws Exception { JSONObject jsonObject = JSON.parseObject(value); JSONArray data = jsonObject.getJSONArray("data"); String ts = jsonObject.get("ts").toString(); List<epidemicUser> epidemicuserList = data.toJavaList(epidemicUser.class); epidemicUser epidemicUserTemp = epidemicuserList.get(0);//获取epidemicuser的对象 return epidemicUserTemp; } } public static class BeforeMapperFunction implements MapFunction<String, epidemicBefore> { @Override public epidemicBefore map(String value) throws Exception { JSONObject jsonObject = JSON.parseObject(value); JSONArray data = jsonObject.getJSONArray("data"); String ts = jsonObject.get("ts").toString(); List<epidemicBefore> epidemicBeforeList = data.toJavaList(epidemicBefore.class); epidemicBefore epidemicBeforeTemp = epidemicBeforeList.get(0);//获取epidemicBefore的对象 return epidemicBeforeTemp; } } public static class AfterMapperFunction implements MapFunction<String, epidemicAfter> { @Override public epidemicAfter map(String value) throws Exception { JSONObject jsonObject = JSON.parseObject(value); JSONArray data = jsonObject.getJSONArray("data"); String ts = jsonObject.get("ts").toString(); List<epidemicAfter> epidemicAfterList = data.toJavaList(epidemicAfter.class); epidemicAfter epidemicAfterTemp = epidemicAfterList.get(0);//获取epidemicAfter的对象 return epidemicAfterTemp; } } public static class UserJoinBefore implements JoinFunction<epidemicUser,epidemicBefore,String> { @Override public String join(epidemicUser first, epidemicBefore second) throws Exception { return first.toString() + "=" + second.toString(); } } public static class UserJoinAfter implements JoinFunction<epidemicUser,epidemicAfter,String> { @Override public String join(epidemicUser first, epidemicAfter second) throws Exception { return first.toString() + "=" + second.toString(); } } public static class beforeAllStream implements OutputSelector<String> { @Override public Iterable<String> select(String value) { return (value.contains("student")) ? Collections.singletonList("studentBeforeStream") : Collections.singletonList("teacherBeforeStream"); } } public static class afterAllStream implements OutputSelector<String> { @Override public Iterable<String> select(String value) { return (value.contains("student")) ? Collections.singletonList("studentAfterStream") : Collections.singletonList("teacherAfterStream"); } } public static class beforeAllMapper implements MapFunction<String, beforeAll> { @Override public beforeAll map(String value) throws Exception { String[] fields = value.split("="); return new beforeAll( fields[0] ,fields[1] ,fields[2] ,fields[3] ,fields[4] ,fields[5] ,fields[6] ,fields[7] ,fields[8] ,fields[9] ,fields[10] , fields[11] ,fields[12] ,fields[13] ,fields[14] ,fields[15] ,fields[16] ,fields[17] ,fields[18] ,fields[19] ,fields[20] , fields[21] ,fields[22] ,fields[23] ,fields[24] ,fields[25] ,fields[26] ,fields[27] ,fields[28] ,fields[29] ,fields[30] , fields[31] ,fields[32] ,fields[33] ,fields[34] ,fields[35] ,fields[36] ,fields[37] ,fields[38] ,fields[39] ,fields[40] , fields[41] ,fields[42] ,fields[43] ,fields[44] ,fields[45] ,fields[46] ,fields[47] ,fields[48] ,fields[49] ,fields[50] , fields[51] ,fields[52] ,fields[53] ,fields[54] ,fields[55] ,fields[56] ,fields[57] ,fields[58] ,fields[59] ,fields[60] , fields[61] ,fields[62] ,fields[63] ,fields[64] ,fields[65] ,fields[66] ,fields[67] ,fields[68]); } } public static class afterAllMapper implements MapFunction<String, afterAll> { @Override public afterAll map(String value) throws Exception { String[] fields = value.split("="); return new afterAll( fields[0] ,fields[1] ,fields[2] ,fields[3] ,fields[4] ,fields[5] ,fields[6] ,fields[7] ,fields[8] ,fields[9] ,fields[10] , fields[11] ,fields[12] ,fields[13] ,fields[14] ,fields[15] ,fields[16] ,fields[17] ,fields[18] ,fields[19] ,fields[20] , fields[21] ,fields[22] ,fields[23] ,fields[24] ,fields[25] ,fields[26] ,fields[27] ,fields[28] ,fields[29] ,fields[30] , fields[31] ,fields[32] ,fields[33] ,fields[34] ,fields[35] ,fields[36] ,fields[37] ,fields[38] ,fields[39] ,fields[40] , fields[41] ,fields[42] ,fields[43] ,fields[44] ,fields[45] ,fields[46] ,fields[47] ,fields[48] ,fields[49] ,fields[50] , fields[51] ,fields[52] ,fields[53] ,fields[54] ,fields[55] ,fields[56] ,fields[57] ,fields[58] ,fields[59] ,fields[60] ); } } public static class beforeStudentFilter implements FilterFunction<beforeAll>{ @Override public boolean filter(beforeAll value) throws Exception { return value.getHealth_code().contains("red") || value.getHealth_code().contains("yellow") || value.getHealth_condition().contains("abnormal") || value.getIs_fever().contains("yes") || value.getIs_touch_sick().contains("yes") || value.getIs_monitor().contains("yes") || value.getIs_confirmed().contains("yes") || value.getIs_recover().contains("no") || value.getIs_went_areas().contains("yes") || value.getLiver_went_areas().contains("yes"); } } public static class afterStudentFilter implements FilterFunction<afterAll>{ @Override public boolean filter(afterAll value) throws Exception { return value.getRework_status().contains("isolation") || value.getRework_status().contains("hospital") || value.getPhysical().contains("have") || value.getPhysical().contains("cough") || value.getTemperature_status().contains("abnormal") || value.getIs_went_areas().contains("yes") || value.getLiver_went_areas().contains("yes") || value.getLiver_state().contains("异常"); } } public static class afterTeacherFilter implements FilterFunction<afterAll> { @Override public boolean filter(afterAll value) throws Exception { return value.getRework_status().contains("isolation") || value.getRework_status().contains("hospital") || value.getPhysical().contains("have") || value.getPhysical().contains("cough") || value.getTemperature_status().contains("abnormal") || value.getIs_went_areas().contains("yes") || value.getLiver_went_areas().contains("yes") || value.getLiver_state().contains("异常"); } } public static class beforeTeacherFilter implements FilterFunction<beforeAll>{ @Override public boolean filter(beforeAll value) throws Exception { return value.getHealth_code().contains("red") || value.getHealth_code().contains("yellow") || value.getHealth_condition().contains("abnormal") || value.getIs_fever().contains("yes") || value.getIs_touch_sick().contains("yes") || value.getIs_monitor().contains("yes") || value.getIs_confirmed().contains("yes") || value.getIs_recover().contains("no") || value.getIs_went_areas().contains("yes") || value.getLiver_went_areas().contains("yes"); } } public static class RedisBeforeStudent implements RedisMapper<beforeAll> { @Override public RedisCommandDescription getCommandDescription() { return new RedisCommandDescription(RedisCommand.HSET,"epidemic_student"); } @Override public String getKeyFromData(beforeAll data) { return data.getSno(); } @Override public String getValueFromData(beforeAll data) { ArrayList<String> list = new ArrayList<>(); ArrayList<String> informate = new ArrayList<>(); String condition = null; String status = null; String isFever = null; String isTouchSick = null; String informates = null; String isMonitor = null; String isConfirmed = null; String isRecover = null; String isWentAreas = null; list.add(data.getFullname());//学生姓名 list.add(data.getDepartment_name());//学校 list.add(data.getGrade_name());//年级 list.add(data.getClass_name());//班级 list.add(data.getGuarder_phone());//学生电话 list.add(data.getClass_teacher_jobno());//班主任工号 list.add(data.getClass_teacher_name());//班主任姓名 list.add(data.getClass_teacher_phone());//班主任电话 switch (data.getHealth_code()){ case "none": condition = "未领取"; break; case "green":condition = "绿码"; break; case "yellow":condition = "黄码"; break; case "red":condition = "红码"; break; default: condition = "健康码项未填写"; } switch (data.getHealth_condition()){ case "normal":status="身体正常";break; case "abnormal":status="身体异常";break; default: status = "身体情况项未填写"; } switch (data.getIs_fever()){ case "yes":isFever="有发烧、咳嗽现象";break; case "no":isFever="无发烧、咳嗽现象";break; default: isFever = "是否发烧、咳嗽项未填写"; } switch (data.getIs_touch_sick()){ case "yes":isTouchSick="有接触过病例";break; case "no":isTouchSick="未接触过病例";break; default: isTouchSick = "是否接触过病例项未填写"; } switch (data.getIs_monitor()){ case "yes": isMonitor = "居家隔离或医学观察";break; case "no": isMonitor = "没有居家隔离或医学观察";break; default: isMonitor = "居家隔离或医学观察项未填写"; } switch (data.getIs_confirmed()){ case "yes": isConfirmed = "疑似或确诊新型肺炎";break; case "no": isConfirmed = "没有疑似或确诊新型肺炎";break; default: isConfirmed = "疑似或确诊新型肺炎项未填写"; } switch (data.getIs_recover()){ case "yes": isRecover = "已经病愈";break; case "no": isRecover = "没有病愈";break; default: isRecover = "是否病愈项未填写"; } switch (data.getIs_went_areas()){ case "yes": isWentAreas = "过有风险地区";break; case "no": isWentAreas = "没有过有风险地区";break; default: isWentAreas = "是否过有风险地区项未填写"; } informate.add(condition); //健康码 none=未领取 green = 绿码 yellow = 黄码 red= 红码 informate.add(status); //身体将康状态 normal =正常 abnormal = 异常 informate.add(data.getSympotom_desc()); // 具体症状及检查治疗情况 informate.add(isFever); //是否有发热咳嗽现象,,yes=是,no=否 informate.add(isTouchSick); //是否接触过病例 yes no informate.add(data.getTouch_time()); //接触时间 informate.add(data.getTouch_address()); //接触低点 informate.add(isMonitor); //是否居家隔离或医学观察 yes no informate.add(isConfirmed); //是否疑似或确诊新型肺炎 yes no informate.add(data.getConfirmed_time()); //疑似或确诊新型肺炎的时间 yes no informate.add(isRecover); //是否病愈 informate.add(data.getRecover_time()); //病愈时间 informate.add(isWentAreas); //是否去过有风险地区 yes no informate.add(data.getWent_time()); //去过的时间 informate.add(data.getWent_address()); //去过的地址 informate.add(data.getWent_active()); //活动事由 informate.add(data.getLiver_state()); //同居住人描述 informate.add(data.getLiver_state()); //同居住人状态 informates = informate.toString(); list.add(informates);//异常信息:所有的异常信息都存在一个字段 list.add(data.getUpdate_time()); return list.toString(); } } public static class RedisAfterTeacher implements RedisMapper<afterAll> { @Override public RedisCommandDescription getCommandDescription() { return new RedisCommandDescription(RedisCommand.HSET,"epidemic_teacher"); } @Override public String getKeyFromData(afterAll data) { return data.getSno(); } @Override public String getValueFromData(afterAll data) { ArrayList<String> list = new ArrayList<>(); String department = null; ArrayList<String> informate = new ArrayList<>(); list.add(data.getFullname()); //姓名 String[] partments = data.getDepartment_name().split("/"); if (data.getRole().contains("emplyee")) { department = partments[partments.length - 1]; }else{ department = partments[partments.length - partments.length]; } list.add(department); //部门 list.add(data.getPhone()); //电话 String physical = null; String temperateStatus = null; String inhomeReason = null; String isWentAreas = null; switch (data.getPhysical()){ case "have" : physical = "发烧";break; case "cough" : physical = "咳嗽";break; case "normal" : physical = "正常";break; default: physical = "发烧、咳嗽等项未填写"; } switch (data.getTemperature_status()){ case "normal":temperateStatus="体温正常";break; case "abnormal":temperateStatus="体温异常";break; default:temperateStatus="体温未填写"; } switch (data.getInhome_reason()){ case "rest" : inhomeReason = "休息";break; case "affair" : inhomeReason = "事假";break; case "sick" : inhomeReason = "病假";break; default: inhomeReason = "在家原因项未填写"; } switch (data.getIs_went_areas()){ case "yes": isWentAreas = "去过风险地区";break; case "no": isWentAreas = "未去过风险地区";break; default: isWentAreas = "是否去过风险地区项未填写"; } informate.add(physical); //have=发烧 cough=咳嗽 正常=normal 其他=other informate.add(data.getPhysical_des()); informate.add(temperateStatus);//体温状态 normal=正常 abnormal=不正常 informate.add(data.getTemperature_count()); //体温值 informate.add(data.getIsolate_site()); //隔离地点 informate.add(data.getIsolate_desc()); //隔离具体情况描述 informate.add(data.getIsolate_reason()); //隔离原因 informate.add(inhomeReason); //在家原因 rest=休息 affair=事假 sick=病假 informate.add(data.getInhome_desc()); //在家具体描述 informate.add(data.getHospital()); //医院名称 informate.add(data.getSick_desc()); //病情具体描述 informate.add(isWentAreas); //是否去过风险地区 yes no informate.add(data.getWent_time()); //去的时间 informate.add(data.getWent_address()); //去得地址 informate.add(data.getWent_active()); //活动事由 list.add(informate.toString()); list.add(data.getUpdate_time()); return list.toString(); } } public static class RedisBeforeStudentAbnormal implements RedisMapper<beforeAll> { @Override public RedisCommandDescription getCommandDescription() { return new RedisCommandDescription(RedisCommand.HSET,"epidemic_student_abnormal"); } @Override public String getKeyFromData(beforeAll data) { return data.getSno(); } @Override public String getValueFromData(beforeAll data) { ArrayList<String> list = new ArrayList<>(); ArrayList<String> informate = new ArrayList<>(); String condition = null; String status = null; String isFever = null; String isTouchSick = null; String informates = null; String isMonitor = null; String isConfirmed = null; String isRecover = null; String isWentAreas = null; list.add(data.getFullname());//学生姓名 list.add(data.getDepartment_name());//学校 list.add(data.getGrade_name());//年级 list.add(data.getClass_name());//班级 list.add(data.getGuarder_phone());//学生电话 list.add(data.getClass_teacher_jobno());//班主任工号 list.add(data.getClass_teacher_name());//班主任姓名 list.add(data.getClass_teacher_phone());//班主任电话 switch (data.getHealth_code()){ case "none": condition = "未领取"; break; case "green":condition = "绿码"; break; case "yellow":condition = "黄码"; break; case "red":condition = "红码"; break; default: condition = "健康码项未填写"; } switch (data.getHealth_condition()){ case "normal":status="身体正常";break; case "abnormal":status="身体异常";break; default: status = "身体情况项未填写"; } switch (data.getIs_fever()){ case "yes":isFever="有发烧、咳嗽现象";break; case "no":isFever="无发烧、咳嗽现象";break; default: isFever = "是否发烧、咳嗽项未填写"; } switch (data.getIs_touch_sick()){ case "yes":isTouchSick="有接触过病例";break; case "no":isTouchSick="未接触过病例";break; default: isTouchSick = "是否接触过病例项未填写"; } switch (data.getIs_monitor()){ case "yes": isMonitor = "居家隔离或医学观察";break; case "no": isMonitor = "没有居家隔离或医学观察";break; default: isMonitor = "居家隔离或医学观察项未填写"; } switch (data.getIs_confirmed()){ case "yes": isConfirmed = "疑似或确诊新型肺炎";break; case "no": isConfirmed = "没有疑似或确诊新型肺炎";break; default: isConfirmed = "疑似或确诊新型肺炎项未填写"; } switch (data.getIs_recover()){ case "yes": isRecover = "已经病愈";break; case "no": isRecover = "没有病愈";break; default: isRecover = "是否病愈项未填写"; } switch (data.getIs_went_areas()){ case "yes": isWentAreas = "过有风险地区";break; case "no": isWentAreas = "没有过有风险地区";break; default: isWentAreas = "是否过有风险地区项未填写"; } informate.add(condition); //健康码 none=未领取 green = 绿码 yellow = 黄码 red= 红码 informate.add(status); //身体将康状态 normal =正常 abnormal = 异常 informate.add(data.getSympotom_desc()); // 具体症状及检查治疗情况 informate.add(isFever); //是否有发热咳嗽现象,,yes=是,no=否 informate.add(isTouchSick); //是否接触过病例 yes no informate.add(data.getTouch_time()); //接触时间 informate.add(data.getTouch_address()); //接触低点 informate.add(isMonitor); //是否居家隔离或医学观察 yes no informate.add(isConfirmed); //是否疑似或确诊新型肺炎 yes no informate.add(data.getConfirmed_time()); //疑似或确诊新型肺炎的时间 yes no informate.add(isRecover); //是否病愈 informate.add(data.getRecover_time()); //病愈时间 informate.add(isWentAreas); //是否去过有风险地区 yes no informate.add(data.getWent_time()); //去过的时间 informate.add(data.getWent_address()); //去过的地址 informate.add(data.getWent_active()); //活动事由 informate.add(data.getLiver_state()); //同居住人描述 informate.add(data.getLiver_state()); //同居住人状态 informates = informate.toString(); list.add(informates);//异常信息:所有的异常信息都存在一个字段 list.add(data.getUpdate_time()); return list.toString(); } } public static class RedisAfterTeacherAbnormal implements RedisMapper<afterAll> { @Override public RedisCommandDescription getCommandDescription() { return new RedisCommandDescription(RedisCommand.HSET,"epidemic_teacher_abnormal"); } @Override public String getKeyFromData(afterAll data) { return data.getSno(); } @Override public String getValueFromData(afterAll data) { ArrayList<String> list = new ArrayList<>(); String department = null; ArrayList<String> informate = new ArrayList<>(); list.add(data.getFullname()); //姓名 String[] partments = data.getDepartment_name().split("/"); if (data.getRole().contains("emplyee")) { department = partments[partments.length - 1]; }else{ department = partments[partments.length - partments.length]; } list.add(department); //部门 list.add(data.getPhone()); //电话 String physical = null; String temperateStatus = null; String inhomeReason = null; String isWentAreas = null; switch (data.getPhysical()){ case "have" : physical = "发烧";break; case "cough" : physical = "咳嗽";break; case "normal" : physical = "正常";break; default: physical = "发烧、咳嗽等项未填写"; } switch (data.getTemperature_status()){ case "normal":temperateStatus="体温正常";break; case "abnormal":temperateStatus="体温异常";break; default:temperateStatus="体温未填写"; } switch (data.getInhome_reason()){ case "rest" : inhomeReason = "休息";break; case "affair" : inhomeReason = "事假";break; case "sick" : inhomeReason = "病假";break; default: inhomeReason = "在家原因项未填写"; } switch (data.getIs_went_areas()){ case "yes": isWentAreas = "去过风险地区";break; case "no": isWentAreas = "未去过风险地区";break; default: isWentAreas = "是否去过风险地区项未填写"; } informate.add(physical); //have=发烧 cough=咳嗽 正常=normal 其他=other informate.add(data.getPhysical_des()); informate.add(temperateStatus);//体温状态 normal=正常 abnormal=不正常 informate.add(data.getTemperature_count()); //体温值 informate.add(data.getIsolate_site()); //隔离地点 informate.add(data.getIsolate_desc()); //隔离具体情况描述 informate.add(data.getIsolate_reason()); //隔离原因 informate.add(inhomeReason); //在家原因 rest=休息 affair=事假 sick=病假 informate.add(data.getInhome_desc()); //在家具体描述 informate.add(data.getHospital()); //医院名称 informate.add(data.getSick_desc()); //病情具体描述 informate.add(isWentAreas); //是否去过风险地区 yes no informate.add(data.getWent_time()); //去的时间 informate.add(data.getWent_address()); //去得地址 informate.add(data.getWent_active()); //活动事由 list.add(informate.toString()); list.add(data.getUpdate_time()); return list.toString(); } } public static class RedisBeforeTeacherAbnormal implements RedisMapper<beforeAll>{ @Override public RedisCommandDescription getCommandDescription() { return new RedisCommandDescription(RedisCommand.HSET,"epidemic_teacher_abnormal"); } @Override public String getKeyFromData(beforeAll data) { return data.getSno(); } @Override public String getValueFromData(beforeAll data) { ArrayList<String> list = new ArrayList<>(); ArrayList<String> informate = new ArrayList<>(); String condition = null; String status = null; String isFever = null; String isTouchSick = null; String informates = null; String isMonitor = null; String isConfirmed = null; String isRecover = null; String isWentAreas = null; String department = null; list.add(data.getFullname());//教职工姓名 String[] partments = data.getDepartment_name().split("/"); if (data.getRole().contains("emplyee")) { department = partments[partments.length - 1]; }else{ department = partments[partments.length - partments.length]; } list.add(department); //部门 list.add(data.getPhone()); //电话 switch (data.getHealth_code()){ case "none": condition = "未领取"; break; case "green":condition = "绿码"; break; case "yellow":condition = "黄码"; break; case "red":condition = "红码"; break; default: condition = "健康码项未填写"; } switch (data.getHealth_condition()){ case "normal":status="身体正常";break; case "abnormal":status="身体异常";break; default: status = "身体情况项未填写"; } switch (data.getIs_fever()){ case "yes":isFever="有发烧、咳嗽现象";break; case "no":isFever="无发烧、咳嗽现象";break; default: isFever = "是否发烧、咳嗽项未填写"; } switch (data.getIs_touch_sick()){ case "yes":isTouchSick="有接触过病例";break; case "no":isTouchSick="未接触过病例";break; default: isTouchSick = "是否接触过病例项未填写"; } switch (data.getIs_monitor()){ case "yes": isMonitor = "居家隔离或医学观察";break; case "no": isMonitor = "没有居家隔离或医学观察";break; default: isMonitor = "居家隔离或医学观察项未填写"; } switch (data.getIs_confirmed()){ case "yes": isConfirmed = "疑似或确诊新型肺炎";break; case "no": isConfirmed = "没有疑似或确诊新型肺炎";break; default: isConfirmed = "疑似或确诊新型肺炎项未填写"; } switch (data.getIs_recover()){ case "yes": isRecover = "已经病愈";break; case "no": isRecover = "没有病愈";break; default: isRecover = "是否病愈项未填写"; } switch (data.getIs_went_areas()){ case "yes": isWentAreas = "过有风险地区";break; case "no": isWentAreas = "没有过有风险地区";break; default: isWentAreas = "是否过有风险地区项未填写"; } informate.add(condition); //健康码 none=未领取 green = 绿码 yellow = 黄码 red= 红码 informate.add(status); //身体将康状态 normal =正常 abnormal = 异常 informate.add(data.getSympotom_desc()); // 具体症状及检查治疗情况 informate.add(isFever); //是否有发热咳嗽现象,,yes=是,no=否 informate.add(isTouchSick); //是否接触过病例 yes no informate.add(data.getTouch_time()); //接触时间 informate.add(data.getTouch_address()); //接触低点 informate.add(isMonitor); //是否居家隔离或医学观察 yes no informate.add(isConfirmed); //是否疑似或确诊新型肺炎 yes no informate.add(data.getConfirmed_time()); //疑似或确诊新型肺炎的时间 yes no informate.add(isRecover); //是否病愈 informate.add(data.getRecover_time()); //病愈时间 informate.add(isWentAreas); //是否去过有风险地区 yes no informate.add(data.getWent_time()); //去过的时间 informate.add(data.getWent_address()); //去过的地址 informate.add(data.getWent_active()); //活动事由 informate.add(data.getLiver_state()); //同居住人描述 informate.add(data.getLiver_state()); //同居住人状态 informates = informate.toString(); list.add(informates);//异常信息:所有的异常信息都存在一个字段 list.add(data.getUpdate_time()); return list.toString(); } } /* * 将时间转换为时间戳 */ public static Long dateToStamp(String s) throws ParseException{ SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Date date = simpleDateFormat.parse(s); long ts = date.getTime(); return ts; } public static class MyJDBCSink extends RichSinkFunction<afterAll> { //定义SQL 链接 预编译器 Connection conn = null; PreparedStatement insertSql = null; PreparedStatement deleteSql = null; @Override public void open(Configuration parameters) throws Exception { super.open(parameters); conn = (Connection) DriverManager.getConnection("jdbc:mysql://","",""); insertSql= (PreparedStatement) conn.prepareStatement("insert into eceb_epidemic_staff_warning(" + " job_no,\n" + " name,\n" + " org_code,\n" + " org_name,\n" + " tel,\n" + " abnormal_desc,\n" + " update_time ) values (?,?,?,?,?,?,?)"); } @Override public void close() throws Exception { super.close(); insertSql.close(); conn.close(); } @Override public void invoke(afterAll data, Context context) throws Exception { String department = null; ArrayList<String> informate = new ArrayList<>(); String physical = null; String temperateStatus = null; String inhomeReason = null; String isWentAreas = null; if (data.getRole().contains("emplyee")) { String[] partments = data.getDepartment_name().split("/"); department = partments[partments.length - 1]; }else{ String[] partments = data.getDepartment_name().split("|"); department = partments[partments.length - partments.length]; } switch (data.getPhysical()){ case "have" : physical = "发烧";break; case "cough" : physical = "咳嗽";break; case "normal" : physical = "正常";break; default: physical = "发烧、咳嗽等项未填写"; } switch (data.getTemperature_status()){ case "normal":temperateStatus="体温正常";break; case "abnormal":temperateStatus="体温异常";break; default:temperateStatus="体温未填写"; } switch (data.getInhome_reason()){ case "rest" : inhomeReason = "休息";break; case "affair" : inhomeReason = "事假";break; case "sick" : inhomeReason = "病假";break; default: inhomeReason = "在家原因项未填写"; } switch (data.getIs_went_areas()){ case "yes": isWentAreas = "去过风险地区";break; case "no": isWentAreas = "未去过风险地区";break; default: isWentAreas = "是否去过风险地区项未填写"; } informate.add(physical); //have=发烧 cough=咳嗽 正常=normal 其他=other informate.add(data.getPhysical_des()); informate.add(temperateStatus);//体温状态 normal=正常 abnormal=不正常 informate.add(data.getTemperature_count()); //体温值 informate.add(data.getIsolate_site()); //隔离地点 informate.add(data.getIsolate_desc()); //隔离具体情况描述 informate.add(data.getIsolate_reason()); //隔离原因 informate.add(inhomeReason); //在家原因 rest=休息 affair=事假 sick=病假 informate.add(data.getInhome_desc()); //在家具体描述 informate.add(data.getHospital()); //医院名称 informate.add(data.getSick_desc()); //病情具体描述 informate.add(isWentAreas); //是否去过风险地区 yes no informate.add(data.getWent_time()); //去的时间 informate.add(data.getWent_address()); //去得地址 informate.add(data.getWent_active()); //活动事由 if ((PreparedStatement) conn.prepareStatement("select sno from epidemic_teacher_abnormal where sno = " + data.getSno()) != null){ deleteSql= (PreparedStatement) conn.prepareStatement("delete from epidemic_teacher_abnormal where sno = " + data.getSno()); insertSql.setString(1, data.getSno()); insertSql.setString(2, data.getFullname()); insertSql.setString(3, department); insertSql.setString(4, data.getPhone()); insertSql.setString(5, informate.toString()); insertSql.setString(6, data.getUpdate_time()); insertSql.execute(); }else{ insertSql.setString(1, data.getSno()); insertSql.setString(2, data.getFullname()); insertSql.setString(3, department); insertSql.setString(4, data.getPhone()); insertSql.setString(5, informate.toString()); insertSql.setString(6, data.getUpdate_time()); insertSql.execute(); } } } public static class beforeStudentMySQL extends RichSinkFunction<beforeAll>{ //定义SQL 链接 预编译器 Connection conn = null; PreparedStatement insertSql = null; @Override public void open(Configuration parameters) throws Exception { super.open(parameters); Class.forName("com.mysql.cj.jdbc.Driver"); // conn = (Connection) DriverManager.getConnection("jdbc:mysql://?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&useSSL=false","",""); insertSql= (PreparedStatement) conn.prepareStatement("insert into eceb_epidemic_student_warning(" + " card_no,\n" + " name,\n" + " school_code,\n" + " school_name,\n" + " section_code,\n" + " section_name,\n" + " grade_code,\n" + " grade_name,\n" + " class_code,\n" + " class_name,\n" + " master_name,\n" + " master_tel,\n" + " tel,\n" + " abnormal_desc,\n" + " report_time ) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); } @Override public void close() throws Exception { super.close(); insertSql.close(); conn.close(); } @Override public void invoke(beforeAll value, Context context) throws Exception { String job_no = value.getSno(); String name = value.getFullname(); String role = value.getRole(); String school_code = value.getDepartment_code(); String school_name = value.getDepartment_name(); String section_code = value.getSection_code(); String section_name = value.getSection_name(); String grade_code = value.getGrade_code(); String grade_name = value.getGrade_name(); String class_code = value.getClass_code(); String class_name = value.getClass_name(); String master_name = value.getGuarder_name(); String master_tel = value.getGuarder_phone(); String tel = value.getPhone(); String report_time = value.getUpdate_time(); ArrayList<String> abnormal_desc = new ArrayList<>(); //异常信息 if (value.getHealth_condition().contains("yes")){ abnormal_desc.add("身体异常,具体症状:" + value.getSympotom_desc()); }else if(value.getIs_fever().contains("yes")){ abnormal_desc.add("有发烧、咳嗽现象;"); }else if (value.getIs_touch_sick().contains("yes")){ abnormal_desc.add("接触过病例,接触时间:" + value.getTouch_time() + ",接触地点:" + value.getTouch_address()); }else if(value.getIs_monitor().contains("yes")){ abnormal_desc.add("居家隔离观察或医院医学观察"); }else if(value.getIs_confirmed().contains("yes")){ abnormal_desc.add(value.getConfirmed_time() + "疑似或确诊为新冠肺炎"); }else if(value.getIs_fever().contains("no")){ abnormal_desc.add("目前未病愈"); }else if (value.getIs_went_areas().contains("yes")){ abnormal_desc.add("去过高风险地区(" + value.getWent_address() + "),去的时间:" + value.getWent_time() + ",原因:" + value.getWent_active()); }else if (value.getLiver_went_areas().contains("yes")){ abnormal_desc.add("共同居住人14天内去过高风险地区(" + value.getLiver_desc() + "),共同居住人现在状态描述:" + value.getLiver_state()); } switch (value.getHealth_code()){ case "red": abnormal_desc.add("红码"); break; case "yellow": abnormal_desc.add("黄码"); break; case "green": break; case "none": break; } if (!abnormal_desc.isEmpty()) { insertSql.setString(1, job_no); insertSql.setString(2, name); insertSql.setString(3, school_code); insertSql.setString(4, school_name); insertSql.setString(5, section_code); insertSql.setString(6, section_name); insertSql.setString(7, grade_code); insertSql.setString(8, grade_name); insertSql.setString(9, class_code); insertSql.setString(10, class_name); insertSql.setString(11, master_name); insertSql.setString(12, master_tel); insertSql.setString(13, tel); insertSql.setString(14, abnormal_desc.toString()); insertSql.setString(15, report_time); insertSql.execute(); } } } public static class afterStudentMySQL extends RichSinkFunction<afterAll>{ //定义SQL 链接 预编译器 Connection conn = null; PreparedStatement insertSql = null; // PreparedStatement deleteSql = null; @Override public void open(Configuration parameters) throws Exception { super.open(parameters); Class.forName("com.mysql.cj.jdbc.Driver"); // conn = (Connection) DriverManager.getConnection("jdbc:mysql://?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&useSSL=false","",""); insertSql= (PreparedStatement) conn.prepareStatement("insert into eceb_epidemic_student_warning(" + " job_no,\n" + " name,\n" + " role,\n" + " org_code,\n" + " org_name,\n" + " tel,\n" + " abnormal_desc,\n" + " report_time ) values (?,?,?,?,?,?,?,?)"); } @Override public void close() throws Exception { super.close(); } @Override public void invoke(afterAll value, Context context) throws Exception { } } public static class afterStaffMYSQL extends RichSinkFunction<afterAll>{ //定义SQL 链接 预编译器 Connection conn = null; PreparedStatement insertSql = null; @Override public void open(Configuration parameters) throws Exception { super.open(parameters); Class.forName("com.mysql.cj.jdbc.Driver"); // conn = (Connection) DriverManager.getConnection("jdbc:mysql://?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&useSSL=false","",""); // insertSql= (PreparedStatement) conn.prepareStatement("insert into eceb_epidemic_staff_warning(" + " job_no,\n" + " name,\n" + " role,\n" + " org_code,\n" + " org_name,\n" + " tel,\n" + " abnormal_desc,\n" + " report_time ) values (?,?,?,?,?,?,?,?)"); } @Override public void close() throws Exception { super.close(); insertSql.close(); conn.close(); } @Override public void invoke(afterAll value, Context context) throws Exception { String job_no = value.getSno(); String name = value.getFullname(); String role = value.getRole(); String org_code = value.getDepartment_code(); String org_name = value.getDepartment_name(); String tel = value.getPhone(); // SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:sss"); // java.util.Date report_time = simpleDateFormat.parse(value.getUpdate_time()); String report_time = value.getUpdate_time(); ArrayList<String> abnormal_desc = new ArrayList<>(); //异常信息 if (value.getPhysical().contains("have") || value.getPhysical().contains("cough")) { if (value.getPhysical().contains("have")){ abnormal_desc.add("发烧,异常信息:" + value.getPhysical_des()); }else{ abnormal_desc.add("咳嗽,异常信息:" + value.getPhysical_des()); } }else if(value.getTemperature_status().contains("abnormal")){ abnormal_desc.add("体温异常,温度:" + value.getTemperature_count()); }else if(value.getIs_went_areas().contains("yes")){ abnormal_desc.add("去过高风险地区(" + value.getWent_address() + "),时间:" + value.getWent_time() + ",原因:" + value.getWent_active()); }else if(value.getLiver_went_areas().contains("yes") || value.getLiver_state().contains("异常")){ abnormal_desc.add("共同居住人去过高风险地区或身体异常(异常信息:" + value.getLiver_desc() + ")"); } switch(value.getRework_status()){ case "isolation": abnormal_desc.add("隔离观察,隔离地点:" + value.getIsolate_site() + ",隔离具体描述:" + value.getIsolate_desc() + ",隔离原因:" + value.getIsolate_reason()); break; case "hospital": abnormal_desc.add("就诊医院,医院名称:" + value.getHospital() + ",病情描述:" + value.getSick_desc()); break; case "normal": break; case "other": break; } // if ((PreparedStatement) conn.prepareStatement("select job_no from eceb_epidemic_staff_warning where job_no = " + job_no) != null){ // deleteSql= (PreparedStatement) conn.prepareStatement("delete from epidemic_teacher_abnormal where job_no = " + job_no); // deleteSql.execute(); // } if (!abnormal_desc.isEmpty()){ insertSql.setString(1, job_no); insertSql.setString(2, name); insertSql.setString(3, role); insertSql.setString(4, org_code); insertSql.setString(5, org_name); insertSql.setString(6, tel); insertSql.setString(7, abnormal_desc.toString()); insertSql.setString(8, report_time); insertSql.execute(); } } } public static class beforeStaffMySQL extends RichSinkFunction<beforeAll>{ //定义SQL 链接 预编译器 Connection conn = null; PreparedStatement insertSql = null; @Override public void open(Configuration parameters) throws Exception { super.open(parameters); Class.forName("com.mysql.cj.jdbc.Driver"); // conn = (Connection) DriverManager.getConnection("jdbc:?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&useSSL=false","",""); insertSql= (PreparedStatement) conn.prepareStatement("insert into eceb_epidemic_staff_warning(" + " job_no,\n" + " name,\n" + " role,\n" + " org_code,\n" + " org_name,\n" + " tel,\n" + " abnormal_desc,\n" + " report_time ) values (?,?,?,?,?,?,?,?)"); } @Override public void close() throws Exception { super.close(); insertSql.close(); conn.close(); } @Override public void invoke(beforeAll value, Context context) throws Exception { String job_no = value.getSno(); String name = value.getFullname(); String role = value.getRole(); String org_code = value.getDepartment_code(); String org_name = value.getDepartment_name(); String tel = value.getPhone(); String report_time = value.getUpdate_time(); ArrayList<String> abnormal_desc = new ArrayList<>(); //异常信息 if (value.getHealth_condition().contains("yes")){ abnormal_desc.add("身体异常,具体症状:" + value.getSympotom_desc()); }else if(value.getIs_fever().contains("yes")){ abnormal_desc.add("有发烧、咳嗽现象;"); }else if (value.getIs_touch_sick().contains("yes")){ abnormal_desc.add("接触过病例,接触时间:" + value.getTouch_time() + ",接触地点:" + value.getTouch_address()); }else if(value.getIs_monitor().contains("yes")){ abnormal_desc.add("居家隔离观察或医院医学观察"); }else if(value.getIs_confirmed().contains("yes")){ abnormal_desc.add(value.getConfirmed_time() + "疑似或确诊为新冠肺炎"); }else if(value.getIs_fever().contains("no")){ abnormal_desc.add("目前未病愈"); }else if (value.getIs_went_areas().contains("yes")){ abnormal_desc.add("去过高风险地区(" + value.getWent_address() + "),去的时间:" + value.getWent_time() + ",原因:" + value.getWent_active()); }else if (value.getLiver_went_areas().contains("yes")){ abnormal_desc.add("共同居住人14天内去过高风险地区(" + value.getLiver_desc() + ",共同居住人现在状态描述:" + value.getLiver_state()); } switch (value.getHealth_code()){ case "red": abnormal_desc.add("红码"); break; case "yellow": abnormal_desc.add("黄码"); break; case "green": break; case "none": break; } if (!abnormal_desc.isEmpty()) { insertSql.setString(1, job_no); insertSql.setString(2, name); insertSql.setString(3, role); insertSql.setString(4, org_code); insertSql.setString(5, org_name); insertSql.setString(6, tel); insertSql.setString(7, abnormal_desc.toString()); insertSql.setString(8, report_time); insertSql.execute(); } } } }