英文:
query to include spring batch job param value along with job execution data
问题
对于一个Spring批处理作业，我有诸如requestName和responseId之类的作业参数，这些参数存储在BATCH_JOB_EXECUTION_PARAMS表中。
我正在使用以下查询检索有关作业执行的详细信息：
SELECT je.JOB_EXECUTION_ID,
je.START_TIME,
je.END_TIME,
je.STATUS,
je.EXIT_CODE,
je.EXIT_MESSAGE,
ji.JOB_INSTANCE_ID,
ji.JOB_NAME,
--添加 requestName 的 STRING_VAL
FROM BATCH_JOB_EXECUTION je
INNER JOIN BATCH_JOB_EXECUTION_PARAMS p ON je.JOB_EXECUTION_ID = p.JOB_EXECUTION_ID
INNER JOIN BATCH_JOB_INSTANCE ji ON je.JOB_INSTANCE_ID = ji.JOB_INSTANCE_ID
WHERE p.KEY_NAME = 'responseId' AND p.STRING_VAL = 'f5e1b3e9';
如何在我的查询中填充requestName的值？因此，对于作业执行ID 2，它将返回一个附加列REQUEST_NAME，其值为DUMMY。
英文:
For a Spring batch job, I have job params such as requestName & responseId which is in BATCH_JOB_EXECUTION_PARAMS table.
JOB_EXECUTION_ID | KEY_NAME | STRING_VAL
2 | responseId | f5e1b3e9
2 | requestName | DUMMY
I am retrieving details about the Job Execution with the following query:
SELECT je.JOB_EXECUTION_ID,
je.START_TIME,
je.END_TIME,
je.STATUS,
je.EXIT_CODE,
je.EXIT_MESSAGE,
ji.JOB_INSTANCE_ID,
ji.JOB_NAME,
--add requestName STRING_VAL
FROM BATCH_JOB_EXECUTION je
INNER JOIN BATCH_JOB_EXECUTION_PARAMS p ON je.JOB_EXECUTION_ID = p.JOB_EXECUTION_ID
INNER JOIN BATCH_JOB_INSTANCE ji ON je.JOB_INSTANCE_ID = ji.JOB_INSTANCE_ID
WHERE p.KEY_NAME = 'responseId' AND p.STRING_VAL = 'f5e1b3e9'
How can I populate the value of the requestName in my query? So for JobExecution id 2, its returns an additional columns REQUEST_NAME with value DUMMY
答案1
得分: 0
@MO6H 只需加入另一个 `BATCH_JOB_EXECUTION_PARAMS` 实例，如下所示：
SELECT je.JOB_EXECUTION_ID,
je.START_TIME,
je.END_TIME,
je.STATUS,
je.EXIT_CODE,
je.EXIT_MESSAGE,
ji.JOB_INSTANCE_ID,
ji.JOB_NAME,
p2.STRING_VAL AS `REQUEST_NAME` -- 添加 requestName STRING_VAL
FROM BATCH_JOB_EXECUTION je
INNER JOIN BATCH_JOB_EXECUTION_PARAMS p ON je.JOB_EXECUTION_ID = p.JOB_EXECUTION_ID
INNER JOIN BATCH_JOB_INSTANCE ji ON je.JOB_INSTANCE_ID = ji.JOB_INSTANCE_ID
JOIN BATCH_JOB_EXECUTION_PARAMS AS p2 ON p2.JOB_EXECUTION_ID = p.JOB_EXECUTION_ID
WHERE p.KEY_NAME = 'responseId'
AND p.STRING_VAL = 'f5e1b3e9'
AND p2.KEY_NAME = 'requestName';
如果这不是您想要的，请告诉我，我将尽力提供进一步的帮助。
英文:
@MO6H just join in another instance of BATCH_JOB_EXECUTION_PARAMS like this:
SELECT je.JOB_EXECUTION_ID,
je.START_TIME,
je.END_TIME,
je.STATUS,
je.EXIT_CODE,
je.EXIT_MESSAGE,
ji.JOB_INSTANCE_ID,
ji.JOB_NAME,
p2.STRING_VAL AS `REQUEST_NAME` -- add requestName STRING_VAL
FROM BATCH_JOB_EXECUTION je
INNER JOIN BATCH_JOB_EXECUTION_PARAMS p ON je.JOB_EXECUTION_ID = p.JOB_EXECUTION_ID
INNER JOIN BATCH_JOB_INSTANCE ji ON je.JOB_INSTANCE_ID = ji.JOB_INSTANCE_ID
JOIN BATCH_JOB_EXECUTION_PARAMS AS p2 ON p2.JOB_EXECUTION_ID = p.JOB_EXECUTION_ID
WHERE p.KEY_NAME = 'responseId'
AND p.STRING_VAL = 'f5e1b3e9'
AND p2.KEY_NAME = 'requestName'
please let me know if this doesn't do what you want and I'll try to help further.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论