Hibernate Query, how to use like or = on where clause depend on value passed

Question

I have the following code and query :

String kodeCustomer, kodeDropship, kodeSales, kodePengirim;

kodeCustomer = kodeCustomerTextField.getText().trim();
kodeDropship = kodeDropshipTextField.getText().trim();
kodeSales    = kodeSalesTextField.getText().trim();
kodePengirim = pengirimTextField.getText().trim();

... some other code ... 
      record = session.createQuery("from PenjualanTableDb where"
    + " dtanggalpenjualan >= :dawalParm"
        + " and dtanggalpenjualan < :dakhirParm"
        + " and coalesce(ckodecustomer,'') like :custParm"
        + " and coalesce(ckodedropship,'') like :dropshipParm"
        + " and coalesce(ckodesalesperson,'') like :salesParm"
        + " and coalesce(ckodepengirim,'') like :pengirimParm")
    .setParameter("dawalParm", tanggalMulaiTrx)
        .setParameter("dakhirParm", tanggalAkhirTrx)
        .setParameter("custParm", kodeCustomer + "%")
        .setParameter("dropshipParm", kodeDropship + "%")
        .setParameter("salesParm", kodeSales + "%")
        .setParameter("pengirimParm", kodePengirim + "%")
    .list();

how to modify the query so it can give the correct output based on user input. if textfield empty then the query using like, but if textfield not empty then query using =

Is there an easy way to handle that?

Thanks in advance

It looks like you're asking "if the text field is empty, then query on 'textField LIKE('%')". What's the point of this? Just omit the criterion. — Not a JD, Mar 15 '19 at 15:37
@NotaJD Thanks for the comment, sorry if my question is not clear :) what i need is something like this : _a = "abc" _b = "" so i need query (select ... where a = 'abc' and b like '%') _a = "" _b = "" then query is (select ... where a like '%' and b like '%') i can used if, but it takes a lot of query combination for 4 input variable. Thanks... — ide5, Mar 15 '19 at 15:54
I'm not a DBA but I'd wager that some databases aren't smart enough to realize that LIKE('%') is a nop ->> but I could be completely wrong :) Have you looked into the Criteria API (https://www.tutorialspoint.com/jpa/jpa_criteria_api.htm)? — Not a JD, Mar 15 '19 at 16:12

score 1 · Accepted Answer · answered Mar 15 '19 at 17:04

1

I think you should construct the hql query (including parameters) dynamically according to the parameters present instead of using "a like%".

answered Mar 15 '19 at 17:04

W GDJ

36
3

Thanks for the input, i search about creating dunamic hql query and found this link https://stackoverflow.com/questions/37153385/dynamically-search-query-in-hql-with-optional-parameter i think it can solved my problem, i'll try to implement that. Thanks a lot for the direction.... – ide5 Mar 16 '19 at 04:28
in that case criteria can be better option – bananas Apr 29 '19 at 05:06

score 0 · Answer 2 · answered Apr 29 '19 at 05:12

record = session.createQuery("from PenjualanTableDb where"
    + " dtanggalpenjualan >= :dawalParm"
        + " and dtanggalpenjualan < :dakhirParm"
        + " and coalesce(ckodecustomer,'') like '%:custParm%'"
        + " and coalesce(ckodedropship,'') like '%:dropshipParm%'"
        + " and coalesce(ckodesalesperson,'') like '%:salesParm%'"
        + " and coalesce(ckodepengirim,'') like '%:pengirimParm%'")
    .setParameter("dawalParm", tanggalMulaiTrx)
        .setParameter("dakhirParm", tanggalAkhirTrx)
        .setParameter("custParm", kodeCustomer)
        .setParameter("dropshipParm", kodeDropship)
        .setParameter("salesParm", kodeSales)
        .setParameter("pengirimParm", kodePengirim)
    .list();

Hibernate Query, how to use like or = on where clause depend on value passed

2 Answers2