Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] @Converter, Null Values and queries 
Am 14.09.2011 15:36, schrieb James Sutherland:


You could log a bug that the converter should be used before the = null is
changed to is null, but nulls can be difficult to deal with.


Ahem.. opposite Way.
As I see, JPA should check for Queries with "IS NULL" on a column with a @Converter which replaces null values by other Objects and - if there is one - replace that condition by something 
like " = Converter.objectToDataValue(null)".


What error do you get with = 0?
No error, this just gives no hit (or, Hits on Columns with real NULL in the Database, but not those with 0) 


A workaround may be to use a certain date to mean null (0/0/0), or use
(o.myColumn + 0) = 0.
Hm my most working-well Approach seems to be a "WHERE o.col o.col = :null" in Conjunction with query.setParameter("null", null) - this triggers the @Converter and looks correctly for "=0" in sense of native SQL. But it is not very nice since I have to call Query.setParameter everywhere where the query is used, so this workaround is intransparent. 


eschwenk wrote:


Hello,

Is there any Way to tell JPA to compile a "IS NULL" in a @NamedQuery to
a "= 0" at SQL Level towards the Database?

Background:
I have to work with a legacy Table on an Oracle DB. There is a Column
defined as NUMBER(8) which holds date values written by some legacy
Software which is still used. This legacy Software writes a value of 0
instead of NULL.

Now I have to query, insert into and update this Table with JPA. I
generally would like to treat the Values of that column as
java.util.Date Objects.

My first Step was this:

// Column Definition in Entity Bean
@Column
@Converter(name="MyConverter", converterClass = "MyConverter")
@Convert("MyConverter")
Date myColumn;

// Converter Class
public class MyConverter
extends org.eclipse.mappings.converters.Converter {

        private SimpleDateFormat dateFormat;
        private NumberFormat numberFormat;


public Object convertObjectValueToDataValue(Object o, Session sn) {
    if (o == null) return 0; // Null Value is 0

    try {
      if (o instanceof Date) {
        return numberFormat.format(dateFormat.format((Date) o));
      }
    } catch (Exception e) {
      e.printStackTrace(System.err);
    }

    // o is no Date and not null ->  must not happen!
    throw new IllegalArgumentException(
      "object is not a Date and not null"
    );
}

public Object convertDataValueToObjectValue(Object o, Session sn) {
    try {
      if (o instanceof Number) {
        Number n = (Number) o;
        if (n.doubleValue() == 0) return null;
        return dateFormat.parse(numberFormat.format(n));
      }
    } catch (Exceptino e) {
      e.printStackTrace(System.err);
    }

    // o is no Number and not null ->  must not happen!
    throw new IllegalArgumentException(
      "object is not a Number and not null"
    );
}

public boolean isMutable() {
    return false;
}

public void initialize(DatabaseMapping dm, Sessino sn) {
    numberFormat = new DecimalFormat("00000000");
    dateFormat = new SimpleDateFormat("yyyyMMdd");
}

}

Now, em.persist() writes 0 into the Database if myColumn of Object is
Null and em.find() returns Objects with Null in myColumn for Rows where
myColumn is "0".

Everything works fine except JPA Queries using "IS NULL" - and I am even
not able to solve that using "= 0". It seems that Eclipselink is not
using the @Converter in Conjunction with IS NULL on a @Column.

Is there any simple way to fix this? What is the best workaround with
eclipselink (I have to use the Version bundled with NetBeans 7.0.1)?

--
Erhard Schwenk

Akkordeonjugend Baden-Württemberg - http://www.akkordeonjugend.de/
APAYA running System - http://www.apaya.net/





-----
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland
http://www.eclipse.org/eclipselink/
  EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/
TopLink
Wiki:  http://wiki.eclipse.org/EclipseLink EclipseLink ,
http://wiki.oracle.com/page/TopLink TopLink
Forums:  http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink ,
http://www.nabble.com/EclipseLink-f26430.html EclipseLink
Book:  http://en.wikibooks.org/wiki/Java_Persistence Java Persistence
Blog:  http://java-persistence-performance.blogspot.com/ Java Persistence
Performance



--
Erhard Schwenk

Akkordeonjugend Baden-Württemberg - http://www.akkordeonjugend.de/
APAYA running System - http://www.apaya.net/

Back to the top