通过mybatis将Java对象列表发送到SQL存储过程?

问题描述

我之前曾问过这个问题-最终并没有一个可行的解决方案，那可能是我自己的问题，因为当时我还不知道我到底想干什么.

I've asked this question before - didn't really end up with a working solution, and that might have been somewhat my own problem for not really knowing at the time, what the hell I wanted to do.

请不要将其标记为重复项，因为我打算在其中提供更好的信息.

Please don't flag this as a duplicate, as I intend including better information on it.

这是我的映射器xml:

Here is my mapper xml :

<select id="deleteItems" resultMap="BaseResultMap">
    exec [dbo].[PR_ItemsForDeletion_Delete]
</select>

现在，我们相信这里可以忽略结果图-因为我不担心自己得到的回报(还！)

Now, we can ignore the result map here I believe - because I am not concerned with what I am getting back (yet!)

现在，看到该存储过程了吗?我没有编写它-而且我也没有访问代码的权限-但我确实知道它有效.

Now, see that stored procedure? I didn't write it - and I also don't have access to the code for that - but I do know for a fact that it works.

存储过程采用项目列表.我需要输入以下内容:

The stored procedure takes a list of items. I need to pass in the following :

List<CustomDeletableObject>

相信该存储程序可以正常工作，并且使用映射器，我只想执行该映射XML-并将一个列表传递给它，但是我绝对不知道从哪里开始.

Trusting that the sproc works, and with the mapper I just want to execute that mapping XML - and pass a list to it, but I have ABSOLUTELY no idea where to begin with that.

事先非常感谢，我已经将头撞了几个小时了.

Massive thankyou in advance, I have been banging my head against this one for hours now.

自定义可删除对象类型包含两个属性:

The custom deletable object type contains two properties :

私人长ID-这只是我们使用的ID.私人Sting令牌-我们实际存储的数据.

private long id - which is just an id we use. private Sting token - the data we are actually storing.

存储过程，我无权访问查询本身-但本质上是，它删除了表中与我们要通过myBatis提供给它的列表中的某项匹配的表中的每一项.

the Stored procedure, I don't have access to the query itself - but essentially what it does, is deletes every item in a table which has a match to an item in the list we want to provide to it via myBatis.

这就是我计划调用映射器的方式...

This is how I am planning to call the mapper...

@Override
public boolean deleteItems(List<CustomDeletableObject> tokens){

    // This method handles the deletion.
    jobMapper.deleteItems();

    return false;
}

这是我要使用的存储过程:为了安全起见，表名已替换为废话TABLE标记.

Here is the stored procedure I am trying to use : In the interest of security, the table names have been replaced with nonsense TABLE tags.

CREATE PROCEDURE [dbo].[PR_ItemsForDeletion_Delete]
    @pTokenList dbo.UDT_TokenDeletion READONLY
AS

BEGIN
    BEGIN TRY
        IF EXISTS (SELECT TOP 1 1 FROM @pTokenList)
            BEGIN
                INSERT INTO <TABLE> (Token)
                SELECT
                    Token
                FROM @pTokenList tl
                WHERE NOT EXISTS
                (
                    SELECT TOP 1 1
                    FROM <TABLE> tp
                    WHERE tp.Token = tl.Token
                )
            END
    END TRY
    BEGIN CATCH
    ;THROW
    END CATCH
END

推荐答案

您将需要扩展BaseTypeHandler，尤其是setNonNullParameter方法. 这里是一个例子，还不错，但是请查找更多.

You will need to extend BaseTypeHandler and specially setNonNullParameter method. Here is example, not so bad, but look up for more.

您似乎需要的(接近)表值参数

It seems what you need is (close to) Table-Valued Parameter

这是放入类型处理程序方法中的特定于驱动程序的JDBC代码.

This is is driver specific JDBC code to put in the type handler method.

在调用的参数绑定中引用类型处理程序.

Reference the type handler in the parameter binding in call.

<delete id="deleteItems">
    exec dbo.PR_ItemsForDeletion_Delete #{list, typeHandler=com.fully.qualified.name.TokenListTypeHandler}
</delete>

从理论上讲，本意是删除，即使没有技术影响，也应更好地使用关键字.如果没有结果要映射，则无需结果映射.

Semantically the intention is deleting, better use the keyword even though there is no technical impact. no need for result map if there is no result to map.

这篇关于通过mybatis将Java对象列表发送到SQL存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！