querydsl / querydsl

Unified Queries for Java
https://querydsl.com
Apache License 2.0
4.74k stars 873 forks source link

New IBM DB2 Template Required #3746

Open r4tylmz opened 2 months ago

r4tylmz commented 2 months ago

Why the new feature should be added

Currently, when using the QueryDSL package with IBM DB2 for pageable queries via JPAQuery, the generated SQL query follows this structure:

select * from ( select inner2.*, rownumber() over (order by id) as rownumber_ from (select * from .... fetch first 100 rows only ) inner2 ) where rownumber_ > 50 order by rownumber_

This structure, while functional, is not as efficient or standard as the LIMIT ... OFFSET ... pattern supported by many modern SQL databases. The LIMIT ... OFFSET ... pattern is more concise and can lead to improved performance and readability of the generated SQL queries.

The ability to generate SQL queries using the LIMIT ... OFFSET ... pattern would benefit users who work with databases like IBM DB2, where this syntax is supported and preferred. This feature would make QueryDSL more versatile and aligned with common SQL practices.

How the new feature should work

Currently, the generated SQL query in IBM DB2 with JPAQuery for pageable results uses the ROWNUMBER() window function combined with a nested query structure. The new feature should alter this behavior to generate SQL queries using the LIMIT ... OFFSET ... pattern instead.

The expected behavior would be:

The generated SQL query should look like this: select * from .... limit 100 offset 50;

This change should seamlessly integrate with existing QueryDSL functionality without affecting other database dialects or users who prefer the current behavior. It should be configurable, allowing users to choose between the current ROWNUMBER() based approach and the new LIMIT ... OFFSET ... approach based on their database and performance needs. This enhancement would improve the flexibility and performance of QueryDSL when used with IBM DB2, making it more adaptable to different SQL dialects and user preferences.

velo commented 1 month ago

Hi,

Sadly, you will get no help here. Team gave up on maintaining this project but won't let anyone else to step in to keep project active.

If you keen on fixing this and sharing with the community, you can send to me on my fork and I will get it released. https://github.com/OpenFeign/querydsl/

Testing DB2 on circle CI proved to be very challenging.

Cheers