What is the best or correct way to write the large query in the JdbcCursorItemReader readers class?

Question

I am using the Spring Boot and Batch annotation based approached. I am reading tables data using the JdbcCursorItemReader and writing it into CSV/XML/other tables etc based on the need.

As a spring batch best practice just wanted to know the view the way I have created the SQL query inside the JdbcCursorItemReader method. Is there any way if we can avoid concatenation and follow the best way around this like we do in XML based approached ?

Please let me know if the following way of writing SQL query is the best way ?

Annotation based approached.

@Bean(destroyMethod="")
    public JdbcCursorItemReader<Orders> employeesReader(){
        JdbcCursorItemReader<Orders> itemReader = new JdbcCursorItemReader<>();
        itemReader.setDataSource(dataSource);
        itemReader.setSql("SELECT orderNumber, productName, msrp, priceEach "
                + "FROM products p "
                + "INNER JOIN orderdetails o "
                + "ON p.productcode = o.productcode "
                + "AND p.msrp > o.priceEach "
                + "WHERE p.productcode = ? ");
        itemReader.setRowMapper(new OrdersRowMapper());
        itemReader.setIgnoreWarnings(true);

        return itemReader;
    }

The same XML can be done using XML based approached without concatenation operators

<bean id="ordersItemReader" class="org.springframework.batch.item.database.JdbcCursorItemReader" scope="step" >
    <property name="dataSource" ref="dataSource" />
    <property name="sql">
        <value>
            SELECT orderNumber, productName, msrp, priceEach
            FROM products p INNER JOIN orderdetails o ON p.productcode = o.productcode
            AND p.msrp > o.priceEach
            WHERE p.productcode =  '#{stepExecutionContext[productcode]}';
        </value>
    </property>
    <property name="rowMapper">
        <bean class="com.XXXX.mapper.OrdersRowMapper" scope="step" />
    </property>
</bean>

In my project, we're using annotation based approached and need the guidance on this.

score 1 · Answer 1 · answered Jul 08 '18 at 16:32

1

How about using an XML properties file? I also like to have clean queries, and it's especially nice to have them in one place.

Just create your XML with an entry called orderQuery with that query as the key.

Then you can load the XML properties with a normal @PropertySource and inject it into your @Configuration @Bean with @Value("${orderQuery}) or Environment#getProperty.

Hope that helps!

answered Jul 08 '18 at 16:32

Dovmo

8,121
3
30
44

Agree, basically this is workaround in terms of making query itself configurable and if anything changes, then we may not need to build the code again. I believe at-least query to have in that place only instead of having in .properties file. I may be using the delegates in that may need to write 5 different joins queries. This will gave idea for comparison there itself. For now sticking with the annotation based approached shown above. – Jeff Cook Jul 08 '18 at 16:51
The above solution doesn't mean that the query is configurable, and you should still be able to used the annotation based approach. Do you think the answer needs more clarification? – Dovmo Jul 08 '18 at 16:56
Right. I'm looking for best practice and ideal way of handling the queries – Jeff Cook Jul 08 '18 at 16:58
1

In my opinion,there is no ideal way. There are couple ways I'm seeing so far. 1) Like you are doing, SQL in java code directly at reader. 2) Keep in multiple or single .sql file. 3) Using properties files. Each approach is advantage and disadvantages. For your case, I go with the sql in the reader itself. The reason is: a. We will see right the way the logic of reader instead of finding it in resources outside. b. properties can be easily to override by mistake. c. if the queries changes, it has more chance to change java code as well. – Nghia Do Jul 09 '18 at 14:45

score 0 · Answer 2 · answered Jul 10 '18 at 13:55

0

Personally I think the consistency is the thing to be guided by. If you are using annotations in your project, use annotation based approach. Otherwise it can be tricky for newcomers to understand the reason why are you mixing xmls and annotations (may be there is some sacred meaning for this). From the readability point of view I don't see much difference.

P.S. By the way, I prefer to put @StepScope annotation on method instead of putting (destroyMethod="")

answered Jul 10 '18 at 13:55

Pavel

2,557
1
23
19

Thanks about the PS. We're only talking about the how to use SQL query in method itself or configured in properties file. XML snippet shown because it doesn't use concatenation and will not create unnecessary spring object which is happening through annotation based approached ? – Jeff Cook Jul 10 '18 at 16:14
Please forget about string concatenation at all. First of all, it's irrelevant in this case as the hole string will be concatenated during compilation (see https://stackoverflow.com/questions/11989261/does-concatenating-strings-in-java-always-lead-to-new-strings-being-created-in-m). Even in some other case, if the string is creating by concatenation during run-time, you shouldn't take it into account as this is very "cheap" operation. You should consider performance of concatenation only in loops with more than 10000 operations. – Pavel Jul 10 '18 at 17:57

What is the best or correct way to write the large query in the JdbcCursorItemReader readers class?

2 Answers2