Query in spring boot with Distinct doesn't return diffrent values-CodePudding

I have two classes Port end Person. a Person can have many port so there ManyToOne relationship between the two classes. I want to return all the Port that exists in Person. here is Person class:

@Entity
@Table(name = "person")
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class Person implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequenceGenerator")
    @SequenceGenerator(name = "sequenceGenerator")
    @Column(name = "id")
    private Long id;

    @NotNull
    @Column(name = "age", nullable = false)
    private String age;

    @Column(name = "mdp")
    private String mdp;

    @ManyToOne
    @JoinColumn(name = "puerto_id")
    private Port port;
    //getters & setters

here is Port class:

@Entity
@Table(name = "port")
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class Port implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequenceGenerator")
    @SequenceGenerator(name = "sequenceGenerator")
    @Column(name = "id")
    private Long id;

    @Column(name = "city")
    private String city;

In PortRepository.java I made this Query that I call in PortResource.java

@SuppressWarnings("unused")
@Repository
public interface PortRepository extends JpaRepository<Port, Long> {

    @Query(value = "SELECT DISTINCT * FROM port t1 INNER JOIN person t2 ON t1.ID = t2.puerto_id", nativeQuery = true)
    Page<Port> findPersonsPorts(Pageable pageable);
}

//
@GetMapping("/persons/ports")
    public ResponseEntity<List<Port>> getPersonsPorts(@org.springdoc.api.annotations.ParameterObject Pageable pageable) {
        log.debug("REST request to get a page of Ports");
        Page<Port> page = portRepository.findPersonsPorts(pageable);
        HttpHeaders headers = PaginationUtil.generatePaginationHttpHeaders(ServletUriComponentsBuilder.fromCurrentRequest(), page);
        return ResponseEntity.ok().headers(headers).body(page.getContent());
    }

On postman when execute this http://localhost:8080/api/people I have the following response:

[
    {
        "id": 2902,
        "age": "88",
        "mdp": null,
        "port": null
    },
    {
        "id": 3001,
        "age": "45",
        "mdp": "mdp",
        "port": {
            "id": 2952,
            "city": "rabat"
        }
    },
    {
        "id": 3002,
        "age": "88",
        "mdp": "mdp",
        "port": {
            "id": 2952,
            "city": "rabat"
        }
    } ]

and when send this request http://localhost:8080/api/persons/ports i get duplicate values of Port

[
    {
        "id": 2952,
        "city": "rabat"
    },
    {
        "id": 2952,
        "city": "rabat"
    } ]

UPDATE:

when i execute the same query on h2 database which is : SELECT DISTINCT * FROM PORT t1 INNER JOIN PERSON t2 ON t2.puerto_id = t1.ID

I got this table, as you can see the rows are diffrent because it returns in the same table the columns of person and port table. Im not sure if this is the problem

CodePudding user response：

your DISTINCT keyword does not work because your result set is not really unique, look at your records, ID column has different data, so they are not the same and DISTINCT does not help you.

if you just want the PORT table data try to use t1.* instead of *

use this query:

SELECT DISTINCT t1.* FROM PORT t1 INNER JOIN PERSON t2 ON t2.puerto_id = t1.ID

CodePudding user response：

You can try using specific fields