Oracle JDBC和Oracle CHAR数据类型 [英] Oracle JDBC and Oracle CHAR data type

问题描述

我有一个棘手的问题，Oracle JDBC驱动程序处理 CHAR 数据类型。让我们来看一下这个简单的表：

I have a tricky issue with the Oracle JDBC driver's handling of CHAR data types. Let's take this simple table:

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

所以当我插入 CHAR ），该字符串总是用空格填充。当我执行这样的查询时也是这样：

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:

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 执行时也是如此。现在棘手的事情是当我想使用绑定变量：

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 PreparedStatement as well. Now the tricky thing is when I want to use a bind variable:

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

EDIT ：现在这些是约束：

• 上述解决方法是不可取的，因为它修改 c 和 c <>。 c> c> $ c> CHAR 到 VARCHAR （当然应该是这样）

• The above workaround is not desireable as it modifies both the contents of c and ?, AND it makes using indexes on c quite hard.
• Moving the column from CHAR to VARCHAR (which it should be, of course) is not possible

EDIT ：这些约束的原因是因为我从开发者的角度提出这个问题 jOOQ ，一个数据库抽象库。所以我的要求是提供一个非常通用的解决方案，不会破坏任何东西在jOOQ的客户端代码。这就是为什么我不是真正的解决方法的大粉丝。这就是为什么我没有访问 CHAR 列的声明。但仍然，我想能够处理这种情况。

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 CHAR column's declaration. But still, I want to be able to handle this case.

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

What would you do instead? What's a good practice for handling CHAR data types when I want to ignore trailing whitespace?

推荐答案

如果你想要

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

返回记录，请尝试

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

这篇关于Oracle JDBC和Oracle CHAR数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！