查询以包括Spring Batch作业参数值和作业执行数据。

huangapple
go评论105阅读模式
英文:

query to include spring batch job param value along with job execution data

问题

对于一个Spring批处理作业，我有诸如requestName和responseId之类的作业参数，这些参数存储在BATCH_JOB_EXECUTION_PARAMS表中。

我正在使用以下查询检索有关作业执行的详细信息：

  1. SELECT je.JOB_EXECUTION_ID,
  2.        je.START_TIME,
  3.        je.END_TIME,
  4.        je.STATUS,
  5.        je.EXIT_CODE,
  6.        je.EXIT_MESSAGE,
  7.        ji.JOB_INSTANCE_ID,
  8.        ji.JOB_NAME,
  9.        --添加 requestName 的 STRING_VAL
  10. FROM BATCH_JOB_EXECUTION je
  11. INNER JOIN BATCH_JOB_EXECUTION_PARAMS p ON je.JOB_EXECUTION_ID = p.JOB_EXECUTION_ID
  12. INNER JOIN BATCH_JOB_INSTANCE ji ON je.JOB_INSTANCE_ID = ji.JOB_INSTANCE_ID
  13. 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.

  1. JOB_EXECUTION_ID | KEY_NAME | STRING_VAL 
  2. 2	|             responseId             | f5e1b3e9
  3. 2	|             requestName            | DUMMY

I am retrieving details about the Job Execution with the following query:

  1. SELECT je.JOB_EXECUTION_ID, 
  2.        je.START_TIME, 
  3.        je.END_TIME, 
  4.        je.STATUS, 
  5.        je.EXIT_CODE, 
  6.        je.EXIT_MESSAGE, 
  7.        ji.JOB_INSTANCE_ID, 
  8.        ji.JOB_NAME,
  9.        --add requestName STRING_VAL
  10. FROM BATCH_JOB_EXECUTION je
  11. INNER JOIN BATCH_JOB_EXECUTION_PARAMS p ON je.JOB_EXECUTION_ID = p.JOB_EXECUTION_ID
  12. INNER JOIN BATCH_JOB_INSTANCE ji ON je.JOB_INSTANCE_ID = ji.JOB_INSTANCE_ID
  13. 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

  1. @MO6H 只需加入另一个 `BATCH_JOB_EXECUTION_PARAMS` 实例，如下所示：
  3.     SELECT je.JOB_EXECUTION_ID, 
  4.        je.START_TIME, 
  5.        je.END_TIME, 
  6.        je.STATUS, 
  7.        je.EXIT_CODE, 
  8.        je.EXIT_MESSAGE, 
  9.        ji.JOB_INSTANCE_ID, 
  10.        ji.JOB_NAME,
  11.        p2.STRING_VAL AS `REQUEST_NAME` -- 添加 requestName STRING_VAL
  12.     FROM BATCH_JOB_EXECUTION je
  13.     INNER JOIN BATCH_JOB_EXECUTION_PARAMS p ON je.JOB_EXECUTION_ID = p.JOB_EXECUTION_ID
  14.     INNER JOIN BATCH_JOB_INSTANCE ji ON je.JOB_INSTANCE_ID = ji.JOB_INSTANCE_ID
  15.     JOIN BATCH_JOB_EXECUTION_PARAMS AS p2 ON p2.JOB_EXECUTION_ID = p.JOB_EXECUTION_ID        
  16.     WHERE p.KEY_NAME = 'responseId' 
  17.         AND p.STRING_VAL = 'f5e1b3e9'
  18.         AND p2.KEY_NAME = 'requestName';
  20. 如果这不是您想要的，请告诉我，我将尽力提供进一步的帮助。
英文:

@MO6H just join in another instance of BATCH_JOB_EXECUTION_PARAMS like this:

  1. SELECT je.JOB_EXECUTION_ID, 
  2.    je.START_TIME, 
  3.    je.END_TIME, 
  4.    je.STATUS, 
  5.    je.EXIT_CODE, 
  6.    je.EXIT_MESSAGE, 
  7.    ji.JOB_INSTANCE_ID, 
  8.    ji.JOB_NAME,
  9.    p2.STRING_VAL AS `REQUEST_NAME` -- add requestName STRING_VAL
  10. FROM BATCH_JOB_EXECUTION je
  11. INNER JOIN BATCH_JOB_EXECUTION_PARAMS p ON je.JOB_EXECUTION_ID = p.JOB_EXECUTION_ID
  12. INNER JOIN BATCH_JOB_INSTANCE ji ON je.JOB_INSTANCE_ID = ji.JOB_INSTANCE_ID
  13. JOIN BATCH_JOB_EXECUTION_PARAMS AS p2 ON p2.JOB_EXECUTION_ID = p.JOB_EXECUTION_ID        
  14. WHERE p.KEY_NAME = 'responseId' 
  15.     AND p.STRING_VAL = 'f5e1b3e9'
  16.     AND p2.KEY_NAME = 'requestName'

please let me know if this doesn't do what you want and I'll try to help further.

huangapple
  • 本文由 发表于 2020年7月27日 20:58:40
  • 转载请务必保留本文链接：https://go.coder-hub.com/63115846.html
  • java
  • mysql
  • spring-batch
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定