Oracle JDBC and Oracle CHAR data type
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 '
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
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();
This is a workaround:
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: Now these are the constraints:
- The above workaround is not desireable as it modifies both the contents of
c
and?
, AND it makes using indexes onc
quite hard. - Moving the column from
CHAR
toVARCHAR
(which it should be, of course) is not possible
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.
What would you do instead? What's a good practice for handling CHAR
data types when I want to ignore trailing whitespace?
If you want
stmt.setString(1, "a"); // This won't return any records
to return a record, try
conn.prepareStatement("select * from x where c = cast(? as char(4))")
I don't see any reason to use CHAR datatype even if it is char(1) in Oracle. Can you change the datatype instead?