Dynamic sql with JbdcItemReader/JbdcItemWriter

[joao.santana]

Hi all, my job changes the source table every day... how i can put the new table in sql property each execution. i trying:

@Named
@Dependent
public class PigeonJdbcItemReader extends JdbcItemReader {

    @Inject
    @BatchProperty
    protected String prefix;
    
    @Inject
    @BatchProperty
    protected String sql;

    @Override
    public void open(Serializable checkpoint) throws Exception {
        DecimalFormat formater = new DecimalFormat("00");         
        String sufix = formater.format(LocalDate.now().getYear()) + "_" + formater.format(LocalDate.now().getMonthValue()) + "_" + LocalDate.now().getDayOfMonth();
        sql = sql.replaceAll("src_tbl", prefix + sufix);
        super.open(checkpoint);
    }
}

so... in JdbcItemReader.open the sql stay with <src_tbl> yet... and a sql error certein

[cfang]

When starting a job execution, can you pass in a job parameter to specify the sql, and the job.xml can reference that job parameter. 

Or if only part of the sql statement diffs in each job execution, you can just substitute part of the sql value.

jobParameters.setProperty("sql", "select * from PERSON");

or

jobParameters.setProperty("tableName", "PERSON_001");

<job id="org.jberet.support.io.JdbcReaderTest" xmlns="http://xmlns.jcp.org/xml/ns/javaee" version="1.0">
  <step id="org.jberet.support.io.JdbcReaderTest.step1">
  <chunk item-count="100">
  <reader ref="jdbcItemReader">
  <properties>
  <property name="beanType" value="#{jobParameters['readerBeanType']}"/>
  <property name="sql" value="#{jobParameters['sql']}"/>

Or,

  <property name="sql" value="select * from #{jobParameters['tableName']}"/>

[joao.santana]

OK.

But my job is scheduled... how its works on a scheduled context?

[cfang]

The scheduler, as the batch client, should be able to add job parameters when starting a job execution.  There may be some ways to configure the scheduler.

[joao.santana]

ok. thanks