Spring Boot JPA dynamic query building

Question

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.

Does this answer your question? [Dynamic spring data jpa repository query with arbitrary AND clauses](https://stackoverflow.com/questions/28874135/dynamic-spring-data-jpa-repository-query-with-arbitrary-and-clauses) Out of the 3 provided options the for the presented use cases i would use Query-By-Example. — Robert Niestroj, Jan 02 '20 at 12:39
There's actually join involved in this. The suggestion you are giving contains single table and I've resolved that — , Jan 02 '20 at 15:52

score 2 · Answer 1 · answered Jan 02 '20 at 12:32

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

    }

is there any way to do this without using ResultDTO? by using inner class? — , Jan 02 '20 at 15:50
Yes, Inner class must be public and create constructer for name and title field in that class and use that class inplace of ResultDTO class. — Ashish Bakwad, Jan 02 '20 at 15:54

Spring Boot JPA dynamic query building

1 Answers1