Spring Batch: JdbcPagingItemReader does not work through setSortKeys and cast(id as unsigned)

Question

I am working with Spring Batch version 3.0.7 with Mysql

I had the problem that the person_reader table has the id column defined how varchar and thus the following happens for a simple select ordered:

id
--
1
10 <--- !!!
2
3
...

What is need it is

id
--
1
2
3
...
10 <--- ok!
...

The solution is use ORDER BY cast(id as unsigned)

If I use JdbcCursorItemReader I can use in peace:

setSql("SELECT id, first_name, last_name FROM person_reader ORDER BY cast(id as unsigned)");

It works fine. Until here all is ok.

Note: for the batch process, is very important read the data in the correct order.

The problem is with JdbcPagingItemReader

First Try:

    JdbcPagingItemReader<Person> itemReader = new JdbcPagingItemReader<>();
    itemReader.setDataSource(dataSource);
    itemReader.setPageSize(100);
    itemReader.setRowMapper(new PersonRowMapper());

    MySqlPagingQueryProvider pagingQueryProvider = new MySqlPagingQueryProvider();
    pagingQueryProvider.setSelectClause("SELECT id, first_name, last_name");
    pagingQueryProvider.setFromClause("FROM person_reader ORDER BY cast(id as unsigned)");

    Map<String, Order> sortKeys= new HashMap<>();
    sortKeys.put("id", Order.ASCENDING);
    pagingQueryProvider.setSortKeys(sortKeys);

    itemReader.setQueryProvider(pagingQueryProvider);

Observe:

setFromClause("FROM person_reader ORDER BY cast(id as unsigned)")
put("id", Order.ASCENDING)

The following error appears:

org.springframework.jdbc.BadSqlGrammarException: StatementCallback;
bad SQL grammar [SELECT id, first_name, last_name FROM person_reader ORDER BY cast(id as unsigned) ORDER BY id ASC LIMIT 100];
nested exception is java.sql.SQLSyntaxErrorException:
You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY id ASC LIMIT 100' at line 1

Has sense, the SQL generated SELECT id, first_name, last_name FROM person_reader ORDER BY cast(id as unsigned) ORDER BY id ASC LIMIT 100 is not correct.

Second Try

    JdbcPagingItemReader<Person> itemReader = new JdbcPagingItemReader<>();
    itemReader.setDataSource(dataSource);
    itemReader.setPageSize(100);
    itemReader.setRowMapper(new PersonRowMapper());

    MySqlPagingQueryProvider pagingQueryProvider = new MySqlPagingQueryProvider();
    pagingQueryProvider.setSelectClause("SELECT id, first_name, last_name");
    pagingQueryProvider.setFromClause("FROM person_reader");

    Map<String, Order> sortKeys= new HashMap<>();
    sortKeys.put("cast(id as unsigned)", Order.ASCENDING);
    pagingQueryProvider.setSortKeys(sortKeys);

    itemReader.setQueryProvider(pagingQueryProvider);

Observe:

setFromClause("FROM person_reader")
put("cast(id as unsigned)", Order.ASCENDING)

The following error appears:

org.springframework.jdbc.UncategorizedSQLException: StatementCallback;
uncategorized SQLException for SQL [SELECT id, first_name, last_name FROM person_reader ORDER BY cast(id as unsigned) ASC LIMIT 100];
SQL state [S0022]; error code [0]; Column 'cast(id as unsigned)' not found.;
nested exception is java.sql.SQLException: Column 'cast(id as unsigned)' not found.

Has sense about the 'missing' column

Order is an enum and thus it can't be extended.

What is the correct approach?

Alpha

If I use in MySQL's workbench the following:

SELECT id, first_name, last_name, cast(id as unsigned) as sb_sort_column FROM person_reader ORDER BY sb_sort_column ASC

It works fine. If I use:

