Sunday, February 19, 2017

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

1 comment:

  1. Judy,
    I 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.

    ReplyDelete

I appreciate your time in leaving a comment!