How to get JodaTime DateTime object from SQLTemplate using SQLResult

Question

I'm trying to return a JodaTime DateTime object. It works for the effective_date field below. But I'm not sure how to get it to return DateTime for the Event object. Any ideas?

String sql = "SELECT a.availability_id, a.event_id, a.availability_set_id, " 
    + "e.event_id, "
    + "#result('e.start' 'org.joda.time.DateTime' '' 'event.start'), "
    + "#result('e.end' 'org.joda.time.DateTime' '' 'event.end'), "
    + "e.recurrence_id, "
    + "e.sequence, "
    + "e.uid, "
    + "#result('effective_date' 'org.joda.time.DateTime' '' 'effective_date'), " 
    + "#result('next_effective_date' 'org.joda.time.DateTime' '' 'next_effective_date') "

<snip>

EntityResult eventResult = new EntityResult(Event.class);
eventResult.addDbField(Event.EVENT_ID_PK_COLUMN, "event_id");
eventResult.addObjectField(Event.START_PROPERTY, "start");
eventResult.addObjectField(Event.END_PROPERTY, "end")

<snip>

SQLResult resultDescriptor = new SQLResult();
resultDescriptor.addEntityResult(availabilityResult);
resultDescriptor.addEntityResult(eventResult);

<snip>
List<Object[]> dataList = context.performQuery(query);
for (Object[] data : dataList) {
  Event event = (Event) data[1];
  event.getStart(); // Runs into class cast error where it's returning Date instead of DateTime
}

EDIT:

I've already added Joda DateTime to the ExtendedType. The issue above only occur when I use SQLResult to build the Event object.

  // create custom ExtendedType instance
  ExtendedType dateTimeType = new DateTimeType();

  DataNode node = domain.getNode("MySQLNode");

  // install ExtendedType
  node.getAdapter().getExtendedTypes().registerType(dateTimeType);

It works well when not using SQLResult to instanciate the Event object. Here, Cayenne Modeler generated the _Event class with the DateTime return type.

public abstract class _Event extends CayenneDataObject {

  <snip>

  public void setStart(DateTime start) {
    writeProperty("start", start);
  }
  public DateTime getStart() {
    return (DateTime)readProperty("start");
  }

Answer 1

I've managed to do the following. I've created DbEntity field with OTHER as DB Type and ObjEntity field with org.joda.time.DateTime as Java Type. And it works great for your example.

Also you can try to use any third-party (or your own) ExtendedType for JodaTime DateTime.

UPDATE 1:

Also if you want to use #result directive, you need to use it for each column in your SQL template.

UPDATE 2:

Here is an example which works fine. Note that I use 4.0.M3-SNAPSHOT version with cayenne-joda module from github.

    // add CayenneJodaModule to the ServerRuntime
    ServerRuntime cayenneRuntime = new ServerRuntime(
            "cayenne-project.xml", new CayenneJodaModule());
    ObjectContext context = cayenneRuntime.newContext();

    // add Joda object
    Joda newJoda = context.newObject(Joda.class);
    newJoda.setDatetime(new DateTime());
    context.commitChanges();

    String sql = "SELECT #result('j.ID' 'int' 'ID'), "
            + "#result('j.DATETIME' 'org.joda.time.DateTime' 'DATETIME') "
            + "FROM JODA j";

    EntityResult jodaResult = new EntityResult(Joda.class);
    jodaResult.addDbField(Joda.ID_PK_COLUMN, "ID");
    jodaResult.addObjectField(Joda.DATETIME_PROPERTY, "DATETIME");

    SQLResult resultDescriptor = new SQLResult();
    resultDescriptor.addEntityResult(jodaResult);

    SQLTemplate query = new SQLTemplate(Joda.class, sql);
    query.setResult(resultDescriptor);

    List<Joda> jodaList = context.performQuery(query);
    for (Joda joda : jodaList) {
        System.out.println(joda.getDatetime().getClass());
    }

Where _Joda.class has:

    public void setDatetime(DateTime datetime) {
        writeProperty("datetime", datetime);
    }
    public DateTime getDatetime() {
        return (DateTime)readProperty("datetime");
    }

Result:

class org.joda.time.DateTime