RDB를 조작하는 언어인 SQL문을 JPA와 연관하여 SQL문을 함께 정리를하였습니다.
일반적인 SQL문이 JPA에서는 어떻게 표현이 되는지? 샘플화 하였습니다.
SELECT 구문
데이터베이스의 핵심처리는 검색입니다. 저장되어 있는 테이블에서 필요한 데이터를 뽑아내는 것이며
질의(query),추출(retrieve)이라고도 부릅니다. SQL은 검색과 관련되어 굉장히 많은 기능을 제공합니다.
주소 테이블 생성
Select 구문을 익히기전에 JPA를 통해 테이블을 생성하고 샘플 데이터를 입력하겠습니다.
어플리케이션에서는 데이터모델뿐 아니라 JSON유틸리티/리퀘스트유틸/라우터처리 등
여러가지 성격이 다른 클래스를 다루게 됩니다. 여기서는 data모듈하위에 데이터베이스에
관련된 테이블의 정의를 집합하도록 하겠습니다.
JPA-주소테이블
@Entity
public class Address {
@Id
@GeneratedValue
@Column(name = "ADDRESS_ID")
private Integer id;
private String name;
private String phoneNbr;
private String address;
private String sex;
private int age;
//getter,setter 생략
}
SQL-주소테이블
CREATE TABLE `address` ( `address_id` int(11) NOT NULL AUTO_INCREMENT, `address` varchar(255) DEFAULT NULL, `age` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `phone_nbr` varchar(255) DEFAULT NULL, `sex` varchar(255) DEFAULT NULL, PRIMARY KEY (`address_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
JPA-주소 데이터 입력
package com.example.demo.data;
import org.springframework.data.repository.CrudRepository;
public interface AddressRepo extends CrudRepository<Address, Long>{
}
//사용예
@Autowired
private AddressRepo addressRepo;
public void InsertAddressData(){
String[] addArray = {
"서울","대구","부산","창원","마산","제주"
};
for(int i=0; i<100;i++) {
double random = Math.random();
double x = random*100;
int y = (int)x + 1; //Add 1 to change the range to 1 - 100 instead of 0 - 99
Address address = new Address();
address.setAge(y);
address.setAddress( addArray[i%6] );
address.setPhoneNbr(String.format("010-3333-1%04d", i));
if( i%2 == 0 ) {
address.setName(String.format("민수%d", i+1));
address.setSex("남");
}else {
address.setName(String.format("영희%d", i+1));
address.setSex("여");
}
addressRepo.save(address);
}
}
JPA에서는, SQL문을 직접사용하여 Insert를 하지 않습니다. JPA로 작성된 데이터모델에
Insert를 하기위해서는, CrudRepository 인터페이스를 작성후, 배열을 조작하고
관련 함수를 사용하는것만으로 실제 데이터베이스 테이블에 데이터를 추가하는것과 동일한 효과를 낼수 있습니다.
JPA의 Crud객체는, 데이터 Insert를 효율적으로 수행하기위해
개별 기본 엔티티 추가 / 리스트형태의 엔티티추가 두개의 저장 인터페이스를 지원합니다.
SQL-주소 데이터 입력
Hibernate:
/* insert com.example.demo.data.Address
*/ insert
into
address
(address, age, name, phone_nbr, sex)
values
(?, ?, ?, ?, ?)
실제 위와같은 SQL문을 실행하여 실제 데이터베이스에 입력을 하게 됩니다.
JDBC를 통해 SQL문을 어플리케이션에서 직접 제어한다고 했으면 위 구문에서 각 인자값을 전달하는
함수를 제작하였을것입니다.
SELECT와 FROM
전체선택
| SQL | JPA |
|---|---|
| addressRepo.findAll(); |
WHERE
| SQL | JPA |
|---|---|
| JPA 함수쿼리 작성 public interface AddressRepo extends CrudRepository<Address, Long>{ 조회함수사용 addressRepo.findBySex("여"); |
필터 조합및 연산자
검색을위한 SQL의 연산자조합및 필터기능을 JPA 함수에서 대부분 지원합니다.
하지만, 검색조건이 너무 길어져 함수명의 가독성이 오히려 떨어질수 있기때문에
3가지 이상의 검색 조합일때는 JPQL 또는 QueryDSL을 사용할수도 있습니다.(아래에 설명)
| SQL | JPA | |
|---|---|---|
| < | WHERE 44 < age | findByAgeGreaterThan(int age); |
| <= | WHERE 44 <= age | findByAgeGreaterThanEqual(int age); |
| > | WHERE age < 44 | findByAgeLessThan(int age); |
| 범위 | WHERE age BEETWEEN 10 and 20 | findByAgeBetween(int low,int high); |
| 부분범위 | WHERE age in ( 34,35,36) | findByAgeIn(int age[]); |
| AND | WHERE 40 < age AND sex='남' | findByAgeGreaterThanAndSex(int age,String sex); |
| OR | WHERE 40 < age OR sex='여' | findByAgeGreaterThanOrSex(int age,String sex); |
GROUP BY
집계함수
| 함수 | 용도 | 함수 | 용도 |
|---|---|---|---|
| AVG | MIN | ||
| COUNT | SUM | ||
| COUNT_BIG | STDEV | ||
| GROUPING | STDEVP | ||
| GROUPING_ID | VAR | ||
| CHECKSUM_AGG | VARP | ||
| MAX |
집계 DTO정의
Address데이터를 가지고 특정집계 수행을 한다고 하면 , ex> 성별에따른 회원수 라고 한다면
Address가 가진 2차원 테이블의 모습이 바뀌게 됩니다.
Address는 이미 정의를 하였지만, 성별에 따른 회원수를 표현하는 오브젝트는 새롭게 정의할 필요가 있습니다.
정의없이 모든 Object에 대응하는 map 사용도 가능하지만, DAO정의 없을시 사용레벨에서 필드명을 파악해야하고
제어가 어려워질수 있습니다.
package com.example.demo.data;
public class AddressStatistics {
private String fname; //집계대상 필드는 변동이될수 있기때문에 한정짓지말고,공용이름을 사용합니다.
private Long cnt;
public AddressStatistics(String fname,Long cnt) {
this.fname = fname;
this.cnt = cnt;
}
}
쿼리 정의
쿼리검색 조건을 JPA함수형이 아닌, SQL문과 비슷한 JPQL문을 사용하였습니다.
DAO가 변형이되거나 복잡한 집계를 내는것은 JPA 함수쿼리로 모두 작성하기 어려우며
JPQL/네이티브SQL/Query DSL등을 같이 사용할수가 있습니다. 여기서는 가장간단하게
JPQL(JPA Query Language) 을 사용 하였으며, 함수명을 지정하고 해당 함수에 대응하는 SQL문 지정이 가능합니다.
| SQL | JPA |
|---|---|
| SELECT sex,count(*) FROM db_example.address group by sex | public interface AddressRepo extends CrudRepository<Address, Long>{ @Query("select new com.example.demo.data.AddressStatistics(t.sex,count(t) as cnt) from Address t group by t.sex") } |
SELECT sex,count(*) FROM db_example.address where address group by sex where address like '%Seoul' | @Query("select new com.example.demo.data.AddressStatistics(t.sex,count(t) as cnt) from Address t group by t.sex where t.address like %?1") List<AddressStatistics> findSexCount(String firstAddress); |
List<AddressStatistics> addressStaticBySex = addressRepo.findSexCount();
addressStaticBySex.forEach(item -> {
String itemString = String.format("%s %d", item.getFname(), item.getCnt());
System.out.println(itemString);
});
HAVING 절
지역별로 특정 나이 x 이상만을 대상으로 평균나이를 구하되,
측정된 평균 나이가 y이상인 지역만 표시한다고 가정해봅시다.
x검색 조건은, row단위에서 계산조건을 제거해야함으로 기존과같이 where 절에 추가하면되며
y조건은, 계산된 그룹집합에서 다시, 제거 조건을 걸어야하기때문에 having절에 추가합니다.
JPA에서 인자값 전달시 Type을 명시적으로 매칭시켜야하며 , 함수의 인자값이
Param("minage") => :minage , SQL문으로 전달될수 있음을 알게되었습니다.
| SQL | JPA |
|---|---|
SELECT address,AVG(age) FROM db_example.address WHERE age > x GROUP BY address HAVING AVG(age) > y | @Query("select new com.example.demo.data.AddressStatistics(t.address,AVG(t.age)) from Address t where t.age > :minage GROUP BY t.address HAVING AVG(t.age) > :filterage ") List<AddressStatistics> findRegionAvgage( |
List<AddressStatistics> addressStaticByRegion = addressRepo.findRegionAvgage(10,49); // 결과 //대구 51.40 //마산 57.20 //부산 54.13 //서울 66.75 //제주 54.69 //창원 58.12