Join query in java

JPQL INNER JOIN

In this example, we will see how to use INNER JOIN queries in JPQL.

INNER JOIN queries select the records common to the target tables.

Example

Entities

@Entity public class Employee < @Id @GeneratedValue private long id; private String name; @ManyToMany(cascade = CascadeType.ALL) private Listtasks; . >

Above example has @ManyToMany relationship; one employee can have many tasks and one task can be assigned to many employees (tasks done by teams rather than individuals).

Persisting entities

public static void persistEmployees()
— Employee persisted — Employee, Task]> Employee, Task]> Employee Employee

INNER JOIN Query

private static void executeQuery() < System.out.println("-- executing query --"); EntityManager em = entityManagerFactory.createEntityManager(); Query query = em.createQuery("SELECT DISTINCT e FROM Employee e INNER JOIN e.tasks t"); ListresultList = query.getResultList(); resultList.forEach(System.out::println); em.close(); >

-- executing query -- Employee, Task]> Employee, Task]> Employee, Task]> 

Above query returns the rows where employee task is not null, or simply matching records in both Employee and Task tables exist. Only ‘Jack’ excluded because he is not assigned any task.

Alternatively, we can write above query as:

SELECT DISTINCT e FROM Employee e WHERE e.tasks IS NOT EMPTY

Note that ‘INNER’ keyword is optional, so our original query can be written as:

SELECT DISTINCT e FROM Employee e JOIN e.tasks t

Also we used two identifiers, ‘e’ for Employee entity and ‘t’ for task entity.

INNER JOIN with WHERE Clause

public class ExampleMain2 < private static EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("example-unit"); public static void main(String[] args) < try < persistEmployees(); executeQuery(); >finally < entityManagerFactory.close(); >> . private static void executeQuery() < System.out.println("-- executing query --"); EntityManager em = entityManagerFactory.createEntityManager(); Query query = em.createQuery( "SELECT DISTINCT e FROM Employee e INNER JOIN e.tasks t where t.supervisor='Denise'"); ListresultList = query.getResultList(); resultList.forEach(System.out::println); em.close(); > >

-- Employee persisted --
Employee, Task]>
Employee, Task]>
Employee
Employee 
-- executing query --
Employee
Employee, Task]>

Above query returns only those employees who have the tasks supervised by ‘Denise’.

Another JOIN query with WHERE clause:

public class ExampleMain3 < private static EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("example-unit"); public static void main(String[] args) < try < persistEmployees(); executeQuery(); >finally < entityManagerFactory.close(); >> . private static void executeQuery() < System.out.println("-- executing query --"); EntityManager em = entityManagerFactory.createEntityManager(); Query query = em.createQuery( "SELECT DISTINCT e FROM Employee e INNER JOIN e.tasks t where t.supervisor = e.name"); ListresultList = query.getResultList(); resultList.forEach(System.out::println); em.close(); > >

-- Employee persisted --
Employee, Task]>
Employee, Task]>
Employee
Employee 
-- executing query --
Employee, Task]>

Above query selects only ‘Mike’ because he has a task (‘Documentation’) whose supervisor is himself. The task ‘Documentation’ is assigned to two employees ‘Mike’ and ‘Tim’ and ‘Mike’ is the one who is also supervising the ‘Documentation’ team.

Note that a JOIN query can also be made for single valued relationship as well (other than collection based relationship). Check out an example here .

Example Project

Dependencies and Technologies Used:

• h2 1.4.197: H2 Database Engine.
• hibernate-core 5.2.13.Final: The core O/RM functionality as provided by Hibernate.
  Implements javax.persistence:javax.persistence-api version 2.1
• JDK 1.8
• Maven 3.3.9

Источник

Join в HQL

В SQL можно писать запросы с использованием JOIN. А можно ли так же делать в HQL? Краткий ответ – да. А вот полный ответ будет более интересным.

Во-первых, когда мы пишем JOIN в SQL, то чаще всего это значит, что одна таблица у нас ссылается на другую таблицу. Например, таблица task содержит колонку employee_id, которая ссылается на колонку id таблицы employee.

