Unable to make batch insert into Oracle DB using MyBatis

[Tural Muradbeyli]

I try to make batch insert into table at Oracle database using MyBatis (integrated with Spring).

It is a definition of method in mapper interface:

public void savePayments(@Param("payments") List<MassPaymentItem> payments);

It is a MyBatis XML mapper code:

<insert id="savePayments" parameterType="MassPaymentFileItem" useGeneratedKeys="false">

    INSERT INTO mass_payments

        (payment_id, file_id, msisdn, amount, status)

    <foreach collection="payments" item="payment" index="index" separator=" UNION ALL ">

        SELECT SEQ_MASS_PAYMENT.nextval, #{payment.fileId}, #{payment.msisdn}, #{payment.amount}, 0 FROM DUAL

    </foreach>

</insert> 

When I execute this I receive MyBatisSystemException with message "nested exception is org.apache.ibatis.builder.BuilderException: Improper inline parameter map format. Should be: #{propName,attr1=val1,attr2=val2}".

I also tried this variants:

<insert id="savePayments" parameterType="MassPaymentFileItem" useGeneratedKeys="false">

    INSERT INTO mass_payments

    (payment_id, file_id, msisdn, amount, status)

    <foreach collection="payments" item="payment" index="index" separator=" UNION ALL ">

        (SELECT SEQ_MASS_PAYMENT.nextval from DUAL), #{payment.fileId}, #{payment.msisdn}, #{payment.amount}, 0

    </foreach>

</insert>

and

<insert id="savePayments" parameterType="MassPaymentFileItem" useGeneratedKeys="false">

    INSERT INTO mass_payments

    (payment_id, file_id, msisdn, amount, status)

    SELECT SEQ_MASS_PAYMENT.nextval from DUAL, A.* FROM (

    <foreach collection="payments" item="payment" index="index" separator=" UNION ALL ">

        SELECT #{payment.fileId}, #{payment.msisdn}, #{payment.amount}, 0 FROM dual

    </foreach>

    )A)

</insert>

But received same error.

What is wrong? How to fix it?

[Condor]

Your parameterType is not MassPaymentFileItem; it is java.util.Map (which is the default, so you can remove the attribute).

ps. I thought the recommend method in Oracle was:

<insert id="savePayments">
  INSERT ALL
  <foreach collection="payments" item="payment">


    INSERT INTO mass_payments (payment_id, file_id, msisdn, amount, status)


    VALUES (
      seq_mass_payment.nextval,


      #{payment.fileId},
      #{payment.msisdn},
      #{payment.amount},
      0


    )
  </foreach>
  SELECT * FROM DUAL
</insert>

And you might need to specify a jdbcType in all of your parameters for this to work correctly.

[Tural Muradbeyli]

Thank you. You are right, but script should be corrected a bit. This is right script based on your:

<insert id="savePayments">

    INSERT ALL

    <foreach collection="payments" item="payment">

    INTO

        mass_payments_t (payment_id, file_id, msisdn, amount)

    VALUES

        (seq_mass_payment.nextval, #{payment.fileId, javaType=Integer, jdbcType=NUMERIC}, #{payment.msisdn, javaType=String, jdbcType=VARCHAR}, #{payment.amount, javaType=BigDecimal, jdbcType=NUMERIC})

    </foreach>

    SELECT * FROM dual

</insert>