0

I have to use an existing Database that I can not change that uses this column definition:

"birthdate" date NOT NULL DEFAULT '0000-00-00'

The default value is not a valid date:

Exception in thread "main" java.lang.RuntimeException: Cannot convert value '0000-00-00' from column 29 to TIMESTAMP.

Is there a way to return null from ormlite if the default value '0000-00-00' is found?

skaffman
  • 398,947
  • 96
  • 818
  • 769
user1239866
  • 3
  • 2

3 Answers3

1

The only way you can do this is with a custom persister. You can then control how ORMLite stores and retrieves data from the database. This feature is [unfortunately] not well documented.

You specify the class like this:

@DatabaseField(persisterClass = MyDatePersister.class)
Date birthDate;

Then your persister class might look like:

public class MyDatePersister extends com.j256.ormlite.field.types.BaseDataType {
    private static final MyDatePersister singleTon = new MyDatePersister();
    @SuppressWarnings("deprecation")
    private static final Timestamp ZERO_TIMESTAMP = new Timestamp(0, 0, 0, 0, 0, 0, 0);

    private MyDatePersister() {
        super(SqlType.DATE, new Class<?>[] { Date.class });
    }

    public static MyDatePersister getSingleton() {
        return singleTon;
    }

    @Override
    public Object resultToSqlArg(FieldType fieldType, DatabaseResults results,
            int columnPos) throws SQLException {
        Timestamp timestamp = results.getTimestamp(columnPos);
        if (timestamp == null || ZERO_TIMESTAMP.equals(timestamp)) {
            return null;
        } else {
            return timestamp;
        }
    }
}

DateType source is online. Hope this helps.

Gray
  • 115,027
  • 24
  • 293
  • 354
  • The exception already happens when getTimestamp(columnPos) is called - so im catching it there. Thanx for your help. – user1239866 Mar 01 '12 at 16:14
0

There is another way to do this. For JDBC connection String add following: zeroDateTimeBehavior=convertToNull.

So after adding this to connection string, it will look like as following: jdbc:mysql://:/?zeroDateTimeBehavior=convertToNull

hth,
Narendra

Narendra
  • 5,635
  • 10
  • 42
  • 54
0

How about changing your birthdate from a NOT NULL DEFAULT '0000-00-00' to allow NULL values then you do not have to handle the '0000-00-00' since from your information the birth date can be null.

Stephen Senkomago Musoke
  • 3,528
  • 2
  • 29
  • 27
  • I think the point is that s/he is working with an existing schema and data set. – Gray Feb 29 '12 at 16:07
  • @Gray Just trying to think outside the box, using the principle that a "simple" schema change would save her from writing more code which also needs to be tested, debugged and maintained – Stephen Senkomago Musoke Feb 29 '12 at 18:31
  • @ssmusoke - of course that would be the obvious choice. But a lot of systems and programming languages use those tables and I can not be sure about the side effects. – user1239866 Mar 01 '12 at 16:24