Close

JPQL Arithmetic Functions

[Updated: Jun 9, 2018, Created: Jun 8, 2018]

JPQL provides following built-in arithmetic functions:

  • ABS(number)

    Takes a numeric argument, removes negative sign if there's any and returns a number (integer, float, or double) of the same type as the argument to the function.
    ABS(-1) returns 1
    ABS(1) returns 1

  • SQRT(number)

    Returns the square root of a number.
    SQRT(36) returns 6
    SQRT(1.44) returns 1.2

  • MOD(int, int)

    Returns the remainder of the first argument divided by the second. Similar to Java % operator.
    MOD(3,2) returns 1

  • SIZE(collection)

    Returns an integer of the number of elements in the given collection.
    SIZE (employee.phoneNumbers) returns the number of phone numbers of Employee entity.

  • INDEX(identification_variable)

    Returns an integer value corresponding to the position of its argument in an ordered list. The INDEX function can only be applied to identification variables for which @OrderColumn (check out tutorial here) has been specified on the corresponding collection field/property.
    Following example returns the employees name with their phone numbers and index of the phone number in the phoneNumbers list:
    Select e.name, INDEX(p), p from Employee e JOIN e.phoneNumbers p

Example

Entity

@Entity
public class Employee {
  @Id
  @GeneratedValue
  private long id;
  private String name;
  private double salary;
  private String dept;
  @ElementCollection
  @OrderColumn
  private List<String> phoneNumbers;
    .............
}

Using arithmetic functions

public class ExampleMain {
  private static EntityManagerFactory entityManagerFactory =
          Persistence.createEntityManagerFactory("example-unit");

  public static void main(String[] args) {
      try {
          persistEmployees();
          findAbsoluteDifferenceWithAvgSalary();
          findStandardDeviationOfSalaries();
          findSalariesNearestToThousands();
          findNumberOfPhoneNumbers();
          findEmployeeWithMoreThanOnePhoneNumber();
          findPhoneByIndex();

      } finally {
          entityManagerFactory.close();
      }
  }

  public static void persistEmployees() {
      Employee employee1 = Employee.create("Diana", 2000, "IT", "111-111-111", "666-666-666", "777-777-777");
      Employee employee2 = Employee.create("Rose", 3500, "Admin", "222-222,222", "888-888-888");
      Employee employee3 = Employee.create("Denise", 2500, "Admin", "333-333-333");
      Employee employee4 = Employee.create("Mike", 4000, "IT", "444-444-444");
      Employee employee5 = Employee.create("Linda", 4500, "Sales", "555-555-555");
      EntityManager em = entityManagerFactory.createEntityManager();
      em.getTransaction().begin();
      em.persist(employee1);
      em.persist(employee2);
      em.persist(employee3);
      em.persist(employee4);
      em.persist(employee5);
      em.getTransaction().commit();

      System.out.println("-- Employees persisted --");
      Query query = em.createQuery(
              "SELECT e FROM Employee e");
      List<Employee> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }

  private static void findAbsoluteDifferenceWithAvgSalary() {
      System.out.println("-- Find salaries' positive difference with average salary --");
      EntityManager em = entityManagerFactory.createEntityManager();
      //find average salary first
      Double avgSal = (Double) em.createQuery("SELECT  AVG(e.salary) from Employee e")
                                 .getSingleResult();
      System.out.println("Average Salary: " + avgSal);
      List<Object[]> list = em.createQuery(
              "SELECT e.name, ABS(e.salary - :avgSal) FROM Employee e")
                              .setParameter("avgSal", avgSal).getResultList();
      list.forEach(o -> System.out.println(Arrays.toString(o)));
      em.close();
  }

  private static void findStandardDeviationOfSalaries() {
      System.out.println("-- Find salaries' standard deviation --");
      EntityManager em = entityManagerFactory.createEntityManager();
      //find average salary
      Double avgSal = (Double) em.createQuery("SELECT AVG(e.salary) from Employee e")
                                 .getSingleResult();
      //find square root of squared sum salary difference from avg
      Double deviationSum = (Double) em
              .createQuery("SELECT SQRT(SUM((e.salary - :avgSal)*(e.salary - :avgSal)))"
                      + " from Employee e").setParameter("avgSal", avgSal).getSingleResult();

      //find square root of employee's count
      Double sampleSizeSquaredRoot = (Double) em.createQuery("SELECT SQRT(COUNT(e)) from Employee e")
                                                .getSingleResult();
      System.out.println("standard deviation: " + (deviationSum / sampleSizeSquaredRoot));
      em.close();
  }

