CODEWITHSUNDEEP
spring-bootspring-data-jpacriteria-api
user10767069
user10767069

Reputation:

Can I get the data using using DTO from this using CriteriaQuery, CriteriaBuilder?

these are the structures of my table, I want to find title and name by searching through movie title using LIKE through JPA Criteria Builder

CREATE TABLE `tbl_movies` (
  `id` BIGINT (5) NOT NULL AUTO_INCREMENT,
  `director_id` BIGINT (3) NOT NULL,
  `title` VARCHAR (50) NOT NULL,
  `status` ENUM ('active', 'inactive', 'pending') NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(),
  PRIMARY KEY (`id`),
  UNIQUE KEY (`title`),
  FOREIGN KEY (`director_id`) REFERENCES `tbl_directors` (`id`)
) ;

CREATE TABLE `tbl_directors` (
  `id` BIGINT (3) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR (50) NOT NULL,
  `status` ENUM ('active', 'inactive', 'pending') NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(),
  PRIMARY KEY (`id`),
  UNIQUE KEY (`name`)
) ;

This the code in service 

public List<Movie> searchByAnything(String movieTitle, String directorName) {
        return movieRepository.findAll((Root<Movie> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) -> {
            List<Predicate> predicates = new ArrayList<>();
            if (movieTitle.length() > 0) {
                predicates.add(criteriaBuilder.like(root.join("tbl_directors").<String>get("title"), "%" + movieTitle + "%"));
            }
            return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
        });
    }

Where am I doing it wrong? I'm getting size 0 list when debugging. Would be great if I could get any sort of help.

Upvotes: 0

Views: 736

Answers (1)

PattySpringDev
PattySpringDev

Reputation: 177

You can criteria builder for query implementation like this

// Defined query
     CriteriaBuilder cb = entityManager.getCriteriaBuilder();
     CriteriaQuery<ResultDTO> cq = cb.createQuery(ResultDTO.class);

     // Define FROM clause
     Root<DirectorEntity> root = cq.from(DirectorEntity.class);
     Join<DirectorEntity, Movie> ptSku = root.join("movies", JoinType.LEFT);
     List<Predicate> predicates = new ArrayList<>();
     if (name != null) {
       predicates.add(cb.like(root.get("name"), name));
     }
     if (title != null) {
       predicates.add(cb.like(root.get("title"), title));
     }
     cq.where(cb.and(predicates.toArray(new Predicate[predicates.size()])));

     cq.distinct(true);

     cq.select(cb.construct(ResultDTO.class, root.get("name"), root.get("title")));

     TypedQuery<ResultDTO> query = entityManager.createQuery(cq).setHint(QueryHints.HINT_READONLY, true);


     // Execute query
     return query.getResultList();


     public class ResultDTO {

       private String name;

       private String  title;

        public ResultDTO(String name, String title) {

            .....
        }

       //getter setter

    }

Upvotes: 1

Related Questions