OneToMany join on non PK Composite key - not mapped to a single property

Hi,

I have two schemas on oracle DB. First with EntityA and second with EntityB. In each schemas tables have composite primary key (id and tenantId). But tables between schemas are connected with naturalId and tenantId. I want to join these tables on tentant_id and natural_id. When i’m trying this i got this exception.
It legacy DB and I cant change it.

Maybe similar: JPA error - "not mapped to a single property"

Caused by: org.hibernate.AnnotationException: referencedColumnNames(TENANT_ID, TAX_NUMBER) of org.hibernate.bugs.NonPkCompositeKey$EntityB.entityBSet referencing org.hibernate.bugs.NonPkCompositeKey$EntityA not mapped to a single property
	at org.hibernate.cfg.BinderHelper.createSyntheticPropertyReference(BinderHelper.java:321)
	at org.hibernate.cfg.annotations.CollectionBinder.bindCollectionSecondPass(CollectionBinder.java:1611)

Code:

package org.hibernate.bugs;

import java.io.Serializable;
import java.util.Set;
import javax.persistence.Column;
import javax.persistence.Embeddable;
import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import javax.persistence.JoinColumn;
import javax.persistence.JoinColumns;
import javax.persistence.OneToMany;
import javax.persistence.Persistence;
import org.junit.Before;
import org.junit.Test;

public class NonPkCompositeKey {

  @Before
  public void init() {
    Persistence.createEntityManagerFactory("templatePU");
  }

  @Test
  public void mappingTest() throws Exception {
  }

  @Entity
  public static class EntityA {

    @EmbeddedId
    private TenantId id;
    @Column(name = "TAX_NUMBER")
    private Long entityB_businnesId;
    @OneToMany
    @JoinColumns({
        @JoinColumn(name = "TENANT_ID", referencedColumnName = "TENANT_ID"),
        @JoinColumn(name = "TAX_NUMBER", referencedColumnName = "TAX_NUMBER")
    })
    private Set<EntityB> entityBSet;
  }

  @Entity
  public static class EntityB {

    @EmbeddedId
    private TenantId id;
    @Column(name = "TAX_NUMBER")
    private Long entityB_businnesId;
  }

  @Embeddable
  public static class TenantId implements Serializable {

    private Long id;
    @Column(name = "TENANT_ID")
    private Long tenantId;
  }
}
<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
             http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"
             version="2.1">

    <persistence-unit name="templatePU" transaction-type="RESOURCE_LOCAL">

        <description>Hibernate test case template Persistence Unit</description>
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>

        <exclude-unlisted-classes>false</exclude-unlisted-classes>

        <properties>
            <property name="hibernate.archive.autodetection" value="class, hbm"/>

            <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect"/>
            <property name="hibernate.connection.driver_class" value="org.h2.Driver"/>
            <property name="hibernate.connection.url" value="jdbc:h2:mem:db1;DB_CLOSE_DELAY=-1"/>
            <property name="hibernate.connection.username" value="sa"/>

            <property name="hibernate.connection.pool_size" value="5"/>

            <property name="hibernate.show_sql" value="true"/>
            <property name="hibernate.format_sql" value="true"/>
            <property name="hibernate.hbm2ddl.auto" value="create-drop"/>

            <property name="hibernate.max_fetch_depth" value="5"/>

            <property name="hibernate.cache.region_prefix" value="hibernate.test"/>
            <property name="hibernate.cache.region.factory_class"
                      value="org.hibernate.testing.cache.CachingRegionFactory"/>

            <!--NOTE: hibernate.jdbc.batch_versioned_data should be set to false when testing with Oracle-->
            <property name="hibernate.jdbc.batch_versioned_data" value="true"/>

            <property name="javax.persistence.validation.mode" value="NONE"/>
            <property name="hibernate.service.allow_crawling" value="false"/>
            <property name="hibernate.session.events.log" value="true"/>
        </properties>

    </persistence-unit>
</persistence>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>org.hibernate.testcasetemplate</groupId>
	<artifactId>test-case-template-hibernate-orm5</artifactId>
	<version>1.0.0.Final</version>
	<name>Hibernate ORM 5 Test Case Template</name>

	<properties>
		<version.com.h2database>1.3.176</version.com.h2database>
		<version.junit>4.12</version.junit>
		<!--<version.org.hibernate>5.3.1.Final</version.org.hibernate>-->
		<version.org.hibernate>5.2.17.Final</version.org.hibernate>
		<version.org.slf4j>1.7.2</version.org.slf4j>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-core</artifactId>
			<version>${version.org.hibernate}</version>
		</dependency>
		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-testing</artifactId>
			<version>${version.org.hibernate}</version>
		</dependency>

		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<version>${version.com.h2database}</version>
		</dependency>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>${version.junit}</version>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-log4j12</artifactId>
			<version>${version.org.slf4j}</version>
		</dependency>

		<!-- Not necessary for ORM 5.2 and above -->
		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-entitymanager</artifactId>
			<version>${version.org.hibernate}</version>
		</dependency>
		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-java8</artifactId>
			<version>${version.org.hibernate}</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.1</version>
				<configuration>
					<source>1.8</source>
					<target>1.8</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

when i change join to PK it works:

package org.hibernate.bugs;

import java.io.Serializable;
import java.util.Set;
import javax.persistence.Column;
import javax.persistence.Embeddable;
import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import javax.persistence.JoinColumn;
import javax.persistence.JoinColumns;
import javax.persistence.OneToMany;
import javax.persistence.Persistence;
import org.junit.Before;
import org.junit.Test;

public class NonPkCompositeKey {

  @Before
  public void init() {
    Persistence.createEntityManagerFactory("templatePU");
  }

  @Test
  public void mappingTest() throws Exception {
  }

