@Param("myParam") String myParam is Casting to NVARCHAR(4000)-CodePudding

I'm writing a simple native query using Hibernate (Spring 2.0.8) but when my param is a String, it is being cast passed as a NVARCHAR(4000), which is leading to performance issues.

Bellow is a sample of my code

@Modifying
@Query(value = "INSERTO INTO MyTable (a, b, c) SELECT :myParam1, :myParam2, a.xpto FROM myTable a WHERE a.id = :myParam3", nativeQuery = true)
void insertMyTable(@Param("myParam1") String myParam1, @Param("myParam2") String myParam2, @Param("myParam3") int myParam3);

The problem is, myParam1 and myParam2 are nvarchar(100) (example), but when we attach a profiler to SQLServer2008 Instance, we found the execution of the following query

@P0 NVARCHAR(4000), @P1 NVARCHAR(4000), @P2 INTEGER

INSERTO INTO MyTable (a, b, c) SELECT @P0, @P1, a.xpto FROM myTable a WHERE a.id = @P2

This is leading to a conversion from NVARCHAR(4000) to NVARCHAR(100) for example and causing performance issues.

Is there anyway to explicity declare @Param with it respective size ? Something like @Param("myParam1", "nvarchar(100)")

CodePudding user response：

You can declare the columnDefinition of that variable as text in model class(with this there is no limit to that variable) like this

    @Column(name = "content", columnDefinition = "text")
    private String content;

or you can directly set the datatype of that variable as text in database.

CodePudding user response：

you can use @Column annotation to specify the data type of column

  @Column(columnDefinition="VARCHAR(40)") 
   private String columnName;

  @Column(columnDefinition="text") 
      private String columnName;