Grails on Groovy - Modify SQL Query Just Before Execute The Query Finally Run

If you want to modify the SELECT query just before Hibernate executes it, you can achieve this by leveraging a combination of Hibernate Interceptors These allow you to intercept the SQL being generated and make modifications.

Step 1: Create a Custom Interceptor Create a class that extends org.hibernate.EmptyInterceptor. Override the onPrepareStatement method to intercept and modify the SQL query.

Example: CustomSQLInterceptor.groovy import org.hibernate.EmptyInterceptor class CustomSQLInterceptor extends EmptyInterceptor { @Override String onPrepareStatement(String sql) { // Modify the SQL query just before execution if (sql.toLowerCase().startsWith("select")) { // Example: Add a condition dynamically sql = sql.replace("WHERE", "WHERE 1=1 AND user_id = 100 AND") } return sql } } This interceptor will allow you to inspect and modify SQL queries generated by Hibernate.

Step 2: Register the Interceptor You need to register the interceptor with Hibernate. For Grails, you can configure it in application.groovy or use a Spring bean in resources.groovy. Option 1: Register in application.groovy Add the following Hibernate configuration in application.groovy: hibernate { interceptor = new CustomSQLInterceptor() } Option 2: Register in resources.groovy Alternatively, define the interceptor as a Spring bean in grails-app/conf/spring/resources.groovy: beans = { customSQLInterceptor(CustomSQLInterceptor) } Step 3: Test the Interceptor Run your Grails application and observe how the interceptor modifies the SQL queries. You can enable Hibernate SQL logging in application.groovy to verify: hibernate { format_sql = true use_sql_comments = true show_sql = true } Run a sample query and observe the logs to confirm that the SQL is being modified correctly.