Query interface in java

Аннотация @Query

Мы рассматривали запросы, генерируемые из имени метода. Так удобно писать простые запросы. Но если в запросе более 1-2 параметров или несколько join, то удобнее использовать аннотацию @Query.
Для этого в репозитории создаем метод, аннотированный @Query, и внутри аннотации прописываем запрос — можно как JPQL, так и native SQL.

Использование @Query

public interface PostRepository extends JpaRepository  < @Transactional(readOnly = true) @Query("select p from Post p where p.user.id=:id and p.title like :title") ListfindMySuperPosts(String title, long id); >

Запрос выбирает посты данного пользователя по заголовку.

Посты и пользователи находятся в отношении @ManyToOne. Класс Post:

@Entity @Data @NoArgsConstructor public class Post
@Data @Entity @NoArgsConstructor public class User

Native Query

Такой же запрос можно написать на SQL, указав nativeQuery = true:

@Transactional(readOnly = true) @Query(nativeQuery = true, value = "select * from post p where p.user_id=:id and p.title like :title") List findMySuperPostsNative(String title, long id);

Pagination и Sorting

Можно получать результат постранично, указав в качестве параметра Pagable:

@Transactional(readOnly = true) @Query("select p from Post p where p.title like :title") List findSuperPosts(String title, Pageable pageable);

Создавать запрос страницы можно так:

Pageable pageable= PageRequest.of(0, 2, Sort.by("title"));

Выше приведен запрос первой страницы (страницы нумеруются с 0), при этом на странице должно быть два элемента. Задано упорядочивание выборки по title. Подробнее о постраничном выводе и сортировке тут.

select post0_.id as id1_0_, post0_.text as text2_0_, post0_.title as title3_0_, post0_.user_id as user_id4_0_ from post post0_ where post0_.title like ? order by post0_.title asc limit ? select user0_.id as id1_1_0_, user0_.email as email2_1_0_, user0_.locked as locked3_1_0_, user0_.nickname as nickname4_1_0_, user0_.password as password5_1_0_, user0_.role as role6_1_0_ from user user0_ where user0_.id=?

Аннотация @Modifying

С помощью @Query можно не только читать, но и обновлять базу. Но в таких запросах необходима дополнительная к @Query аннотация @Modifying.

update

Пример обновления заголовков всех постов пользователя:

@Modifying @Transactional @Query("update Post p set p.title=:title where p.id=:id") int setFixedPostTitleFor(String title, long id);

delete

Пример удаления всех постов пользователя:

@Modifying @Transactional @Query("delete from Post p where p.user.id=:id") int deleteInBulkByUserId(long id);

Стоит отметить, что так посты удаляются пакетно, поскольку генерируется один SQL :

delete from post where user_id=?

Если же использовать генерируемый по имени метод, то посты удаляются по одному:

@Transactional int deleteByUserId(long id);

SQL отправляется свой для каждого поста:

Hibernate: delete from post where delete from post where delete from post where >Обратите внимание, что методы мы аннотировали @Transactional, поскольку писали их сами. (Если бы использовали стандартные методы репозитория, унаследованные от SimpleJpaRepository, то они уже @Transactional.)

Все методы должны быть в рамках транзакции, другое дело что @Transactional может быть на уровне сервиса или теста. Но в данном случае тест у нас не @Transactional, класс аннотирован @SpringBootTest, а не @DataJpaTest (внутри которого есть @Transactional). Это потому, что @DataJpaTest откатывает транзакции и некоторые SQL не логируются (возможно, потому что не выполняются?). А хотелось бы видеть все сгенерированные SQL.

@SpringBootTest public class PostRepositoryTest < @Autowired private PostRepository postRepository; @DirtiesContext @DisplayName("посты должны удаляться по user.id единым запросом в @Query") @Test void titlesShouldBeDeletedByIdInBulk() < int n = postRepository.deleteInBulkByUserId(1l); Assertions.assertEquals(3, n); >. >

А для того, чтобы подтвержденные транзакции не портили базу после каждого теста, каждый тест аннотирован @DirtiesContext — это пересоздает H2 базу. Не оптимально, зато наглядная консоль с выводом всех SQL-запросов.

Итоги

Источник

Query interface in java

Get the parameter object corresponding to the declared positional parameter with the given position and type.

Method Detail

getResultList

getSingleResult

executeUpdate

setMaxResults

Query setMaxResults(int maxResult)

getMaxResults

The maximum number of results the query object was set to retrieve. Returns Integer.MAX_VALUE if setMaxResults was not applied to the query object.

setFirstResult

Query setFirstResult(int startPosition)

getFirstResult

The position of the first result the query object was set to retrieve. Returns 0 if setFirstResult was not applied to the query object.

setHint

Query setHint(String hintName, Object value)

Set a query property or hint. The hints elements may be used to specify query properties and hints. Properties defined by this specification must be observed by the provider. Vendor-specific hints that are not recognized by a provider must be silently ignored. Portable applications should not rely on the standard timeout hint. Depending on the database in use and the locking mechanisms used by the provider, this hint may or may not be observed.

getHints

setParameter

 Query setParameter(Parameter param, T value)

setParameter

Query setParameter(ParameterCalendar> param, Calendar value, TemporalType temporalType)

setParameter

Query setParameter(ParameterDate> param, Date value, TemporalType temporalType)