  private static void findSalariesNearestToThousands() {
      System.out.println("-- Find salaries nearest to 1000 --");
      EntityManager em = entityManagerFactory.createEntityManager();

      List<Object[]> list = em.createQuery("SELECT e.name, e.salary - MOD(e.salary, 1000)"
              + " from Employee e").getResultList();
      list.forEach(o -> System.out.println(Arrays.toString(o)));
      em.close();
  }

  private static void findNumberOfPhoneNumbers() {
      System.out.println("-- Find number of phone numbers --");
      EntityManager em = entityManagerFactory.createEntityManager();

      List<Object[]> list = em.createQuery("SELECT e.id, e.name, SIZE(e.phoneNumbers)"
              + " from Employee e GROUP BY e.id, e.name").getResultList();
      list.forEach(o -> System.out.println(Arrays.toString(o)));
      em.close();
  }

  private static void findEmployeeWithMoreThanOnePhoneNumber() {
      System.out.println("-- Find employees who have more than 1 phone number --");
      EntityManager em = entityManagerFactory.createEntityManager();
      List<Employee> list = em.createQuery("SELECT DISTINCT e from Employee e"
              + " JOIN e.phoneNumbers p WHERE INDEX(p) > 0")
                              .getResultList();
      list.stream().forEach(System.out::println);
  }

  private static void findPhoneByIndex() {
      System.out.println("-- Find phones by index in phoneNumbers list --");
      EntityManager em = entityManagerFactory.createEntityManager();
      List<Object[]> list = em.createQuery("Select e.name, INDEX(p), p from Employee e JOIN e.phoneNumbers p")
                              .getResultList();
      list.forEach(arr -> System.out.println(Arrays.toString(arr)));
      em.close();
  }
}
-- Employees persisted --
Employee{id=1, name='Diana', salary=2000.0, dept='IT', phoneNumbers=[111-111-111, 666-666-666, 777-777-777]}
Employee{id=2, name='Rose', salary=3500.0, dept='Admin', phoneNumbers=[222-222,222, 888-888-888]}
Employee{id=3, name='Denise', salary=2500.0, dept='Admin', phoneNumbers=[333-333-333]}
Employee{id=4, name='Mike', salary=4000.0, dept='IT', phoneNumbers=[444-444-444]}
Employee{id=5, name='Linda', salary=4500.0, dept='Sales', phoneNumbers=[555-555-555]}
-- Find salaries' positive difference with average salary --
Average Salary: 3300.0
[Diana, 1300.0]
[Rose, 200.0]
[Denise, 800.0]
[Mike, 700.0]
[Linda, 1200.0]
-- Find salaries' standard deviation --
standard deviation: 927.3618495495705
-- Find salaries nearest to 1000 --
[Diana, 2000.0]
[Rose, 3000.0]
[Denise, 2000.0]
[Mike, 4000.0]
[Linda, 4000.0]
-- Find number of phone numbers --
[5, Linda, 1]
[4, Mike, 1]
[3, Denise, 1]
[2, Rose, 2]
[1, Diana, 3]
-- Find employees who have more than 1 phone number --
Employee{id=1, name='Diana', salary=2000.0, dept='IT', phoneNumbers=[111-111-111, 666-666-666, 777-777-777]}
Employee{id=2, name='Rose', salary=3500.0, dept='Admin', phoneNumbers=[222-222,222, 888-888-888]}
-- Find phones by index in phoneNumbers list --
[Diana, 0, 111-111-111]
[Diana, 1, 666-666-666]
[Diana, 2, 777-777-777]
[Rose, 0, 222-222,222]
[Rose, 1, 888-888-888]
[Denise, 0, 333-333-333]
[Mike, 0, 444-444-444]
[Linda, 0, 555-555-555]

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

JPQL Arithmetic Functions Select All Download
  • jpql-arithmetic-functions
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • ExampleMain.java
          • resources
            • META-INF

    See Also