CODEWITHSUNDEEP
javaoracle-databasejdbc
user1472409
user1472409

Reputation: 407

Oracle JDBC PreparedStatement Ignore Trailing Spaces

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.

Upvotes: 0

Views: 1362

Answers (3)

Chris
Chris

Reputation: 1

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.

Upvotes: 0

Sylvain Leroux
Sylvain Leroux

Reputation: 52040

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.

Upvotes: 1

nomarlegnar
nomarlegnar

Reputation: 1

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

Upvotes: 0

Related Questions