how can i write a native query in spring boot whith join clause in PostgreSQL

user_14657792 0
Viewed 403

I write a native query in spring boot with join clause in PostgreSQL, and here is the query:

@Query(value = "SELECT c.name AS \"c.name\"\n" +
"FROM city c ,state s\n" +
"WHERE c.state_id = s.id \n" +
"AND s.name like :name",nativeQuery = true)
List<City> findCityByStateParamsNative(@Param("name") String name);

but i have this issue: Request processing failed; nested exception is

org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [SELECT c.name AS "c.name" FROM city c ,state s WHERE c.state_id = s.id AND s.name like ?]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query] with root cause
org.postgresql.util.PSQLException: Le nom de colonne id n'a pas été trouvé dans ce ResultSet.

who can help me to fix the issue

spring-boot postgresql spring database java
2 Answers

Try this code:

@Query(value = "SELECT c.name " +
"FROM city c JOIN state s " +
"ON c.state_id = s.id " +
"WHERE s.name = ?1", nativeQuery = true)
List<String> findCityByStateParamsNative(String name);

If the problem is not solved, then write in the comment under the answer with a new error.

user_170106470

Try this code:

@Query(value = "SELECT c.* FROM city c INNER JOIN state s ON c.state_id = s.id WHERE s.name = :name ", nativeQuery = true);
List<City> findCityByStateParamsNative(String name);
user_69027390
Related