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 함수쿼리 인터페이스는, 검색 필터/연산을 함수명네이밍 조합으로 할수 있다는게 특이합니다.

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

결과값에서 필터걸기

최종 쿼리가 실행된 내용을 가지고, 응용프로그램에서의 필터방법입니다.

1차적인 필터는 DB에서할수도 있지만, 2차 필터를 어플리케이션에서 수행하는 방법입니다.

메모리에서 리스트가 복제됨을 유의하여 사용합니다.

List<GoodsData> sList = sPage.getContent().stream()
	.filter( p -> p.getSalecnt() >= 0 )
	.collect(Collectors.toList());

함수쿼리 짧게만들기

public interface AddressRepo extends CrudRepository<Address, Long>{
	List<Address> findByAgeGreaterThanAndSex(int age,String sex);
	default List<Address> findList(int age,String sex)
	{
    	return findByAgeGreaterThanAndSex(age,sex)
	}
}

함수쿼리는 조건이 많아질수록 길어지는 단점이 있으며, 짧게 사용하고싶으면

위와같이 함수맵핑을 통해 짧게사용가능합니다.

GROUP BY

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

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

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

집계함수

집계 DTO정의

Address데이터를 가지고  특정집계 수행을 한다고 하면 , ex> 성별에따른 회원수 라고 한다면

Address가 가진 2차원 테이블의 모습이 바뀌게 됩니다. 

Address는 이미 정의를 하였지만, 성별에 따른 회원수를 표현하는 오브젝트는 새롭게 정의할 필요가 있습니다.

정의없이 모든 Object에 대응하는 map 사용도 가능하지만, Entity 를  정의해서 사용하느냐?

정의없이 구현레벨에서 분석해서 사용하느냐?  Entity의 생명주기에 따라 각각 장단점이 있습니다.

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문을 사용하였습니다.

Entity가 변형이되거나 복잡한 집계를 내는것은 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의 처리순서 규칙을 어렵게 알아내었다고 해도  상황에따라 달라질수도 있기때문에

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

Order by을 사용안했을시, 암묵적인 Order처리가 어떻게 되냐? 를 두고 때로 갑론을박이 이루어지기도합니다.

고성능 암묵적인 순서보장을 위해 DBMS의 숨겨진 특성을 이용하고 마치 규칙적인 오더를 발견 할수도 있지만

제공되는 기능은 아닐뿐더러 언제 그 규칙이 깨질지 예측이 불가능하며 동일 DBMS라 할지라도 버젼에따라

그 규칙이 유지되리란 보장이 없는것은 명확한 사실입니다.  이러한 숨겨진 특성을 가지고

DBMS의 성능을 비교한다는것은 소모적인 논쟁이 될가능성이 높습니다. 

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

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

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

뷰

데이터 베이스를 사용하다보면, 자주 사용하는 Select문이 생기며 개발중에 읽기 전용으로 공유되어

같이 사용하기도 합니다. 이럴때 데이터베이스안에 직접 읽기전용 Select문을 관리할수 있는 기능이

View이며 데이터를 가지고 있지 않고 수정불가능한것이 Table과의 차이이며

그외 읽기용도의 구조로서는 Table과 동일하다고 생각하면 되겠습니다.

JPA에서는 변경불가능한 설정인 Immutable이라는 속성만 지정하며, 나머지는 TableEntity를 지정하는

방식과 동일합니다.

==> addressViewRepo는 조회가능한 함수쿼리 작성만 가능합니다. 

서브쿼리

위 두 쿼리는 결과적으로 수행결과 가 같습니다.  View와 서브쿼리를 한꺼번에 설명을 하기 위한 예제이며

View는 실제적으로 데이터가 없기때문에, 추가적인 Select구문을 실행하는 중첩(nested)구조가 됩니다.

결국 서브쿼리 형태로 작동됨을 의미를 하고 From 구 이후에 직접 Select하는 방식을 서브쿼리라고 불립니다.

실제는 Select,From,Where 이후 모든 위치에 서브쿼리 작성이 가능하지만, 추가하는 위치에따라 성능적으로

문제가 되기 때문에 유의해야합니다.  뷰의 관점에서 서브쿼리와 동일한 작동이 수행되었지만, 둘의 사용목적은 완전하게 다릅니다.

서브쿼리가 어떻게 활용이 되는지 살펴보도록 하겠습니다.

서브 쿼리를 이용한 편리한 매칭조건 설정

select * from address_view av where av.name in( select name from vipUsers );

