Зачастую выборка из таблицы должна содержать не только колонки с данными, но и колонки с вычисляемыми (обычно с помощью функций и подзапросов) значениями. Это могут быть, например, средние, минимальные и максимальные величины. Если для таких случаев по тем или иным причинам в БД не созданы представления (view), то мы можем указать Hibernate’у усложнить запрос и выбрать дополнительные данные в выборку с помощью аннотации @Formula.
Создадим минимальное Spring Boot приложение с поддержкой JPA/Hibernate
Если в директории /src/main/resources нет файла application.properties, то создайте его там и добавьте в него следующую строчку:
|
1 |
spring.jpa.show-sql=true |
Это позволит видеть в консоли SQL выражения, которые будет создавать Hibernate, выполняю свою работу.
Затем создадим класс предметной области следующего содержания:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
@Entity public class Person { @Id @GeneratedValue private Long id; private String name; private Integer age; @org.hibernate.annotations.Formula( "(select avg(p.age) from person p)" ) private BigDecimal averageAge; //Конструкторы, геттеры и сеттеры, equals(), hashCode() и т.д. } |
В качестве параметра value в аннотацию @org.hibernate.annotations.Formula мы передаём фрагмент SQL, который соберёт данные для заполнения новой колонки average_age, которая будет сформирована только для этого запроса. Как видите, в производных полях можно использовать полноценные подзапросы SELECT.
Главное, что нужно учитывать при составлении подобных подзапросов, это то, что они пишутся на диалекте конкретной СУБД. Есть возможность использовать любые стоковые функции СУБД, но всё это может нанести ущерб переносимости.
Создадим репозиторий:
|
1 2 3 |
@Repository public interface PersonRepository extends JpaRepository<Person, Long> { } |
Создадим тест следующего содержания:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
@SpringBootTest class TestApplicationTests { @Autowired PersonRepository personRepository; @Test void formulaeTest() { personRepository.save(new Person("Vladimir", 39)); personRepository.save(new Person("Irina", 30)); List<Person> tableData = personRepository.findAll(); assertEquals(tableData.get(0).getAverageAge(), BigDecimal.valueOf(34.5)); } } |
После запуска тест должен быть пройден. В случае, если тест падает, возможно стоит поэксперементировать с возрастами из-за возможных неточностей арифметики чисел с плавающей точкой (вероятность невелика, но всякое бывает).
В консоли можно будет увидеть следующий вывод работы Hibernate (может быть перемешан с выводом других систем программы):
|
1 2 3 4 5 |
Hibernate: select next value for person_seq Hibernate: insert into person (age, name, id) values (?, ?, ?) Hibernate: select next value for person_seq Hibernate: insert into person (age, name, id) values (?, ?, ?) Hibernate: select p1_0.id,p1_0.age,(select avg(p.age) from person p),p1_0.name from person p1_0 |
В первых четырёх строках Hibernate запрашивает сиквенсы и делает вставки двух строк в таблицу. А в последней пятой строчке делает выборку. Причём третьей колонкой запрашивает наше производное значение.
Таким образом можно запрашивать различные производные значения. Но обычно стоит всё-таки держать в уме проблему переносимости и обходится только стандартными функциями.