NOTE: In my effort to make this a generic solution, I'm hopeful that this example is not more confusing because of how generic the table and column names are.
This worked with:
Windows 7
Oracle Java 1.8.0_66
MySql 5.7.10.0
Spring Boot 1.3.1.RELEASE
Tables:
- entity1: There is a domain class called Entity1.java with a corresponding repository class
- entity2: There is a domain class called Entity2.java with a corresponding repository class
- entity3: There is a domain class called Entity3.java with a corresponding repository class
- rel_entity2_entity2: relationship table that associates data from one entity1 id to many entity2 ids. There is a domain class called Rel_Entity1_Entity2.java with a corresponding repository class
- Create a custom domain class called Entity1Entity2Entity3.java to represent the object that will be returned from the custom query.
package com.cherryshoe.domain.data.custom; import java.io.Serializable; import javax.persistence.EmbeddedId; import javax.persistence.Entity; /* * This is a custom domain model that is a result of multiple SQL joins, one of which is the rel_entity1_entity2 table, which is a table * that associates entity1 ids with entity2 ids. * This class does not represent a real table, but a logical table that has a composite key that is driven off of the * rel_entity1_entity2 table, which is entity2_id and entity1_id. * * JPA entities must have an Id, or if a combination of all OR some columns make it unique, * then make a composite key of those columns. */ @Entity public class Entity1Entity2Entity3 implements Serializable { /** * */ private static final long serialVersionUID = -6947625046963217352L; // non-nullable @EmbeddedId private Entity1Entity2Entity3Key compositeKey; // some entity1 table attributes private String entity1LegacyId; private String entity1Type; // some entity2 table attributes private String entity2_name; // some entity3 table attributes private Long entity3_id; private String entity3_code; private String entity3_group; public Entity1Entity2Entity3() { super(); } public Entity1Entity2Entity3(Entity1Entity2Entity3Key compositeKey) { super(); this.compositeKey = compositeKey; } public Entity1Entity2Entity3(Entity1Entity2Entity3Key compositeKey, String entity1LegacyId, String entity1Type, String entity2_name, Long entity3_id, String entity3_code, String entity3_group) { super(); this.compositeKey = compositeKey; this.entity1LegacyId = entity1LegacyId; this.entity1Type = entity1Type; this.entity2_name = entity2_name; this.entity3_id = entity3_id; this.entity3_code = entity3_code; this.entity3_group = entity3_group; } ... getters and setters, hash, equals, toString }
- Create a custom domain composite key class called Entity1Entity2Entity3Key.java
package com.cherryshoe.domain.data.custom; import java.io.Serializable; import javax.persistence.Embeddable; @Embeddable public class Entity1Entity2Entity3Key implements Serializable{ /** * */ private static final long serialVersionUID = -8407380808173613520L; // entity1 table id private Long entity1_id; // entity2 table id private Long entity2_id; public Entity1Entity2Entity3Key() { super(); } public Entity1Entity2Entity3Key(Long entity1_id, Long entity2_id) { super(); this.entity1_id = entity1_id; this.entity2_id = entity2_id; } ... getters and setters, hash, equals, toString }
- The JPA custom repository query is searching for a list of custom Entity1Entity2Entity3 records by entity1 legacy's id that are provided in a Set.
- Notice the Entity1Entity2Entity3RepositoryCustom is typed to the domain class Entity1Entity2Entity3, with Entity1Entity2Entity3 as the Key.
- Notice the return object is a list of custom Entity1Entity2Enity3 custom domain objects.
-
Here's the Integration Test for the custom repository method:
package com.cherryshoe.repository.data.custom; import static org.junit.Assert.assertNotNull; import java.util.Arrays; import java.util.HashSet; import java.util.List; import java.util.Set; import org.junit.Test; import org.springframework.beans.factory.annotation.Autowired; import com.cherryshoe.domain.data.custom.Entity1Entity2Entity3; import com.cherryshoe.repository.template.TemplateITTest; /* * This is an IT Test for custom repository Entity1Entity2Entity3Repository */ public class Entity1Entity2Entity3RepositoryCustomITTest extends TemplateITTest { @Autowired Entity1Entity2Entity3RepositoryCustom entity1Entity2Entity3Repo; @Test public void testRetrieveByEntity1LegacyId() throws Exception { // these can be any value, we just need to test this is value sql Set<String> entity1LegacyIdSet = new HashSet<String>(Arrays.asList("legacy1", "legacy2")); List<Entity1Entity2Entity3> entity1Entity2Entity3List = entity1Entity2Entity3Repo.retrieveLegacyEntity1Info(entity1LegacyIdSet); assertNotNull(entity1Entity2Entity3List); log.info("count[" + entity1Entity2Entity3List.size() + "]"); for (Entity1Entity2Entity3 curr : entity1Entity2Entity3List) { System.out.println(curr); } } }
package com.cherryshoe.repository.data.custom;
import java.util.List;
import java.util.Set;
import com.cherryshoe.domain.data.custom.Entity1Entity2Entity3;
import com.cherryshoe.domain.data.custom.Entity1Entity2Entity3Key;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
public interface Entity1Entity2Entity3RepositoryCustom extends CrudRepository<Entity1Entity2Entity3, Entity1Entity2Entity3Key> {
@Query(value = "SELECT entity1.id as entity1_id, entity1.entity1LegacyId, entity1.entity1Type, "
+ "entity2.id as entity2_id, entity2.entity2_name, entity3.id as entity3_id, entity3.entity3_name, "
+ "entity3.entity3_code, entity3.entity3_group "
+ " FROM entity1 "
+ " LEFT JOIN rel_entity1_entity2 re1e2 ON entity1.id = re1e2.entity1_id "
+ " LEFT JOIN entity2 ON re1e2.entity2_id = entity2.id "
+ " LEFT JOIN entity3 ON entity2.entity3_id = entity3.id "
+ "WHERE entity1.entity1LegacyId in (?1) "
+ "ORDER BY entity1.id, entity2.id",
nativeQuery = true)
public List<Entity1Entity2Entity3> retrieveLegacyEntity1Info(
@Param("entity1LegacyId") Set<String> entity1LegacyIdSet);
}
Judy,
ReplyDeleteI was looking for this particular solution for long time. Glad to find it. Thanks. Any chance you have a complete example including getters/setters,hash, equals etc.? I will be very appreciate whole working example.