0

I'm trying to create a data filter using SpringBoot and PostgreSQL, I'm trying to run a query to get a customer by its name or id or account id in a searchBar, running into an issue when running a native SQL statement in Spring Data JPA, I'm getting the following error:

Trace Error

The error says that Could not execute query. I've tried running the statement in different ways but still giving me the error, these are the ways I have tried:

@Query(
        value = "SELECT json_data FROM contact WHERE name = :searchText OR account_id = :searchText OR id = :searchText", 
        nativeQuery = true)

@Query(
        value = "SELECT json_data FROM contact WHERE contact.name LIKE %:searchText% OR contact.account_id LIKE %:searchText% OR contact.id LIKE %:searchText%", 
        nativeQuery = true)

@Query(
        value = "SELECT json_data FROM contact WHERE name = ?1 OR account_id = ?1 OR id = ?1", 
        nativeQuery = true)

Page<Contact> findByWord(Pageable pageable, @Param("searchText") String searchText);

I'm able to run successfully the query in PGAdmin as follows:

SELECT json_data FROM contact 
WHERE name = 'TESLA ARQUITECTURA S.A.S' 
OR account_id = '' 
OR id = ''

or:

SELECT json_data FROM contact 
WHERE name = '' 
OR contact.account_id = '' 
OR contact.id = '2'

or:

SELECT json_data FROM contact 
WHERE name = '' 
OR account_id = '1674590687S' 
OR id = ''

All of these statements are working and the idea is that if I pass as a parameter to the SQL statement it will return the information from the column json_data.

So I'm not sure if im doing something wrong when creating the query in the repository, I would appreciate so much your help on this.

andrewJames
  • 19,570
  • 8
  • 19
  • 51
Rosh343
  • 33
  • 6
  • IIRC, native queries do not support named parameters, only positional parameters. – Turing85 Jan 27 '23 at 23:38
  • Does this answer your question? [Native query with named parameter fails with "Not all named parameters have been set"](https://stackoverflow.com/questions/28829818/native-query-with-named-parameter-fails-with-not-all-named-parameters-have-been) – Turing85 Jan 27 '23 at 23:39
  • Hey @Turing85 Oh I didn't know, however I also tried with positional parameters in the following way `SELECT json_data FROM contact WHERE name = ?1 OR account_id = ?1 OR id = ?1` but still giving me exactly the same error `Resolved [org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query`. I edited the post and added the method and parameter I'm setting up in the repository layer. – Rosh343 Jan 27 '23 at 23:50
  • Have you removed the `@Param` annotation? Can you provide a [MRE], e.g. in form of a git repository? – Turing85 Jan 27 '23 at 23:59
  • Which version of spring data are you using? Also the error `column name seq_id was not found in this result set` is obviously what you should be googling. – Igor Flakiewicz Jan 28 '23 at 01:01
  • Bang, figured it out, do `select *` not `select json_data`, you are trying to map a single column into a whole Entity. – Igor Flakiewicz Jan 28 '23 at 01:04
  • You completely right! It just worked out! – Rosh343 Jan 28 '23 at 02:04

1 Answers1

2

Do select * not select json_data in your query, currently you are trying to map a single column into a whole Entity that's why it's complaining the id is missing from the result set. column name seq_id was not found in this result set was there in the error log all along.

Igor Flakiewicz
  • 694
  • 4
  • 15