CODEWITHSUNDEEP
javahibernatehqlwhere-clausesql-like
ide5
ide5

Reputation: 13

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

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

Upvotes: 1

Views: 1086

Answers (2)

Shanks D Shiva
Shanks D Shiva

Reputation: 197

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

Upvotes: 0

W GDJ
W GDJ

Reputation: 36

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

Upvotes: 1

Related Questions