CODEWITHSUNDEEP
springspring-data-jpa
user10767069
user10767069

Reputation:

Spring Boot JPA dynamic query building

I've two table, following are the structures of tables 

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`)
) ;

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`)
) ;

I want to implement the following kind of queries where the where clause would be dynamic 

  td.`name`,
  tm.`title` 
FROM
  `tbl_directors` td 
  LEFT JOIN `tbl_movies` tm 
    ON td.`id` = tm.`director_id` 
WHERE tm.`title` LIKE '%yo%' ;

SELECT 
  td.`name`,
  tm.`title` 
FROM
  `tbl_directors` td 
  LEFT JOIN `tbl_movies` tm 
    ON td.`id` = tm.`director_id` 
WHERE td.`name` LIKE '%AB%' ;

I want to implement this using Criteria building option in spring boot. I'll be adding code to this question as I move ahead with code, meanwhile I would appreciate any sort of help regarding this.

Upvotes: 1

Views: 311

Answers (1)

Ashish Bakwad
Ashish Bakwad

Reputation: 811

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: 2

Related Questions