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
         

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

SELECT와 FROM

전체선택

WHERE

필터 조합및 연산자

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

...

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

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

GROUP BY

...

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

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

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

집계함수

...

결과값에서 필터걸기

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

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가

...

집계 DTO정의

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

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

...

정의없이 모든 Object에 대응하는 map 사용도 가능하지만, DAO정의 없을시 사용레벨에서 필드명을 파악해야하고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문을 사용하였습니다.

DAO가 Entity가 변형이되거나 복잡한 집계를 내는것은 JPA 함수쿼리로 모두 작성하기 어려우며

...

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

HAVING 절

...

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

Order BY

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

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

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

...

.69
//창원 58.12

Order BY

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

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

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

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

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

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

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

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

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

...

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

뷰

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

...

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

서브쿼리

...

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

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

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

...

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

JPQL에서 서브쿼리 사용

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

...

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

서브쿼리 실행계획 검증

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

...

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

JPA Query 수행방법

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

...

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

Query Method

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

...

어플리케이션에서도 자주 사용하는,검색 조건 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
)

...

만약 오라클에만 있는 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();

...

동일한 목표를 가지고, 가독성이 더좋은 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);

...