Эту зависимость можно описать с помощью аннотаций в Hibernate. Для начала давай просто создадим Entities для наших таблиц. Сначала опишем таблицу employee:

 @Entity @Table(name="employee") class Employee < @Column(name="id") public Integer id; @Column(name="name") public String name; @Column(name="salary") public Integer salary; @Column(name="join_date") public Date joinDate; > 

И класс EmployeeTask для таблицы task:

 @Entity @Table(name="task") class EmployeeTask < @Column(name="id") public Integer id; @Column(name="name") public String name; @Column(name="employee_id") public Integer employeeId; @Column(name="deadline") public Date deadline; > 

Все отлично, но есть одно предложение. Давай посмотрим на поле employeeId в последнем примере:

 @Column(name="employee_id") public Integer employeeId; 

Ничего странного не замечаешь? Если нет, то это значит, что у тебя уже сформировалось мышление на языке SQL.

Все дело в том, что в языке Java, мы обычно описываем такую зависимость немного по-другому:

Нам не нужно указывать id, мы обычно сразу указываем переменную, которая хранит ссылку на объект Employee. Или хранит null, если такого объекта нет.

И Hibernate позволяет нам описать такую ситуацию с помощью аннотаций:

 @ManyToOne @JoinColumn(name="employee_id", nullable=true) public Employee employee; 

Аннотация @ManyToOne говорит Hibernate, что много сущностей EmployeeTask могут ссылаться на одну сущность Employee.

А аннотация @JoinColumn указывает имя колонки, из которой будет браться id. Вся остальная необходимая информация будет взята из аннотаций класса Employee.

Итоговый результат будет выглядеть так:

 @Entity @Table(name="task") class EmployeeTask < @Column(name="id") public Integer id; @Column(name="name") public String name; @ManyToOne @JoinColumn(name="employee_id", nullable=true) public Employee employee; @Column(name="deadline") public Date deadline; > 

3.2 Использование join в HQL

А теперь разберем, как писать запросы к связанным сущностям на HQL.

Первая ситуация.

У нас есть сотрудник (Employee) и мы хотим получить список его задач. Вот как будет выглядеть этот запрос на SQL:

 SELECT task.* FROM task JOIN employee ON task.employee_id = employee.id WHERE employee.name = "Иван Иванович"; 

А теперь запишем этот же запрос на HQL:

 from EmployeeTask where employee.name = "Иван Иванович" 

У класса EmployeeTask есть поле employee, а у него есть поле name, так что такой запрос будет работать.

Ситуация вторая.

Вернуть список сотрудников, которые имеют просроченные задачи. Вот как будет выглядеть этот запрос на SQL:

 SELECT DISTINCT employee.* FROM task JOIN employee ON task.employee_id = employee.id WHERE task.deadline < CURDATE(); 

DISTINCT используется, потому что может быть много задач назначенных на одного пользователя.

А теперь запишем этот же запрос на HQL:

 select distinct employee from EmployeeTask where deadline < CURDATE(); 

employee в этом запросе – это поле класса EmployeeTask

Ситуация третья.

Назначим все неназначенные задачи на директора. Запрос на SQL будет выглядеть так:

 UPDATE task SET employee_id = 4 WHERE employee_id IS NULL 

А теперь запишем этот же запрос на HQL:

 update EmployeeTask set employee = :user where employee is null 

Последний запрос самый сложный. Нам нужно передать ID, директора, но класс EmployeeTask не содержит поля, куда можно записать id, вместо этого он содержит поле Employee, куда нужно присвоить ссылку на объект типа Employee.

В Hibernate эта проблема решается с помощью параметров к запросу, которые передаются в объект Query. А в самом HQL такие параметры записываются через двоеточие: :user . Но об этом мы расскажем немного позже.

Источник

Spring Data JPA Join Query for Like Search on One to Many and Many to Many Examples

In this Spring article, I’d like to share with you some examples about writing join queries in Spring Data JPA for like search on one-to-many and many-to-many entity relationships. The join queries which I’m going to share are based on JPQL (Java Persistence Query Language) – not regular relational SQL queries.

