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 ProjectDependencies 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
|
|