This tutorial shows you how to perform multiple-column join in Spring Data JPA + Hibernate using @JoinColumns annotation and native query.
While fetching rows of a database table, it's possible to join the result with other tables based on related columns. Sometimes, the number of related columns used for the join criteria can be more than one. You may already know that in native SQL query, you can write JOIN {table_name} ON {criteria 1} AND {criteria 2} AND ... AND {criteria n}. But, if you want to use JPQL, you need to know how to define which columns are used for the join criteria. In the example below, we are going to use @JoinColumns annotation for that purpose. Our goal is to fetch the related entities and include them in the result, not only used in the criteria.
This tutorial explains two ways of how to perform multiple-column join. The first one is using the @JoinColumns annotation which works for JPA in general. The other solution is by using a native query, in case the JPA solution is not suitable for your case.
Using @JoinColumns
For example, we have an entity named Product.
@Table(name = "products", uniqueConstraints = {
@UniqueConstraint(name = "uq_products_category_id_code", columnNames = {"categoryId", "code"})
})
public class Product {
// Constructors, getters, setters, and builder are not included
@Id
@GeneratedValue
@GenericGenerator(name = "UUID", type = UuidGenerator.class)
private UUID id;
private UUID categoryId;
private String code;
private String name;
private BigDecimal price;
}There is another entity named Order.
@Entity
@Table(name = "orders")
public class Order {
@Id
@GeneratedValue
@GenericGenerator(name = "UUID", type = UuidGenerator.class)
private UUID id;
private int quantity;
private BigDecimal price;
private UUID categoryId;
private String productCode;
}A Product can be present in many Order rows, while an Order only has one Product. In other words, Product has a one-to-many relationship with Order. That means Order has a many-to-one relationship with the Product entity.
When fetching an Order entity, we also want to fetch the related Product which has the same category ID and product code. Let's assume we cannot store the product ID in the Order entity and we have to use categoryId and productCode for the join. Therefore, we have to find the Product where the OrderLine's categoryId is equal to Product's categoryId and the OrderLine's productCode is equal to Product's code. The SQL query can be written as below.
SELECT * FROM order_lines ol
INNER JOIN products p
ON ol.category_id = pl.category_id AND ol.product_code = p.code;While you can create a native query for it, using JPQL should be the preferred way unless the query is very complex. In addition, it's actually easier and more convenient to use JPQL if you already know how to perform multi-column join and fetch.
In order to allow join and fetch using JPA, we have to define the Product entity as a field in the OrderLine class and tell JPA which related columns should be used in the join criteria. By default, if you don't explicitly define the related columns, JPA will assume the column with the @Id annotation as the referenced column and the referencing table is required to have a foreign key that refers to that column. However, we want to use different columns in this case. The solution is by using the @JoinColumns annotation.
The @JoinColumns annotation accepts multiple @JoinColumn annotations. Each related column that's used in the join criteria must be defined as a @JoinColumn annotation. In the annotation, pass the foreign key column as the name attribute. Meanwhile, the referenced column has to be passed as the referencedColumnName.
By default, JPA reads the column names defined in the @JoinColumn annotations as fields that can be persisted in the database columns. In the example above, the categoryId and productCode fields are already defined in the Order class. That may cause duplication that makes the application cannot be started. The solution is by adding insertable and updatable attributes to the @JoinColumn annotation and setting the values to false.
Below is what we need to add in the Order class.
public class Order {
// Other class members
@JoinColumns({
@JoinColumn(name ="categoryId", referencedColumnName = "categoryId", insertable = false, updatable = false),
@JoinColumn(name ="productCode", referencedColumnName = "code", insertable = false, updatable = false),
})
@ManyToOne
private Product product;
}Now, we need to test whether it works by trying to fetch an order by its ID. First, create the JpaRepository. Since Spring Data JPA already defines the query for retrieving an entity by its id, we don't need to define a derived query for it.
public interface OrderRepository extends JpaRepository<Order, UUID> {
}Then, write a service for fetching the entity.
@RequiredArgsConstructor
@Service
public class OrderService {
private final OrderRepository orderRepository;
public void fetch(UUID id) {
Optional<Order> orderOptional = this.orderRepository.findById(id);
if (orderOptional.isEmpty()) {
System.out.println("Not found");
return;
}
Order order = orderOptional.get();
System.out.println("ID: " + order.getId());
System.out.println("Category ID: " + order.getCategoryId());
System.out.println("Product code: " + order.getProductCode());
System.out.println("Quantity: " + order.getQuantity());
System.out.println("Price: " + order.getPrice());
System.out.println("Product - ID: " + order.getProduct().getId());
System.out.println("Product - Category ID: " + order.getProduct().getCategoryId());
System.out.println("Product - Code: " + order.getProduct().getCode());
System.out.println("Product - Name: " + order.getProduct().getName());
System.out.println("Product - Price: " + order.getProduct().getPrice());
}
}If you run the code above and enable the query logger, you'll get the following output.
select o1_0.id,o1_0.category_id,o1_0.price,p1_0.id,p1_0.category_id,p1_0.code,p1_0.name,p1_0.price,o1_0.product_code,o1_0.quantity from orders o1_0 left join products p1_0 on p1_0.category_id=o1_0.category_id and p1_0.code=o1_0.product_code where o1_0.id=?
binding parameter [1] as [UUID] - [81b7b41c-4590-46f7-94e2-9e5786b8ba04]Since the default fetch type for @ManyToOne is EAGER, the Product entity is fetched by default. You can also see that the join criteria uses the columns defined using @JoinColumns annotation.
What if the fetch type is changed to LAZY. Below is the output.
select o1_0.id,o1_0.category_id,o1_0.price,o1_0.product_code,o1_0.quantity from orders o1_0 where o1_0.id=?
binding parameter [1] as [UUID] - [81b7b41c-4590-46f7-94e2-9e5786b8ba04]
select p1_0.id,p1_0.category_id,p1_0.code,p1_0.name,p1_0.price from products p1_0 where (p1_0.category_id,p1_0.code) in ((?,?))
binding parameter [1] as [UUID] - [a812a592-d948-4ecb-8976-3ceaee9b97dc]
binding parameter [2] as [VARCHAR] - [001]It turns out that Hibernate will still fetch the Product, but in a separate query. That means the LAZY annotation doesn't really work in this case. The reason is Hibernate can't proxy the object if we're not referencing the primary key. Therefore, it performs an eager fetch to make sure that there is only one proxy per object ID. The solution is to use a native query, which will be explained in the next section.
Similarly, for fetching the related OrderLines of a Product entity, we need to define the OrderLine entity in the Product class. Since the relationship is one-to-many, we can use a Collection such as List or Set as the field type and annotate it with @OneToMany annotation. As the @JoinColumns annotation is already defined in the OrderLine, defining the join mapping can be done by passing the mappedBy attribute in the @OneToMany annotation.
public class Product {
// Other class members
@OneToMany(mappedBy = "product")
private Set<Order> orders;
}In case the @JoinColumns annotation is not defined in the OrderLine entity, you can also define it in the Product entity. However, it's not allowed to define both mappedBy and @JoinColumns.
public class Product {
// Other class members
@JoinColumns({
@JoinColumn(name ="categoryId", referencedColumnName = "categoryId", insertable = false, updatable = false),
@JoinColumn(name ="productCode", referencedColumnName = "code", insertable = false, updatable = false),
})
@OneToMany
private Set<Order> orders;
}To fetch a Product entity, first define the JpaRepository.
public interface ProductRepository extends JpaRepository<Product, UUID> {
}Then, create the service for fetching a Product by its ID.
@RequiredArgsConstructor
@Service
public class ProductService {
private final ProductRepository productRepository;
public void fetch(UUID id) {
Optional<Product> productOptional = this.productRepository.findById(id);
if (productOptional.isEmpty()) {
System.out.println("Not found");
return;
}
Product product = productOptional.get();
System.out.println("ID: " + product.getId());
System.out.println("Category ID: " + product.getCategoryId());
System.out.println("Code: " + product.getCode());
System.out.println("Name: " + product.getName());
System.out.println("Price: " + product.getPrice());
for (Order order : product.getOrders()) {
System.out.println("---------------------------------");
System.out.println("Order - ID: " + order.getId());
System.out.println("Order - Category ID: " + order.getCategoryId());
System.out.println("Order - Product code: " + order.getProductCode());
System.out.println("Order - Quantity: " + order.getQuantity());
System.out.println("Order - Price: " + order.getPrice());
}
}
}When the findById is executed, it will generate the following query. The related Order entities aren't fetched by default because the default fetch type for @OneToMany is LAZY.
select p1_0.id,p1_0.category_id,p1_0.code,p1_0.name,p1_0.price from products p1_0 where p1_0.id=?
binding parameter [1] as [UUID] - [09af2dc3-b6db-4869-b00f-9ee02ce37f71]But when the product.getOrders() is executed, it will fetch the Order entities. Since the Order class has a field that references the Product class, there will be another query that fetches the related Order entities. From the log below, you can see that the join is performed based on the column defined in the @JoinColumns annotation.
select o1_0.category_id,o1_0.product_code,o1_0.id,o1_0.price,o1_0.quantity from orders o1_0 where (o1_0.category_id,o1_0.product_code) in ((?,?))
binding parameter [1] as [UUID] - [a812a592-d948-4ecb-8976-3ceaee9b97dc]
binding parameter [2] as [VARCHAR] - [001]
select p1_0.id,p1_0.category_id,p1_0.code,p1_0.name,p1_0.price from products p1_0 where (p1_0.category_id,p1_0.code) in ((?,?))
binding parameter [1] as [UUID] - [a812a592-d948-4ecb-8976-3ceaee9b97dc]
binding parameter [2] as [VARCHAR] - [001]In summary, the @JoinColumns annotation can be used to define which columns should be used for join. However, it may force eager fetch depending on the relationship between entities.
Using Native Query
If you want to fetch the related entity lazily, you cannot rely on Hibernate to use a @JoinColumns annotation that refers to a non primary key. The solution is using a native query. With this method, you don't need to define the related entity to be fetched in the 'root' class.
There are several ways to create a native query. Spring Data JPA provides @Query annotation and you can set the nativeQuery attribute to true to create native query. Another way is by using EntityManager's createNativeQuery.
The default return type for a native query is Object[] (or List<Object[]> if the result is more than one). The elements of the Object[] are the selected fields. As a result, it's a bit more difficult to process the result since you have to manually cast each field into the correct type.
If you use Hibernate, you can define a transformer for converting the Object[] value to an instance of another class. Therefore, the output from the query is a ready-to-use object. What you need to do is to create a JPA Query instance using EntityManager's createNativeQuery and unwrap it to a Hibernate Query object in order to use Hibernate specific features. Then, apply a transformer to return a different object.
public Optional<OrderWithProductDto> findOneWithProductById(UUID id) {
String sql = "SELECT o.*, p FROM orders o INNER JOIN products p"
+ " ON o.category_id = p.category_id AND o.product_code = p.code"
+ " WHERE o.id = :id";
@SuppressWarnings("unchecked")
Query query = this.entityManager.createNativeQuery(sql)
.unwrap(org.hibernate.query.Query.class)
.<OrderWithProductDto>setTupleTransformer(((tuple, aliases) -> {
Object[] productData = (Object[]) tuple[5];
return OrderWithProductDto.builder()
.id((UUID) tuple[0])
.quantity((int) tuple[1])
.price((BigDecimal) tuple[2])
.categoryId((UUID) tuple[3])
.productCode((String) tuple[4])
.product(ProductDto.builder()
.id(UUID.fromString((String) productData[0]))
.categoryId(UUID.fromString((String) productData[1]))
.code((String) productData[2])
.name((String) productData[3])
.price(new BigDecimal((String) productData[4]))
.build()
)
.build();
}))
.setParameter("id", id)
.setMaxResults(1);
try {
OrderWithProductDto result = (OrderWithProductDto) query.getSingleResult();
return Optional.of(result);
} catch (NoResultException ex) {
return Optional.empty();
}
}Below is the generated query.
SELECT o.*, p FROM orders o INNER JOIN products p ON o.category_id = p.category_id AND o.product_code = p.code WHERE o.id = ? fetch first ? rows only
binding parameter [1] as [UUID] - [81b7b41c-4590-46f7-94e2-9e5786b8ba04]Here is another example for fetching a Product entity along with Order entities that have a reference to the product. Since the relationship is one-to-many, the result can be more than one row.
public Optional<ProductWithOrdersDto> findOneWithOrdersById(UUID id) {
String sql = "SELECT p.*, o FROM products p LEFT JOIN orders o"
+ " ON p.category_id = o.category_id AND p.code = o.product_code"
+ " WHERE p.id = :id";
@SuppressWarnings("unchecked")
Query query = this.entityManager.createNativeQuery(sql)
.unwrap(org.hibernate.query.Query.class)
.<ProductWithOrderDto>setTupleTransformer(((tuple, aliases) -> {
Object[] productData = (Object[]) tuple[5];
return ProductWithOrderDto.builder()
.id((UUID) tuple[0])
.categoryId((UUID) tuple[1])
.code((String) tuple[2])
.name((String) tuple[3])
.price((BigDecimal) tuple[4])
.order(OrderDto.builder()
.id(UUID.fromString((String) productData[0]))
.quantity(Integer.parseInt((String) productData[1]))
.price(new BigDecimal((String) productData[2]))
.categoryId(UUID.fromString((String) productData[3]))
.productCode((String) productData[4])
.build()
)
.build();
}))
.setParameter("id", id);
try {
@SuppressWarnings("unchecked") List<ProductWithOrderDto> productWithOrderList = (List<ProductWithOrderDto>) query.getResultList();
ProductWithOrdersDto result = ProductWithOrdersDto.builder()
.id(productWithOrderList.get(0).getId())
.categoryId(productWithOrderList.get(0).getCategoryId())
.code(productWithOrderList.get(0).getCode())
.name(productWithOrderList.get(0).getName())
.price(productWithOrderList.get(0).getPrice())
.orders(productWithOrderList.stream()
.map(ProductWithOrderDto::getOrder)
.collect(Collectors.toList())
)
.build();
return Optional.of(result);
} catch (NoResultException ex) {
return Optional.empty();
}
}And here's the query log.
SELECT p.*, o FROM products p LEFT JOIN orders o ON p.category_id = o.category_id AND p.code = o.product_code WHERE p.id = ?
binding parameter [1] as [UUID] - [09af2dc3-b6db-4869-b00f-9ee02ce37f71]Summary
JPA's @JoinColumns annotation can be a simple solution to perform multi-column join. However, it may force eager fetches which can affect the system performance. The workaround is by using a native query, convert it to Hibernate's Query, and transform the result by using a custom transformer.
The file for this tutorial can be downloaded here.
You can also read about:
