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

Question

I have two classes Port and 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

why do you use JOIN statement, as I check your web-service it seems that you just return all the ports in your table, if you wanted to return specific ports of a single person using JOIN could be a solution but why now you are using it? — Sobhan, Feb 23 '22 at 10:37
also when you use JOIN statement you need to define ON clause for it, which i don't see in your query — Sobhan, Feb 23 '22 at 10:41
@Sobhan I have a lot of port records but I'm only intersted in ports that exist in Person (the ports that thier ID exist in person table like a foreign key) So I searched on the internt I found that this will be possible only with JOIN statement — hakima maarouf, Feb 23 '22 at 10:46
try with this one: SELECT DISTINCT * FROM port t1 INNER JOIN person t2 ON t1.ID = t2.puerto_id where t2.puerto_id is not null — Sobhan, Feb 23 '22 at 10:55
this one will work for your requirements, without JOIN: SELECT DISTINCT * FROM port t1 where t2.puerto_id is not null — Sobhan, Feb 23 '22 at 11:01
I got this error using query without JOIN and I guess it's obvious since t2 is not linked to person table `"detail": "could not prepare statement; SQL [SELECT DISTINCT * FROM port t1 where t2.puerto_id is not null limit ?]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement",` — hakima maarouf, Feb 23 '22 at 11:04
check out these answers for using Pageable with native-query and the way of implementing it https://stackoverflow.com/questions/38349930/spring-data-and-native-query-with-pagination but i myself when want to use native query i use the database functions for pagination like below for oracle DB: SELECT DISTINCT o2.* FROM USERS u2 INNER JOIN ORDERS o2 ON u2.ID = o2.USER_ID WHERE o2.USER_ID IS NOT null OFFSET :offset ROWS FETCH NEXT :length ROWS ONLY — Sobhan, Feb 23 '22 at 11:14
@Sobhan okey i will try it but please can you see my update above it may help us finding the error — hakima maarouf, Feb 23 '22 at 11:16
i think your distinct keyword does not work because each of your records is uniqe because of the different ID --> 3001, 3002 if you just want the port data use "t1.*" instead of "*" — Sobhan, Feb 23 '22 at 11:19

score 0 · Accepted Answer · answered Feb 23 '22 at 11:31

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

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

1 Answers1