How to search with multiple filter using criteria builder

Question

I would like to make a multiple filter in java, I use Spring Boot. And my query are written using criteria.

SearchApp.java:

@Entity
@Table(name = "searchapp")
public class SearchApp implements Serializable {

@Column(name = "code")
private String code;
@Column(name = "release")
private String release;
@Column(name = "environnement")
private String environnement;
@Column(name = "date")
private Date date;
private static final long serialVersionUID = -1701849052760947052L;

public SearchApp() {
    super();
    // TODO Auto-generated constructor stub
}
public DeploiementApp(String code, String release, String environnement, Date date) {
    super();
    this.code = code.toLowerCase();
    this.release = release;
    this.environnement = environnement;
    this.date= date;    
}

I have a search controller, which is the door to enter my application

SearchController.java:

@Autowired
SearchService searchService;

@GetMapping("/filtersearch")
public List<SearchApp> filter(@RequestParam("code") String code, @RequestParam("release") String release, @RequestParam("environnement") String environnement, @RequestParam("date") String date) {

    return searchService.filter(code.toUpperCase(), release.toUpperCase(), environnement.toUpperCase(), date);
}

SearchService.java :

List<SearchApp> filter(String code, String environnement, String release, String date);

SearchServiceImpl.java :

@PersistenceContext(type = PersistenceContextType.EXTENDED)
private EntityManager em;

public SearchServiceImpl(EntityManager em) {
    super();
    this.em = em;
}

@Override
public List<SearchApp> filter(String code, String release, String environnement, String date) {


    DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
    format.setTimeZone(TimeZone.getTimeZone("Europe/Paris"));
    Date dateBD = new Date();
         dateBD = format.parse(date);
    } catch (ParseException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
    //select
    CriteriaQuery<SearchApp> criteriaQuery = criteriaBuilder.createQuery(SearchApp.class);
    //from
    Root<SearchApp> itemRoot = criteriaQuery.from(SearchApp.class);
    // attribut

    List<Predicate> predicate = new ArrayList<Predicate>();

    if(  code != "" || code != null) {
        Predicate predicateCode = criteriaBuilder.equal(itemRoot.get("code"), code);
        predicate.add(predicateCode);
    }

    if(  release != "" || release != null) {
        Predicate predicateRelease = criteriaBuilder.equal(itemRoot.get("release"), release);
        predicate.add(predicateRelease);
    }

    if(  environnement != "" || environnement != null) {
        Predicate predicateEnvironnement = criteriaBuilder.equal(itemRoot.get("environnement"), environnement);
        predicate.add(predicateEnvironnement);
    }

    if(  dateBD != null ) {
        Predicate predicateDate = criteriaBuilder.equal(itemRoot.get("date"), date);
        predicate.add(predicateDate);
    }

    Predicate And = criteriaBuilder.and(predicate.toArray(new Predicate[predicate.size()]));

    //where
    criteriaQuery.where(And);
    List<SearchApp> result = em.createQuery(criteriaQuery).getResultList();

    return result;
}

It does not work.

error: org.hibernate.query.criteria.internal.predicate.ComparisonPredicate@41f4f353 org.hibernate.query.criteria.internal.predicate.ComparisonPredicate@77ef22f7 also date errors

Firstly you should change || to && with your if empty checks. I would also be inclined to use isEmpty() https://docs.oracle.com/javase/7/docs/api/java/lang/String.html#isEmpty() - and then not that too. — JGFMK, Dec 24 '19 at 12:29
Predicate predicateDate = criteriaBuilder.equal(itemRoot.get("date"), date); value should be dateBD instead of date. — dassum, Dec 24 '19 at 12:42
There are no more exceptions, but the expected result is not good — static, Dec 24 '19 at 13:05

Vivek · Answer 1 · 2019-12-26T05:42:29.337

3

Pass the values directly to service layer as you are using toUpperCase() which will break if values are null and after checking null at service layer, use upper in criteria builder like,

"criteriaBuilder.equal(criteriaBuilder.upper(itemRoot.get("code"), code.toUpperCase());"

Use dateDB instead of date and string date should be parsed to Date object. Hopefully it will resolve your issue. @Column(name = "date") private Date date; date column should be Date in database table.

edited Dec 26 '19 at 05:42

answered Dec 24 '19 at 18:17

Vivek

376
1
14

Thank you for your answer, which solves part of my problem, I still have my date problem which is visibly linked to my database. – static Dec 25 '19 at 14:56
Date is not dependent to database, we can simply match dates. – Vivek Dec 26 '19 at 05:43
1

We can also use predicate as below, predicates.add(criteriaBuilder.equal(itemRoot.get(date), dateBD)); – Vivek Dec 26 '19 at 06:03

How to search with multiple filter using criteria builder

1 Answers1