如何在spring data JPA中编写动态本机SQL查询？

Answer 1

您需要执行customRepository并添加带有本机查询的方法。

我是这样做：

>

创建自定义存储库：

public interface OcorrenciaRepositoryCustom {
   List<Object[]> getStrings(List<String> valores);
}

public class OcorrenciaRepositoryImpl implements OcorrenciaRepositoryCustom {
    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public List<Object[]> getStrings(List<String> strings) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT count(o.id) FROM soebm.ocorrencia o WHERE 1=1 ");

        if(strings.get(0)!=null && StringUtils.isNotEmpty(strings.get(0))) {
            sb.append(" AND to_char(o.date, 'YYYY-MM-DD') >= :dataInicio ");
        }

        Query query = entityManager.createNativeQuery(sb.toString());

        if(strings.get(0)!=null && StringUtils.isNotEmpty(strings.get(0).toString())) {
            query.setParameter("dataInicio", strings.get(0));
        }
        return query.getResultList();
    }
}

public interface OcorrenciaRepository extends JpaRepository<Ocorrencia, Long>, OcorrenciaRepositoryCustom {
    Ocorrencia findByPosto(Posto posto);
}

@Autowired
private OcorrenciaRepository repository;

public List<Object[]> findOcorrenciaCustom(String str) {
    List<String> strings = new ArrayList<String>() {{add(dataInicio);}};
    return repository.getStrings(strings);
}