In my previous post, Simpler JPA with Spring Data-JPA I showed how to configure a repository interface which Spring Data will implement for us. Let me show how easy it is to add queries.
Modify the Repository Interface
Simply define the method interface and use the @Query annotation to define the OQL.
package com.gordondickens.myapp.repository;
import org.springframework.data.repository.CrudRepository;
import com.gordondickens.myapp.entity.Product;
public interface ProductRepository
extends CrudRepository<Product, Long> {
@Query("FROM Product")
List<Product> findAllProducts();
}
Modifying Queries
- Parameters can be marked by position with ?1, ?2, etc.
- Parameters can be marked by name with :paramName and annotation
@Param("paramName") - Modifying method signature can only return
void,Integerorint - Updating queries MUST be transactional, mark with
@Transactional - Spring Data will drop all non-flushed changes pending in the
EntityManager, change with@Modifying(clearAutomatically=false)
package com.gordondickens.myapp.repository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;
@Transactional(readOnly=true)
public interface ProductRepository
extends CrudRepository<Product, Long> {
@Query("FROM Product")
List<Product> findAllProducts();
// Example with positional params
@Modifying
@Transactional(readOnly=false)
@Query("update Product p set p.description = ?2 where p.productId = ?1")
Integer setNewDescriptionForProduct(String productId, String description);
// Example with named params
@Modifying
@Query("update Product p set p.description = :description where p.productId = :productId")
Integer setNewDescriptionForProduct(@Param("productId") String productId,
@Param("description") String description);
}
Automatic Query Generation
The <jpa:repositories/> has an option query-lookup-strategy which defaults to “create-if-not-found” which will generate queries for us.
The default is “create-if-not-found“. Other options are “create” or “use-declared-query“.
<jpa:repositories base-package="com.gordondickens.myapp.repository" query-lookup-strategy="create-if-not-found"/>
To create a find method that effectively does @Query("FROM Product p where p.productId = :productId")
public interface ProductRepository extends CrudRepository<Product, Long> {
...
@Query
Product findByProductId(String productId);
...
Summary
We see how simple interface additions provide custom methods based on query language. We can query either by positional or named parameters.
Pingback: Technophile Blog » Simpler JPA with Spring Data-JPA
It reminds me to the EoD SQL project — was originally planned to be a part of the standard JDK. Same wizardry, same overhead, nice… but you can do _almost_ the same w spring JdbcTemplate classes, even with BeanPropertyRowMapper, that manages the mapping via bean utils.
The problem with this wizardry comes when you need more complex or dynamic queries, reports, etc, or perhaps when you need performance
Pingback: Revolução Java - Artigos, Tutoriais, Livros e Dicas atualizadas sobre o Mundo Java!
Hello,
i’m new to JPA and can’t figure how to use a custom repository interface annotated with Query. In your case e.g. ProductRepository.findAllProducts.
How do you call this method from your service?
Don’t you need implementing class?
Thanks in advance.
Regards, V
You do not need an implementation as it is automatically generated by Spring Data JPA. As in the examples, autowire the Repository interface and the methods will be available.
Hi All,
We are about to start a new project, and have never been involved in spring-data-jpa. We are in considering the available technologies including JpaDaoSupport from spring as well as hibernate dao support. then we found about spring-data-jpa. While learning and understanding we realized that spring-data-spa provides on the fly query generation, which provides that you need not to write queries. Which i found very good quality of job done by spring community.
But then sudden we got stuck into some problems related to query migration. We are less able to formulate the below types of queries. our purpose is if we are using spring-data-jpa then we should write less queries by using @Query or @NamedQuery annotation. Is spring data jpa lacks of these types of queries or we have analyzed and understood less.
Sample: How do we could handle below queries in spring-data-jpa
SELECT DISTINCT colA
FROM TableA
WHERE colB = ‘XYZ’
ORDER BY colC
SELECT A.colA, B.colB
FROM TableA A, TableB B
WHERE A.colC = B.colC
(In above query it is given — there is no foreign key relation shared by TableA and TableB)
Thanks in adv.
In addition, I got stuck for below types of example.
@ how we can handle table synonyms in spring-data-jpa (for example, i have a table A, and for this i have defined a view name VA then i assigned a synonym to this view as SYN_VA), how would i handle this scenario.
@ how can we handle joined queries in spring-data-jpa where the joining tables don’t have any has-a relationship (i.e. no one-to-many kind of relation exists between them)
please answer, as we have to finalized our approach ASAP on using this technology.
Tyring to execute below query but giving errors as
2012-05-14 06:44:24,324 [main] ERROR org.hibernate.util.JDBCExceptionReporter – Invalid column name
@namedNativeQuery(name=”myQuery”, query=”select TO_CHAR(a, ‘DDMONYYYY’), b, c from tableX where d=? and e >= ? and e<=?", resultClass=TableX.class)
In above query: "a, d and e" are SQL Date columns
Don't know but facing a lots of problem while using spring-data-jpa. we are coming to know it is quite easy with using direct to entitymanager class rather than going with JpaRepository style. But anyway looking for resolutions for this and for the above problems. Please help.
Although I have not tried this specifically, remember that JPA is simply a SQL generator. You should simply be able to use the synonym as you would a normal table.
For the second question, there has to be some relationship to join the tables or you get a cartesian product. The database tables do not have to have a relationship, you can create your own query if you have joining column(s).
I recommend turning on logging for your ORM Provider (Hibernate, Eclipselink, etc) – I have other blogs for that. Also turn on showSql. That usually sheds some light on what the ORM tool is generating.
See my ORM configuration examples here: https://github.com/gordonad/core-spring-demos/tree/master/demos/orms
For sane logging, I HIGHLY recommend SLF4j w/ Logback – used in examples above.
Thanks Gordan.
But due to some limitations we have dropped the idea for using JPARepository. We are now directly using entitymanager.
Hi All,
I have situation where I need to build a “select distinct a.address from Person a” (where address is a Address entity inside the Person) kind of query. I am using Specification to dynamically build my where clause and using the findAll(Specification) function to get the result. The problem is I can not use specification to build my select clause and hence can not use findAll(Spcification) function.
What would be the best way to do something like this?
Is there a way to call Stored Procedure using Spring DATA JPA?
@nirav , you can use createnativequery
for eg
Query query =
em.createNativeQuery(“call getname(:name)”, Employee.class);
query.setParameter(“name”, name);
List results = query.getResultList();
Regards
Anshul