How to Adapt Pagination Syntax for Different Databases

[luckygc]

Hi,

I’m currently working on a project that uses MyBatis Dynamic SQL, and I’ve encountered a challenge when dealing with pagination. Different databases often have distinct syntax for handling pagination, such as LIMIT and OFFSET in PostgreSQL/MySQL or ROWNUM and FETCH NEXT in Oracle/SQL Server.

Is there a recommended approach within MyBatis Dynamic SQL to dynamically adapt pagination queries to the syntax of different databases? For example:

• Is there a built-in abstraction layer that handles this?
• Do I need to extend or customize any existing features?

Any guidance or suggestions would be greatly appreciated. Thank you for your support!

[jeffgbutler]

The library doesn't have any abstraction layer for different SQL dialects, and we don't want to add it.

OFFSET and FETCH FIRST are standard in SQL:2011 and many, if not most, databases support them. I would just use OFFSET and FETCH FIRST and handle the outliers in some other way.

[luckygc]

Thank you for your response! I understand your perspective on not adding an abstraction layer for different SQL dialects. If I were to fork the library and add support for multiple SQL dialects myself, do you have any suggestions or best practices on how to approach this? Any insights or advice would be greatly appreciated.

[jeffgbutler]

My only advice for building an SQL abstraction is... don't do it! You can't begin to imagine the corner cases 😃

JPA/Hibernate is the closest you will get to a fully functional abstraction and there have been hundreds of engineers working on it for many years. I would recommend using JPA if you need this kind of abstraction.

If it were me, I would simply write different queries for the different DBs and inject the proper ones at runtime based on the target DB. I think that would be the easiest for future maintainers to understand.