Custom Limit and Offset in MySQLDialect, Hibernate 6.1.6 Final + Spring boot 3.0.1

Hibernate ORM
1

Hello Experts,
I’m currently working on customising the limit and offset in MySQLDialect, and I’ve encountered a bit stuck. Typically, using limit and offset in MySQLDialect generates the following SQL query:

SELECT b1_0.id, b1_0.name FROM book b1_0 LIMIT 1, 10;

However, I would like to modify the SQL query to adjust the limit and offset as follows:

SELECT b1_0.id, b1_0.name FROM book b1_0 LIMIT 10 OFFSET 1;

Could you please guide me on how to achieve this?
Thank you for your assistance.
The following is my code:

application.properties

spring.datasource.username=root
spring.datasource.password=root
spring.datasource.url=jdbc:mariadb://localhost:3306/csrfdb

spring.jpa.properties.hibernate.dialect = com.example.demoopenai.config.CustomDialect

Controller

@GetMapping("/getBook")
public List<Book> getBook() {
		Query query =  entityManager.createNamedQuery("Book.findByBook") 
		.setFirstResult(1) 
		.setMaxResults(10); 
		
		return query.getResultList();
	}

CustomDialect

@Slf4j
public class CustomDialect extends MySQLDialect {

	public CustomDialect() {
		super();
		log.info("======CustomDialect");
	}

	// here I tried logging the output "processSql", but the log doesn't appear in the console.
	private static final LimitHandler LIMIT_HANDLER = new AbstractLimitHandler() {
		@Override
		public String processSql(String sql, Limit selection) {
			log.info("======processSql");
			final boolean hasOffset = LimitLimitHandler.hasFirstRow(selection);
			String _sql = sql + " limit %d".formatted(selection.getMaxRows());
			if (hasOffset) {
				_sql += " offset %d".formatted(selection.getFirstRow());
			}
			return _sql;
		}
	};
}

However, despite my efforts, the result log still returns:

SELECT b1_0.id, b1_0.name FROM book b1_0 LIMIT 1, 10;

Log:

Screenshot 2024-08-11 235034
Screenshot 2024-08-11 2350341563×486 30.6 KB

Versions:

Spring boot 3.0.1
Hibernate 6.1.6 Final
mariadb-java-client: 3.0.9

2

Hello @chrisq, the static LimitHandler instance you’re initializing is never used - you would need to return it in the Dialect#getLimitHandler() method for it to be considered.

May I ask why you need to change the limit/offset clause this way? The LIMIT clause can take either one or two numeric arguments, and in the latter case the first of the parameters is the offset for the query. As stated in the DB’s documentation:

For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax.

the offset syntax is equivalent, and only there for compatibility purposes.