Get something other than null in one-to-one mapped column?

I have some historical data in a table that links to two other tables with a one-to-one relationship. Except that, since it's historical, the entries in the two other tables don't always exist. Using NotFoundAction.IGNORE means that I get NULL for mediaInfo and/or fileInfo if there is no appropriate record in the referenced table.

So now I need to be able to do an order by fileInfo.fileName and the result set contains only those rows where the reference fileInfo isn't null. Is there a way to make it so that the result set contains all the rows of MigrationHistory, even when fileInfo would normally be null (by having it return a default value instead of null)? Or will it be necessary to do two selects, one that gets all of the rows where fileInfo is not null, and then a second that gets those rows that are null?

The query in this case is a simple Hibernate from MigrationHistory order by fileInfo.fileName asc.

@Entity
@Table(name = "MigrationHistory")
class MigrationHistory extends Serializable with TableObject with MigrationKeys {
  @Id
  @Column(name = "FileLocationID", nullable = false, insertable = true, updatable = true, length = 19, precision = 0)
  @BeanProperty var fileLocationID: Long = _

  @Id
  @Column(name = "MediaInfoID", nullable = false, insertable = true, updatable = true, length = 19, precision = 0)
  @BeanProperty var mediaInfoID: Long = _

  @Column(name = "priority", nullable = false, insertable = true, updatable = true, length = 5, precision = 0)
  @BeanProperty var priority: Short = _

  @Column(name = "isNewPrimary", nullable = false, insertable = true, updatable = true, length = 1, precision = 0)
  @BeanProperty var isNewPrimary: Boolean = _

  @Column(name = "copyOrMove", nullable = false, insertable = true, updatable = true, length = 1, precision = 0)
  @BeanProperty var copyOrMove: String = _

  @Column(name = "migrationStatus", nullable = false, insertable = true, updatable = true, length = 15, precision = 0)
  @BeanProperty var migrationStatus: String = _

  @Column(name = "insertDate", nullable = false, insertable = true, updatable = true, length = 23, precision = 3)
  @BeanProperty var insertDate: Date = _

  @Column(name = "comment", nullable = true, insertable = true, updatable = true, length = 1000, precision = 0)
  @BeanProperty var comment: String = _

  @OneToOne
  @JoinColumn(name = "MediaInfoID", referencedColumnName = "MediaInfoID", insertable = false, updatable = false, nullable = false)
  var mediaInfo: MediaInfo = _

  @OneToOne(optional = true)
  @JoinColumn(name = "FileLocationID", referencedColumnName = "FileInfoID", insertable = false, updatable = false, nullable = true)
  @NotFound(action = NotFoundAction.IGNORE)
  var fileInfo: FileInfo = _
}

@Entity
@Table(name="FileInfo")
class FileInfo extends Serializable {
  @GeneratedValue
  @Column(name = "FileInfoID", nullable = false, insertable = false, updatable = false, length = 19, precision = 0)
  @Id
  @BeanProperty var id: Long = _

  @Column(name = "fileName", nullable = false, insertable = true, updatable = true, length = 30, precision = 0)
  @BeanProperty var fileName: String = _

  @Column(name = "fileSize", nullable = false, insertable = true, updatable = true, length = 7, precision = 0)
  @BeanProperty var fileSize: Float = _
}

Answers


Chris was correct. What I needed was a left outer join. Here is the query that works:

from edu.stsci.aofl.dads.MigrationHistory as mh left outer join mh.fileInfo fi left outer join mh.mediaInfo mi order by fi.fileName asc

One interesting thing about this query, and I'm guessing that it's caused by the three "as" clauses, is that instead of producing a result set consisting of a bunch of MigrationHistory objects, it produces a result set that consists of a bunch of three element arrays. The first element is a MigrationHistory object and the rest are null.

Another interesting thing about this query is that most DBMSes treat null as the lowest possible value. Some DBMSes (such as Derby) provide an option to make them the highest possible value (NULLS FIRST versus NULLS LAST). MS SQL Server does not.


Need Your Help

Gametime in Android

java android

Is there a straight forward way to keep track of game time in Android (java)? For instance in order to keep movement fluid no matter what frame rate, I need to factor in the amount of time which has

Bluetooth sound output on Android

android audio bluetooth

I just noticed, using the HTC Dream that using a bluetooth headset was really easy for phone calls, but nothing seemed to be available for videos / music sound output. V1.0 limitations are not at f...