How to get ID fields from table using spring batch



I´m trying to get all fields from a table using spring batch.

I got all fields, except the ID fields (primary key e foreign key)

Below is my reader:

@Configuration
public class ReaderConfig {

@Bean
public JdbcCursorItemReader<Entity> Reader(
        @Qualifier("datasource") DataSource dataSource) {
    
    return new JdbcCursorItemReaderBuilder<Entity>()
            .name("Reader")
            .dataSource(dataSource)
            .sql("select * from table")
            .rowMapper(new BeanPropertyRowMapper<Entity>(Entity.class))
            .build();
    }
}

Below is my writer:

@Configuration
public class WriterConfig {

    @Bean
    public ItemWriter<Entity> Writer() {
    
        return entities-> entities.forEach(System.out::println);
    
    }
}

The others fields comes with success, but the ID fields like transactionId and deposit comes null.

I think there is some kind of protection that not permit these fields show its values.

Someone can help me?

Answer

The column name in your table is ID_TRANSACTION, but the getter/setter that you seem to be generating with lombok would be getTransactionId/setTransactionId, which do not match the column name. According to the javadoc of BeanPropertyRowMapper, you can use an alias if the column name does not match the field name. Here is an excerpt from the javadoc:

To facilitate mapping between columns and fields that don't have matching names,
try using column aliases in the SQL statement like
"select fname as first_name from customer".

In your case, you need to update your query to something like:

select ID_TRANSACTION as transactionId, ID_DEPOSIT as depositId, /* other fields */  from rm_transaction

Here is a quick example:

import javax.sql.DataSource;

import org.springframework.batch.core.Job;
import org.springframework.batch.core.JobParameters;
import org.springframework.batch.core.configuration.annotation.EnableBatchProcessing;
import org.springframework.batch.core.configuration.annotation.JobBuilderFactory;
import org.springframework.batch.core.configuration.annotation.StepBuilderFactory;
import org.springframework.batch.core.launch.JobLauncher;
import org.springframework.batch.item.ItemWriter;
import org.springframework.batch.item.database.JdbcCursorItemReader;
import org.springframework.batch.item.database.builder.JdbcCursorItemReaderBuilder;
import org.springframework.batch.item.file.FlatFileItemWriter;
import org.springframework.batch.item.file.builder.FlatFileItemWriterBuilder;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.FileSystemResource;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabase;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType;

@Configuration
@EnableBatchProcessing
public class MyJobConfig {

    @Bean
    public JdbcCursorItemReader<Person> itemReader() {
        String sql = "select person_id as id, name from person";
        return new JdbcCursorItemReaderBuilder<Person>()
                .name("personItemReader")
                .dataSource(dataSource())
                .sql(sql)
                .beanRowMapper(Person.class) // equivalent to .rowMapper(new BeanPropertyRowMapper<>(Person.class)) 
                .build();
    }

    @Bean
    public ItemWriter<Person> itemWriter() {
        return items -> items.forEach(System.out::println);
    }

    @Bean
    public Job job(JobBuilderFactory jobs, StepBuilderFactory steps) {
        return jobs.get("job")
                .start(steps.get("step")
                        .<Person, Person>chunk(5)
                        .reader(itemReader())
                        .writer(itemWriter())
                        .build())
                .build();
    }

    public static void main(String[] args) throws Exception {
        ApplicationContext context = new AnnotationConfigApplicationContext(MyJobConfig.class);
        JobLauncher jobLauncher = context.getBean(JobLauncher.class);
        Job job = context.getBean(Job.class);
        jobLauncher.run(job, new JobParameters());
    }

    @Bean
    public DataSource dataSource() {
        EmbeddedDatabase embeddedDatabase = new EmbeddedDatabaseBuilder()
                .setType(EmbeddedDatabaseType.HSQL)
                .addScript("/org/springframework/batch/core/schema-hsqldb.sql")
                .build();
        JdbcTemplate jdbcTemplate = new JdbcTemplate(embeddedDatabase);
        jdbcTemplate.execute("create table person (person_id int primary key, name varchar(20));");
        for (int i = 1; i <= 10; i++) {
            jdbcTemplate.execute(String.format("insert into person values (%s, 'foo%s');", i, i));
        }
        return embeddedDatabase;
    }

    static class Person {
        private int id;
        private String name;

        public Person() {
        }

        public int getId() {
            return id;
        }

        public void setId(int id) {
            this.id = id;
        }

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public String toString() {
            return "Person{id=" + id + ", name='" + name + ''' + '}';
        }
    }

}

This prints:

Person{id=1, name='foo1'}
Person{id=2, name='foo2'}
Person{id=3, name='foo3'}
Person{id=4, name='foo4'}
Person{id=5, name='foo5'}
Person{id=6, name='foo6'}
Person{id=7, name='foo7'}
Person{id=8, name='foo8'}
Person{id=9, name='foo9'}
Person{id=10, name='foo10'}

If you change the sql query to select * from person, you will see the that the id field won’t be mapped correctly, because the BeanPropertyRowMapper won’t find a getter/setter named getPerson_Id/setPerson_Id.



Source: stackoverflow