0

So, are there any methods to execute native SQL queries from Repository interface?

Yes, I know about @Query annotation, but how to execute queries that can be changed in runtime? Like in JDBC executeQuery() method?

Stephan Bauer
  • 9,120
  • 5
  • 36
  • 58
Roman Shmandrovskyi
  • 883
  • 2
  • 8
  • 22
  • Take a look at [`JdbcTemplate`](https://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/jdbc/core/JdbcTemplate.html) class which is one of the primary JDBC integration points in Spring. – Karol Dowbecki Apr 11 '19 at 14:39
  • Duplicate of https://stackoverflow.com/questions/15948795/is-it-possible-to-use-raw-sql-within-a-spring-repository – Michal Apr 11 '19 at 14:42

3 Answers3

0

Implement JpaRepository and use 

@PersistenceContext
private EntityManager em;

to use the full power of Java to create query of type string and then:

 final Query emQuery = em.createNativeQuery(query);
 final List<Object[]> resultList = emQuery.getResultList();
igorepst
  • 1,230
  • 1
  • 12
  • 20
0

If you mean using Spring Data you could do something like :

@Query(value = "SELECT p from Person p where r.name = :person_name")
Optional<Person> findPersonByName(@Param("person_name") String personName);

You can use native query as well :

@Query(value = "select * from person p where r.name = :person_name")", nativeQuery = true)
enter code here
Rick Sander
  • 1
  • 1
0

You can use a Specification with your JpaRepository to make a dynamic query built at runtime.

Add JpaSpecificationExecutor to your JpaRepository interface...

@Repository
public interface MyRepo extends JpaRepository<MyEntity, Long>, JpaSpecificationExecutor {
}

Then make a class with a static method that returns a Specification....

public class MyEntitySearchSpec {
  private MyEntitySearchSpec() {
    // Remove this private constructor if need to add public non-static methods.
  }

  public static Specification<MyEntity> myEntitySearch(
      final mysearchCriteria MySearchCriteria) {
    return (root, query, cb) -> {
      List<Predicate> predicates = new ArrayList<>();

      if (mysearchCriteria.isOnlyActive()) {
        predicates.add(cb.isNull(root.get("closeDate")));
      }

      if (mysearchCriteria.getCaseNumber() != null) {
        predicates.add(cb.equal(root.get("caseNumber"),
            mysearchCriteria.getCaseNumber()));
      }

      return cb.and(predicates.toArray(new Predicate[] {}));
    };
  }
}

The you can call like this...

myRepo.findAll(myEntitySearch(mysearchCriteria));
thesb
  • 219
  • 1
  • 6