How to do searching with parameters using Repository Hibernate and Pageable with Java Spring?

I have just learnt how to do Pageable with Repository Java ( I have a BBDD MySQL) . I basically use DAO with my model (pregunta) and I cast my model to my DTO (preguntaDTO).

public Page<PreguntaDTO> getAllPreguntasPorPagina(Pageable pageable){

  return preguntaRepository.findAll(pageable).map(PreguntaDTO::fromEntity);

}	

My controller receives the parameters for the pagination.

@GetMapping("/listadoPreguntasPaginadas")

public ResponseEntity<Page<PreguntaDTO>> listadoPreguntasPaginadas(

  @RequestParam(defaultValue = "0") int page,

  @RequestParam(defaultValue = "10") int size,

  @RequestParam(defaultValue = "pregunta") String order,

  @RequestParam(defaultValue = "true") boolean asc

  ) {

  Page<PreguntaDTO> preguntaDTOpaginada= preguntaService.listadoPreguntasPageables(PageRequest.of(page, size, Sort.by(order)));

  return new ResponseEntity<Page<PreguntaDTO>>(preguntaDTOpaginada, HttpStatus.OK);

}

But now, I need To use parameters in my JQUERY ( text and id) . I´ll try to explain it better.
My model is:

@Entity

@Table (name="preguntas")

@EntityListeners(AuditingEntityListener.class) // sin esto no genera fecha automatica

@Getter

@Setter

public class Pregunta implements Serializable  {

  private static final long serialVersionUID = 5191280932150590610L;

	

  @Id // definimos clave primaria

  @GeneratedValue(strategy=GenerationType.AUTO)  // que se genere de forma automatica y crezca

  private Long id;

	

  @Temporal(TemporalType.TIMESTAMP)

  @LastModifiedDate    

  private  Date createAt;	

	

  @NotBlank // que no este campo en blanco, validar documento

  @NotNull(message = "Nombre no puede ser nulo")

  private String pregunta;

  private Boolean activa=true;

  private Long deEnfermedadById;//ID de Enfermedad 

  private Long createdById;//ID de usuario 

  private Boolean denunciado=false;	

  private String aux="Campo auxiliar sin usar";

}

My search, in addition to be pageable, must return all my questions ( pregunta )that have:

• in field deEnfermedadById with an id (deEnfermedadById :number) and,
• of text ( cadena: string) in field pregunta ( type, pregunta :string)

