How can I run this SQL statement with SpringBoot data JPA?-CodePudding

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:

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.

CodePudding user response：

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.