Hibernate not able to register 'LIMIT' function of MySQL in custom dialect

Question

I want to write a custom dialect for Hibernate to support 'LIMIT' function of MYSQL but even if I register function like this

    public class SiddheyMySQL57InnoDBDialect extends 
        MySQL57InnoDBDialect {

            public SiddheyMySQL57InnoDBDialect(){
            super();
            registerFunction("LIMIT",
            new SQLFunctionTemplate(StandardBasicTypes.INTEGER, "limit ?1"));
            registerFunction("MATCH", new SQLFunctionTemplate(
            StandardBasicTypes.INTEGER, "match(?1) against  (?2 in boolean mode)"));
  }

}

But when I write this query in HQL the hibernate gives me an exception

     @Query("SELECT p FROM #{#entityName} p LIMIT(1)")
   List<Product> getLimitedProducts(@Param("name") final String name);

And yes , I have added path of custom dialect in application.properties

Answer 1

That's not how you do pagination with JPA and Hibernate. LIMIT is a MySQL and PostgreSQL-specific keyword, not a JPQL one.

More, LIMIT(1) is not valid in MySQL because LIMIT is not a function. You should have used it like this instead LIMIT 1 in a native SQL query, not a JPQL one.

So, you need to use setMaxResults method:

List<Product> products = entityManager.createQuery("""
    SELECT p
    FROM Product p
    ORDER BY p.created_on DESC
    """, Product.class)
.setMaxResults(50)
.getResultList();

You didn't use ORDER BY in your query which is also wrong since the order is not guaranteed unless you use ORDER BY.