How to use Custom Query with spring boot data jpa

Question

I am trying to create a web application using spring boot. My persistent data is stored on PostgreSQL database. However, I need to use elasticsearch for Full Text Search. (I know there is postgres Full text search but I need elastic). For this purpose I'm using zombodb. This is an extension of Postgres that allows postgres and ElasticSearch to work together. But it has its own sql syntax. Example of a full text search:

SELECT * FROM products WHERE products ==> 'sports or box'

When I use directly this query, it works. However, when I want to use native query with parameter like

SELECT * FROM products WHERE products ==> :searched

it gives "operator does not exist: products ==> character varying" error. İs there any solutions?

score 0 · Answer 1 · answered Jan 14 '19 at 13:41

0

Well, in our project we also use an extension of postgresql (POSTGIS). In our case we used native query of JPA directly, and even postgis specific methods works perfectly. So I suggest you to use native query support.

Here is an example:

public interface ProductRepository extends JpaRepository<Product,Long>{
    @Query(value = "SELECT * FROM products WHERE products ==> 'sports or box'", nativeQuery=true)
    List<Product> someFancyMethodName();
}

answered Jan 14 '19 at 13:41

oldborn

86
2

Thanks for the answer. Unfortunately I tried this but did not work. I guess even native queries are kind of parsed and interpreted, so it gives something like sql syntax error (I don't remember the whole error, when I get to home, I'll edit here) – hayridurmaz Jan 14 '19 at 13:58

score 0 · Accepted Answer · answered Jan 18 '19 at 22:08

0

@Query annotation did not work for me. I was able to implement this soulution, working quite fine.

answered Jan 18 '19 at 22:08

hayridurmaz

608
8
24

How to use Custom Query with spring boot data jpa

2 Answers2