java传入对象进存储过程,通过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.

Here is my mapper xml :

exec [dbo].[PR_ItemsForDeletion_Delete]

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

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 :

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

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 tokens){

// This method handles the deletion.

jobMapper.deleteItems();

return false;

}

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

SELECT

Token

FROM @pTokenList tl

WHERE NOT EXISTS

(

SELECT TOP 1 1

FROM

WHERE tp.Token = tl.Token

)

END

END TRY

BEGIN CATCH

;THROW

END CATCH

END

解决方案

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

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

Reference the type handler in the parameter binding in call.

exec dbo.PR_ItemsForDeletion_Delete #{list, typeHandler=com.fully.qualified.name.TokenListTypeHandler}

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.