Oracle JDBC PreparedStatement Ignore Trailing Spac

问题:

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      "

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';

(no whitespace)

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

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

(no whitespace)

and when I try:

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

(with whitespace)

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

Thanks for all your help.

回答1:

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

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

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'

(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.

回答2:

Maybe you can use it like this...

...and yfs_organization.ORGANIZATION_KEY like '%testFTP_receipt521%';

this way returns you all reg where contains 'testFTP_receipt521' independently of whitespace.

Antoher thing that i saw in your code in this part

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

i thing this is the correct way

...and yfs_organization.ORGANIZATION_KEY='?'");

you need to put quotes around the criteria

回答3:

If you have the ability to modify the query, you can TRIM(...) the column value and perform the comparison. For example:

...and TRIM(yfs_organization.ORGANIZATION_KEY)=?");

Hope it helps.