I want to use spring data repository interface to execute native queries - I think this way is the simplest because of low complexity.
But when extending interface ex. CrudRepository<T, ID> I need to write T - my entity, which is not available.
My native queries does not return any concrete entity, so what is the best way to create spring repository without entity?
CrudRepository or JpaRepository were not designed to work without an <Entity,ID> pair.
You are better off creating a custom repo, inject EntityManager and query from there:
@Repository
public class CustomNativeRepositoryImpl implements CustomNativeRepository {
@Autowired
private EntityManager entityManager;
@Override
public Object runNativeQuery() {
entityManager.createNativeQuery("myNativeQuery")
.getSingleResult();
}
}
CustomNativeRepository and how do you define it?
Currently there is no functionality in JPA to create repositories with only native or JPQL/HQL queries (using @Query notation). To get around this, you can create a dummy object to insert into the extension interface like below:
@Entity
public class RootEntity {
@Id
private Integer id;
}
@Repository
public interface MyRepository extends JpaRepository<RootEntity, Integer> {
}
This works for us. See the entity manager
https://www.baeldung.com/hibernate-entitymanager
@Repository
public class MyRepository {
@PersistenceContext
EntityManager entityManager;
public void doSomeQuery(){
Query query = entityManager.createNativeQuery("SELECT foo FROM bar");
query.getResultsList()
...
}
}
@Repository is not needed. @Component will be enaugh @Akankshit_98
You can just annotate your implementation with @Repository, and get an instance of EntityManager.
public interface ProductFilterRepository {
Page<Product> filter(FilterTO filter, Pageable pageable);
}
@Repository
@AllArgsConstructor
public class ProductFilterRepositoryImpl implements ProductFilterRepository {
private final EntityManager em;
@Override
public Page<Product> filter(FilterTO filter, Pageable pageable) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Product> cq = cb.createQuery(Product.class);
Root<Product> root = cq.from(Product.class);
List<Predicate> predicates = new ArrayList<>();
if (filter.getPriceMin() != null) {
predicates.add(cb.ge(root.get("price"), filter.getPriceMin()));
}
if (filter.getPriceMax() != null) {
predicates.add(cb.le(root.get("price"), filter.getPriceMax()));
}
if (filter.getBrands() != null && !filter.getBrands().isEmpty()) {
predicates.add(root.get("brand").in(filter.getBrands()));
}
if (filter.getCategories() != null && !filter.getCategories().isEmpty()) {
predicates.add(root.get("category").in(filter.getCategories()));
}
cq.where(predicates.toArray(new Predicate[0]));
TypedQuery<Product> tq = em.createQuery(cq);
tq.setMaxResults(pageable.getPageSize());
tq.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
CriteriaQuery<Long> countCq = cb.createQuery(Long.class);
countCq.select(cb.count(countCq.from(Product.class)));
countCq.where(predicates.toArray(new Predicate[0]));
TypedQuery<Long> countTq = em.createQuery(countCq);
Long count = countTq.getSingleResult();
return new PageImpl<>(tq.getResultList(), pageable, count);
}
}
I think that using JdbcTemplate can be considered as an alternative when you do not have a concrete entity class for the resultset of a native query. Querying data using JdbcTemplate requires a POJO class for the resultset and a mapper implementing the RowMapper interface for the POJO class.
If you are using JPA you need Entities. As previous answers you can create NativeQueries or use Criteria API directly from EntityManager.
Some documentation about custom reports and common repo behaviour:
If you want to use JPA without implementing your own Entity Manager: Even if your native query does not return a concrete entity, Create a dummy entity. Use that here: CrudRepository<DummyEntity, ID> Then use
@Query("<query here>", nativeQuery=true)
CustomInterfaceType methodName()
in your repository interface.
You also need to create an interface with getter methods for the columns you are selecting in your query. This order should match with what is there in the query.
