Error with Query after updating to spring boot 3

I update project from spring boot 2 to spring boot 3. After updating i've got errors (at the end).

My Entity:

@Entity
@Table(name = "rules")
@Getter
@Setter
@Builder
@ToString
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
public class Rules {
  @Id
  private Long id;

  @JdbcTypeCode(SqlTypes.ARRAY)
  @Column(name = "message_department_ids")
  private List<Long> messageDepartmentIds;
}

Repository:

@Repository
@Retryable(value = { SQLException.class }, maxAttemptsExpression = "#{${retry.maxAttempts:2147483647}}",
        backoff = @Backoff(delayExpression = "#{${retry.delay:1000}}"))
public interface RulesRepository extends JpaRepository<Rules, Long>, JpaSpecificationExecutor<Rules> {

    @Modifying
    @Query("UPDATE Rules " +
            "SET messageDepartmentIds = FUNCTION('ARRAY_REMOVE', messageDepartmentIds, :departmentId) " +
            "WHERE id = :id")
    void deleteDepartmentId(@Param("id") Long id, @Param("departmentId") Long departmentId);
}

And exception:

Validation failed for query for method public abstract void project.repository.RulesRepository.deleteDepartmentId(java.lang.Long,java.lang.Long)

Caused by: org.hibernate.query.sqm.InterpretationException: Error interpreting query [Cannot invoke "org.hibernate.query.sqm.SqmExpressible.getSqmType()" because the return value of "org.hibernate.query.sqm.tree.SqmTypedNode.getExpressible()" is null] [UPDATE Rules SET messageDepartmentIds = FUNCTION('ARRAY_REMOVE', messageDepartmentIds, :departmentId) WHERE id = :id] [UPDATE Rules SET messageDepartmentIds = FUNCTION('ARRAY_REMOVE', messageDepartmentIds, :departmentId) WHERE id = :id]

Caused by: java.lang.NullPointerException: Cannot invoke "org.hibernate.query.sqm.SqmExpressible.getSqmType()" because the return value of "org.hibernate.query.sqm.tree.SqmTypedNode.getExpressible()" is null

Can't understand what should i do. Change SqlTypes or maybe change Query, but how?

Version of Hibernate is 6.4.1 Final. Version of Spring-boot 3.2.2

Solution

The error you're encountering is related to Hibernate and its inability to interpret the query properly. Specifically, the issue arises because Hibernate cannot determine the type of the

FUNCTION('ARRAY_REMOVE', ...)

expression in your query. This is likely due to a mismatch or lack of proper type handling for the custom SQL functionARRAY_REMOVE.

Here’s a breakdown of the issue and how to fix it:

Understanding the Problem

Custom SQL Function (ARRAY_REMOVE):
1. The query uses the ARRAY_REMOVE function, which is a PostgreSQL-specific function used to remove an element from an array.
2. Hibernate does not natively understand this function, so it cannot infer the type of the result.
Error Cause:
1. Hibernate tries to interpret the query and determine the type of the result of
  
  FUNCTION('ARRAY_REMOVE', ...)
  
  .
2. Since ARRAY_REMOVE is not a built-in Hibernate function, Hibernate cannot determine the type (SqmExpressible) of the result, leading to a NullPointerException.

Query:

UPDATE Rules
SET messageDepartmentIds = FUNCTION('ARRAY_REMOVE', messageDepartmentIds, :departmentId)
WHERE id = :id

Solution

To fix this issue, you need to register the ARRAY_REMOVE function with Hibernate and explicitly define its return type. This can be done by creating a custom SQL function in Hibernate.

Step 1: Register the Custom SQL Function

You need to register the ARRAY_REMOVE function in Hibernate's dialect. If you're using PostgreSQL, extend the PostgreSQLDialect class and register the function.

import org.hibernate.dialect.PostgreSQLDialect;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.type.StandardBasicTypes;

public class CustomPostgreSQLDialect extends PostgreSQLDialect {

    public CustomPostgreSQLDialect() {
        super();
        // Register the ARRAY_REMOVE function
        this.registerFunction("ARRAY_REMOVE", new StandardSQLFunction("ARRAY_REMOVE", StandardBasicTypes.ARRAY));
    }
}

Explanation:
1. StandardSQLFunction is used to register the SQL function.
2. StandardBasicTypes.ARRAY specifies that the return type of the function is an array.

Step 2: Configure Hibernate to Use the Custom Dialect

Update your application.properties or application.yml to use the custom dialect.

For application.properties:

spring.jpa.properties.hibernate.dialect=com.example.config.CustomPostgreSQLDialect

For application.yml:

spring:
  jpa:
    properties:
      hibernate:
        dialect: com.example.config.CustomPostgreSQLDialect

Replace com.example.config.CustomPostgreSQLDialect with the actual package name where your custom dialect is located.

Step 3: Update the Query

Now that Hibernate understands the ARRAY_REMOVE function, you can use it in your query. Ensure the query is written correctly in your repository.

@Modifying
@Query("UPDATE Rules r SET r.messageDepartmentIds = FUNCTION('ARRAY_REMOVE', r.messageDepartmentIds, :departmentId) WHERE r.id = :id")
void deleteDepartmentId(@Param("departmentId") Long departmentId, @Param("id") Long id);

Explanation:
1. The
  
  FUNCTION('ARRAY_REMOVE', ...)
  
  syntax is used to call the custom SQL function.
2. The @Modifying annotation is required for UPDATE queries in Spring Data JPA.

Step 4: Ensure Proper Transaction Management

Since this is an UPDATE query, ensure that the method is executed within a transaction. Add the @Transactional annotation to the service or repository method that calls this query.

@Service
public class RulesService {

    @Transactional
    public void removeDepartmentFromRule(Long departmentId, Long ruleId) {
        rulesRepository.deleteDepartmentId(departmentId, ruleId);
    }
}

Step 5: Verify the Database Schema

Ensure that the messageDepartmentIds column in the Rules table is of type ARRAY in PostgreSQL. For example:

ALTER TABLE Rules
ADD COLUMN messageDepartmentIds BIGINT[];

If the column is not of type ARRAY, the ARRAY_REMOVE function will not work.

Alternative Solution: Use Native Query

If you don't want to register the custom function, you can use a native query instead of a JPQL query.

@Modifying
@Query(value = "UPDATE Rules SET messageDepartmentIds = ARRAY_REMOVE(messageDepartmentIds, :departmentId) WHERE id = :id", nativeQuery = true)
void deleteDepartmentId(@Param("departmentId") Long departmentId, @Param("id") Long id);

Explanation:
1. The nativeQuery = true flag tells Hibernate to execute the query as raw SQL.
2. This bypasses Hibernate's type inference issues.