SpringBoot JPA Repository - get selected fields shows error "Column not found"

Question

I want to fetch selected columns, preferably dynamically.

For now, I'm specifying static columns - id, title, description.

Category.java

@Entity(name="Category")
@Table(name="categories")
public class Category {

   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private long id;
   private String slug;
   private String title;
   private String description;
   private String preview_image;
   private int isFeatured;

   /*  Getters, setters etc */
}

CategoryRepository.java

public interface CategoryRepository extends JpaRepository<Category, Long> {

    @Query(
        value = "SELECT id, title, description FROM categories", 
        nativeQuery = true
    )
    List<Category> findAll();
}

This gives the error:

Column 'is_featured' not found. Exception class: class org.springframework.dao.InvalidDataAccessResourceUsageException Exception [could not execute query; SQL [SELECT id, title, description FROM categories]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query]

Any idea how this error can be resolved? I was thinking of using another model instead of Category but I need to make the fields dynamic later on. It's hard to make model class if I'm unaware of which fields to return.

Also, is there a way I can make this @Query code dynamic such that it returns columns mentioned in parameter?

Any help would be appreciated. Thanks in advance! :)

As metioned by Patrick, you need to add Column annotation on isFeatured field. For dynamic columns, it is not supported in Query annotation. But have a look the this [article](https://stackoverflow.com/questions/47407043/spring-data-jpa-pass-column-name-and-value-as-parameters) for some suggested solutions. — Priyam, Apr 08 '20 at 06:54

Patrick · Answer 1 · 2020-04-08T06:51:50.497

Its because of the naming strategy of Spring and Hibernate. It will convert camelCase to SNAKE_CASE by default. So in your case its isFeatured -> is_featured.

If you dont want to change the naming strategy just add @Column("isFeatured") on your property. This will override the default behavior of this property.

Here you can find more Spring Boot + JPA : Column name annotation ignored

Regarding dynamic query. You should look up the features in the documentation of Spring-data-jpa and about querydsl

SpringBoot JPA Repository - get selected fields shows error "Column not found"

I want to fetch selected columns, preferably dynamically.

1 Answers1