Adding Queries to Spring Data-JPA

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, Integer or int
  • 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.

About Gordon

Technology enthusiast primarily focused on Java and Open Source projects. Spring Certified Professional and Trainer. http://twitter.com/gdickens http://linkedin.com/in/gordondickens http://github.com/gordonad
This entry was posted in Spring. Bookmark the permalink.

14 Responses to Adding Queries to Spring Data-JPA

  1. Pingback: Technophile Blog » Simpler JPA with Spring Data-JPA

  2. hizsnyaig says:

    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

  3. Pingback: Revolução Java - Artigos, Tutoriais, Livros e Dicas atualizadas sobre o Mundo Java!

  4. Vikee says:

    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

  5. Gordon says:

    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.

  6. Jaggs says:

    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.

  7. Jaggs says:

    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.

  8. Jaggs says:

    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.

  9. Gordon says:

    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).

  10. Gordon says:

    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.

  11. Jaggs says:

    Thanks Gordan.
    But due to some limitations we have dropped the idea for using JPARepository. We are now directly using entitymanager.

  12. Nirav Shah says:

    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?

  13. Nirav Shah says:

    Is there a way to call Stored Procedure using Spring DATA JPA?

  14. Anshul says:

    @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

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>