I know how to look for that kind of list with Entity Management, BUT here I don`t know tu use pageable.

above you can see how I did it.

public List<Pregunta> listadoPreguntasByIdEnfermedad(Long idEnf, String cadena) {

  List<Pregunta> lista = new ArrayList<>();

  String sql = "select * from sanihelp.preguntas where "

							+ "de_enfermedad_by_id = ?";

  if (cadena != null) {

	sql = sql.concat(" and pregunta like ? ");

  };

  try {

	Query query = em.createNativeQuery(sql);

	int indice=1;

	query.setParameter(indice , idEnf);

	if (cadena != null) {

	  query.setParameter(++indice, "%" + cadena + "%");

	}

	for (Object o : query.getResultList()) {

	  Object[] objeto = (Object[]) o;

	  Pregunta modelo = new Pregunta();	

	  modelo.setId(((BigInteger) objeto[0]).longValue());											    modelo.setActiva(Boolean.getBoolean(String.valueOf(objeto[1])));

	  modelo.setAux(String.valueOf(objeto[2]));

	  modelo.setCreateAt((java.util.Date)objeto[3]);

	  modelo.setCreatedById(((BigInteger) objeto[4]).longValue());

	  modelo.setDeEnfermedadById(((BigInteger) objeto[5]).longValue());						modelo.setDenunciado(Boolean.getBoolean(String.valueOf(objeto[6])));	

	  modelo.setPregunta(String.valueOf(objeto[7]));		

	  lista.add(modelo);

	}

  } catch (Exception e) {

	System.out.println("excepcion lanzada" + e);

  }

  return lista;

}

How you can see, I am a bit Stuck. With Entity Management I cannot do pagination and with Repository Java I don´t know how to add to my pageable method some parameters ( id and text )

Thank you for your help.

Above some extra information of the interface .

package com.uned.project.sanitaUned.repository;



import org.springframework.data.jpa.repository.JpaRepository;

import org.springframework.stereotype.Repository;



import com.uned.project.sanitaUned.model.Pregunta;



@Repository

public interface PreguntaRepository extends JpaRepository<Pregunta, Long> {



}

that extends of :

 */

@NoRepositoryBean

public interface JpaRepository<T, ID> extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T> {

and that extends of pageable

@NoRepositoryBean

public interface PagingAndSortingRepository<T, ID> extends CrudRepository<T, ID> {



	/**

	 * Returns all entities sorted by the given options.

	 *

	 * @param sort

	 * @return all entities sorted by the given options

	 */

	Iterable<T> findAll(Sort sort);



	/**

	 * Returns a {@link Page} of entities meeting the paging restriction provided in the {@code Pageable} object.

	 *

	 * @param pageable

	 * @return a page of entities

	 */

	Page<T> findAll(Pageable pageable);

}

I tried to do :

@Repository

public interface PreguntaRepository extends JpaRepository<Pregunta, Long> {

	

/*  */ 

	

	 @Query ("select * from sanihelp.preguntas where de_enfermedad_by_id = :de_enfermedad_by_id") 

	 Page <Pregunta> findAllWithFields(Pageable pageable , @Param ("de_enfermedad_by_id")  long de_enfermedad_by_id );

	

}

But it didn't work, it failed in the server when I started with the next error.

Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.

2020-04-07 18:16:44.091 ERROR 8424 --- [  restartedMain] o.s.boot.SpringApplication               : Application run failed



org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'preguntaController': Unsatisfied dependency expressed through field 'preguntaService'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'preguntaService': Unsatisfied dependency expressed through field 'preguntaDAO'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'preguntaDAO': Unsatisfied dependency expressed through field 'preguntaRepository'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'preguntaRepository': 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.uned.project.sanitaUned.repository.PreguntaRepository.findAllWithFields(org.springframework.data.domain.Pageable,long)!

	at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:596) ~[spring-beans-5.1.9.RELEASE.jar:5.1.9.RELEASE]

	at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:90) ~[spring-beans-5.1.9.RELEASE.jar:5.1.9.RELEASE]

	

my friend Agustín Lidon Martinez give me the answer. Thank you.

@Repository
public interface PreguntaRepository extends JpaRepository<Pregunta, Long> {
	
	 @Query ("SELECT p FROM Pregunta p WHERE p.deEnfermedadById = :de_enfermedad_by_id") 
	 Page <Pregunta> findAllWithFieldsContaining ( Pageable pageable, @Param ("de_enfermedad_by_id")   Long   de_enfermedad_by_id  );  
}

I didn`t know that when you use HQL is not the same as SQL . You must use the model Pregunta and not the name of the table sanihelp.preguntas how I was doing all the time . Thank you.

Working ok:

1. my controller.

	@GetMapping("/listadoPreguntasPaginadasConIdEnfermedad")
	public ResponseEntity<Page<PreguntaDTO>> listadoPreguntasPaginadasConIdEnfermedad(
			@RequestParam(defaultValue = "0") int page,
            @RequestParam(defaultValue = "10") int size,
            @RequestParam(defaultValue = "pregunta") String order,
            @RequestParam(defaultValue = "true") boolean asc,
            @RequestParam(defaultValue = "") Long idEnfermedad
			) {
		    Page<PreguntaDTO> preguntaDTOpaginada= preguntaService.listadoPreguntasPaginadasConIdEnfermedad(
		    	                                   PageRequest.of(page, size, Sort.by(order)), idEnfermedad);
			
		    return new ResponseEntity<Page<PreguntaDTO>>(preguntaDTOpaginada, HttpStatus.OK);
	}

my service :

	@Override
	public Page<PreguntaDTO> listadoPreguntasPaginadasConIdEnfermedad (Pageable pageable, Long idEnfe) {
		return preguntaDAO.getAllPreguntasPorPaginaYCampos(pageable, idEnfe);
	}

mi DAO:

	/* obtener lista de todas las preguntas tabla PAGEABLE con mapeo incluido de page  y campos 		*/
				public Page<PreguntaDTO> getAllPreguntasPorPaginaYCampos (Pageable pageable, Long enfermedad){
					return preguntaRepository.findAllWithFieldsContaining(pageable, enfermedad).map(PreguntaDTO::fromEntity);
				}	

you have in my question more classes you need. Thanks again

I have used in my PreguntaRepository this:

@Query ("SELECT p FROM Pregunta p WHERE p.deEnfermedadById = :de_enfermedad_by_id and p.pregunta LIKE %:texto%") 
	 Page <Pregunta> findAllWithFieldsContaining ( Pageable pageable,  Long   de_enfermedad_by_id,  String texto  );  
}

without
@Param ("de_enfermedad_by_id") @Param ("texto") and works as well.