0

I am use MariaDB 10.x , MyBatis, Spring Boot 2.x , Java 8 . I need write query.

I have 2 functions:

function UuidFromBin

RETURN LCASE(CONCAT_WS('-',
    HEX(SUBSTR(_bin, 5, 4)),
    HEX(SUBSTR(_bin, 3, 2)),
    HEX(SUBSTR(_bin, 1, 2)),
    HEX(SUBSTR(_bin, 9, 2)),
    HEX(SUBSTR(_bin, 11))
)

function uuidToBin

RETURN UNHEX(CONCAT(
    SUBSTR(_uuid, 15, 4),
    SUBSTR(_uuid, 10, 4),
    SUBSTR(_uuid, 1, 8),
    SUBSTR(_uuid, 20, 4),
    SUBSTR(_uuid, 25)
)

datatype of field po.id and po.shop_id are binary.

List<String> list1 = Arrays.asList("8766103-c1e7-4189-940b-3aeeeef041db", "00000000-0000-3100--0000-00000000");
List<String> list2 = Arrays.asList("00000000-0000-3100--0000-00000000" , "00000000-0000-3100--0000-00000002");

SELECT DISTINCT
    UuidFromBin(po.shop_id) AS shop_id,
    po.name
    po.state
FROM product_offering po
WHERE po.id IN (UuidToBin('8766103-c1e7-4189-940b-3aeeeef041db'), UuidToBin('00000000-0000-3100--0000-00000000')) -- list1
AND po.shop_id IN (UuidToBin('00000000-0000-3100--0000-00000000'), UuidToBin('00000000-0000-3100--0000-00000002')) -- list2

How to write XML MyBatis query in case using list of strings in parameters?

Addition information: I see https://stackoverflow.com/a/44022017/3728901 , I have difficult with call UuidToBin in per item of String list.

Vy Do
  • 46,709
  • 59
  • 215
  • 313
  • Two questions: 1) what's the column type of `shop_id`? 2) can you use `java.util.UUID` instead of `String`? – ave Aug 09 '21 at 15:01
  • 1) db: MariaDB 10.x . Column type of `shop_id` is binary. 2) You maybe suggest me a good practices, but in my case, for safety, let follow my previous steps. (you can add more arround information about it.) thank you – Vy Do Aug 09 '21 at 15:14
  • 1
    If you use `String`, it's a text book usage of [``](https://mybatis.org/mybatis-3/dynamic-sql.html#foreach). e.g. `UuidToBin(#{s})` – ave Aug 09 '21 at 17:51
  • Thank you very much! I know you is key MyBatis' commiter. I will try to follow your suggestion with UUID later. – Vy Do Aug 09 '21 at 23:27
  • YW! It's just that other committers are busier lately. ;D Using `java.util.UUID` might make the statement simpler because you can use type handler. See this [demo](https://github.com/harawata/mybatis-issues/tree/0194194873963d263544d314a76f84625e208992/so-60613619) if you are interested. Note that your type handler may need to call `setBytes()`/`getBytes()` instead. You can do the 'rearranging', too. This [answer](https://stackoverflow.com/a/29836273/1261766) may help. If you have any difficulty, please update the question with the details. – ave Aug 10 '21 at 04:02

0 Answers0