setParameter

Query setParameter(String name, Object value)

setParameter

Query setParameter(String name, Calendar value, TemporalType temporalType)

setParameter

Query setParameter(String name, Date value, TemporalType temporalType)

setParameter

Query setParameter(int position, Object value)

setParameter

Query setParameter(int position, Calendar value, TemporalType temporalType)

setParameter

Query setParameter(int position, Date value, TemporalType temporalType)

getParameters

Get the parameter objects corresponding to the declared parameters of the query. Returns empty set if the query has no parameters. This method is not required to be supported for native queries.

getParameter

Get the parameter object corresponding to the declared parameter of the given name. This method is not required to be supported for native queries.

getParameter

 Parameter getParameter(String name, Class type)

Get the parameter object corresponding to the declared parameter of the given name and type. This method is required to be supported for criteria queries only.

getParameter

Get the parameter object corresponding to the declared positional parameter with the given position. This method is not required to be supported for native queries.

getParameter

 Parameter getParameter(int position, Class type)

Get the parameter object corresponding to the declared positional parameter with the given position and type. This method is not required to be supported by the provider.

isBound

getParameterValue

getParameterValue

Object getParameterValue(String name)

getParameterValue

Object getParameterValue(int position)

setFlushMode

Query setFlushMode(FlushModeType flushMode)

Set the flush mode type to be used for the query execution. The flush mode type applies to the query regardless of the flush mode type in use for the entity manager.

getFlushMode

Get the flush mode in effect for the query execution. If a flush mode has not been set for the query object, returns the flush mode in effect for the entity manager.

setLockMode

Query setLockMode(LockModeType lockMode)

getLockMode

Get the current lock mode for the query. Returns null if a lock mode has not been set on the query object.

unwrap

Return an object of the specified type to allow access to the provider-specific API. If the provider’s query implementation does not support the specified class, the PersistenceException is thrown.

Copyright © 1996-2015, Oracle and/or its affiliates. All Rights Reserved. Use is subject to license terms.

Источник

Query interface in java

Get the parameter object corresponding to the declared positional parameter with the given position and type.

Method Detail

getResultList

getResultStream

Execute a SELECT query and return the query results as an untyped java.util.stream.Stream . By default this method delegates to getResultList().stream() , however persistence provider may choose to override this method to provide additional capabilities.

getSingleResult

executeUpdate

setMaxResults

Query setMaxResults(int maxResult)

getMaxResults

The maximum number of results the query object was set to retrieve. Returns Integer.MAX_VALUE if setMaxResults was not applied to the query object.

setFirstResult

Query setFirstResult(int startPosition)

getFirstResult

The position of the first result the query object was set to retrieve. Returns 0 if setFirstResult was not applied to the query object.

setHint

Query setHint(String hintName, Object value)

Set a query property or hint. The hints elements may be used to specify query properties and hints. Properties defined by this specification must be observed by the provider. Vendor-specific hints that are not recognized by a provider must be silently ignored. Portable applications should not rely on the standard timeout hint. Depending on the database in use and the locking mechanisms used by the provider, this hint may or may not be observed.

getHints

setParameter

 Query setParameter(Parameter param, T value)

setParameter

Query setParameter(ParameterCalendar> param, Calendar value, TemporalType temporalType)

setParameter

Query setParameter(ParameterDate> param, Date value, TemporalType temporalType)

setParameter

Query setParameter(String name, Object value)

setParameter

Query setParameter(String name, Calendar value, TemporalType temporalType)

setParameter

Query setParameter(String name, Date value, TemporalType temporalType)

setParameter

Query setParameter(int position, Object value)

setParameter

Query setParameter(int position, Calendar value, TemporalType temporalType)

setParameter

Query setParameter(int position, Date value, TemporalType temporalType)

getParameters

Get the parameter objects corresponding to the declared parameters of the query. Returns empty set if the query has no parameters. This method is not required to be supported for native queries.

getParameter

Get the parameter object corresponding to the declared parameter of the given name. This method is not required to be supported for native queries.

getParameter

 Parameter getParameter(String name, Class type)

Get the parameter object corresponding to the declared parameter of the given name and type. This method is required to be supported for criteria queries only.

getParameter

Get the parameter object corresponding to the declared positional parameter with the given position. This method is not required to be supported for native queries.

getParameter

 Parameter getParameter(int position, Class type)

Get the parameter object corresponding to the declared positional parameter with the given position and type. This method is not required to be supported by the provider.

isBound

getParameterValue

getParameterValue

Object getParameterValue(String name)

getParameterValue

Object getParameterValue(int position)

setFlushMode

Query setFlushMode(FlushModeType flushMode)

Set the flush mode type to be used for the query execution. The flush mode type applies to the query regardless of the flush mode type in use for the entity manager.

getFlushMode

Get the flush mode in effect for the query execution. If a flush mode has not been set for the query object, returns the flush mode in effect for the entity manager.

setLockMode

Query setLockMode(LockModeType lockMode)

getLockMode

Get the current lock mode for the query. Returns null if a lock mode has not been set on the query object.

unwrap

Return an object of the specified type to allow access to the provider-specific API. If the provider’s query implementation does not support the specified class, the PersistenceException is thrown.

Copyright © 1996-2017, Oracle and/or its affiliates. All Rights Reserved. Use is subject to license terms.

Источник