How should I call the following stored procedure(sqlserver 2008 r2 with User-Defined Table Type) in springboot, mybatis with annotation

[Ricky Paranoid]

How should I call the following stored procedure(sqlserver 2008 r2) in springboot, mybatis with annotation

    declare @p1 TbType_Order
    insert into @p1 values('00005',20.01)
    insert into @p1 values('00006',15.99)
    exec Gp_UpdateOrderBillDT @tb=@p1,@intFlag=0,@billNumberId=16,@billType=301

the "TbType_Order" is a User-Defined Table Type

   

    CREATE TYPE [dbo].[TbType_Order] AS TABLE(
        [No] [varchar](50) NULL,
        [Price] [numeric](24, 10) NULL
    )
    GO

I try to call using this way:

 

   public interface BillProcedureMapper {
        @Select({
            "<script>" +
            " declare @p1 TbType_Order " +
            " <foreach item=\"item\" collection=\"details\"> ",
            " insert into @p1 values( #{item.no},#{item.price} )" +
            " </foreach> ",
            " exec Gp_UpdateOrderBillDT " +
                "@tb=@p1," +
                "@intFlag=0," +
                "@billNumberId=#{billNumberId}," +
                "@billType=301; " +
            "</script>"
        })
        @Options(statementType = StatementType.CALLABLE)
        void savePurchaseOrderDetail(
            @Param("billNumberId") Long billNumberId,
            @Param("details") List<DetailDTO> details
        );
    }

but jdbc say "variable @p1 already declared"
and I try use type handler :

    public class TableTypeOrderHandler extends BaseTypeHandler {
        @Override
        public void setNonNullParameter(
            PreparedStatement ps, int i, Object parameter, JdbcType jdbcType
        ) throws SQLException {

            SQLServerDataTable sourceDataTable = new SQLServerDataTable();

            sourceDataTable.addColumnMetadata("No", Types.VARCHAR);

            sourceDataTable.addColumnMetadata("Price", Types.DECIMAL);

            List<DetailDTO> dataList = (List<DetailDTO>) parameter;

            for (DetailDTO o : dataList) {

                sourceDataTable.addRow(
                    o.getNo(),
                    o.getPrice()
                );

            }

            ps.setObject(i, sourceDataTable);
        }

        @Override
        public Object getNullableResult(ResultSet rs, String columnName) throws SQLException {
            return null;
        }

        @Override
        public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
            return null;
        }

        @Override
        public Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
            return null;
        }
    }

then change the select method:

   

    @Select({
        "exec Gp_UpdateOrderBillDT " +
                "@tb=#{details, jdbcType=VARCHAR, typeHandler=com.xxx.TableTypeOrderHandler}," +
                "@intFlag=0," +
                "@billNumberId=#{billNumberId}," +
                "@billType=301; "
    })
    @Options(statementType = StatementType.CALLABLE)
    void savePurchaseOrderDetail(
        @Param("billNumberId") Integer billNumberId,
        @Param("details") List<DetailDTO> details
    );

but jdbc exception is: Operand type conflict: table type is not compatible with int

What should I do to get it right？

[ri yo]

test:

debug:error

run:ok

====

config file :

mybatis.type-handlers-package=com.xxxx.xxxxtypehandler

Ricky Paranoid <rick...@gmail.com> 于 2019年12月21日周六 上午11:56写道：

--
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 mybatis-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/59b00afa-98ac-42e2-9fae-1205f419944c%40googlegroups.com.

[Iwao AVE!]

Just for future reference, I left a comment on the same question on StackOverflow:

https://stackoverflow.com/q/59433324

And the following answer seemed to help (another use case of 'unwrap' method).

https://stackoverflow.com/a/56651222/

[Ricky Paranoid]

Sorry for replying so late
I also asked stackoverflow questions
thanks for your help，it works， great！！！

To unsubscribe from this group and stop receiving emails from it, send an email to mybati...@googlegroups.com.