JPA Custom Repository Query Example

I'm using spring boot with JPA and a MySQL backend database.  There was a need to create a custom query (with a list custom domain object's returned) with joins across multiple tables; the custom repository query is primarily driven off of the composite key of the relationship table.

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

Solution:

1. 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
   }
   

2. 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
   }
   

3. 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.  
1. Notice the Entity1Entity2Entity3RepositoryCustom is typed to the domain class Entity1Entity2Entity3, with Entity1Entity2Entity3 as the Key.
2. Notice the return object is a list of custom Entity1Entity2Enity3 custom domain objects.




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);

}



4. 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);
           }
   
       }
   }