mysql - eclipselink MySQL左连接错误: unknown column

Question

我有这个数据模型:

AbstractEntity (abstract, @MappedSuperClass)
    |
    +---- Subject (abstract, @Entity, joined)
    |       |
    |       +---- Person (@Entity)
    |       |
    |       +---- ...
    |
    +---- Metadata (abstract, @Entity, joined)
            |
            +---- ...

实现方式:

@MappedSuperclass
public abstract class AbstractEntity implements Serializable
{
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(nullable = false)
    protected Long id;

    @Version
    protected Integer version;

    ...
}

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
public abstract class Subject extends AbstractEntity
{
    @ManyToOne
    @JoinColumn(name = "PROFILE_ID")
    protected Profile profile;

    @ElementCollection
    @CollectionTable(name = "SUBJECT_RIGHTS", joinColumns = @JoinColumn(name = "OWNER_ID"))
    @Column(name = "CODE")
    protected Set<String> rightSet = new HashSet<String>();

    @Transient
    public abstract String getTitle();

    ...
}

@Entity
public class Person extends AbstractEntity
{
    @NotBlank
    private String firstName;

    @NotBlank
    private String lastName;

    ...
}

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
public abstract class Metadata extends AbstractEntity
{
    @ElementCollection
    @CollectionTable(name = "METADATA_PREFERENCE", joinColumns = @JoinColumn(name = "METADATA_ID"))
    @MapKeyJoinColumn(name = "PERSON_ID", nullable = false)
    @Enumerated(EnumType.STRING)
    @Column(name = "PREFERENCE", nullable = false)
    protected Map<Person, PreferenceType> preferenceMap = new LinkedHashMap<Person, PreferenceType>();

    ...
}

我正在尝试执行此查询:

select m.id from Metadata m left join m.preferenceMap p [where not important]

然后 eclipselink 生成这个 SQL:

SELECT t0.ID FROM METADATA t0 LEFT OUTER JOIN METADATA_PREFERENCE t3 ON ((t3.METADATA_ID = t0.ID) AND (t1.ID = t3.PERSON_ID)), PERSON t2, SUBJECT t1 [where not important]

但是这个查询产生了一个异常:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 't1.ID' in 'on clause'

我知道 MySQL 连接优先级，所以我需要 eclipselink 以这种方式重写查询:

SELECT t0.ID FROM (METADATA t0, SUBJECT t1) LEFT OUTER JOIN METADATA_PREFERENCE t3 ON ((t3.METADATA_ID = t0.ID) AND (t1.ID = t3.PERSON_ID)), PERSON t2 [where not important]

这是怎么做到的？？？

但是我正在使用:

mysql:
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 1.1.7                        |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.5.13-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86                          |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+

eclipselink: 
Eclipse Persistence Services - 2.4.1.v20121003-ad44345

这是 persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
    <persistence-unit name="prime" transaction-type="JTA">
        <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
        <jta-data-source>jdbc/prime</jta-data-source>
        <exclude-unlisted-classes>false</exclude-unlisted-classes>
        <properties>
            <property name="eclipselink.target-database" value="MySQL"/>
            <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/>
            <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/prime"/>
            <property name="javax.persistence.jdbc.user" value="root"/>
            <property name="javax.persistence.jdbc.password" value="password"/>
        </properties>
    </persistence-unit>
</persistence>

Answer 1

加入复杂的 Map ElementCollection 似乎变得很困惑。似乎是一个错误，请记录错误并为其投票，同时确保您使用的是最新的补丁版本。

您可以将 ElementCollection 作为 OneToMany 映射到具有人物和类型的 Preference 对象。这将是一个更直接的模型。

如果您不需要外连接(或者如果您需要外连接语义，则可能使用子查询)，内连接也可能有效。