How to select substring on entity column using Spring Data JPA

user_2802521 0
Viewed 85

I have an entity called Note which has an attribute text, and I implemented a JpaRepository as follow:

@Entity
@Data
public class Note {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String title;
    private String text;
}

public interface NoteRepository extends JpaRepository<Note, Long> {
}

in my view I want to show just a chunk of the column text, in SQL it would be substr(text, 1, 200), I could use java substring but I'd like to retrieve the text from database already chunked and inject the result into the Note.text attribute, is it possible?

spring-data-jpa jpql spring java
1 Answers

substring is a standard function of JPQL (See the [Hibernate ORM documentation about functions][1]). This query will work:

select n.id, n.title, substring(n.text, 1, 200) as text from Note n

The problem is that Spring doesn't recognize that the fields are part of of the entity and won't transform the result automatically.

You can fix this issue adding a constructor to Note:

public Note(Long id, String title, String text) {
    this.id = id;
    this.title = title;
    this.text = text;
}

And now you can have:

@Query("select new Note(n.id as id, n.title as title, substring(n.text, 1, 200)) from Note n")
List<Note> findListWithShortenedText();

If you don't want the constructor, this will work:

@Query("select n.id as id, n.title as title, substring(n.text, 1, 200) from Note n")
List<Object[]> findNotesWithShortenedText();

And you can read the value this way:

List<Object[]> results = repository.findListWithSHortenedText();
for(Object[] row : results) {
    Long id = (Long) row[0];
    String title = (String) row[1];
    String text = (String) row[2];
}
user_24046830
Related