SELECT 구문

데이터베이스의 핵심처리는 검색입니다. 저장되어 있는 테이블에서 필요한 데이터를 뽑아내는 것이며

질의(query),추출(retrieve)이라고도 부릅니다. SQL은 검색과 관련되어 굉장히 많은 기능을 제공합니다.

@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 생략	
}

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

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);			
		}		
	}

Hibernate: 
    /* insert com.example.demo.data.Address
        */ insert 
        into
            address
            (address, age, name, phone_nbr, sex) 
        values
            (?, ?, ?, ?, ?)

SELECT와 FROM

전체선택

WHERE

필터 조합및 연산자

검색을위한 SQL의 연산자조합및 필터기능을 JPA 함수에서 대부분 지원합니다.

하지만, 검색조건이 너무 길어져 함수명의 가독성이 오히려 떨어질수 있기때문에

3가지 이상의 검색 조합일때는 JPQL 또는 QueryDSL을 사용할수도 있습니다.(아래에 설명)

==> JPA 함수쿼리 인터페이스는, 검색 필터/연산을 함수명네이밍 조합으로 할수 있다는게 특이합니다.

처음작성은 어색하지만, 완성이되면 가독성및 사용성이 훨씬더 간편해집니다.

GROUP BY

Group By를 이용하면, 데이터를 이쁘게 조각낼수가 있으며

이 조각을 그룹이라고 부릅니다.  이렇게 했을때 이점은

다양한 함수를 이용하여, 그룹별 집계처리가 가능합니다.

집계함수

집계 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문 지정이 가능합니다.

		List<AddressStatistics> addressStaticBySex = addressRepo.findSexCount();		
		addressStaticBySex.forEach(item -> {
			String itemString = String.format("%s %d", item.getFname(), item.getCnt());
			System.out.println(itemString);
		});

HAVING 절

지역별로 특정 나이 x(10세) 이상만을 대상으로 평균나이를 구하되,

측정된 평균 나이가 y(49세) 이상인 노령화 지역을 표시한다고 가정해봅시다.

x검색 조건은, row단위에서 계산조건을 제거해야함으로 기존과같이 where 절에 추가하면되며

y조건은, 계산된 그룹집합에서 다시, 제거 조건을 걸어야하기때문에 having절에 추가합니다.

having조건이 AVG(age) > 49 이상으로 하였다고 하면, 평균나이가 40인 경기지역의 그룹은 버려지게됩니다.

성능관점으로본다고 하면, 해당 평균을 구한후 버릴지 여부가 판단되기 때문에 , 각 Row(여기서는 10세 이상인것만)별로 계산은 수행되지만

havaing의 경우 마지막 아웃풋으로 나오기 직전 필터가 작동됨을 의미합니다.

List<AddressStatistics> addressStaticByRegion = addressRepo.findRegionAvgage(10,49);
// 결과
//대구 51.40
//마산 57.20
//부산 54.13
//서울 66.75
//제주 54.69
//창원 58.12

Order BY

지금까지 SELECT구문의 몇가지 예제를 살펴보았습니다. 그런데 그 결과 레코드는 어떠한 순일까요?

그것은 딱히 정해진 규칙없이 DBMS에따라 다르게 작동이됩니다. 어떠한 규칙에(입력순서,빠르게처리되는순서) 의해 작동될수도 있지만

DBMS의 처리순서 규칙을 어렵게 알아내었다고 해도  상황에따라 달라질수도 있기때문에

순서를 보장받으려면 명시적으로 지정을 하여야 합니다. 

Sort의 경우 가변적인 인자값으로 활용될수 있기때문에,  복합 Sort옵션 지정이 가능한 Sort객체를 사용하였습니다.

만약 순서를 옵션없이 고정하고 싶으면 ,OrderByAgeDesc 와같이 JPA함수쿼리 네이밍을 하면 됩니다.

SQL문으로 처리하고 싶으면, 앞장에서 배운 @Query 어노테이션을 통해 쿼리문 수행이 가능합니다.