Re: Collection and DB2

[Guy Rouillier]

On 5/6/2013 3:50 PM, jj wrote:
> Does anyone know if there are know issues with using a collection with
> DB2 (v9.7) with mybatis v3.0.4?
>
> I followed the documentation and coded a straight forward xml resultMap
> consisting of a a collection. This works as expected with an in-memory
> DB. However, with DB2 multiple objects are created each with one list
> entry instead of one object and a list of multiple entries.
>
> I have this working with subselects but would rather have the join. I
> haven't provided any xml/sql as it's as simple as using the user guide
> examples.

Should work. Sounds to me like the well-known problem of not correctly
specifying the id columns for the resultMap. Which example are you
referring to? We can't do much to help without something concrete to
reference.

--
Guy Rouillier

[jj]

The problem appears when the id is a _byte[]. When it is _int it is fine.
Thoughts? If nothing stands out with using a byte[] id, I will post the simplified problem config.

On Monday, May 6, 2013 3:50:44 PM UTC-4, jj wrote:

Does anyone know if there are know issues with using a collection with DB2 (v9.7) with mybatis v3.0.4?

I followed the documentation and coded a straight forward xml resultMap consisting of a a collection. This works as expected with an in-memory DB. However, with DB2 multiple objects are created each with one list entry instead of one object and a list of multiple entries.

I have this working with subselects but would rather have the join. I haven't provided any xml/sql as it's as simple as using the user guide examples.

Thanks!
JJ

[jj]

It's when the id is a byte[]. I have tried many different configurations to get the byte[] to work.

WORKING: When ID is _int;

CREATE TABLE A (
   A_UUID decimal(19,0) NOT NULL
   ,USER_ID decimal(19,0) NOT NULL
   ,NAME varchar(50) NOT NULL
);
CREATE UNIQUE INDEX A_1 ON A (A_UUID);
ALTER TABLE A ADD CONSTRAINT PK_A PRIMARY KEY (A_UUID);

CREATE TABLE B (
   A_UUID decimal(19,0) NOT NULL
   ,B_ID decimal(19,0) NOT NULL
   ,DETAIL varchar(75) NOT NULL
);
CREATE UNIQUE INDEX B_1 ON B (A_UUID, B_ID);
ALTER TABLE B ADD CONSTRAINT PK_B PRIMARY KEY (A_UUID, B_ID);

INSERT INTO A (A_UUID, USER_ID, NAME) VALUES (10, 12345, 'ONE');
INSERT INTO B (A_UUID, B_ID, DETAIL) VALUES (10, 1, 'DETAILA');
INSERT INTO B (A_UUID, B_ID, DETAIL) VALUES (10, 2, 'DETAILB');

<select id="getAObject" resultMap="resultA">
    SELECT
      aTable.A_UUID
      ,aTable.USER_ID
      ,aTable.NAME
      ,bTable.B_ID
      ,bTable.DETAIL
    FROM A aTable
    LEFT OUTER JOIN B bTable ON (aTable.A_UUID = bTable.A_UUID)
    WHERE aTable.A_UUID = 10
  </select>

  <resultMap id="resultA" type="com.pkg.A">
    <constructor>
        <idArg column="a_uuid"  javaType="_int"/>
        <arg   column="user_id" javaType="_long"/>
        <arg   column="name"    javaType="String"/>
    </constructor>
    <collection property="lists" ofType="com.pkg.B">
        <constructor>
            <idArg column="b_id"   javaType="_int"/>
            <arg   column="detail" javaType="String"/>
        </constructor>
    </collection>
  </resultMap>

public class A {
    private int id;
    private long userId;
    private String name;
    private List<B> lists;
   
    public A(int id, long userId, String name) {
        this.id = id;
        this.userId = userId;
        this.name = name;
    }   
    @Override
    public String toString() {
        return "A [id=" + id + ", name=" + name + ", lists=" + lists + "]";
    }
}
public class B {
    private int id;
    private String detail;
   
    public B(int id, String detail) {
        this.id = id;
        this.detail = detail;
    }
    @Override
    public String toString() { return "B [id=" + id + ", detail=" + detail + "]"; }
}

RESULT: [A [id=10, name=ONE, lists=[B [id=1, detail=DETAILA], B [id=2, detail=DETAILB]]]]



NOT WORKING: When ID is _byte[];

CREATE TABLE A (
   A_UUID char(16) for bit data NOT NULL
   ,USER_ID decimal(19,0) NOT NULL
   ,NAME varchar(50) NOT NULL
);
CREATE UNIQUE INDEX A_1 ON A (A_UUID);
ALTER TABLE A ADD CONSTRAINT PK_A PRIMARY KEY (A_UUID);

CREATE TABLE B (
   A_UUID char(16) for bit data NOT NULL
   ,B_ID decimal(19,0) NOT NULL
   ,DETAIL varchar(75) NOT NULL
);
CREATE UNIQUE INDEX B_1 ON B (A_UUID, B_ID);
ALTER TABLE B ADD CONSTRAINT PK_B PRIMARY KEY (A_UUID, B_ID);

INSERT INTO A (A_UUID, USER_ID, NAME) VALUES (x'a4f18727a44c46eab61b3a8454fe50e9', 12345, 'ONE');
INSERT INTO B (A_UUID, B_ID, DETAIL) VALUES (x'a4f18727a44c46eab61b3a8454fe50e9', 1, 'DETAILA');
INSERT INTO B (A_UUID, B_ID, DETAIL) VALUES (x'a4f18727a44c46eab61b3a8454fe50e9', 2, 'DETAILB');

<select id="getAObject" resultMap="resultA">
    SELECT
      aTable.A_UUID
      ,aTable.USER_ID
      ,aTable.NAME
      ,bTable.B_ID
      ,bTable.DETAIL
    FROM A aTable
    LEFT OUTER JOIN B bTable ON (aTable.A_UUID = bTable.A_UUID)
    WHERE aTable.A_UUID = x'a4f18727a44c46eab61b3a8454fe50e9'
  </select>

  <resultMap id="resultA" type="com.pkg.A">
    <constructor>
        <idArg column="a_uuid"  javaType="_byte[]"/>
        <arg   column="user_id" javaType="_long"/>
        <arg   column="name"    javaType="String"/>
    </constructor>
    <collection property="lists" ofType="com.pkg.B">
        <constructor>
            <idArg column="b_id"   javaType="_int"/>
            <arg   column="detail" javaType="String"/>
        </constructor>
    </collection>
  </resultMap>

public class A {
    private byte[] id;
    private long userId;
    private String name;
    private List<B> lists;
   
    public A(byte[] id, long userId, String name) {
        this.id = id;
        this.userId = userId;
        this.name = name;
    }   
    @Override
    public String toString() {
        return "A [id=" + id + ", name=" + name + ", lists=" + lists + "]";
    }
}
public class B {
    private int id;
    private String detail;
   
    public B(int id, String detail) {
        this.id = id;
        this.detail = detail;
    }
    @Override
    public String toString() { return "B [id=" + id + ", detail=" + detail + "]"; }
}

RESULT: [A [id=[bytes…, name=ONE, lists=[B [id=1, detail=DETAILA]]], A [id=[bytes..., name=ONE, lists=[B [id=2, detail=DETAILB]]]]