  @Entity
  public static class EntityA {

    @EmbeddedId
    private TenantId id;
    @Column(name = "TAX_NUMBER")
    private Long entityB_businnesId;
    @OneToMany
    @JoinColumns({
        @JoinColumn(name = "TENANT_ID", referencedColumnName = "TENANT_ID"),
        @JoinColumn(name = "ID", referencedColumnName = "ID")
    })
    private Set<EntityB> entityBSet;
  }

  @Entity
  public static class EntityB {

    @EmbeddedId
    private TenantId id;
    @Column(name = "TAX_NUMBER")
    private Long entityB_businnesId;
  }

  @Embeddable
  public static class TenantId implements Serializable {

    private Long id;
    @Column(name = "TENANT_ID")
    private Long tenantId;
  }
}

The @OneToMany is for mapping child entities, meaning that the EntityB is a child.

Therefore, you need to add a @ManyToOne association in EntityB:

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumns({
	@JoinColumn(name = "TENANT_ID", referencedColumnName = "TENANT_ID", 
				insertable=false, updatable=false),
	@JoinColumn(name = "ID", referencedColumnName = "ID",
				insertable=false, updatable=false)
})
private EntityA entityA;

And in EntityA, use the mappedBy attribute:

@OneToMany(mappedBy = "entityA")
private Set<EntityB> entityBSet = new HashSet<>();

Thx @vlad for response.

I change to mappedBy and it works for primary key relation.
But I need join on non primary key column and one from primary key.
I use TENANT_ID and TAX_NUMBER instead of TENANT_ID and ID.

Example with exception:

package org.hibernate.bugs;

import java.io.Serializable;
import java.util.Set;
import javax.persistence.Column;
import javax.persistence.Embeddable;
import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.FetchType;
import javax.persistence.JoinColumn;
import javax.persistence.JoinColumns;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.Persistence;
import javax.persistence.TypedQuery;
import org.hibernate.annotations.Formula;
import org.junit.Before;
import org.junit.Test;

public class NonPkCompositeKey {

  private EntityManagerFactory templatePU;

  @Before
  public void init() {
    templatePU = Persistence.createEntityManagerFactory("templatePU");
  }

  @Test
  public void mappingTest() throws Exception {
    final EntityManager entityManager = templatePU.createEntityManager();
    final TypedQuery<EntityA> query = entityManager
        .createQuery("from NonPkCompositeKey$EntityA a join fetch a.entityBSet", EntityA.class);
    query.getResultList();
  }


  @Entity
  public static class EntityA {

    @EmbeddedId
    private TenantId id;
    @Column(name = "TAX_NUMBER")
    private Long entityB_businessId;
    @OneToMany(mappedBy = "entityA")
    private Set<EntityB> entityBSet;
  }

  @Entity
  public static class EntityB {

    @EmbeddedId
    private TenantId id;
    @Column(name = "TAX_NUMBER")
    private Long entityB_businessId;
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumns({
        @JoinColumn(name = "TENANT_ID", referencedColumnName = "TENANT_ID",
            insertable=false, updatable=false),
        @JoinColumn(name = "TAX_NUMBER", referencedColumnName = "TAX_NUMBER",
            insertable=false, updatable=false)
    })
    private EntityA entityA;
  }

  @Embeddable
  public static class TenantId implements Serializable {

    private Long id;
    @Column(name = "TENANT_ID")
    private Long tenantId;
  }
}

There’s no exception in your post.

Sorry, i mean when you run it.

Exception for code above:

org.hibernate.AnnotationException: referencedColumnNames(TENANT_ID, TAX_NUMBER) of org.hibernate.bugs.NonPkCompositeKey$EntityB.entityA referencing org.hibernate.bugs.NonPkCompositeKey$EntityA not mapped to a single property

	at org.hibernate.cfg.BinderHelper.createSyntheticPropertyReference(BinderHelper.java:321)
	at org.hibernate.cfg.ToOneFkSecondPass.doSecondPass(ToOneFkSecondPass.java:101)
	at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.processEndOfQueue(InFlightMetadataCollectorImpl.java:1771)
	at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.processFkSecondPassesInOrder(InFlightMetadataCollectorImpl.java:1715)
	at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.processSecondPasses(InFlightMetadataCollectorImpl.java:1602)
	at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:278)
	at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:861)
	at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:888)
	at org.hibernate.jpa.HibernatePersistenceProvider.createEntityManagerFactory(HibernatePersistenceProvider.java:58)
	at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:55)
	at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:39)
	at org.hibernate.bugs.NonPkCompositeKey.init(NonPkCompositeKey.java:28)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
	at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:24)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
	at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:51)
	at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:237)
	at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)

I try debug this issue and I think that problem is in BinderHelper.matchColumnsByProperty method. It doesn’t match property for column declared in Embeddable (TENANT_ID).

If I’m not wrong, here is problem

BinderHelper:403

//first naive implementation
		//only check 1 columns properties
		//TODO make it smarter by checking correctly ordered multi column properties
		List<Property> orderedProperties = new ArrayList<>();
		for (Column column : orderedColumns) {
			boolean found = false;
			for (Property property : columnsToProperty.get( column ) ) {
				if ( property.getColumnSpan() == 1 ) {
					orderedProperties.add( property );
					found = true;
					break;
				}
			}
			if ( !found ) {
				//have to find it the hard way
				return null;
			}
		}

These relationships were designed for matching identifiers. It could be that this use case is not supported. Check out the @JoinColumnsOrFormulas with 2 @JoinColumnsOrFormula and use a SELECT clause instead as explained in this article.

Two subselects is not an option. I just duplicate this column.
Is there any chance to support it?

Is there any chance to support it?

In the true spirit of OSS development, you should provide a Pull Request with a fix for this issue if you think it’s worth to be supported.