Oracle JDBC PreparedStatement忽略尾随空间 [英] Oracle JDBC PreparedStatement Ignore Trailing Spaces

问题描述

我目前正在编写与Oracle数据库连接的Java Web应用程序.我之所以使用PreparedStatements，是因为Hibernate会使事情变得过于复杂.

I am currently writing a Java web application which interfaces with an Oracle database. I am using PreparedStatements because Hibernate would complicate things too much.

由于正在写入数据库的程序中的错误，我需要搜索的字段具有写入值的尾随空格.我已经用引号将值引起来以演示空白.

Due to a bug in the program which is writing to the database, the field I need to search for has trailing spaces written to the value. I have surrounded the value with quotation marks to demonstrate the whitespace.

"testFTP_receipt521      "

当我使用SQLDeveloper进行选择查询时，我可以在运行时得到结果:

When I do a select query with SQLDeveloper, I am able to get a result when I run:

...and yfs_organization.ORGANIZATION_KEY='testFTP_receipt521';

(无空格)

但是，当我使用PreparedStatement时，尝试时没有任何结果.

However, when I use a PreparedStatement, I get no results when I try:

...and yfs_organization.ORGANIZATION_KEY=?");
preparedStatement.setString(1, "testFTP_receipt521");

(无空格)

当我尝试时:

...and yfs_organization.ORGANIZATION_KEY=?");
preparedStatement.setString(1, "testFTP_receipt521      ");

(带空格)

我是否可以使用PreparedStatement查询此结果，还是应该尝试其他方法?

Are there any ways that I can query for this result with a PreparedStatement, or should I try another approach?

感谢您的所有帮助.

推荐答案

由于正在写入数据库的程序中的错误，我需要搜索的字段带有尾随空格

Due to a bug in the program which is writing to the database, the field I need to search for has trailing spaces

在特定情况下，并且如果您的Oracle版本足够新，则可以考虑添加

Maybe, given the circumstances, and if your version of Oracle is recent enough, you might consider adding a virtual column to your table containing the correct value?

ALTER TABLE yfs_organization ADD (
  ORGANIZATION_KEY_FIXED VARCHAR(80)
    GENERATED ALWAYS AS (TRIM(ORGANIZATION_KEY)) VIRTUAL
  );

然后在您的代码中，唯一的更改是使用ORGANIZATION_KEY_FIXED查询数据库:

Then in your code, the only change will be to use the ORGANIZATION_KEY_FIXED to query the DB:

SELECT ID,ORGANIZATION_KEY_FIXED
  FROM yfs_organization
  WHERE ORGANIZATION_KEY_FIXED='testFTP_receipt521'

(在 http://sqlfiddle.com/#!4/8251d/1 <上尝试/a>)

(try it on http://sqlfiddle.com/#!4/8251d/1)

这可以避免将您的应用程序散布在解决该错误所需的代码上.一旦确定，过渡可能会缓解.

This might avoid to scatter around your application the code required to work around that bug. And might ease the transition once it will be fixed.

作为附加的好处，如果需要，您可以在虚拟列上添加索引.

As an added benefice, you could add index on virtual columns if you need too.

这篇关于Oracle JDBC PreparedStatement忽略尾随空间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！