Skip to content
Advertisement

Spring Data @Query use VARCHAR instead of NVARCHAR

The code that I posted is not the code that our companies uses, it is just example code to correctly illustrate the problem.

I have the following Spring Data Query:

@Query(value = "FROM CustomerTable WHERE Name.firstName = ?1 AND Name.lastName = ?2")
List<Customer> findByName(String firstName, String lastName);

For entity Customer:

public class Customer {
  Name name;
  // ...
}

and Name:

public class Name {
  String firstName;
  String lastName;
  // ...
}

This works okay, but Spring data passes these parameters as NVARCHAR instead of VARCHAR. Therefore, the DB has to perform expensive conversion that is very expensive.

How can I make sure that Spring Data passes these parameters as VARCHAR instead of NVARCHAR?

EDIT: The way I know that it is being sent in as NVARCHAR is by using the SQL Profiler. This provides me with this info:

exec sp_executesql N'select customer.id as id1_7_, ...
from customers custome0_ where custome0_.first_name=@P0 and custome0_.last_name=@P1',N'@P0 nvarchar(4000),@P1 nvarchar(4000)',N'Tom',N'Cruise'

Advertisement

Answer

I managed to circumvent the expensive conversion by changing the column types in the database to NVARCHAR, but then I also had to add a @Type annotation to the Name instance variable on my Customer entity:

public class Customer {
  @Type(type="org.hibernate.type.StringNVarcharType")
  Name name;
  // ...
}

This works, but it’s not the preferred solution, as it is not part of the JPA specification.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement