Search code examples
javamysqlhibernateconcat-ws

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!

Solution

  • 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