Re: [eclipselink-users] @Converter, Null Values and queries

[James Sutherland <jamesssss@xxxxxxxxx>]

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.

What error do you get with = 0?
A workaround may be to use a certain date to mean null (0/0/0), or use
(o.myColumn + 0) = 0.


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 
-- 
View this message in context: http://old.nabble.com/%40Converter%2C-Null-Values-and-queries-tp32454390p32463522.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.