JPA동영상, Querydsl 동영상교육, JPA, Querydsl, 페이징, PAging, Insert, Update, Delete, Grouping, Having, JPA교육, 자바교육, 스프링교육
http://ojc.asia/bbs/board.php?bo_table=LecJpa&wr_id=374
ojc.asia
https://www.youtube.com/watch?v=Xb0OEOiZ3Rg&list=PLxU-iZCqT52AlV-Y7UlwSQp8N6pMjijFX&index=6
https://www.youtube.com/watch?v=GrvbpLy46y8&list=PLxU-iZCqT52AlV-Y7UlwSQp8N6pMjijFX&index=5
https://www.youtube.com/watch?v=w6XoZ3Luzus&list=PLxU-iZCqT52AlV-Y7UlwSQp8N6pMjijFX&index=4
실습
다음 요청사항에 맞는 코드를 작성하시오.
- emp 테이블에서 직업의 종류를 출력하시오.
- 이름이 "S"로 시작하는 직원을 출력하시오.
- 이름중에 "s" 또는 "S"가 들어가는 직원을 출력하시오.
- 이름중에 "A"자가 포함되는 직원을 출력하시오.
- 직원번호가 1000부터 2000번 사이에 있는 직원을 출력하시오.
8. Paging
페이징은 offset과 limit를 사용합니다. 페이징처리를 위해서는 전체건수를 알아야 하므로 count 쿼리를 내부적으로 실행합니다.
public QueryResults<Emp> getEmpForPaging(long offset, long count) {
JPAQuery<Emp> query = queryFactory.select(emp).from(emp)
.orderBy(emp.empno.asc());
query.offset(offset).limit(count);
QueryResults<Emp> result = query.fetchResults();
return result;
}
@Test
public void testGetEmpForPaging() {
long offset = 10;
long count = 10;
QueryResults<Emp> result = dao.getEmpForPaging(offset, count);
long resultCount = result.getTotal();
System.out.println("resultCount = " + resultCount);
if (!result.isEmpty()) {
List<Emp> emps = result.getResults();
for (Emp emp : emps) {
System.out.println(emp);
}
}
}
4.2.2. Insert, Update, Delete
데이터를 조작하는 방법을 살펴보겠습니다. 코드 자체가 설명적이므로 자세한 설명은 생략합니다.
DmlDeptDao.java
package com.example.employee.repository;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import org.springframework.stereotype.Repository;
import com.example.employee.model.Dept;
import com.example.employee.model.QDept;
import com.querydsl.jpa.impl.JPADeleteClause;
import com.querydsl.jpa.impl.JPAQuery;
import com.querydsl.jpa.impl.JPAUpdateClause;
@Repository
public class DmlDeptDao {
@PersistenceContext
private EntityManager entityManager;
// select count(d.deptno) as col_0_0_ from dept d
public long count() {
QDept dept = QDept.dept;
JPAQuery<?> query = new JPAQuery<Void>(entityManager);
long count = query.select(dept.deptno).from(dept).fetchCount();
return count;
}
// select
// d.deptno as deptno1_0_,
// d.dname as dname2_0_,
// d.loc as loc3_0_
// from dept d
// where d.deptno=10
public Dept getDeptByDeptno(long deptno) {
QDept dept = QDept.dept;
JPAQuery<?> query = new JPAQuery<Void>(entityManager);
Dept result = query.select(dept).from(dept).where(dept.deptno.eq(deptno)).fetchOne();
return result;
}
// insert into dept (dname, loc, deptno) values ('XXX', 'SEOUL', 50)
public long insert(Dept dept) {
// Querydsl은 입력쿼리를 따로 지원하지 않는다.
// 엔티티매니저를 통해 입력을 처리한다.
entityManager.persist(dept);
entityManager.flush();
return dept.getDeptno();
}
// update dept set dname='YYY' where deptno=50
public long update(Dept dept) {
QDept department = QDept.dept;
long affected = new JPAUpdateClause(entityManager, department)
.where(department.deptno.eq(dept.getDeptno()))
.set(department.dname, dept.getDname()).execute();
return affected;
}
// delete from dept where deptno=50
public long delete(Long deptno) {
QDept department = QDept.dept;
long affected = new JPADeleteClause(entityManager, department)
.where(department.deptno.eq(deptno)).execute();
return affected;
}
}
JPAQueryFactory 객체를 주입받아 사용하는 경우 사용방법은 다음과 같습니다.
long affected = queryFactory.update(department)
.where(department.deptno.eq(dept.getDeptno()))
.set(department.dname, dept.getDname()).execute();
long affected = queryFactory.delete(department)
.where(department.deptno.eq(deptno)).execute();
DmlDeptDaoTest.java
package com.example.employee.repository;
import static org.hamcrest.CoreMatchers.is;
import static org.junit.Assert.assertThat;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.test.context.SpringBootTest.WebEnvironment;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.transaction.annotation.Transactional;
import com.example.employee.model.Dept;
@Transactional
@RunWith(SpringRunner.class)
@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)
public class DmlDeptDaoTest {
@Autowired
private DmlDeptDao deptDao;
@Test
public void testCount() {
long count = deptDao.count();
System.out.println("count = "+count);
assertThat(count, is(4L));
}
@Test
public void testGetDeptByDeptno() {
Dept dept = deptDao.getDeptByDeptno(10L);
System.out.println(dept);
assertThat(dept.getDname(), is("ACCOUNTING"));
}
@Test
public void testInsert() {
long count = deptDao.count();
Dept dept = new Dept();
dept.setDeptno(50L);
dept.setDname("XXX");
dept.setLoc("SEOUL");
long key = deptDao.insert(dept);
System.out.println(key);
assertThat(key, is(50L));
assertThat(deptDao.count(), is(count+1));
}
@Test
public void testUpdate() {
Dept dept = new Dept();
dept.setDeptno(50L);
dept.setDname("XXX");
dept.setLoc("SEOUL");
// insert into dept (dname, loc, deptno) values ('XXX', 'SEOUL', 50)
deptDao.insert(dept);
dept.setDname("YYY");
// update dept set dname='YYY' where deptno=50
long affected = deptDao.update(dept);
assertThat(affected, is(1L));
// select
// d.deptno as deptno1_0_,
// d.dname as dname2_0_,
// d.loc as loc3_0_
// from dept d
// where d.deptno=50
Dept result = deptDao.getDeptByDeptno(dept.getDeptno());
assertThat(result.getDname(), is("YYY"));
}
@Test
public void testDelete() {
long count = deptDao.count();
Dept dept = new Dept();
dept.setDeptno(50L);
dept.setDname("XXX");
dept.setLoc("SEOUL");
deptDao.insert(dept);
assertThat(deptDao.count(), is(count+1));
long affected = deptDao.delete(dept.getDeptno());
assertThat(affected, is(1L));
assertThat(deptDao.count(), is(count));
}
}
4.2.3. Grouping, Having
GroupEmpDao.java
package com.example.employee.repository;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import org.springframework.stereotype.Repository;
import com.example.employee.model.QEmp;
import com.querydsl.core.Tuple;
import com.querydsl.jpa.impl.JPAQuery;
@Repository
public class GroupEmpDao {
@PersistenceContext
private EntityManager entityManager;
public List<Tuple> getEmpGroupByDeptno(){
QEmp emp = QEmp.emp;
JPAQuery<?> query = new JPAQuery<Void>(entityManager);
List<Tuple> rows = query
.select(emp.dept.deptno,
emp.count(),
emp.sal.avg(),
emp.sal.max())
.from(emp).groupBy(emp.dept.deptno).fetch();
return rows;
}
public List<Tuple> getEmpGroupByDeptnoHaving(){
QEmp emp = QEmp.emp;
JPAQuery<?> query = new JPAQuery<Void>(entityManager);
List<Tuple> rows = query
.select(emp.dept.deptno,
emp.count(),
emp.sal.avg(),
emp.sal.max())
.from(emp).groupBy(emp.dept.deptno)
.having(emp.count().goe(5)).fetch();
return rows;
}
}
GroupEmpDaoTest.java
package com.example.employee.repository;
import java.util.List;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.test.context.SpringBootTest.WebEnvironment;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.transaction.annotation.Transactional;
import com.querydsl.core.Tuple;
@Transactional
@RunWith(SpringRunner.class)
@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)
public class GroupEmpDaoTest {
@Autowired
private GroupEmpDao empDao;
@Test
public void testGetEmpGroupByDeptno() {
List<Tuple> rows = empDao.getEmpGroupByDeptno();
for (Tuple row : rows) {
System.out.println("--------------------");
System.out.println(row.get(0, Long.class));
System.out.println(row.get(1, Long.class));
System.out.println(Math.round(row.get(2, Double.class)));
System.out.println(row.get(3, Long.class));
}
}
@Test
public void testGetEmpGroupByDeptnoHaving() {
List<Tuple> rows = empDao.getEmpGroupByDeptnoHaving();
for (Tuple row : rows) {
System.out.println("--------------------");
System.out.println(row.get(0, Long.class));
System.out.println(row.get(1, Long.class));
System.out.println(Math.round(row.get(2, Double.class)));
System.out.println(row.get(3, Long.class));
}
}
}
SQL 로그
select
e.deptno as col_0_0_,
count(e.empno) as col_1_0_,
avg(e.sal) as col_2_0_,
max(e.sal) as col_3_0_
from emp e
group by e.deptno
select
e.deptno as col_0_0_,
count(e.empno) as col_1_0_,
avg(e.sal) as col_2_0_,
max(e.sal) as col_3_0_
from emp e
group by e.deptno
having count(e.empno)>=5
#JPA, #Querydsl, #JPA페이징, #JPAPaging, #JPAInsert, #JPAUpdate, #JPADelete, #JPAGrouping, #JPAHaving, #PA, Querydsl, JPA페이징, JPAPaging, JPAInsert, JPAUpdate, #PADelete, JPAGrouping, JPAHaving,
