Oracle JDBC 和 Oracle CHAR 数据类型

Question

我在 Oracle JDBC 驱动程序处理 CHAR 数据类型时遇到了一个棘手的问题。让我们看一下这个简单的表：

create table x (c char(4));
insert into x (c) values ('a');  -- inserts 'a   '

因此，当我向 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

这里，常量 '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();

这是一种解决方法：

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();

编辑：现在这些是约束：

• 上面的解决方法是不可取的，因为它修改了 的内容c 和 ?，并且它使得在 c 上使用索引变得非常困难。
• 将列从 CHAR 移动到 VARCHAR （当然，它应该是）是不可能的

编辑：这些约束的原因是因为我从数据库抽象库 jOOQ 的开发人员的角度提出这个问题。所以我的要求是提供一个非常通用的解决方案，不会破坏 jOOQ 客户端代码中的任何内容。这就是为什么我不太喜欢这种解决方法。这就是为什么我无法访问该 CHAR 列的声明。但我仍然希望能够处理这个案子。

你会怎么做？当我想忽略尾随空格时，处理 CHAR 数据类型的好习惯是什么？

Answer 1

如果你想

stmt.setString(1, "a");    // This won't return any records

返回一条记录，请尝试

conn.prepareStatement("select * from x where c = cast(? as char(4))")

Answer 2

我不认为有任何理由使用 CHAR 数据类型，即使它在 Oracle 中是 char(1) 。您可以更改数据类型吗？

Answer 3

加里的解决方案效果很好。这是一个替代方案。

如果您使用的是 Oracle JDBC 驱动程序，则对 prepareStatement() 的调用实际上会返回一个 OraclePreparedStatement，它具有一个 setFixedCHAR() 方法自动用空格填充您的输入。

String sql = "select * from x where c = ?";
OraclePreparedStatement stmt = (OraclePreparedStatement) conn.prepareStatement(sql);
stmt.setFixedCHAR(1, "a");
...

显然，只有使用 Oracle 驱动程序时，转换才是安全的。

我建议您使用这个而不是 Gary 的答案的唯一原因是您可以更改列大小而无需修改 JDBC 代码。驱动程序会填充正确数量的空格，而开发人员无需了解/管理列大小。

Answer 4

我对此有很好的解决办法。从数据库获取连接时，您必须添加一项属性。

NLS_LANG=american_america.AL32UTF8

或者在 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);

Answer 5

另一种方法是将你的sql修改为

select * from x where NVL(TRIM(c),' ') = NVL(TRIM('a'),' ')

Answer 6

只需将 RTRIM() 添加到更新查询中的列名（已定义）即可。