Oracle JDBC 和 Oracle CHAR 数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5332845/
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
Oracle JDBC and Oracle CHAR data type
提问by Lukas Eder
I have a tricky issue with the Oracle JDBC driver's handling of CHARdata types. Let's take this simple table:
我在 Oracle JDBC 驱动程序处理CHAR数据类型时遇到了一个棘手的问题。让我们看一下这个简单的表格：
create table x (c char(4));
insert into x (c) values ('a'); -- inserts 'a '
So when I insert something into CHAR(4), the string is always filled with whitespace. This is also done when I execute queries like this:
因此，当我向 中插入内容时CHAR(4)，字符串总是充满空格。当我执行这样的查询时也会这样做：
select * from x where c = 'a'; -- selects 1 record
select * from x where c = 'a '; -- selects 1 record
select * from x where c = 'a '; -- selects 1 record
Here, the constant 'a'is filled with whitespace as well. That's why the record is always returned. This holds true when these queries are executed using a JDBC PreparedStatementas well. Now the tricky thing is when I want to use a bind variable:
在这里，常量'a'也填充了空格。这就是为什么总是返回记录的原因。当使用 JDBC 执行这些查询时也是如此PreparedStatement。现在棘手的是当我想使用绑定变量时：
PreparedStatement stmt =
conn.prepareStatement("select * from x where c = ?");
stmt.setString(1, "a"); // This won't return any records
stmt.setString(1, "a "); // This will return a record
stmt.executeQuery();
This is a workaround:
这是一种解决方法：
PreparedStatement stmt =
conn.prepareStatement("select * from x where trim(c) = trim(?)");
stmt.setString(1, "a"); // This will return a record
stmt.setString(1, "a "); // This will return a record
stmt.executeQuery();
EDIT: Now these are the constraints:
编辑：现在这些是约束：
- The above workaround is not desireable as it modifies both the contents of
cand?, AND it makes using indexes oncquite hard. - Moving the column from
CHARtoVARCHAR(which it should be, of course) is not possible
- 上述解决方法是不可取的，因为它修改了 和 的内容，
c并且?它使得使用索引c变得非常困难。 - 将列从
CHAR到VARCHAR（当然应该是）移动是不可能的
EDIT: The reasons for these constraints is because I ask this question from the point of view of the developer of jOOQ, a database abstraction library. So my requirements are to provide a very generic solution that doesn't break anything in jOOQ's client code. That is why I'm not really a big fan of the workaround. And that's why I don't have access to that CHARcolumn's declaration. But still, I want to be able to handle this case.
编辑：这些限制的原因是因为我从数据库抽象库jOOQ的开发人员的角度提出了这个问题。所以我的要求是提供一个非常通用的解决方案，不会破坏 jOOQ 的客户端代码中的任何内容。这就是为什么我不是这种解决方法的忠实粉丝。这就是为什么我无权访问该CHAR列的声明。但是，我仍然希望能够处理这种情况。
What would you do instead? What's a good practice for handling CHARdata types when I want to ignore trailing whitespace?
你会怎么做？CHAR当我想忽略尾随空格时，处理数据类型的好做法是什么？
采纳答案by Gary Myers
If you want
如果你想
stmt.setString(1, "a"); // This won't return any records
to return a record, try
要返回记录，请尝试
conn.prepareStatement("select * from x where c = cast(? as char(4))")
回答by Piyush Mattoo
I don't see any reason to use CHAR datatype even if it is char(1) in Oracle. Can you change the datatype instead?
我看不出有任何理由使用 CHAR 数据类型，即使它是 Oracle 中的 char(1)。您可以更改数据类型吗？
回答by RustyTheBoyRobot
Gary's solution works well. Here's an alternative.
加里的解决方案运作良好。这是一个替代方案。
If you are using an Oracle JDBC driver, the call to prepareStatement()will actually return an OraclePreparedStatement, which has a setFixedCHAR()method that automatically pads your inputs with whitespace.
如果您使用的是 Oracle JDBC 驱动程序，对 的调用prepareStatement()实际上会返回一个OraclePreparedStatement，它有一个setFixedCHAR()方法可以自动用空格填充您的输入。
String sql = "select * from x where c = ?";
OraclePreparedStatement stmt = (OraclePreparedStatement) conn.prepareStatement(sql);
stmt.setFixedCHAR(1, "a");
...
Obviously, the cast is only safe if you are using the Oracle driver.
显然，转换只有在您使用 Oracle 驱动程序时才是安全的。
The only reason I would suggest that you use this over Gary's answer is that you can change your column sizes without having to modify your JDBC code. The driver pads the correct number of spaces without the developer needing to know/manage the column size.
我建议您使用它而不是 Gary 的答案的唯一原因是您可以更改列大小而无需修改您的 JDBC 代码。驱动程序填充正确数量的空格，而无需开发人员知道/管理列大小。
回答by swapna ailineni
I have nice fix for this. You have to add one property while getting connection from database.
我有很好的解决办法。从数据库获取连接时，您必须添加一个属性。
NLS_LANG=american_america.AL32UTF8
or in Java connection you can use below code:
或在 Java 连接中，您可以使用以下代码：
java.util.Properties info = new java.util.Properties();
info.put ("user", user);
info.put ("password",password);
info.put("fixedString","TRUE");
info.put("NLS_LANG","american_america.AL32UTF8");
info.put("SetBigStringTryClob","TRUE");
String url="jdbc:oracle:thin:@"+serverName;
log.debug("url="+url);
log.debug("info="+info);
Class.forName("oracle.jdbc.OracleDriver");
conn = DriverManager.getConnection(url,info);
回答by u9190867
the other way is modify your sql as
另一种方法是将您的 sql 修改为
select * from x where NVL(TRIM(c),' ') = NVL(TRIM('a'),' ')
