Overview
Spring Data JPA paired with Hibernate gives you a whole lot of power, just right out of the box. You can write plain language queries that will do things like findAllEntities() or findOneBySpecificProperty(propertyValue). You can even query by related entity properties like findAllWithRelatedEntitySpecificProperty(propertyValue).
If you can't find a way to do it using the query keywords, you can even write your own hand written queries. These are great if you want to do specific joins and references that you wouldn't usually use.
But, what if you want to use some platform specific keyword/function? One that isn't generic enough that Hibernate has implemented it across all its dialects.
Use Case
For a specific use case, we had a developer want to use LISTAGG, which is a Oracle specific method that was rather useful... but not available out of the box. Oh noooo, what happens now?
Well, we went through the default list of available functions declared in the out of the box hibernate dialect, you can see the full list here. Bunch of stuff, right? But, no LISTAGG.
Dialect Extension
Because of how inheritance works in Java, I can just extend that class and then append my own declaration of LISTAGG.
package com.sethkellas.dialectextensions.dialects;
import static org.hibernate.type.StandardBasicTypes.STRING;
import org.hibernate.dialect.H2Dialect;
import org.hibernate.dialect.function.SQLFunctionTemplate;
public class H2CustomDialect extends H2Dialect {
public H2CustomDialect() {
super();
registerFunction("LISTAGG", new SQLFunctionTemplate(STRING,
"LISTAGG(distinct ?1, ',') WITHIN GROUP(ORDER BY ?1)"));
}
}
Configurations
Yeah. It's that simple.
Another dev spent a full day writing a native query in order to use LISTAGG directly against the Oracle database we were pointing at. But because it was a native query, when I went back around to refactor the end point to allow for pagination... it didn't play all that well.
So, we finally took a minute and looked into the above implementation. Extending that class, and then showing Spring how to use the dialect in our application.yml.
spring:
jpa:
database-platform: com.sethkellas.dialectextensions.dialects.H2CustomDialect
Testing
And then we obviously have to test this, because... what's an article without proof.
Entities
@Entity
@Data
@Builder(toBuilder = true)
@AllArgsConstructor(access = AccessLevel.PACKAGE)
@NoArgsConstructor
public class Professor {
@Id
@GeneratedValue
private Long id;
@Column(name = "first_name")
private String firstName;
@Column(name = "last_name")
private String lastName;
@Default
@OneToMany(fetch = FetchType.EAGER, mappedBy = "professor")
private Set<Lecture> lectures = new HashSet<>();
}
@Entity
@Data
@Builder(toBuilder = true)
@AllArgsConstructor(access = AccessLevel.PACKAGE)
@NoArgsConstructor
public class Lecture {
@Id
@GeneratedValue
private Long id;
@Column(name = "title")
private String title;
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "professor_id")
private Professor professor;
}
Repository/Query
@Repository
public interface ProfessorRepository extends JpaRepository<Professor, Long> {
@Query( value = "SELECT new com.sethkellas.dialectextensions.views.ProfessorAndLectureView("
+ " CONCAT(p.firstName, ' ', p.lastName)"
+ " , LISTAGG(l.title, ',')"
+ ")"
+ " FROM Professor p"
+ " LEFT JOIN Lecture l on l.professor = p"
+ " GROUP BY p.firstName",
countQuery = "SELECT COUNT(*) FROM Professor")
Set<ProfessorAndLectureView> findProfessorsWithLecturers();
}
Test
@Test
void shouldReturnViews() {
// Given
Professor testProfessor = professorRepo.save(Professor.builder().firstName(randomAlphabetic(8)).lastName(randomAlphabetic(12)).build());
Lecture lectureAlpha = lectureRepo.save(Lecture.builder().title(randomAlphabetic(16)).professor(testProfessor).build());
Lecture lectureBeta = lectureRepo.save(Lecture.builder().title(randomAlphabetic(24)).professor(testProfessor).build());
// When
Set<ProfessorAndLectureView> professors = professorRepo.findProfessorsWithLecturers();
// Then
softly.assertThat(professors).as("Matches Full Name")
.extracting("fullName").contains(format("%s %s", testProfessor.getFirstName(), testProfessor.getLastName()));
softly.assertThat(professors).as("Contains First Lecture Name")
.extracting("lectureList").asString().contains(lectureAlpha.getTitle());
softly.assertThat(professors).as("Contains Second Lecture Name")
.extracting("lectureList").asString().contains(lectureBeta.getTitle());
}
We can see that we are able to aggregate the related lecturers when we ask for the ProfessorView response object. The only way this test passes is if we have that application.yml file change in place. If we rely on the out of the box dialect, we get an immediate IllegalStateException when we try to stand up the Spring Boot Application.
It's ugly.
BUT we did it! We can use our custom dialect and get the use of any of the custom functions that we declare. We can get fancy there... or we can just use some of the DBMS specific functions that aren't originally available.
Summary
Truth time.
I'm writing this all out because I don't want to forget how to do this in the future. One of the other devs was a bit upset when I showed him how small the code change was in order to get around the @NativeQuery that he had to write.
Always remember that you can extend the "black magic" that Spring uses. The Spring team has done tons of work in order to offer you 80% of the functionality that you'll need and it's up to you to extend their work and fulfill that last 20%.
Thanks for reading!
Full source code available @ GitHub.
Oldest comments (0)