MyBatys Batch Insert doubt/error retrieving generated id (binding to POJO)

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

MyBatys Batch Insert doubt/error retrieving generated id (binding to POJO)

rafael.cruz
Hello.
I was wondering if I could get a hint from the group;

I'm trying to performa  batch insert using mybatis. (With Springboot) MyBatis: 3.4.4 and MyBatisSpring

My Mapper
@Insert({
            "<script> " +
                "insert into notification (user_area, agent_id, event, ref, created_at, `read`, received) values (" +
                "<foreach collection='notifications' item='notification' separator='),('> " +
                    "#{notification.userArea}, " +
                    "#{notification.agentId}, " +
                    "#{notification.event, typeHandler=org.apache.ibatis.type.EnumTypeHandler} ," +
                    "#{notification.referenceId}, " +
                    "#{notification.createDate, typeHandler=org.apache.ibatis.type.DateTypeHandler}, " +
                    "#{notification.read, typeHandler=org.apache.ibatis.type.BooleanTypeHandler}, " +
                    "#{notification.received, typeHandler=org.apache.ibatis.type.BooleanTypeHandler} " +
                "</foreach> )" +
            "</script>"
    })
    @Options(useGeneratedKeys = true, keyColumn = "id", keyProperty = "id")
    List<Notification> insertNotificationInBatch(@Param("notifications") List<Notification> notifications);

My Pojo
public class Notification implements Serializable {

    public Long id;
    private long userArea;
    private long agentId;
    private long referenceId;
    private NotificationEvent event;
    private Date createDate;
    private boolean read = false;
    private boolean received = false;


    //Transient
    private String userAreaCode;
    private String agentName;
    private FileMembershipVO agentPicture;
    private Long page;
    private String pageName;
    private String moduleName;
    private Long season;

   // getters and setters....
}

The error:

2017-09-20 17:35:22.738 ERROR 3283 --- [enerContainer-1] h.c.n.h.NotificationListenerErrorHandler : Error in listener: Listener method 'public void com.club.notification.processor.NotificationProcessor.sqsNotificationEventProcessor(java.lang.String) throws javax.jms.JMSException' threw exception; nested exception is org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.executor.ExecutorException: Error getting generated key or setting result to parameter object. Cause: org.apache.ibatis.binding.BindingException: Parameter 'id' not found. Available parameters are [notifications, param1]

Thanks in advance.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: MyBatys Batch Insert doubt/error retrieving generated id (binding to POJO)

Guy Rouillier-2
The method you are using to insert multiple rows within a values clause is not guaranteed to work for all DBMSs.  You don't mention which one you are using.  However, if your DBMS supports it, then apparently MyBatis does as well; see, for example, https://stackoverflow.com/questions/34305082/mybatis-batch-insert-update-using-annotations.

Hmm, I was going to say I don't see how this can work with your foreach approach, but apparently some people have gotten it to work with MySql:


Looks like your param name for the list you are passing needs to be "list" instead of "notifications".  I found this using Google with "mybatis batch insert usegeneratedkeys".

--
Guy Rouillier

------ Original Message ------
To: "mybatis-user" <[hidden email]>
Sent: 9/20/2017 4:46:08 PM
Subject: MyBatys Batch Insert doubt/error retrieving generated id (binding to POJO)

Hello.
I was wondering if I could get a hint from the group;

I'm trying to performa  batch insert using mybatis. (With Springboot) MyBatis: 3.4.4 and MyBatisSpring

My Mapper
@Insert({
            "<script> " +
                "insert into notification (user_area, agent_id, event, ref, created_at, `read`, received) values (" +
                "<foreach collection='notifications' item='notification' separator='),('> " +
                    "#{notification.userArea}, " +
                    "#{notification.agentId}, " +
                    "#{notification.event, typeHandler=org.apache.ibatis.type.EnumTypeHandler} ," +
                    "#{notification.referenceId}, " +
                    "#{notification.createDate, typeHandler=org.apache.ibatis.type.DateTypeHandler}, " +
                    "#{notification.read, typeHandler=org.apache.ibatis.type.BooleanTypeHandler}, " +
                    "#{notification.received, typeHandler=org.apache.ibatis.type.BooleanTypeHandler} " +
                "</foreach> )" +
            "</script>"
    })
    @Options(useGeneratedKeys = true, keyColumn = "id", keyProperty = "id")
    List<Notification> insertNotificationInBatch(@Param("notifications") List<Notification> notifications);

My Pojo
public class Notification implements Serializable {

    public Long id;
    private long userArea;
    private long agentId;
    private long referenceId;
    private NotificationEvent event;
    private Date createDate;
    private boolean read = false;
    private boolean received = false;


    //Transient
    private String userAreaCode;
    private String agentName;
    private FileMembershipVO agentPicture;
    private Long page;
    private String pageName;
    private String moduleName;
    private Long season;

   // getters and setters....
}

The error:

