Call method inside sql query in Spring Batch Reader

Question

I have an Enum, that returns gender from gender char:

public enum Gender {
        FEMALE('F'), MALE('M');
        private char gender;

        Gender(char gender) {
            this.gender = gender;
        }

        public static Gender getSex(char gender) {
            return Arrays.stream(Gender.values())
                    .filter(s -> s.gender == gender)
                    .findFirst()
                    .orElseThrow(NoSuchElementException::new);
        }
    }

In a Job, I have a reader with a queryprovider that selects from DB the column sex (with values F or M).

 ItemReader<UserDTO> userDatabaseReader() {
        JdbcPagingItemReader<UserDTO> reader = new JdbcPagingItemReader<>();
        reader.setDataSource(dataSource);
        reader.setPageSize(100);
        reader.setRowMapper(new BeanPropertyRowMapper<>(UserDTO.class));
        MySqlPagingQueryProvider queryProvider = new MySqlPagingQueryProvider();

        queryProvider.setSelectClause("SELECT u.id, " +
                "u.firstname as firstName, " +
                "u.sex as gender, " +
                "u.eliminated ^ 1 as active");

        queryProvider.setFromClause("FROM users u");
        HashMap<String, Order> sortKeys = new HashMap<>();
        sortKeys.put("u.id", Order.DESCENDING);
        queryProvider.setSortKeys(sortKeys);

        reader.setQueryProvider(queryProvider);
        return reader;

    }

I have UserDTO with gender Enum field. How can I get gender Enum from column char calling getSex() method, and pass it to the UserDTO as enum?

Any suggestion?

YanetP1988
– YanetP1988

2019-02-13 13:09:21 +00:00
Commented Feb 13, 2019 at 13:09 — YanetP1988
– YanetP1988, Commented Feb 13, 2019 at 13:09

YanetP1988 · Accepted Answer · 2019-02-13 13:34:42Z

1

The approach is to process each item of reader result in Job Processor. I had to create new attribute in DTO called sex to store char sex column from Database, and get gender enum column using that attribute value. Here processor:

@Bean
ItemProcessor<UserDTO, UserDTO> userProcessor() {
    return item -> {
        try {
            item.setGender(UserDTO.Gender.getGender(item.getSex()));
        } catch (Exception ex) {
            log.error("Error trying to process user '{}'", item.getId());
        }
        return item;
    };
}

And in query:

 queryProvider.setSelectClause("SELECT u.id, " +
                "u.firstname as firstName, " +
                "u.sex as sex, " +
                "u.eliminated ^ 1 as active");

answered Feb 13, 2019 at 13:34

YanetP1988

1,3564 gold badges19 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

YanetP1988 · Accepted Answer · 2019-02-14 16:20:47Z

You can implement a RowMapper manually.

something like

class UserDTORowMapper implements RowMapper<UserDTO> {

    @Override
    public UserDTO mapRow(ResultSet rs, int rowNum) throws SQLException {

        return new UserDTO(rs.getString("id"), 
                           rs.getString("firstName"), 
                           GenderEnum.valueOf(rs.getString("gender")));
    }
}

and than usit in your reader

 ItemReader<UserDTO> userDatabaseReader() {
        JdbcPagingItemReader<UserDTO> reader = new JdbcPagingItemReader<>();
        reader.setDataSource(dataSource);
        reader.setPageSize(100);
        reader.setRowMapper(new UserDTORowMapper());
        MySqlPagingQueryProvider queryProvider = new MySqlPagingQueryProvider();

        queryProvider.setSelectClause("SELECT u.id, " +
                "u.firstname as firstName, " +
                "u.sex as gender, " +
                "u.eliminated ^ 1 as active");

        queryProvider.setFromClause("FROM users u");
        HashMap<String, Order> sortKeys = new HashMap<>();
        sortKeys.put("u.id", Order.DESCENDING);
        queryProvider.setSortKeys(sortKeys);

        reader.setQueryProvider(queryProvider);
        return reader;

    }

Little bit more code but you have way more flexibility

Collectives™ on Stack Overflow

Call method inside sql query in Spring Batch Reader

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related