Querying multiple records using composite key in the mybatis

Question

I have a table with composite key. I want to query it in bulk in mybatis. Is it possible? if yes, how?

Table structure of table Foo with foo_id_1 and foo_id_2 as composite primary key.

{
 foo_id_1 uniqueIdentifier,
 foo_id_2 uniqueIdentifier,
 foo_name nvarchar
}

I will have a list of foo POJOs and I want to query them to check if they exist or not. Below is for getting a single object

<select id="getFooList" resultMap="foo">
        SELECT
            f.foo_id_1,
            f.foo_id_2,
            f.foo_name
        FROM foo f
        WHERE f.foo_id_1 = #{foo_id_1} and f.foo_id_2 = #{foo_id_2}
    </select>

    <resultMap id="foo" type="Foo">
        <id property="fooId1" column="foo_id_1"/>
        <id property="fooId2" column="foo_id_2"/>
        <result property="fooName" column="foo_name"/>
    </resultMap>

But I want to fetch in bulk. Corresponding SQL query would be

Select *
From foo
Where (foo_id_1 = '1a' and foo_id_2 = '1b') or
      (foo_id_1 = '2a' and foo_id_2 = '2b') or
      (foo_id_1 = '3a' and foo_id_2 = '3b');

With [``](https://mybatis.org/mybatis-3/dynamic-sql.html#foreach), you can iterate the parameter (e.g. `List>`) to build the condition. — ave, Feb 17 '23 at 21:07

T N · Answer 1 · 2023-02-17T21:49:44.580

If your calling application can combine the keys into a single delimited string, the query can split the values back out into individual search values. Since your use case involves multiple key columns, you will need two different delimiters - one to separate tuples, and one to separate keys within a tuple.

For example, if you can build a parameter like:

DECLARE @SearchIDs VARCHAR(MAX) = '1a,1b;2a,2b;3a,3b'

The following will separate the keys and perform an efficient key lookup:

SELECT *
FROM (
    SELECT
        MAX(CASE WHEN S2.ordinal = 1 THEN S2.value END) AS search_id_1,
        MAX(CASE WHEN S2.ordinal = 2 THEN S2.value END) AS search_id_2
    FROM STRING_SPLIT(@SearchIDs, ';', 1) S1
    CROSS APPLY STRING_SPLIT(S1.value, ',', 1) S2
    GROUP BY S1.ordinal
) P
JOIN foo f
    ON f.foo_id_1 = P.search_id_1 and f.foo_id_2 = P.search_id_2

Sample results:

search_id_1	search_id_2	foo_id_1	foo_id_2	foo_name
1a	1b	1a	1b	A
2a	2b	2a	2b	CCC
3a	3b	3a	3b	EEEEE

In you actual code, you should cast the search_id_1 and search_id_2 values to the proper data type.

        CAST(MAX(...) AS UNIQUEIDENTIFIER) AS search_id_1,
        CAST(MAX(...) AS UNIQUEIDENTIFIER) AS search_id_2

See this db<>fiddle.

Other more formal ways to pass multiple repeating values in a parameter involve constructing XML or JSON, but sometimes a plain string is the simplest as long as you can guarantee that the values cannot themselves contain any delimiters.

Querying multiple records using composite key in the mybatis

1 Answers1