I need the user id, user name, asesor nombre, asesor telefono, asesor correo and the tipo asesor but the json response repeat data.

this is the sql query in the spring reposotiry.

'''

@Query( value = "SELECT U.id as userid, u.name as cliente,"  
"A.nombre, A.correo, A.telefono, TA.tipo as asesoria "  
"from tc_users U, tc_usuario_asesores UA , "  
"tc_asesor A,tc_tip_asesor TA "  
"where U.id = UA.userid "  
"and UA.asesorid= A.id "  
"and A.tipo_asesor = TA.id",
 nativeQuery = true)

'''

and this query in sql workbench return this data

query in sql workbench

but the json response return this data

Json response

and dont know because this haappend, a need help with this, sorry for my bad inglish.

CodePudding user response：

Your query returns a cartesian product because of the join.

You can add DISTINCT to get unique rows.

@Query( value = "SELECT DISTINCT U.id as userid, u.name as cliente,"  
"A.nombre, A.correo, A.telefono, TA.tipo as asesoria "  
"from tc_users U, tc_usuario_asesores UA , "  
"tc_asesor A,tc_tip_asesor TA "  
"where U.id = UA.userid "  
"and UA.asesorid= A.id "  
"and A.tipo_asesor = TA.id",
 nativeQuery = true)

CodePudding user response：

Aditional Info.

Entity's Code.

@Entity
public class smartcare_entity {

    @Id
    @Getter
    @Setter
    @Column(name = "userid", nullable = false)
    private Integer userid;

    @Getter
    @Setter
    private String cliente;

    @Getter
    @Setter
    private String nombre;

    @Getter
    @Setter
    private String telefono;

    @Getter
    @Setter
    private String asesoria;

Repository's code

@Repository
public interface smartcare_repository extends CrudRepository<smartcare_entity,Integer> {

    @Query( value = "SELECT DISTINCT U.id as userid, u.name as cliente,"  
            "A.nombre, A.correo, A.telefono, TA.tipo as asesoria "  
            "from tc_users U, tc_usuario_asesores UA , "  
            "tc_asesor A,tc_tip_asesor TA "  
            "where U.id = UA.userid "  
            "and UA.asesorid= A.id "  
            "and A.tipo_asesor = TA.id",
            nativeQuery = true)
    List<smartcare_entity> SC();


}

service's code.

@Service
public class smartcare_service {

    @Autowired
    smartcare_repository smartcare_repository;

    public ArrayList<smartcare_entity> getsc(){
        return (ArrayList<smartcare_entity>) smartcare_repository.SC();
    }
}

controller's code.

@RestController
@RequestMapping("/SmartCare")
public class smartcare_controller {

    @Autowired
    com.ubik.service.smartcare_service smartcare_service;

    @GetMapping()
    public ResponseEntity<JsonResponseBody> ObtenerSM(){
        return ResponseEntity.status(HttpStatus.OK)
                .body(new JsonResponseBody(HttpStatus.OK.value(),
                        "OK", smartcare_service.getsc()));
    }

The database ERD.

enter image description here