So, spring-data
does some extra magic that helps with complex queries. It is strange at first and you totally skip it in the docs but it is really powerful and useful.
It involves creating a custom Repository
and a custom `RepositoryImpl' and telling Spring where to find it. Here is an example:
Configuration class - point to your still-needed xml config with annotation pointing to your repositories package (it looks for *Impl
classes automatically now):
@Configuration
@EnableJpaRepositories(basePackages = {"com.examples.repositories"})
@EnableTransactionManagement
public class MyConfiguration {
}
jpa-repositories.xml - tell Spring
where to find your repositories. Also tell Spring
to look for custom repositories with the CustomImpl
file name:
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:jpa="http://www.springframework.org/schema/data/jpa"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:util="http://www.springframework.org/schema/util"
xsi:schemaLocation="http://www.springframework.org/schema/data/mongo http://www.springframework.org/schema/data/jpa/spring-jpa.xsd
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd">
<jpa:repositories base-package="com.example.repositories" repository-impl-postfix="CustomImpl" />
</beans>
MyObjectRepository
- this is where you can put annotated and unannotated query methods. Note how this repository interface extends the Custom
one:
@Transactional
public interface MyObjectRepository extends JpaRepository<MyObject, Integer>, MyObjectRepositoryCustom {
List<MyObject> findByName(String name);
@Query("select * from my_object where name = ?0 or middle_name = ?0")
List<MyObject> findByFirstNameOrMiddleName(String name);
}
MyObjectRepositoryCustom
- repository methods that are more complex and cannot be handled with a simple query or an annotation:
public interface MyObjectRepositoryCustom {
List<MyObject> findByNameWithWeirdOrdering(String name);
}
MyObjectRepositoryCustomImpl
- where you actually implement those methods with an autowired EntityManager
:
public class MyObjectRepositoryCustomImpl implements MyObjectRepositoryCustom {
@Autowired
private EntityManager entityManager;
public final List<MyObject> findByNameWithWeirdOrdering(String name) {
Query query = query(where("name").is(name));
query.sort().on("whatever", Order.ASC);
return entityManager.find(query, MyObject.class);
}
}
Amazingly, this all comes together and methods from both interfaces (and the CRUD interface, you implement) all show up when you do:
myObjectRepository.
You will see:
myObjectRepository.save()
myObjectRepository.findAll()
myObjectRepository.findByName()
myObjectRepository.findByFirstNameOrMiddleName()
myObjectRepository.findByNameWithWeirdOrdering()
It really does work. And you get one interface for querying. spring-data
really is ready for a large application. And the more queries you can push into simple or annotation only the better off you are.
All of this is documented at the Spring Data Jpa site.
Good luck.
JpaRepository
extends PagingAndSortingRepository
which in turn extends CrudRepository
.
Their main functions are:
Because of the inheritance mentioned above, JpaRepository
will have all the functions of CrudRepository
and PagingAndSortingRepository
. So if you don't need the repository to have the functions provided by JpaRepository
and PagingAndSortingRepository
, use CrudRepository
.
Best Answer
Solution for JPQL queries
This is supported for JPQL queries within the JPA specification.
Important notes
MyBean
and it is in packagecom.path.to
, the fully-qualified path to the bean will becom.path.to.MyBean
. Simply providingMyBean
will not work (unless the bean class is in the default package).new
keyword.SELECT new com.path.to.MyBean(...)
will work, whereasSELECT com.path.to.MyBean(...)
will not.@Query("SELECT ...")
, or@Query(value = "SELECT ...")
, or@Query(value = "SELECT ...", nativeQuery = false)
will work, whereas@Query(value = "SELECT ...", nativeQuery = true)
will not work. This is because native queries are passed without modifications to the JPA provider, and are executed against the underlying RDBMS as such. Sincenew
andcom.path.to.MyBean
are not valid SQL keywords, the RDBMS then throws an exception.Solution for native queries
As noted above, the
new ...
syntax is a JPA-supported mechanism and works with all JPA providers. However, if the query itself is not a JPA query, that is, it is a native query, thenew ...
syntax will not work as the query is passed on directly to the underlying RDBMS, which does not understand thenew
keyword since it is not part of the SQL standard.In situations like these, bean classes need to be replaced with Spring Data Projection interfaces.
Use the SQL
AS
keyword to map result fields to projection properties for unambiguous mapping.