CODEWITHSUNDEEP
javaspringhibernatejpaspring-data-jpa
mehran qorbani
mehran qorbani

Reputation: 61

Union tables with spring data jpa

I have 2 tables one is Users and the other is UserGroup Having ManyToMany relationship , how can I union them into a single List with spring-data-JPA .

** don't want to use native Queries

UPDATE:

The exact query that I need:

SELECT email FROM user

UNION

SELECT title FROM group

Upvotes: 3

Views: 19673

Answers (3)

KHanusova
KHanusova

Reputation: 197

You can use nativeQuery:

@Query("select u.email from user u union select g.title from group", nativeQuery = true)
public String findResult();

Spring documentation: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.at-query.native

Upvotes: 0

mehran qorbani
mehran qorbani

Reputation: 61

So I solved my problem with creating a view in database itself and then map that to my java entity because it seems that hibernate doesn't support 'union' query

Upvotes: 3

Patrice Blanchardie
Patrice Blanchardie

Reputation: 1361

If you want union, I guess the structure of these 2 entities are similar.

You could create a super entity and make User and UserGroup extend this new entity. Add @Inheritance(strategy = InheritanceType.TABLE_PER_CLASS) on the new entity if you want to keep 2 separate tables.

Then, create a Spring Data JPA Repository for the parent entity.

/**
 * new abstract Superclass
 */
@Entity
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
public abstract class User {

    @Id
    private String id;

    //...
}

/**
 * Existing User entity
 */
@Entity
@Table(name="my_users")
public class LoginUser extends User {

    private String password;

    @ManyToOne
    private Group group;

    //...
}

/**
 * Existing Group entity
 */
@Entity
@Table(name="my_groups")
public class Group extends User {

    private String name;

    @OneToMany(mappedBy="group")
    private Set<LoginUser> users;

    //...
}

/**
 * Super repository, providing UNION
 */
public interface UserRepository extends JpaRepository<User, String> {}

Calling UserRepository.findAll will make JPA do the UNION of the 2 tables, and can return a List<User>. You'll have both LoginUser and Group instances in this list:

// UNION query returning both LoginUser and Group instances
List<User> myUnion = userRepository.findAll();

See https://en.wikibooks.org/wiki/Java_Persistence/Inheritance for more details about JPA inheritance.

JPA/Hibernate don't support custom UNION queries, but inheritance provides out-of-the-box UNION between entities of the same hierarchy when using TABLE_PER_CLASS strategy.

You cant generate the query you want with JPA. It's not possible to UNION the different fields. But if you map email and title to the same field (which is a nonsense) and write the following JPQL, JPA will generate your query.

 SELECT u.yourField FROM User u;

Upvotes: 7

Related Questions