Case insensitive column names in Hibernate query

While working on migrating an application using Spring 5.3.39 to 6.2.1, Spring Boot from 2.7.18 to 3.4.1, Hibernate from 5.6.15.Final to 6.6.4.Final, with Derby 10.14.2.1, I encountered the following exception.

org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.query.SemanticException: Could not interpret path expression 'mycolumn'

The exception occurs when the method call repo.findIdsByQuery("mycolumn = 'value'") is being processed, with entity and repository classes declared as shown below. The same method call worked correctly before version upgrade.

When the method is called using camel case column name repo.findIdsByQuery("myColumn = 'value'") it works correctly. Is there a way to configure Hibernate to treat column names as case insensitive in queries?

Entity:

@Entity
@Table(name = "MyEntities")
public class MyEntity implements Serializable {
    private static final long serialVersionUID = 1L;

    private Long    id;
    private String  myColumn;
...
    @Id
    @Column(name = "IDX", nullable = false)
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    @Column(name = "MYCOLUMN")
    public String getMyColumn() {
        return myColumn;
    }

    public void setMyColumn(String myColumn) {
        this.myColumn = myColumn;
    }
...
}

JPA repository:

public interface MyEntitiesRepository extends JpaRepository<MyEntity, Long>, QueryItf {
...
class MyEntitiesRepositoryImpl implements QueryItf {
    @Autowired
    MyEntitiesRepository    repo;
    @PersistenceContext
    EntityManager       em;

    @Override
    @Transactional(readOnly = true)
    public List<Long> findIdsByQuery(String query) {
        TypedQuery<Long> typeQuery = em.createQuery("select id from MyEntities where " + query, Long.class);
        return typeQuery.getResultList();
    }
...
}

As hibernate 6+ enforces stricter SQL and JPQL query parsing

reference: https://discourse.hibernate.org/t/semanticexception-after-migrating-to-hibernate-6-x/9183?utm_source=chatgpt.com

There are multiple ways to achieve this:

-You can leverage the database built in functions (e.g., LOWER, UPPER) in your queries

em.createQuery("select id from MyEntity where LOWER(myColumn) = 'value'", Long.class);

-You can set parameters like below:

em.createNativeQuery("SELECT IDX FROM MyEntities WHERE MYCOLUMN = ?", Long.class).setParameter(1, "value").getResultList();