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);
}
This is my first visit to your blog, keep writing more content.
ReplyDeletecloud computing training in chennai
Hadoop Training in Chennai
Selenium Training in Chennai
Digital Marketing Course in Chennai
JAVA Training in Chennai
German Classes in chennai
Java classes in chennai
The development of artificial intelligence (AI) has propelled more programming architects, information scientists, and different experts to investigate the plausibility of a vocation in machine learning. Notwithstanding, a few newcomers will in general spotlight a lot on hypothesis and insufficient on commonsense application. machine learning projects for final year In case you will succeed, you have to begin building machine learning projects in the near future.
DeleteProjects assist you with improving your applied ML skills rapidly while allowing you to investigate an intriguing point. Furthermore, you can include projects into your portfolio, making it simpler to get a vocation, discover cool profession openings, and Final Year Project Centers in Chennai even arrange a more significant compensation.
Data analytics is the study of dissecting crude data so as to make decisions about that data. Data analytics advances and procedures are generally utilized in business ventures to empower associations to settle on progressively Python Training in Chennai educated business choices. In the present worldwide commercial center, it isn't sufficient to assemble data and do the math; you should realize how to apply that data to genuine situations such that will affect conduct. In the program you will initially gain proficiency with the specialized skills, including R and Python dialects most usually utilized in data analytics programming and usage; Python Training in Chennai at that point center around the commonsense application, in view of genuine business issues in a scope of industry segments, for example, wellbeing, promoting and account.
The Nodejs Projects Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training
"It is actually a great and helpful piece of information. I am satisfied that you simply shared this helpful information with us. Please stay us informed like this. Thanks for sharing.
ReplyDeleteDigital 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
"
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.
ReplyDeleteDigital 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
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.
supreme
ReplyDeletebalenciaga sneakers
supreme clothing
nike lebron 15
moncler
golden goose outlet
balenciaga
yeezy shoes
yeezy shoes
supreme hoodie
jordan shoes
ReplyDeletenike epic react
off white
jordan shoes
nike off white
supreme clothing
stephen curry shoes
hermes belt
curry 7
yeezy boost 350 v2