CODEWITHSUNDEEP
javakotlinjdbisqlbatch
fred
fred

Reputation: 1822

JDBI SqlBatch only inserts one entry

I'm having problems with JDBI only inserting the first item in the list

    data class UserResourceRow(
      val userId: UserId,
      val roleId: RoleId,
      val resource: Resource,
      val modified: Instant,
      val auditUserId: UserId,
      val deleted: Boolean
    )

    CREATE TABLE `user_resource` (
      `user_id` varchar(36) CHARACTER SET ascii NOT NULL,
      `resource` varchar(36) CHARACTER SET ascii NOT NULL,
      `role_id` int(11) NOT NULL,
      `modified_timestamp` timestamp(3) NOT NULL DEFAULT current_timestamp(3),
      `audit_user_id` varchar(36) CHARACTER SET ascii NOT NULL,
      `deleted` tinyint(1) NOT NULL DEFAULT 0,
      PRIMARY KEY (`user_id`,`resource`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb;

    @Transaction
    @SqlBatch(
        """
        INSERT INTO user_resource
        VALUES (:userId, :resource, :roleId, :modified, :auditUserId, :deleted)
        """
    )
    @Throws(JdbiException::class)
    fun upsert(@BindBean grants: List<UserResourceRow>): IntArray

I call this with eg

    val upsert = grantsDao.upsert(grants)

where grants is a list containing these two

    UserResourceRow(userId=22, roleId=123, resource=resource.property.789, modified=2020-05-05T00:48:01.644Z, auditUserId=321, deleted=false)

    UserResourceRow(userId=22, roleId=123, resource=resource.property.456, modified=2020-05-05T00:48:01.644Z, auditUserId=321, deleted=false)

the call succeeds and upsert returns an intArray or [-2, -2]

The fact that there's two entries tells me it thinks it inserted two rows, but I don't understand why the values of the intArray are -2 and -2, shouldn't it be an intArray of [1, 1]?

and the table contains only the first list entry

    mysql> select * from user_resource;
    +---------+-----------------------+---------+-------------------------+---------------+---------+
    | user_id | resource              | role_id | modified_timestamp      | audit_user_id | deleted |
    +---------+-----------------------+---------+-------------------------+---------------+---------+
    | 22      | resource.property.789 |      123 | 2020-05-05 00:48:01.644 | 321           |       0 |
    +---------+-----------------------+---------+-------------------------+---------------+---------+
    1 row in set (0.00 sec)

The fact that it successfully inserted one entry tells me there's no problem with @BindBean mapping the columns to the attributes of the data class UserResourceRow, but I don't understand why only the first entry is inserted?

Upvotes: 2

Views: 636

Answers (1)

fred
fred

Reputation: 1822

I spent A LOT of time trying to debug this, and couldn't find anything wrong with neither JDBI or the driver - the statements that went out on the socket to the server looked fine. (and they were)

Turns out this must have had to do with some subtle version discrepancy between the underlying in-memory db used in the test

        <dependency>
            <groupId>ch.vorburger.mariaDB4j</groupId>
            <artifactId>mariaDB4j</artifactId>
            <version>2.3.0</version>
        </dependency>

and the mariadbclient used

        <dependency>
            <groupId>org.mariadb.jdbc</groupId>
            <artifactId>mariadb-java-client</artifactId>
            <version>2.2.1</version>
        </dependency>

because when I bump them to

        <dependency>
            <groupId>ch.vorburger.mariaDB4j</groupId>
            <artifactId>mariaDB4j</artifactId>
            <version>2.4.0</version>
        </dependency>

and

        <dependency>
            <groupId>org.mariadb.jdbc</groupId>
            <artifactId>mariadb-java-client</artifactId>
            <version>2.6.0</version>
        </dependency>

the issue doesn't reproduce.

There are some interesting lines in org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol to do with versions and bulk/batch statements, maybe the mismatch was in there somewhere.

Upvotes: 2

Related Questions