Search code examples
javaoracle-databaseibatis

Why is String " " being read as NULL when mapping from the database to an object using ibatis?

In my oracle database I have table called PERSON with columns code, surname, forename.

eg. 'PER001', '________________', 'Bob' (NOTE: _ = space)

surname is set to NOT NULL but you can enter in a string of spaces like " ".

I am having a problem with this because when Ibatis maps this column to an Object it is mapping it as NULL! when it should be mapping it as " " ?

Any ideas?

Solution

  • It is one of the quirks of Oracle that it treats empty strings as NULL. This is controversial in some quarters (trying googling on http://www.google.co.uk/search?q=oracle+null+"empty+string" ). Oracle may change this behaviour in the future. Just don't hold your breath.

    Edit

    As has been pointed out, it's not an empty string, it's a string of blanks. Well, in that case it's not Oracle itself.

    SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                     NOT NULL VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> insert into emp (empno, ename) values (9999, '      ')
  2  /

1 row created.


SQL> select length(ename) from emp
  2  where empno = 9999
  3  /

LENGTH(ENAME)
-------------
            6
SQL>

    Possible culprits are:

    1. a trigger applying a TRIM() to the column in question (or some similar processing)

    2. IBATIS intervening in some fashion