2017-09-20 17:35:22.738 ERROR 3283 --- [enerContainer-1] h.c.n.h.NotificationListenerErrorHandler : Error in listener: Listener method 'public void com.club.notification.processor.NotificationProcessor.sqsNotificationEventProcessor(java.lang.String) throws javax.jms.JMSException' threw exception; nested exception is org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.executor.ExecutorException: Error getting generated key or setting result to parameter object. Cause: org.apache.ibatis.binding.BindingException: Parameter 'id' not found. Available parameters are [notifications, param1]

Thanks in advance.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: MyBatys Batch Insert doubt/error retrieving generated id (binding to POJO)

rafael.cruz
Hello Guy.
Thank you very much for the reply.
Sorry, I forgot to tell.
I'm using MySQL

And your hint on changing the value inside the collection attribute to "list" worked like a charm.
Thank you too for the google hint. I wasn't getting good results with my queries.

My problem is now solved. MyBatis updates my objects with their respective generated IDs

Thanks a lot!

On Thursday, September 21, 2017 at 2:31:58 AM UTC-3, Guy Rouillier wrote:
The method you are using to insert multiple rows within a values clause is not guaranteed to work for all DBMSs.  You don't mention which one you are using.  However, if your DBMS supports it, then apparently MyBatis does as well; see, for example, <a href="https://stackoverflow.com/questions/34305082/mybatis-batch-insert-update-using-annotations" style="font-size:12pt" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F34305082%2Fmybatis-batch-insert-update-using-annotations\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNEii7X2f5N_kMWKZy9pVZgzB587rw&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F34305082%2Fmybatis-batch-insert-update-using-annotations\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNEii7X2f5N_kMWKZy9pVZgzB587rw&#39;;return true;">https://stackoverflow.com/questions/34305082/mybatis-batch-insert-update-using-annotations.

Hmm, I was going to say I don't see how this can work with your foreach approach, but apparently some people have gotten it to work with MySql:

<a href="https://stackoverflow.com/questions/28453475/mybatis-getting-id-from-inserted-array-of-object-returns-error" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F28453475%2Fmybatis-getting-id-from-inserted-array-of-object-returns-error\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNEdaodu7oXr2WUu9KJJ4dsiOPwYUA&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F28453475%2Fmybatis-getting-id-from-inserted-array-of-object-returns-error\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNEdaodu7oXr2WUu9KJJ4dsiOPwYUA&#39;;return true;">https://stackoverflow.com/questions/28453475/mybatis-getting-id-from-inserted-array-of-object-returns-error

Looks like your param name for the list you are passing needs to be "list" instead of "notifications".  I found this using Google with "mybatis batch insert usegeneratedkeys".

--
Guy Rouillier

------ Original Message ------
From: <a href="javascript:" target="_blank" gdf-obfuscated-mailto="tVYcZeX4CwAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">rafae...@...
To: "mybatis-user" <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="tVYcZeX4CwAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">mybati...@...>
Sent: 9/20/2017 4:46:08 PM
Subject: MyBatys Batch Insert doubt/error retrieving generated id (binding to POJO)

Hello.
I was wondering if I could get a hint from the group;

I'm trying to performa  batch insert using mybatis. (With Springboot) MyBatis: 3.4.4 and MyBatisSpring

My Mapper
@Insert({
            "<script> " +
                "insert into notification (user_area, agent_id, event, ref, created_at, `read`, received) values (" +
                "<foreach collection='notifications' item='notification' separator='),('> " +
                    "#{notification.userArea}, " +
                    "#{notification.agentId}, " +
                    "#{notification.event, typeHandler=org.apache.ibatis.type.EnumTypeHandler} ," +
                    "#{notification.referenceId}, " +
                    "#{notification.createDate, typeHandler=org.apache.ibatis.type.DateTypeHandler}, " +
                    "#{notification.read, typeHandler=org.apache.ibatis.type.BooleanTypeHandler}, " +
                    "#{notification.received, typeHandler=org.apache.ibatis.type.BooleanTypeHandler} " +
                "</foreach> )" +
            "</script>"
    })
    @Options(useGeneratedKeys = true, keyColumn = "id", keyProperty = "id")
    List<Notification> insertNotificationInBatch(@Param("notifications") List<Notification> notifications);

My Pojo
public class Notification implements Serializable {

    public Long id;
    private long userArea;
    private long agentId;
    private long referenceId;
    private NotificationEvent event;
    private Date createDate;
    private boolean read = false;
    private boolean received = false;


    //Transient
    private String userAreaCode;
    private String agentName;
    private FileMembershipVO agentPicture;
    private Long page;
    private String pageName;
    private String moduleName;
    private Long season;

   // getters and setters....
}

The error:

2017-09-20 17:35:22.738 ERROR 3283 --- [enerContainer-1] h.c.n.h.NotificationListenerErrorHandler : Error in listener: Listener method 'public void com.club.notification.processor.NotificationProcessor.sqsNotificationEventProcessor(java.lang.String) throws javax.jms.JMSException' threw exception; nested exception is org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.executor.ExecutorException: Error getting generated key or setting result to parameter object. Cause: org.apache.ibatis.binding.BindingException: Parameter 'id' not found. Available parameters are [notifications, param1]

Thanks in advance.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="tVYcZeX4CwAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">mybatis-user...@googlegroups.com.
For more options, visit <a href="https://groups.google.com/d/optout" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;">https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.