java 为什么 PreparedStatement.setNull 需要 sqlType？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4243513/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why does PreparedStatement.setNull requires sqlType?
提问by MosheElisha
According to the java docs of PreparedStatement.setNull: "Note: You must specify the parameter's SQL type". What is the reason that the method requires the SQL type of the column?
I noticed that passing java.sql.Types.VARCHAR also works for non-varchar columns. Are there scenarios in which VARCHAR won't be suitable (certain column types or certain DB providers)?
根据 PreparedStatement.setNull 的 java 文档：“注意：您必须指定参数的 SQL 类型”。该方法需要列的SQL类型的原因是什么？
我注意到传递 java.sql.Types.VARCHAR 也适用于非 varchar 列。是否存在不适合 VARCHAR 的场景（某些列类型或某些数据库提供程序）？
Thanks.
谢谢。
采纳答案by Sanjay T. Sharma
According to the java docs of PreparedStatement.setNull: "Note: You must specify the parameter's SQL type". What is the reason that the method requires the SQL type of the column?
根据 PreparedStatement.setNull 的 java 文档：“注意：您必须指定参数的 SQL 类型”。该方法需要列的SQL类型的原因是什么？
For maximum compatibility; as per the specification, there are some databases which don't allow untyped NULL to be sent to the underlying data source.
为了最大的兼容性；根据规范，有些数据库不允许将无类型的 NULL 发送到基础数据源。
I noticed that passing java.sql.Types.VARCHAR also works for non-varchar columns. Are there scenarios in which VARCHAR won't be suitable (certain column types or certain DB providers)?
我注意到传递 java.sql.Types.VARCHAR 也适用于非 varchar 列。是否存在不适合 VARCHAR 的场景（某些列类型或某些数据库提供程序）？
I don't think that sort of behaviour really is part of the specification or if it is, then I'm sure there is some sort of implicit coercion going on there. In any case, relying on such sort of behaviour which might break when the underlying datastore changes is not recommended. Why not just specify the correct type?
我不认为这种行为真的是规范的一部分，或者如果是，那么我确定那里存在某种隐式强制。在任何情况下，都不推荐依赖这种在底层数据存储更改时可能会中断的行为。为什么不指定正确的类型？
回答by user250343
JDBC drivers appear to be moving away from setNull. See Add support for setObject(<arg>, null).
JDBC 驱动程序似乎正在远离setNull. 请参阅添加对 setObject(<arg>, null) 的支持。
My list of databases supporting the more logical behaviour is:
我支持更合乎逻辑的行为的数据库列表是：
- Oracle
- MySQL
- Sybase
- MS SQL Server
- HSQL
- 甲骨文
- MySQL
- 赛贝斯
- 微软 SQL 服务器
- HSQL
My list of databases NOT supporting this logical behaviour is:
我的不支持这种逻辑行为的数据库列表是：
- Derby Queries with guarded null Parameter fail
- PostgreSQL Cannot pass null in Parameter in Query for ISNULLSuggested solution
- 带有受保护的空参数的德比查询失败
- PostgreSQL无法在查询中的参数中为 ISNULL 传递 null建议的解决方案
回答by Lasse Jenssen
When it comes to Oracle it would be very unwise to use varchar2 towards other datatypes. This might fool the optimizer and you could get an bad execution plan. For instance filtering on a date column using a timestamp datatype in your bind, Oracle could end up reading all your rows converting all dates to timestamp, then filtering out the wanted rows. If you have a index on your date column, it could even get worse (if oracle chose to use it) - doing single reads on your oracle blocks.
当涉及到 Oracle 时，将 varchar2 用于其他数据类型是非常不明智的。这可能会愚弄优化器，您可能会得到一个糟糕的执行计划。例如，在绑定中使用时间戳数据类型对日期列进行过滤，Oracle 最终可能会读取所有行，将所有日期转换为时间戳，然后过滤掉所需的行。如果您的日期列上有一个索引，它甚至可能变得更糟（如果 oracle 选择使用它） - 对您的 oracle 块进行单次读取。
--Lasse
--拉瑟
