Oracle JDBC 和 Oracle CHAR 数据类型

2021-12-05 00:00:00 char types oracle prepared-statement jdbc

我在 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(4) 中插入一些东西时，字符串总是用空格填充.当我执行这样的查询时也会这样做:

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

编辑:现在这些是限制:

  • 上述解决方法是不可取的，因为它修改了 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))")

相关文章