JPA - JPQL Query Examples

    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");
        List<Employee> resultList = query.getResultList();
        resultList.forEach(System.out::println);
        em.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'");
        List<Employee> resultList = query.getResultList();
        resultList.forEach(System.out::println);
        em.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");
        List<Employee> resultList = query.getResultList();
        resultList.forEach(System.out::println);
        em.close();
    }

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

    private static void executeQuery() {
        System.out.println("-- executing query --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery(
                "SELECT DISTINCT e.name, t.description FROM Employee e LEFT OUTER JOIN e.tasks t");
        List<Object[]> resultList = query.getResultList();
        resultList.forEach(r -> System.out.println(Arrays.toString(r)));
        em.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");
        List<Employee> resultList = query.getResultList();
        for (Employee employee : resultList) {
            System.out.println(employee.getName() + " - " + employee.getTasks());
        }
        em.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 FETCH e.tasks t");
        List<Employee> resultList = query.getResultList();
        for (Employee employee : resultList) {
            System.out.println(employee.getName() + " - " + employee.getTasks());
        }
        em.close();
    }

    private static void executeQuery() {
        System.out.println("-- executing query --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery("SELECT DISTINCT e FROM Employee e LEFT JOIN FETCH e.tasks t");
        List<Employee> resultList = query.getResultList();
        for (Employee employee : resultList) {
            System.out.println(employee.getName() + " - " + employee.getTasks());
        }
        em.close();
    }

    private static void findEmployeeBySalary() {
        System.out.println("-- Employee with salary BETWEEN 2000 and 4000 --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery(
                "SELECT e FROM Employee e WHERE e.salary BETWEEN 2000L AND 4000L order by e.salary");
        List<Employee> resultList = query.getResultList();
        resultList.forEach(System.out::println);
        em.close();
    }

    private static void findEmployeeByJoinDate() {
        System.out.println("-- Employee with join date BETWEEN '1990-01-01' and '2010-05-01' --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery(
                "SELECT e FROM Employee e WHERE e.joinDate BETWEEN '1990-01-01' AND '2010-05-01' order "
                        + "by e.joinDate");
        List<Employee> resultList = query.getResultList();
        resultList.forEach(System.out::println);
        em.close();
    }

    private static void findEmployeeByJoinDate2() {
        System.out.println("-- Employee with join date BETWEEN '2005-01-01' and '2018-01-01' --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery(
                "SELECT e FROM Employee e WHERE e.joinDate BETWEEN :startDate AND :endDate order "
                        + "by e.joinDate");
        query.setParameter("startDate", localToTimeStamp(LocalDate.of(2005,1,1)));
        query.setParameter("endDate", localToTimeStamp(LocalDate.of(2018,1,1)));
        List<Employee> resultList = query.getResultList();
        resultList.forEach(System.out::println);
        em.close();
    }

    private static void findEmployeeByJoinDate3() {
        System.out.println("-- Employee with join date NOT BETWEEN'2005-01-01' and '2018-01-01' --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery(
                "SELECT e FROM Employee e WHERE e.joinDate NOT BETWEEN :startDate AND :endDate order "
                        + "by e.joinDate");
        query.setParameter("startDate", localToTimeStamp(LocalDate.of(2005,1,1)));
        query.setParameter("endDate", localToTimeStamp(LocalDate.of(2018,1,1)));
        List<Employee> resultList = query.getResultList();
        resultList.forEach(System.out::println);
        em.close();
    }

    private static void findEmployeeBySalary() {
        System.out.println("-- Employees with salary IN 2000, 3000 and 4000 --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery(
                "SELECT e FROM Employee e WHERE e.salary IN (2000L, 3000, 4000L) order by e.salary");
        List<Employee> resultList = query.getResultList();
        resultList.forEach(System.out::println);
        em.close();
    }

    private static void findEmployeeByName() {
        System.out.println("-- Employees name IN Diana, Mike and Rose --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery(
                "SELECT e FROM Employee e WHERE e.name IN ('Diana', 'Mike', 'Rose')");
        List<Employee> resultList = query.getResultList();
        resultList.forEach(System.out::println);
        em.close();
    }

    private static void findEmployeeByDept() {
        System.out.println("-- Employees name IN IT, Sales and HR --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery(
                "SELECT e FROM Employee e WHERE e.dept IN :deptNames");
        query.setParameter("deptNames", Arrays.asList("IT", "Sales", "HR"));
        List<Employee> resultList = query.getResultList();
        resultList.forEach(System.out::println);
        em.close();
    }

    private static void findEmployeeByDept2() {
        System.out.println("-- Employees name NOT IN IT, Sales and HR --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery(
                "SELECT e FROM Employee e WHERE e.dept NOT IN :deptNames");
        query.setParameter("deptNames", Arrays.asList("IT", "Sales", "HR"));
        List<Employee> resultList = query.getResultList();
        resultList.forEach(System.out::println);
        em.close();
    }

    private static void findEmployeeBySalary() {
        System.out.println("-- Employees with salary LIKE _500 --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery(
                "SELECT e FROM Employee e WHERE e.salary LIKE '_500' order by e.salary");
        List<Employee> resultList = query.getResultList();
        resultList.forEach(System.out::println);
        em.close();
    }

    private static void findEmployeeByName() {
        System.out.println("-- Employees name LIKE D% --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery(
                "SELECT e FROM Employee e WHERE e.name LIKE 'D%'");
        List<Employee> resultList = query.getResultList();
        resultList.forEach(System.out::println);
        em.close();
    }

    private static void findEmployeeByName2() {
        System.out.println("-- Employees name NOT LIKE D% --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery(
                "SELECT e FROM Employee e WHERE e.name NOT LIKE :nameStartsWith");
        query.setParameter("nameStartsWith", "D%");
        List<Employee> resultList = query.getResultList();
        resultList.forEach(System.out::println);
        em.close();
    }

    private static void findEmployeeByDept() {
        System.out.println("-- Employees dept LIKE '%@_%' ESCAPE '@' --");
        EntityManager em = entityManagerFactory.createEntityManager();
        //our escape character is '@'
        Query query = em.createQuery(
                "SELECT e FROM Employee e WHERE e.dept LIKE '%@_%' ESCAPE '@'");
        List<Employee> resultList = query.getResultList();
        resultList.forEach(System.out::println);
        em.close();
    }

    private static void findEmployeeByDeptNull() {
        System.out.println("-- Employees with dept is NULL --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery(
                "SELECT e FROM Employee e WHERE e.dept IS NULL");
        List<Employee> resultList = query.getResultList();
        resultList.forEach(System.out::println);
        em.close();
    }

    private static void findEmployeeByDeptNotNull() {
        System.out.println("-- Employees with dept is NOT NULL --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery(
                "SELECT e FROM Employee e WHERE e.dept IS NOT NULL");
        List<Employee> resultList = query.getResultList();
        resultList.forEach(System.out::println);
        em.close();
    }

    private static void findEmployeeWithNoTask() {
        System.out.println("-- Employees with no tasks --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery("SELECT e FROM Employee e where e.tasks is EMPTY");
        List<Employee> resultList = query.getResultList();
        resultList.forEach(System.out::println);
        em.close();
    }

    private static void findEmployeeWithTasks() {
        System.out.println("-- Employees with tasks --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery("SELECT e FROM Employee e where e.tasks is NOT EMPTY");
        List<Employee> resultList = query.getResultList();
        resultList.forEach(System.out::println);
        em.close();
    }

    private static void findEmployeeWithNoTaskSupervisor() {
        System.out.println("-- Employees with no task supervisor --");
        EntityManager em = entityManagerFactory.createEntityManager();
        //IS EMPTY is used for collections only. To find employee with task having no supervisor use a join
        Query query = em.createQuery("SELECT DISTINCT e FROM Employee e INNER JOIN e.tasks t "
                + "where t.supervisor is NULL");
        List<Employee> resultList = query.getResultList();
        resultList.forEach(System.out::println);
        em.close();
    }

    private static void findEmployeeByPhoneNumber() {
        System.out.println("-- Employees with phone number 222-222-222 --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery("SELECT e FROM Employee e where '222-222-222' MEMBER OF e.phoneNumbers");
        List<Employee> resultList = query.getResultList();
        resultList.forEach(System.out::println);
        em.close();
    }

    private static void findEmployeeByPhoneNumber2() {
        System.out.println("-- Employees with phone number NOT 222-222-222 --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery("SELECT e FROM Employee e where :theNumber NOT MEMBER OF e.phoneNumbers");
        query.setParameter("theNumber", "222-222-222");
        List<Employee> resultList = query.getResultList();
        resultList.forEach(System.out::println);
        em.close();
    }

    private static void findEmployeeWithTask() {
        System.out.println("-- Employees with task 'Designing' --");
        EntityManager em = entityManagerFactory.createEntityManager();
        //find task by name first
        Query query = em.createQuery("SELECT t FROM Task t where t.description LIKE 'Design%'");
        List<Task> tasks = query.getResultList();
        if(tasks.size()==0){
            return;
        }
        Task theTask = tasks.get(0);
        // using MEMBER OF
        Query query2 = em.createQuery("SELECT e FROM Employee e where :requiredTask MEMBER OF e.tasks");
        query2.setParameter("requiredTask", theTask);
        List<Employee> resultList = query2.getResultList();
        resultList.forEach(System.out::println);

        //alternatively we can use JOIN
        System.out.println("-- Employees with task 'Designing' using JOIN --");
        Query query3 = em.createQuery("SELECT e FROM Employee e LEFT JOIN e.tasks t where "
                + "t.description LIKE 'Design%'");
        List<Employee> resultList2 = query3.getResultList();
        resultList2.forEach(System.out::println);
        em.close();
    }

    private static void findEmployeeCount() {
        System.out.println("-- Employee COUNT --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery(
                "SELECT COUNT(e) FROM Employee e");
        Long result = (Long) query.getSingleResult();
        System.out.println(result);
        em.close();
    }

    private static void findEmployeeAvgSalary() {
        System.out.println("-- Employee AVG Salary --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery(
                "SELECT AVG(e.salary) FROM Employee e");
        Double result = (Double) query.getSingleResult();
        System.out.println(result);
        em.close();
    }

    private static void findEmployeeMaxSalary() {
        System.out.println("-- Employee MAX Salary --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery(
                "SELECT MAX(e.salary) FROM Employee e");
        Long result = (Long) query.getSingleResult();//salary of type long
        System.out.println(result);
        em.close();
    }

    private static void findEmployeeMinSalary() {
        System.out.println("-- Employee MIN Salary --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery(
                "SELECT MIN(e.salary) FROM Employee e");
        Long result = (Long) query.getSingleResult();//salary of type long
        System.out.println(result);
        em.close();
    }

    private static void findEmployeeSalariesSum() {
        System.out.println("-- Employee SUM Salary --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery(
                "SELECT SUM(e.salary) FROM Employee e");
        Long result = (Long) query.getSingleResult();
        System.out.println(result);
        em.close();
    }

    private static void findEmployeeCountGroupByDept() {
        System.out.println("-- Employee count group by dept --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery(
                "SELECT e.dept, COUNT(e) FROM Employee e GROUP BY e.dept");
        List<Object[]> resultList = query.getResultList();
        resultList.forEach(r -> System.out.println(Arrays.toString(r)));
        em.close();
    }

    private static void findEmployeeAvgSalariesGroupByDept() {
        System.out.println("-- Employees avg salary group by dept --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery(
                "SELECT e.dept, AVG(e.salary) FROM Employee e GROUP BY e.dept");
        List<Object[]> resultList = query.getResultList();
        resultList.forEach(r -> System.out.println(Arrays.toString(r)));
        em.close();
    }

    private static void findEmployeeCountGroupBySalary() {
        System.out.println("-- Employee count group by salary --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery(
                "SELECT e.salary, COUNT(e) FROM Employee e GROUP BY e.salary");
        List<Object[]> resultList = query.getResultList();
        resultList.forEach(r -> System.out.println(Arrays.toString(r)));
        em.close();
    }

    private static void findEmployeeMaxSalariesGroupBySelectedDept() {
        System.out.println("-- Employees max salary group by dept - only in IT and Admin dept --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery(
                "SELECT e.dept, MAX(e.salary) FROM Employee e GROUP BY e.dept HAVING e.dept IN ('IT', 'Admin')");
        List<Object[]> resultList = query.getResultList();
        resultList.forEach(r -> System.out.println(Arrays.toString(r)));
        em.close();
    }

    private static void findEmployeeByPhoneCount() {
        System.out.println("-- Employees who have more than 2 phones --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery(
                "SELECT e FROM Employee e where (SELECT COUNT(p) FROM e.phoneNumbers p) >= 2");
        List<Employee> resultList = query.getResultList();
        resultList.forEach(System.out::println);
        em.close();
    }

    private static void findDeptHavingAboveNetAverage() {
        EntityManager em = entityManagerFactory.createEntityManager();
        System.out.println("-- net average salary --");
        Object singleResult = em.createQuery("SELECT AVG(e.salary) FROM Employee e")
                                .getSingleResult();
        System.out.println(singleResult);

        System.out.println("-- Dept by average salaries --");
        List<Object[]> list = em.createQuery("SELECT e.dept, AVG(e.salary) FROM Employee e GROUP BY e.dept")
                                .getResultList();
        list.forEach(ar -> System.out.println(Arrays.toString(ar)));

        System.out.println("-- Dept having AVG salaries greater than net AVG salary --");
        Query query = em.createQuery(
                "SELECT e.dept, AVG(e.salary) FROM Employee e GROUP BY e.dept HAVING AVG(e.salary) > "
                        + "(SELECT AVG(e2.salary) FROM Employee e2)");
        List<Object[]> resultList = query.getResultList();
        resultList.forEach(r -> System.out.println(Arrays.toString(r)));
        em.close();
    }

    private static void findEmployeeWithLessThanAverageSalary() {
        System.out.println("-- Employees who have less than average salary --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery(
                "SELECT e FROM Employee e where  e.salary < (SELECT AVG(e2.salary) FROM Employee e2)");
        List<Employee> resultList = query.getResultList();
        resultList.forEach(System.out::println);
        em.close();
    }

    private static void runTypeEqualsQuery(EntityManagerFactory emf) {
        System.out.println("-- running TYPE with '=' query --");
        EntityManager em = emf.createEntityManager();
        List<Employee> entityAList = em.createQuery("SELECT t FROM Employee t WHERE TYPE(t) = FullTimeEmployee")
                                       .getResultList();
        entityAList.forEach(System.out::println);
        em.close();
    }

    private static void runTypeInQuery(EntityManagerFactory emf) {
        System.out.println("-- running TYPE with 'IN' query --");
        EntityManager em = emf.createEntityManager();
        List<Employee> entityAList =
                em.createQuery("SELECT t FROM Employee t WHERE TYPE(t) IN (ContractEmployee, PartTimeEmployee)")
                  .getResultList();
        entityAList.forEach(System.out::println);
        em.close();
    }

    private static void runTypeNotInQuery(EntityManagerFactory emf) {
        System.out.println("-- running TYPE NOT 'IN' query --");
        EntityManager em = emf.createEntityManager();
        List<Employee> entityAList =
                em.createQuery("SELECT t FROM Employee t WHERE TYPE(t) NOT IN (ContractEmployee, PartTimeEmployee)")
                  .getResultList();
        entityAList.forEach(System.out::println);
        em.close();
    }

    private static void runProjectQuery(EntityManagerFactory emf) {
        System.out.println("-- running query --");
        EntityManager em = emf.createEntityManager();
        List<Project> entityAList = em
                .createQuery("SELECT DISTINCT p FROM Project p JOIN TREAT(p.employees AS FullTimeEmployee) e"
                        + " WHERE e.annualSalary > 100000 ")
                .getResultList();
        entityAList.forEach(System.out::println);
        em.close();
    }

    private static void executeQuery() {
        System.out.println("-- executing query EXISTS --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery("SELECT e FROM Employee e"
                + " WHERE EXISTS (SELECT j from JobInfo j WHERE j.jobName = e.job)");
        List<Employee> resultList = query.getResultList();
        resultList.forEach(System.out::println);
        em.close();
    }

    private static void executeQuery2() {
        System.out.println("-- executing query NOT EXISTS --");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query query = em.createQuery("SELECT e FROM Employee e"
                + " WHERE NOT EXISTS (SELECT j from JobInfo j WHERE j.jobName = e.job)");
        List<Employee> resultList = query.getResultList();
        resultList.forEach(System.out::println);
        em.close();
    }