Hello! I am currently using Spring Boot with Java 17, Hibernate 6.4.8, and SQL Server 2008. In the previous version of Hibernate, I had no issues since Hibernate provided a specific dialect for SQL Server 2008. However, now there is only the option for SQLServerDialect. I need to modify the dialect to avoid issues with queries containing OFFSET.
To address this, I created a custom dialect that extends SQLServerDialect and set it in the application.properties file as follows:
spring.jpa.properties.hibernate.dialect=com.ase.beneficiario.config.SQLServer2008CustomDiale
However, for some reason, it is not being applied, and I can see in the logs that queries are still being attempted with OFFSET. If anyone could help me with this, share a similar experience, or suggest another way to solve it, I would greatly appreciate it!
My Custom dialect:
import org.hibernate.dialect.SQLServerDialect;
import org.hibernate.dialect.pagination.AbstractLimitHandler;
import org.hibernate.dialect.pagination.LimitHandler;
import org.hibernate.query.sqm.FetchClauseType;
import org.hibernate.query.sqm.sql.SqmTranslatorFactory;
import org.hibernate.query.sqm.sql.StandardSqmTranslatorFactory;
import org.hibernate.sql.ast.SqlAstTranslatorFactory;
public class SQLServer2008DialectCustom extends SQLServerDialect {
private final int doce = 12;
private final int cero = 0;
private final SqmTranslatorFactory sqmTranslatorFactory;
public SQLServer2008DialectCustom() {
super();
registerKeyword("OFFSET"); // Por si hay un conflicto
this.sqmTranslatorFactory = new StandardSqmTranslatorFactory(); //
// Instanciamos directamente
}
/**
*
* @return
*/
@Override
public LimitHandler getLimitHandler() {
System.out.println("SQLServer2008DialectCustom getLimitHandler");
// Usamos el manejador de límites de SQL Server
// 2005 que se basa en ROW_NUMBER()
return AbstractLimitHandler.NO_LIMIT;
}
/**
*
* @return
*/
@Override
public SqlAstTranslatorFactory getSqlAstTranslatorFactory() {
System.out.println("SQLServer2008DialectCustom getLimitHandler");
return super.getSqlAstTranslatorFactory();
}
/**
*
* @return
*/
@Override
public boolean supportsFetchClause(final FetchClauseType fetchClauseType) {
System.out.println("SQLServer2008DialectCustom getLimitHandler");
// Deshabilitamos cualquier soporte de FETCH para evitar errores
// en SQL Server 2008
return false;
}
/**
*
* @return
*/
@Override
public SqmTranslatorFactory getSqmTranslatorFactory() {
System.out.println("SQLServer2008DialectCustom getLimitHandler");
// Devolvemos la instancia de StandardSqmTranslatorFactory
// creada en el constructor
return sqmTranslatorFactory;
}
/**
*
* @return
*/
@Override
public String getQueryHintString(final String sql, final String hints) {
System.out.println("SQLServer2008DialectCustom getLimitHandler");
// Modificamos las consultas para agregar hints al estilo de SQL Server
StringBuilder buffer = new StringBuilder(sql.length()
+ hints.length() + doce);
int pos = sql.indexOf(';');
if (pos > -1) {
buffer.append(sql, cero, pos);
} else {
buffer.append(sql);
}
buffer.append(" OPTION (").append(hints).append(")");
if (pos > -1) {
buffer.append(";");
}
return buffer.toString();
}
}
and my properties:
server.servlet.context-path=/beneficiario-api/ase
server.port = 8081
# SQLServer
spring.datasource.jdbc-url=jdbc:sqlserver://${ASE_DB_HOST}:${ASE_DB_PORT};databaseName=${ASE_DB_NAME_ASE};trustServerCertificate=true;useSSL=false;
spring.datasource.username=${ASE_DB_USER}
spring.datasource.password=${ASE_DB_PASSWORD}
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.second-datasource.jdbc-url=jdbc:sqlserver://${ASE_DB_HOST}:${ASE_DB_PORT};databaseName=${ASE_DB_NAME_REASE};trustServerCertificate=true;useSSL=false;
spring.second-datasource.username=${ASE_DB_USER}
spring.second-datasource.password=${ASE_DB_PASSWORD}
spring.second-datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.third-datasource.jdbc-url=jdbc:sqlserver://${ASE_DB_HOST}:${ASE_DB_PORT};databaseName=${ASE_DB_NAME_OWN};trustServerCertificate=true;useSSL=false;
spring.third-datasource.username=${ASE_DB_USER}
spring.third-datasource.password=${ASE_DB_PASSWORD}
spring.third-datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
#spring.jpa.properties.hibernate.ejb.interceptor=com.ase.beneficiario
# .interceptor.HibernateInterceptorCustom
spring.jpa.show-sql=${ASE_API_SHOW_SQL:false}
spring.jpa.properties.hibernate.format_sql=true
ase.database=${ASE_DB_NAME_ASE}
rease.database=${ASE_DB_NAME_REASE}
opdevase.database=${ASE_DB_NAME_OWN}
## Hibernate Properties
# The SQL dialect makes Hibernate generate better SQL for the chosen database
#spring.jpa.properties.hibernate.dialect=com.ase.beneficiario.config
# .SQLServer2008DialectCustom
# Para evitar que JPA cambie a snake case los nombres de las columnas de las entidades
spring.jpa.properties.hibernate.dialect=com.ase.beneficiario.config.SQLServer2008CustomDialect
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
nomenclador.base-url=${ASE_API_NOMENCLADOR}
autorizacion-api-url=${ASE_API_AUTORIZACION}
management.endpoint.health.probes.enabled=true
management.health.livenessState.enabled=true
management.health.readinessState.enabled=true
management.endpoints.web.exposure.include=health,info
#maximum number of milliseconds that a client will wait for a connection
spring.datasource.hikari.connection-timeout=20000
#minimum number of idle connections maintained by HikariCP in a connection pool
spring.datasource.hikari.minimum-idle=1
#maximum pool size
spring.datasource.hikari.maximum-pool-size=1
#maximum idle time for connection
spring.datasource.hikari.idle-timeout=10000
#maximum lifetime in milliseconds of a connection in the pool after it is closed.
spring.datasource.hikari.max-lifetime=1000
sonar.token=${SONAR_TOKEN:squ_2c0f23a71e5164197c4b26588d2e0fdbe3667ea7}
#logging.level.org.hibernate.SQL=trace
#logging.level.org.hibernate = trace
#logging.level.org.hibernate.engine.jdbc.env.internal=DEBUG
logging.level.org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator=TRACE