JPA JAVA EE INNER JOIN example:  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();
}
LEFT OUTER JOIN: 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();
}
INNER JOIN example: 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();
}
INNER JOIN FETCH Example: 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();
}
LEFT JOIN FETCH example: 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();
}
BETWEEN Example 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();
}
IN expression example: 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();
}
LIKE expression examples 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();
}
IS NULL example 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();
}
IS EMPTY examples 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();
}
MEMBER OF examples 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();
}
Aggregate Function examples: 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();
}
GROUP BY example: 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();
}
Subqueries: 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();
}
Polymorphic restriction using TYPE operator 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();
}
Downcasting to a sub type using TREAT 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();
}
EXISTS expression 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();
}
|
|
