I got an error when trying to do a request with JPA.

I have specified in my class entity, the schema where the table is :

@Data 
@NoArgsConstructor 
@AllArgsConstructor 
@Entity
@Table(schema = "dwp_schema")
public class Corridor {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id_corridor;
    private Integer id_floor;
    private String orientation;

}

And when I am doing a specific request in my repository :

public interface CorridorRepository extends JpaRepository<Corridor, Integer> {
    @Query(value = "select * from corridor c inner join floor f on f.id_floor=c.id_floor INNER JOIN building b on f.id_building = b.id_building WHERE b.building_name=?1 AND f.floor_number=?2" ,nativeQuery = true)
    List<Corridor> getCorridorsByFloor(String building_name, int floor);
}

I have the following error in Postgres :

org.postgresql.util.PSQLException: ERROR: relation "corridor" does not exist

Does someone have an idea ?

Thank you.

CodePudding user response：

Try write schema name before table name:

    @Query(value = "select * from dwp_schema.corridor c inner join floor f on f.id_floor=c.id_floor INNER JOIN building b on f.id_building = b.id_building WHERE b.building_name=?1 AND f.floor_number=?2" ,nativeQuery = true)

CodePudding user response：

You need to specify your DB and Schema in the connection string:

jdbc:postgresql://localhost:5432/dbname?currentSchema=dwp_schema

Putting schema name right in the entity declaration would be a bad idea anyway - your DBA should be able to decide on schema names.