1. Join Query for Like Search on Many-to-Many Relationship between User and Role

Let’s get started with a very common entity relationship between User and Role, as depicted in the following class diagram:

In this entity relationship, a user can have one or more roles, and a role can be assigned to one or many users. The Java code of these entity classes would be like this:

@Entity @Table(name = "users") public class User < @Id @GeneratedValue(strategy = GenerationType.IDENTITY) protected Integer id; private String email; @ManyToMany(cascade = CascadeType.PERSIST, fetch = FetchType.EAGER) @JoinTable( name = "users_roles", joinColumns = @JoinColumn(name = "user_id"), inverseJoinColumns = @JoinColumn(name = "role_id") ) private Setroles = new HashSet<>(); // constructors, getters and setters are not shown >

@Entity @Table(name = "roles") public class Role < private String name; private String description; // constructors, getters and setters are not shown >

Here’s an example join query for that like search:

SELECT u FROM User u JOIN u.roles r WHERE r.name LIKE '%role name%'

public interface UserRepository extends JpaRepository  < @Query("SELECT u FROM User u JOIN u.roles r WHERE r.name LIKE %?1%") public PagefindAll(String keyword, Pageable pageable); >

SELECT DISTINCT u FROM User u JOIN u.roles r WHERE r.name LIKE '%role name%'

2. Join Query for Like Search on Many-to-Many Relationship between Brand and Category

Suppose that we have another one-to-many entity relationship between Brand and Category. A brand can belong to one or many categories, and a category can have one or many brands, as illustrated in the following class diagram:

The Java code of these entity classes and database tables are similar to the previous example with user and role.

So, the question is How to list brands by a specific category?

Here’s an answer using JPQL:

SELECT DISTINCT b FROM Brand b JOIN b.categories c WHERE c.name LIKE '%category name%'

public interface BrandRepository extends JpaRepository  < @Query("SELECT DISTINCT b FROM Brand b JOIN b.categories c WHERE c.name LIKE %?1%") public PagefindAll(String keyword, Pageable pageable); >

3. Join Query for Like Search on One-to-Many Relationship between Multiple Entities (Multiple Tables)

Here, the entity relationship between Order and OrderDetail is one to many, and so is the association between Product and OrderDetail.

The corresponding tables in database would be something like this:

Code of these entity classes are as follows.

@Entity @Table(name = "orders") public class Order < @Id @GeneratedValue(strategy = GenerationType.IDENTITY) protected Integer id; @OneToMany(mappedBy = "order", cascade = CascadeType.ALL, orphanRemoval = true) private SetorderDetails = new HashSet<>(); // constructors, getters and setters are not shown >

OrderDetail.java:

@Entity @Table(name = "order_details") public class OrderDetail < @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Integer id; @ManyToOne @JoinColumn(name = "order_id") private Order order; @ManyToOne @JoinColumn(name = "product_id") private Product product; // constructors, getters and setters are not shown >

Product.java:

@Entity @Table(name = "products") public class Product < @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Integer id; // constructors, getters and setters are not shown >

Now, the question is How to search for orders by product name? or How to get orders that contain a specific product?

Below is the answer using JPQL:

SELECT DISTINCT o FROM Order o JOIN o.orderDetails d JOIN d.product p WHERE p.name LIKE ‘%product name%’

You see, this query joins 3 entities together and performs like search by product name. And use it in the OrderRepository as follows:

@Repository public interface OrderRepository extends JpaRepository  < @Query("SELECT DISTINCT o FROM Order o JOIN o.orderDetails od JOIN od.product p" + " WHERE p.name LIKE %?1%") public PagefindAll(String keyword, Pageable pageable); >

So that’s a few examples of Spring Data JPA join query for like search on one-to-many and many-to-many entity relationships. To see real-world scenarios, I recommend you to watch this video:

Related Spring and Database Tutorials:

Other Spring Boot Tutorials:

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He started programming with Java in the time of Java 1.4 and has been falling in love with Java since then. Make friend with him on Facebook and watch his Java videos you YouTube.

Источник