使用Hibernate和LEFT JOIN从单独的表加载默认值

I am writing as system to allow notification options to be set by users. I have created two classes, Notification and NotificationOption. Notification is to specify the different events for which a notification is to be sent, as well as default value, and notification option would contain the preferences chosen by the users. These are the hbm files:

我写的系统允许用户设置通知选项。我创建了两个类,Notification和NotificationOption。通知是指定要发送通知的不同事件以及默认值,通知选项将包含用户选择的首选项。这些是hbm文件:

Notification.hbm

<hibernate-mapping>
    <class name="com.entities.Notification" table="engine_notification">
        <id name="id">
            <generator class="sequence">
                <param name="sequence">seq_engine_notification_id</param>
            </generator>
        </id>

        <property name="name" not-null="true"/>
        <property name="descriptor" not-null="true"/>
        <property name="defaultValue" column="default_value"/>
    </class>
</hibernate-mapping>

NotificationOption.hbm

<hibernate-mapping>
    <class name="com.entities.NotificationOption" table="engine_notification_option">
        <composite-id>
           <key-many-to-one name="player"
                     class="com.entities.Profile"
                     foreign-key="id"
                     lazy="false" />
           <key-many-to-one name="notification"
                     class="com.entities.Notification"
                     foreign-key="id"
                     lazy="false" />
       </composite-id>
        <property name="value" not-null="true"/>
    </class>
</hibernate-mapping>

Now I don't want to have to create a row in NotificationOption table every time a new Notification type is created, so I want to be able to execute a query on the Notification table which LEFT JOINs NotificationOption on Notification.id = NotificationOption.notification.

现在我不想在每次创建新的Notification类型时在NotificationOption表中创建一行,所以我希望能够在Notification表上执行查询,该表在Notification.id = NotificationOption.notification上LEFT JOINs NotificationOption 。

Using the following SQL I get the expected result:

使用以下SQL我得到了预期的结果:

SELECT * FROM engine_notification n
LEFT JOIN 
  (SELECT o.* FROM engine_notification_option o WHERE o.player = :playerid) nop
ON n.ID = nop.notification
ORDER BY n.ID;

So I added the following to the hbm file:

所以我在hbm文件中添加了以下内容:

<sql-query name="notificationsForPlayer">
    <return alias="option" class="com.otto.engine.entities.NotificationOption"/>
    <return-join alias="notification" property="option.notification" />
    select
        n.id as {notification.id},
        n.name as {notification.name},
        n.descriptor as {notification.descriptor},
        n.default_Value as {notification.defaultValue},
        nop.player as {option.player},
        nop.value as {option.value}
    from engine_notification n
    left join (select o.* from engine_notification_option o where o.player = :playerID) nop
    on n.id = nop.notification
    order by n.ID
</sql-query>

This however is giving me the following:

然而,这给了我以下内容:

java.sql.SQLException: Invalid column name

Any idea how to fix it, or a different solution to achieve what I'm trying to achieve?

任何想法如何解决它,或者不同的解决方案来实现我想要实现的目标?

Thanks

1 个解决方案

#1

I guess select o.* in the join is the problem. This kind of a join is not really standard.

我想在连接中选择o。*就是问题所在。这种联接并不是真正的标准。

How about this select:

这个选择怎么样:

select
    n.id as {notification.id},
    n.name as {notification.name},
    n.descriptor as {notification.descriptor},
    n.default_Value as {notification.defaultValue},
    nop.player as {option.player},
    nop.value as {option.value}
from engine_notification n
left join engine_notification_option nop
on n.id = nop.notification
where nop.player = :playerID
order by n.ID

#1