Overview

This is an application implemented by Spring Boot and Spring Data JPA (ORM is Hibernate), and is a sample code that uses a database user-defined function (create function xxx ...) from JPQL.

environment

Windows10 Professional
Java 1.8.0_144
Spring Boot 1.5.6
- Spring Data JPA 1.11.6
- Hibernate 5.0.1
MySQL 5.6.25

reference

Sample code

Database user-defined functions

This is a user-defined function used in the sample JPQL. Returns the sum of the two arguments.

DELIMITER //

DROP FUNCTION IF EXISTS calculate//

CREATE FUNCTION calculate(x INT, y INT) RETURNS INT
DETERMINISTIC
CONTAINS SQL
BEGIN
    RETURN x + y;
END
//

SHOW WARNINGS//

DELIMITER ;

select calculate(1,1);
+----------------+
| calculate(1,1) |
+----------------+
|              2 |
+----------------+
1 row in set (0.00 sec)

Dialect

Customize Dialect to register user-defined functions. This implementation is required when using user-defined functions in the select clause. Not required for use in the where clause.

package com.example.domain.datasource;

import org.hibernate.dialect.MySQL5Dialect;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.type.IntegerType;

public class CustomMySQLDialect extends MySQL5Dialect {
    public CustomMySQLDialect() {
        super();
        registerFunction("calculate",
                new StandardSQLFunction("calculate", new IntegerType()));
    }
}

application.yml

Specify in application.yml to use a customized Dialect.

spring:
  jpa:
    properties:
      hibernate:
        dialect: com.example.domain.datasource.CustomMySQLDialect

Use user-defined functions in JPQL

To use a user-defined function, write FUNCTION ('function name', argument 1, argument 2, ...).

Integer result = (Integer) entityManager.createQuery("SELECT FUNCTION('calculate', i.price, 100) FROM Item AS i WHERE i.id=:id")
        .setParameter("id", 1L)
        .getSingleResult();
System.out.println(result);
// → 300

SQL issued

select
    calculate(item0_.price,
    100) as col_0_0_ 
from
    item item0_ 
where
    item0_.id=?

By the way, although it is not in the sample code, the price of the Item entity ID = 1 is 200.

select price from item where id = 1;
+-------+
| price |
+-------+
|   200 |
+-------+
1 row in set (0.00 sec)

Use database user-defined functions from JPQL

Overview

Sample code

Database user-defined functions

Use user-defined functions in JPQL