java - sqlserver : what are the differences between execute sql with jdbc driver and execute with sql client

Question

我有一个名为“T_ROLE”的表，它只有一个名为“NAME”的列，类型是 nvarchar(255)，sqlserver 排序规则是“SQL_Latin1_General_CP1_CI_AS”(en_US)，现在我想插入日文字符，所以我知道我需要像这样执行 sql:

INSERT INTO T_ROLE(NAME) VALUES(N'japaneseString')

这样可以成功。

如果我做 sql:

INSERT INTO T_ROLE(NAME) VALUES('japaneseString')

如果没有 N 前缀，它将保存为 '?'，我可以在这些行为下。

但是当我使用 sqlserver jdbc 驱动程序做这样的插入操作时:

String sql = "INSERT INTO T_ROLE (NAME) VALUES(?)";
stmt.setString(1, "");
stmt.execute(sql);

注意:我没有使用stmt.setNString()方法，但是可以保存成功，为什么？

Answer 1

查看此博客:https://blogs.msdn.microsoft.com/sqlcat/2010/04/05/character-data-type-conversion-when-using-sql-server-jdbc-drivers/

It turns out that the JDBC driver sends character data including varchar() as nvarchar() by default. The reason is to minimize client side conversion from Java’s native string type, which is Unicode.

So how do you force the JDBC driver not to behave this way? There is a connection string property, named sendStringParametersAsUnicode. By default it’s true.

One would ask what if I want to pass both varchar and nvarchar parameters at the same time? Well, even with the connection property set false, you can explicitly specify nvarchar type like this:

pStmt.setObject(2,Id,Types.NVARCHAR); //Java app code 

简单 Google search对于 sql server jdbc nvarchar 找到了这个答案。