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.