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

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

CodePudding user response：

Try this code:

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

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

CodePudding user response：

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);