CODEWITHSUNDEEP
javamysqljpaspring-data-jpapersistence
zendu
zendu

Reputation: 1218

Spring Data JPA join 2 tables

I have 2 tables in MySQL database: user and user_additional_details with columns described below.

User

User Additional Details

Table user_additional_details contains 0 or 1 row for each userId in user table. However, database does not have a foreign key constraint defined. Ideally, columns from user_additional_details should have been added as nullable columns in user table, but that was not done for some unknown reason. Now I need to define the entity for following query.

select user.userId, user.phone, user_a_d.personalPhone
from user
join user_additional_details as user_a_d
    on user.userId = user_additional_details.userId

I tried defining JPA entities for the tables, but not able to figure out how to create an entity that uses columns from different tables.

Upvotes: 0

Views: 6304

Answers (2)

Mathias Dpunkt
Mathias Dpunkt

Reputation: 12184

It seems like the SecondaryTable annotation is what you are looking for

Specifies a secondary table for the annotated entity class. Specifying one or more secondary tables indicates that the data for the entity class is stored across multiple tables.

Here you find a detailed example of how to use it - http://www.thejavageek.com/2014/09/18/jpa-secondarytable-annotation-example/

Upvotes: 1

codeLover
codeLover

Reputation: 2592

Create UserEntity (with all the columns from User table) and UserAdditionalDetailsEntity(with all the columns from user_additional_details table). I assume you are aware how to create JPA entities and map them to database table.

I hope you would have create entity manager factory object in your spring configuration file. With the help of that create entity manager object .

Once EntutyManager Object is created:

Query q= em.createQuery("select user.userId, user.phone, userDetails.personalPhone
from UserEntity user
join UserAdditionalDetailsEntity as userDetails
    on user.userId = userDetails.userId");
List<Object[]> resultList= q.getResultList();

Once you get resultList you can iterate over the list of object array and get data. Each index of the resultList will contain the object array representing one row Keep in mind that field name mentioned in query should be same as the one mentioned in your JPA Entites.

Upvotes: 1

Related Questions