存储过程的jdbc-varbinary(max)out参数被截断为8000字节

Question

ALTER procedure [User].[pTest]
      @data varbinary(max) out
      ,@name nvarchar(max) = null
      ,@date datetime
as
begin
.
.
.

@Entity
@NamedStoredProcedureQuery(name = "User.getUser", procedureName = "User.pTest", parameters = {
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "data", type = byte[].class),  
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "name", type = String.class),
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "date", type = Date.class)
          })
@Data //lombok
public class User {


    // serves no purpose other than to meet
    // JPA requirement
    @Id
    private Long id;
}

存储库代码是

public interface UserRepository  extends Repository<User, Long> {

    @Procedure("User.pTest")
    byte[] getUserSession(@Param("name") String name, 
            @Param("date") Date date
            );

}

我的测试代码如下所示，当我运行它时，我会得到错误：

@Test
public void testGettingApxSession() {
    Calendar cal = new GregorianCalendar(2016,6,5);
    byte[] b = userRepository.getUserSession("myName", cal.getTime());
}

当我注销@data时，使用：

    StringBuilder sb = new StringBuilder();
    for (byte a : b) {
        sb.append(String.format("%02X ", a));
    }

Answer 1

使用Microsoft的JDBC驱动程序并将输出参数类型指定为blob:

@Entity
@NamedStoredProcedureQuery(name = "User.getUser", procedureName = "User.pTest", parameters = {
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "data", type = Blob.class),  
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "name", type = String.class),
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "date", type = Date.class)
          })

注意：这将不适用于JTDS。它仍然会截断任何大于8000字节的内容。

如果需要将blob传递回SQL Server，则必须按以下方式转换回字节数组：

byte[] bytes = blob.getBytes(1, (int) blob.length());