Spring Specification with @query

user_5002881 0
Viewed 2562

I have a table named 'content' which has a field named 'created_at'. I am trying to use pageable and specifications in this table.

Specifications works perfectly but i have a problem with pageable. If i use the inherited method from the repository to search the pageable don't recognize the field with underscore and tries to split him. Givin this error:

"No property created found for type Content!"

If i create a method in the repository pageable works but specifications don't. Here is my repository:

@Repository
public interface ContentRepository extends JpaRepository<Content, 
String>,JpaSpecificationExecutor<Content> {

   @Query(value = "SELECT * FROM content", nativeQuery = true)
   public Page<Content> findAll(Specification<Content> specification, Pageable pageable);    

}

How can i do both?

Content class:

 @Entity
 @Table(name = "content")
 @Setter
 @Getter
 public class Content {

  @Id
  @Column(name = "id")
  private String id;

  @Column
  private String name;

  @Column
  private String description;

  @Column(columnDefinition = "TEXT")
  private String content;

  @Column(columnDefinition = "TEXT")
  private String reference;

  @ManyToOne
  @JoinColumn(nullable = false)
  private User author;

  @ManyToOne
  @JoinColumn(nullable = true)
  private Agenda agenda;

  @ManyToOne
  @JoinColumn(nullable = false)
  private ContentType contenttype;

  @Column(columnDefinition = "boolean default true")
  private boolean enabled;

  @Column(columnDefinition = "boolean default false")
  private boolean approved;

  @Column
  private Date sent_at;
  @Column
  private Date created_at;
  @Column
  private Date updated_at;
  @Column
  private Date deleted_at;

}
spring-data spring-data-jpa spring-boot spring java
1 Answers

Avoid using underscores in the entity property names if you have control over the property naming. This will resolve your repository woes, and will result in a cleaner code-base. Developers dealing with the code after you will thank you.

Note, it's not just my opinion: Spring specifically discourages using underscores.

As we treat underscore as a reserved character we strongly advise to follow standard Java naming conventions (i.e. not using underscores in property names but camel case instead).

this JIRA issue shows why the documentation was updated with this reccomendation, and the part describing the double underscore option were removed.

I suspect your root problem is that Spring/Hibernate is not mapping camel case property names to the snake case names you have for your columns in the database. What you really need is for your property name to be interpreted in the SQL that hiberate generates as created_at.

Is that why underscores in your property name are "required"? If so, there are a few solutions:

Option 1: @Column annotation

To get JPA/Hibernate to map to the correct column names you can tell it the names explicitly. Use the annotation @Column(name="...") to tell it what column names to use in SQL. Then the field names are not constrained by the column names.

@Entity
@Table(name = "content")
@Setter
@Getter
public class Content {
     @Id
     @Column(name="created_at")
     private String createdAt;
}

Option 2: Improved Naming Strategy
Or if your application has a large number of entities, rather than adding @Column to every property, change the default naming strategy in your configuration file to the hibernate improved naming strategy.

<prop key="hibernate.ejb.naming_strategy">org.hibernate.cfg.ImprovedNamingStrategy</prop>

This naming strategy will convert camelCase to SNAKE_CASE. Then your class could look as simple as this:

@Entity
public class Content{
     @Id
     private String createdAt;

}

Using either of those options, when it creates the SQL it will resolve the column names to:

 created_at

Note: If you are using, or can use Spring Boot, the auto-configuration default will use SpringNamingStrategy, which is a slightly modified version of the hibernate improved strategy. You won't have to do anything to get this improved naming strategy.

The finish line:

Using camel case in your property names you can write your repository method name using camel case, and you can stop trying to wrangle the double underscore:

@Repository
@Transactional
public interface ContentRepository extends CrudRepository<Content, String> {  
      @Query(value = "SELECT * FROM content", nativeQuery = true)
       List<Student> findAll(Specification<Content> specification, Pageable pageable);   
}
user_58097200
Related