I'm using jquery Datatables together with Spring JPA. I want to create a custom Query so that my Datatable will show a list of items based on the id of a ManyToOne related object.
PS. I have obviously declared Repositories, Mapper and Entities for these DTOs, I'm just avoiding to write all the classes because I find it useless.
public class SezioniDTO {
private static final long serialVersionUID = 1L;
private long id;
private LocalDate sezDtaggiornamento;
private Comune Comune;
}
public class Comune {
private static final long serialVersionUID = 1L;
private long id;
private String comCap;
private String comCodbelfiore;
private String comCodcomune;
}
These are my classes (i use mapstruct to map the dtos from the entities). How can i use criteria builder inside my repository and services to search for Sezionis based on Comunes id?
I'm new to QueryDSL and Specifications, i just would like to obtain something like this:
@Query("Select * from Sezioni s WHERE s.id_Comune = :id", native="true")
public DataTablesOutput <Object> findByField (@Param(value="id", input);
This is the current Service Implementation
@Service
public class SezioniServiceImpl implements SezioniService{
@Autowired
SezioniRepository repo;
@Autowired
SezioniMapper mapper;
@Autowired
SezioniSpecifications sezSpec;
@Override
public List<SezioniDTO> findAll() {
return repo.findAll().stream().map(x -> mapper.entityToDto(x, new CycleAvoidingMappingContext()))
.collect(Collectors.toList());
}
@Override
public List<SezioniDTO> findByIdComune(Long idcom){
return repo.findSezionibyIdComune(idcom).stream().map(x -> mapper.entityToDto(x, new CycleAvoidingMappingContext()))
.collect(Collectors.toList());
}
@Override
public SezioniDTO save(SezioniDTO entity) {
return null;
}
@Override
public Optional<SezioniDTO> findById(Long id) {
// TODO Auto-generated method stub
return null;
}
@Override
public void delete(SezioniDTO entity) {
// TODO Auto-generated method stub
}
@Override
public void deleteById(Long id) {
// TODO Auto-generated method stub
}
@Override
public long count() {
// TODO Auto-generated method stub
return 0;
}
@Override
public DataTablesOutput<SezioniDTO> getSezioniTable(@Valid DataTablesInput input) {
return repo.findAll(input, null, null, a -> mapper.entityToDto(a, new CycleAvoidingMappingContext()) );
}
}
and the current Repository for SezioniDTO
@Repository
public interface SezioniRepository extends JpaRepository<Sezione,Long>, JpaSpecificationExecutor<Sezione>, DataTablesRepository<Sezione,Long> {
@Query(value = "SELECT * FROM db.sezione WHERE sez_com_prg = :id ORDER BY sez_numsezione", nativeQuery = true)
public List <Sezione> findSezionibyIdCom(@Param(value = "id") Long id);
}
Where Sezione is the current Entity. As you can see, it extends , and DataTablesOutput work only with Specifications, which I haven't understood at all. I simply would like to create a method similar to the public List I have in the repo, but with a DataTablesOutput return instead.
