How to convert Select with Inner Join query in Spring Query

Question

This is my SQL statement which works properly:

SELECT cedula FROM paciente INNER JOIN titular ON paciente.id = titular.paciente_id WHERE cedula = '19163676';

returns cedula field from paciente table when the ids match

I've read this post: How To Define a JPA Repository Query with a Join

But in that post the relationship is One-to-One.

In my case, the relationship is Many-to-One where the father model is paciente and the child model is titular

This is my Paciente domain:

Paciente.java

@Id
@Column(name="pacienteId")
@SequenceGenerator(name="paciente_id_seq",
    sequenceName="paciente_id_seq",
    allocationSize=1)
@GeneratedValue(strategy = GenerationType.SEQUENCE,
    generator="paciente_id_seq")
private Long id;

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

And this is my Titular domain with the relationship:

Titular.java

 @Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@ManyToOne
@JoinColumn(name="pacienteId")
private Paciente paciente;

And this is my PacienteRepository.java which will contain the query:

public interface PacienteRepository extends JpaRepository<Paciente,Long> {
List<Paciente> findByNombreContainsIgnoreCase(String nombre);
List<Paciente> findByNombreLikeIgnoreCaseAndApellidoLikeIgnoreCase(String nombre, String apellido);
List<Paciente> findByCedula(String cedula);
Page<Paciente> findByCedulaStartsWith(String cedula, Pageable pageable);

}

How can I do it?

Try `SELECT DISTINCT...` if you are trying to only return 1 unique value. — Jacob H, Jul 06 '17 at 15:07
The issue is related to Java/Spring and its query methods, in SQL works properly by this way. — Ulises Vargas De Sousa, Jul 06 '17 at 15:11
Find only cedulas (ID cards) by paciente (patient) which match with Titular (account's owner) — Ulises Vargas De Sousa, Jul 06 '17 at 15:28
I tried this @Query: `@Query("select p.cedula from Paciente p inner join Titular.paciente_id_seq p where p.cedula = :cedula") Page findByCedulaEndsWith(@RequestParam("cedula") String cedula, Pageable pageable);` — Ulises Vargas De Sousa, Jul 06 '17 at 16:13
But throws me the following error: `Error creating bean with name 'pacienteResource': Unsatisfied dependency expressed through field 'pacienteRepository'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'pacienteRepository': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract org.springframework.data.domain.Page com.setech.segminedu.repository.PacienteRepository.findByCedulaEndsWith(java.lang.String,org.springframework.data.domain.Pageable)!` — Ulises Vargas De Sousa, Jul 06 '17 at 16:15
Why are you using `@ReqeustParam`? Use `@Param` from `Spring-Data-JPA`. `@RequestParam` applies to `Spring-MVC` to get request parameters. — Rana_S, Jul 06 '17 at 16:44

Ulises Vargas De Sousa · Accepted Answer · 2017-07-07T20:37:42.833

SOLVED (2017-07-06):

JPQL isn't the same that SQL. JPQL can return the desired object through Entities (even, inside of a different Java repository Entity), no needs Inner Join command.

i.e.:

PacienteRepository.java:

@Query("select titular.paciente from Titular titular where titular.paciente.cedula in ?1")
Page<Paciente> findByCedulaEndsWith(String cedula, Pageable pageable);

That returns the patient from Titular table (owners), when its 'cedula' (ID card, DNI, etc) match with 'cedula' from Paciente (patients) table. Else, returns an empty array.

How to convert Select with Inner Join query in Spring Query

1 Answers1