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 어노테이션을 통해 쿼리문 수행이 가능합니다.

뷰

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

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

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

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

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

방식과 동일합니다.

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

서브쿼리

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

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

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

뷰의 관점에서 서브쿼리와 동일한 작동과정이 수행되었지만, 둘의 사용목적은 완전하게 다릅니다.

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

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 절 이후에는 사용불가

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