address를 조회하는 공통 뷰를 address_view라고 정의를 하였고, 이렇게 약속하였기때문에

어플리케이션에서는 address를 통해 직접 조회하기보다 view를 통해서만 조회만 가능합니다.

이것은 권한적으로도 어플리케이션 레벨에 특정한 정보(주민번호)를 은닉하여 정보조회를

보호하고자 할때또는, address에 대한 접근 권한 통제가 가능합니다. 

위 예제는 vip사용자의 이름이 별도로 관리되고 있고..( 이름이 유니크하다고 가정합시다)

address_view에서 where조건에 서브쿼리를 사용하여 특정테이블이 가진 데이터의 매칭 조건을 설정한 예입니다.

샘플 SQL은 서브쿼리부터 실행을 합니다. 

그래서 SELECT에서의 서브쿼리가 아래와같은 상수로 변경이됩니다.

select * from address_view av where av.name in( '민수','영희' );

이것은  where조건이 하드코딩이냐?  특정 테이블의 설정화? 이냐 차이이며

이러한 서브쿼리는 하드코딩을 없앨수 있는 요소로 활용이 될수 있습니다.

JPQL에서 서브쿼리 사용

JPQL 에서 서브쿼리는 몇가지 제약조건이 있습니다.

• WHERE , HAVING 절에서만 사용가능
• SELECT,FROM 절 이후에는 사용불가

public interface AddressViewRepo extends Repository<AddressView, Long>{			
	@Query("select t from AddressView t where t.name in (select v.name from VipUser v)")
	List<AddressView> findVipAddress();	
}


//쿼리조회
Iterable<AddressView> userList = addressViewRepo.findVipAddress();

위 샘플은, View테이블 조회 조건을 서브쿼리로 처리한 예입니다.

서브쿼리 실행계획 검증

서브쿼리는 성능적으로 안좋은 영향을 줄수 있기때문에, 서브쿼리 사용시에는

DBMS가 어떠한 실행계획을 세우고 수행이 되는지 체크할 필요가 있습니다.

filtered : row 수가  10/100 이기때문에 , 10개의 데이터를 찾으려고 100번의 순차 FullScan을 한 케이스이기때문에 최적탐색이

되었다라고는 볼수없으나,서브 쿼리를 사용하여 그런것은 아니며   where name in ('A','B','C')  형태의 하드코딩을 3번의 스캔으로

만으로 성능에 큰 변화없이 설정화가 되었다라고 알아둡시다.

JPA와 관련한 실행계획은 이후에 다시 다룰예정입니다.

JPA Query 수행방법

JPA에서 전략에따라, 다양한 방법으로  쿼리문 수행이 가능하다란것을 알아보았습니다.

JPA의 Repository Interface를 활용했을때 QueryMethod/JPQL 로만 충분히 다양한 SQL문 처리가 가능 한것을 살펴보았지만

SQL문을 Builder하는 몇가지 방법을 더 살펴보겠습니다. ( Criteria / QueryDSL)

Query Method

findByAgeGreaterThan(int age);
findBySex(String sex); 

JPA가 지원한는 Repository 인터페이스 내에서, 정의된 함수 네이밍이 검색 쿼리를 지원합니다.

어플리케이션에서도 자주 사용하는,검색 조건 3가지 이내 비교적 간단한 SQL문으로 유추되는 검색조건

에서 유용합니다.

JPQL

@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(
	@Param("minage") int minage,
	@Param("filterage") double filterage
)

SQL문이 비교적 복잡해지면, 작동 함수에 직접 Query문을 매칭시켜 작성가능합니다.

nativeQuery=false 옵션에따라 JPQL or NativeQuery 선택이 가능합니다.

만약 오라클에만 있는 Connect BY기능을 사용하려면 NativeQuery를 사용해야합니다.

Criteria Query

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Member> query = cb.createQuery(Member.class);
Root<Member> m = query.from(Member.class);
CreteriaQuery<Member> cq = query.select(m).where(cb.equal(m.get("username","kim"));
List<Member> resultList = em.createQuery(cq).getResultList();

JPQL문을 직접 만들수 있는 JPQL빌드 입니다.

동적 쿼리작성에 편하지만 복잡하고 가독이 어려운 단점이 있습니다.

동일한 목표를 가지고, 가독성이 더좋은 Query DSL이 권장됩니다.

Query DSL

JPAQuery query = new JPAQuery(em);
QMember = member = Qmember.member;
List<Member> members = 
	query.from(member)
	.where(member.username.eq("Kim"))
	.list(member);