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

4 comments:

  1. Great post! I am actually getting ready to across this information, It’s very helpful for this blog.Also great with all of the valuable information you have Keep up the good work you are doing well.
    Digital Marketing Training Course in Chennai | Digital Marketing Training Course in Anna Nagar | Digital Marketing Training Course in OMR | Digital Marketing Training Course in Porur | Digital Marketing Training Course in Tambaram | Digital Marketing Training Course in Velachery

    ReplyDelete
  2. 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!