Criteria Builder and Jquery DataTables - Custom query

Question

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.

Answer 1

Criteria Builder's advantage is to build queries dynamically upon your business login needs:

Consider next example:

    @Service
public class SezioniQuery {

    @PersistenceContext
    private EntityManager entityManager;

    List<Sezioni> select(TriFunction<CriteriaBuilder, Root<Sezioni>, CriteriaQuery<Sezioni>, CriteriaQuery<Sezioni>> builder) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Sezioni> query = criteriaBuilder.createQuery(Sezioni.class);
        // SQL FROM clause
        Root<Sezioni> from = query.from(Sezioni.class);
        // SQL SELECT clause
        CriteriaQuery<Sezioni> select = query.select(from);

        // build WHERE somewhere later
        CriteriaQuery<Sezioni> apply = builder.apply(criteriaBuilder, from, query);

        // execute
        TypedQuery<Sezioni> typedQuery = entityManager.createQuery(apply);
        return typedQuery.getResultList();
    }
}

^^ here we define boilerplate.

Next we can reuse it to build different queires:

    // @BeforeEach void setUp() {...} omitted see prev. answer

    @Test
    void testEqual() {
        Long id = 1L;
        List<Sezioni> sezionis = sezioniQuery.select((cb, from, query) ->
                // WHERE id=1  
                query.where(cb.equal(from.get("id"), id)));

        assertEquals(1, sezionis.size());
        assertEquals("cap42",sezionis.get(0).getComune().getComCap());
    }

    @Test
    void testGreater() {
        List<Sezioni> sezionis = sezioniQuery.select((cb, from, query) ->
                // WHERE id > 0
                query.where(cb.gt(from.get("id"), 0)));

        assertEquals(1, sezionis.size());
        assertEquals("cap42",sezionis.get(0).getComune().getComCap());
    }

So, using CriteriaBuilder you can build queries dynamically but this requires a bit more code, non-type-safe code.

Whereas JpaRepository extension is type-safe but non-dynamiс

Answer 2

1. Define Entities:

    @Entity
    public class Sezioni {
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Long id;
        private LocalDate sezDtaggiornamento;
    
        @OneToOne(cascade = {CascadeType.ALL})
        @JoinColumn(name = "comune_id")
        private Comune Comune;
    
        // getters & setter are omitted
    }

and

    @Entity
    public class Comune {
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Long id;
        private String comCap;
        private String comCodbelfiore;
        private String comCodcomune;
    
        // getters & setter are omitted
    }

2. Define repository

    @Repository
    public interface SezioniRepository extends JpaRepository<Sezioni, Long> {
    
        @Query("select s from Sezioni s where s.Comune.id = :id")
        List<Sezioni> findByComuneId(Long id);
    }

3. Use (here in test)

    @DataJpaTest
    class SezioniRepositoryTest {
        @Autowired
        SezioniRepository sezioniRepository;
    
        @BeforeEach
        void setUp() {
            Comune comune = new Comune();
            comune.setComCap("cap42");
            comune.setComCodcomune("cod43");
    
            Sezioni sezioni = new Sezioni();
            sezioni.setComune(comune);
            sezioni.setSezDtaggiornamento(LocalDate.of(1970, 1, 1));
    
            sezioniRepository.save(sezioni);
        }
    
        @Test
        void test() {
            List<Sezioni> sezionis = sezioniRepository.findByComuneId(1L);
            assertEquals(1, sezionis.size());
            assertEquals("cap42",sezionis.get(0).getComune().getComCap());
        }
    }

4. Next you can use MapStruct to map entities into DTO (if you prefer to expose DTO on your API)