MySqlPagingQueryProvider pagingQueryProvider = new MySqlPagingQueryProvider();
pagingQueryProvider.setSelectClause("SELECT id, first_name, last_name, cast(id as unsigned) as sb_sort_column");
pagingQueryProvider.setFromClause("FROM person_reader");

Map<String, Order> sortKeys= new HashMap<>();
sortKeys.put("sb_sort_column", Order.ASCENDING);
pagingQueryProvider.setSortKeys(sortKeys);

itemReader.setQueryProvider(pagingQueryProvider);

The following error arises:

org.springframework.jdbc.BadSqlGrammarException: 
PreparedStatementCallback; 
bad SQL grammar [SELECT id, first_name, last_name, cast(id as unsigned) as sb_sort_column FROM person_reader WHERE ((sb_sort_column > ?)) ORDER BY sb_sort_column ASC LIMIT 100]; 
nested exception is java.sql.SQLSyntaxErrorException: 
Unknown column 'sb_sort_column' in 'where clause'

From above see carefully the 3rd line about the sql statement generated.

Possible duplicate of https://stackoverflow.com/questions/39577390/spring-batch-with-column-alias-as-sort-key-malformed-where-statement — Luca Basso Ricci, Jun 06 '17 at 14:35
Realistically this is a problem with your data model. Can you modify the `id` column to be numeric? — Dean Clark, Jun 06 '17 at 15:31
Even if is possible, there are scenarios where it is not possible, such as a legacy app working with other apps. — Manuel Jordan, Jun 06 '17 at 15:37

Dean Clark · Answer 1 · 2017-06-06T18:44:38.470

1

Manuel's earlier answer will work, so removing the duplicate portion of mine...

Unfortunately, despite being functionally accurate, doing an ORDER BY on a function will probably run pretty poorly, since it will have to scan the entire table to do the cast, and I don't believe MySQL lets you create an index on function yet.

That said, as of MySQL 5.7.6, you could add a virtual column CAST(ID as UNSIGNED) as SORT_ID to the table and then index the virtual column.

edited Jun 06 '17 at 18:44

answered Jun 06 '17 at 17:45

Dean Clark

3,770
1
11
26

Did you test your code? I did something similar and it does not work. Read my own answer – Manuel Jordan Jun 06 '17 at 17:55
You put it in the `SELECT`, you didn't put the sub-select in the `FROM` – Dean Clark Jun 06 '17 at 17:56
Ah, didn't see that when I was drafting my answer. I'm confused though... are you saying your answer below did or did not work? If it didn't work, what's the new stacktrace? – Dean Clark Jun 06 '17 at 18:14
It works, I can't check how the correct answer until tomorrow (SO's restriction). My snippet code contains the `Java` + `sql` correct configuration. In other links just cover the `sql` part – Manuel Jordan Jun 06 '17 at 18:41
Gotcha... I removed the duplicate portions of my answer, leaving only the portions related to mitigating performance issues. Not a concern on a small table, but probably worth adding that virtual column and index if you have a recent MySQL version and a decent volume of data. – Dean Clark Jun 06 '17 at 18:46

Manuel Jordan · Accepted Answer · 2017-06-06T18:40:20.927

Solution:

The link provided in the comment was useful. It from:

Spring batch with column alias as sort key - malformed “where” statement

Therefore the correct configuration is:

    MySqlPagingQueryProvider pagingQueryProvider = new MySqlPagingQueryProvider();
    pagingQueryProvider.setSelectClause("SELECT id, first_name, last_name, sb_sort_column");
    pagingQueryProvider.setFromClause("FROM (SELECT id, first_name, last_name, cast(id as unsigned) as sb_sort_column FROM person_reader) person_reader");

    Map<String, Order> sortKeys= new HashMap<>();
    sortKeys.put("sb_sort_column", Order.ASCENDING);
    pagingQueryProvider.setSortKeys(sortKeys);

Note: be sure to include in the setSelectClause method the alias column, in this case sb_sort_column

Spring Batch: JdbcPagingItemReader does not work through setSortKeys and cast(id as unsigned)

2 Answers2