Apache nifi将sql查询结果发送邮件_nifi将数据库中查询结果做成表格发送邮件-优快云博客

本文链接：https://blog.youkuaiyun.com/nansen0324/article/details/136650310

文章详细描述了如何在ApacheNiFi中执行SQL查询，获取数据后转换为JSON格式，并将结果插入到HTML邮件正文中的过程，涉及Java类型转换和NiFi的文件操作.

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.设定cron表达式，sql查询语句

在这里插入图片描述

2.查询sql语句

在这里插入图片描述

3.将查询结果转为JSON格式

在这里插入图片描述

4.设定邮件正文html

在这里插入图片描述

html部分

var InputStreamCallback = Java.type("org.apache.nifi.processor.io.InputStreamCallback")
    var IOUtils = Java.type("org.apache.commons.io.IOUtils")
    var StandardCharsets = Java.type("java.nio.charset.StandardCharsets")

    flowFile = session.get();
if (flowFile != null) {
    var content = '';
    session.read(flowFile, new InputStreamCallback(function (inputStream) {
            content = IOUtils.toString(inputStream, StandardCharsets.UTF_8);
        }));

    var html = '';
    if (content) {
        var data = content.substring(1).split('{');
        html = '    <html>'
        html += '       <head>'
        html += '           <meta http-equiv=Content-Type content="text/html; charset=utf-8">'
        html += '           <meta name=Generator content="Microsoft Word 15 (filtered medium)">'
        html += '       </head>'
        html += '       <body>'
        html += '           <div>'
        html += '               <p>'
        html += '                   <b>'
        html += '                       <span style="font-size:9.0pt;font-family:Consolas;color:#44546A">Dear All:</span>'
        html += '                   </b>'
        html += '               </p>'
        html += '               <p>'
        html += '                   <span style="font-size:10.0pt;font-family:SimSun;color:#44546A">&nbsp;这是这个月加班的部分！</span>'
        html += '               </p>'
        html += '               <table cellspacing=0 cellpadding=0 width=0 style=""margin-left:-.05pt;border-collapse:collapse">'
        html += '                   <tr style="height:16.2pt">'
        html += '                       <td nowrap style="border:solid windowtext 1.0pt;background:#00B0F0;padding:0cm 1.4pt 0cm 1.4pt;height: 16px;">'
        html += '                           <p style="text-align:center">'
        html += '                               <span style="font-size:9.0pt;font-family:"微軟正黑體",sans-serif;color:white">Row</span>'
        html += '                           </p>'
        html += '                       </td>'
        html += '                       <td nowrap style="border:solid windowtext 1.0pt;border-left:none;background:#00B0F0;padding:0cm 1.4pt 0cm 1.4pt">'
        html += '                           <p style="text-align:center">'
        html += '                               <span style="font-size:9.0pt;font-family:"微軟正黑體",sans-serif;color:white">ApplicantDept</span>'
        html += '                           </p>'
        html += '                       </td>'
        html += '                       <td nowrap style="border:solid windowtext 1.0pt;border-left:none;background:#00B0F0;padding:0cm 1.4pt 0cm 1.4pt">'
        html += '                           <p style="text-align:center">'
        html += '                               <span style="font-size:9.0pt;font-family:"微軟正黑體",sans-serif;color:white">ApplicantID</span>'
        html += '                           </p>'
        html += '                       </td>'
        html += '                       <td nowrap style="border:solid windowtext 1.0pt;border-left:none;background:#00B0F0;padding:0cm 1.4pt 0cm 1.4pt">'
        html += '                           <p style="text-align:center">'
        html += '                               <span style="font-size:9.0pt;font-family:"微軟正黑體",sans-serif;color:white">ApplicantName</span>'
        html += '                           </p>'
        html += '                       </td>'
        html += '                       <td nowrap style="border:solid windowtext 1.0pt;border-left:none;background:#00B0F0;padding:0cm 1.4pt 0cm 1.4pt">'
        html += '                           <p style="text-align:center">'
        html += '                               <span style="font-size:9.0pt;font-family:"微軟正黑體",sans-serif;color:white">OVERHOUR</span>'
        html += '                           </p>'
        html += '                       </td>'
        html += '                   </tr>'
        for (var k in data) {
            var record = JSON.parse('{' + data[k]);
            
			html += '               <tr style="height:16.2pt">'
			html += '                   <td nowrap style="border:solid windowtext 1.0pt;border-top:none;padding:0cm 1.4pt 0cm 1.4pt">'
			html += '			            <p style="text-align:center">'
			html += '               			<span style="font-size:9.0pt;font-family:"微軟正黑體",sans-serif;color:black">'+ record.Row +'</span>'
			html += '               		</p>'
			html += '               	</td>'
			html += '               	<td nowrap style="border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0cm 1.4pt 0cm 1.4pt">'
			html += '               		<p style="text-align:center">'
			html += '               			<span style="font-size:9.0pt;font-family:"微軟正黑體",sans-serif;color:black">'+ record.ApplicantDept +'</span>'
			html += '               		</p>'
			html += '               	</td>'
			html += '               	<td nowrap style="border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0cm 1.4pt 0cm 1.4pt">'
			html += '               		<p>'
			html += '               			<span style="font-size:9.0pt;font-family:"微軟正黑體",sans-serif;color:black">'+ record.ApplicantID +'</span>'
			html += '               		</p>'
			html += '               	</td>'
			html += '               	<td nowrap style="border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0cm 1.4pt 0cm 1.4pt">'
			html += '               		<p>'
			html += '               			<span style="font-size:9.0pt;font-family:"微軟正黑體",sans-serif;color:black">'+ record.ApplicantName +'</span>'
			html += '               					</a>'
			html += '               				</span>'
			html += '               			</u>'
			html += '               		</p>'
			html += '               	</td>'
			html += '               	<td nowrap style="border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0cm 1.4pt 0cm 1.4pt">'
			html += '               		<p style="text-align:center">'
			html += '               			<span style="font-size:9.0pt;font-family:"微軟正黑體",sans-serif;color:black">'+ record.OVERHOUR +'</span>'
			html += '               		</p>'
			html += '               	</td>'
			html += '               </tr>'
        }

        html += '       </table>'
        html += '   </div>'
        html += '</body>'
        html += '</html>'
    }
    flowFile = session.putAllAttributes(flowFile, {
        "htmlbody": html
    });
    session.transfer(flowFile, REL_SUCCESS);
}