CODEWITHSUNDEEP
javamysqlhibernateconcat-ws
Sergey Dvoreckih
Sergey Dvoreckih

Reputation: 106

Hibernate concat_ws and null fielnds

Im using CONCAT_WS within hibernate query, to create a giant string and search by all fields using like '%value%' . It works fine, but for some records some fields are null. e.g if actId is null, my whole concat_ws returns null. I don't know why, concat_ws must ignore null values. May be it's because hibernate trying to call getActNumber from null? anyway I'm trying hard to resolve this problem. 

  where CONCAT_WS("_", actItemId.actId.actNumber, DATE_FORMAT(recordDate, '%d.%m.%Y'), actItemId.techniqueId.name, fzkActNumber, ....etc) like '%value%'

thanks!

Upvotes: 2

Views: 448

Answers (2)

Sergey Dvoreckih
Sergey Dvoreckih

Reputation: 106

The reason was implicit usage of INNER JOINs in my query.

It does not select any parent rows without associated child. Solution was LEFT JOINs

select distinct(t) from InvBook as t                            +
                  "left join t.actItemId as actItem          "  +
                  "left join t.actItemId.actId as act        "  +

etc...

even if actItemId or actId is null now, CONCAT_WS ignores it and glues other fields together.

Thanks to Vlad Mihalcea https://discourse.hibernate.org/t/concat-ws-like-value/428

Upvotes: 0

O. Jones
O. Jones

Reputation: 108841

CONCAT_WS() is functioning as designed.

If you want it to work even when some args are NULL, do something like this to convert each possibly-NULL arg to a space. 

CONCAT_WS('_',  IFNULL(cola,''), IFNULL(colb,'') ...)

Upvotes: 0

Related Questions