sql 날짜 비교 where 절 조건 넣을 때 주의할 점

- 문제 : date 자료형에 where 조건 적용 -

sql 에서 WHERE 절로 검색 조건을 적용할 때,
날짜에 공백을 넣으면 제대로 작동하지 않는다.

예를 들어

SELECT * FROM tb_purchase p;

 

위는 결과가 제대로 나오지만
아래는 검색 결과가 하나도 나오지 않는다.

SELECT * FROM tb_purchase p WHERE p.purdate < ''

문제는 " " 라는 공백 조건과 꺾쇠 방향이었다.

SELECT * FROM tb_purchase p WHERE p.purdate > '' --모든 결과 읽어옴
SELECT * FROM tb_purchase p WHERE p.purdate < '' --어떤 결과도 읽어오지 않음

 

 

 

위처럼 초기 화면으로 들어갔을 때는 결과가 여러 건 출력된다.
하지만 구매 일자에 아무 날짜도 넣지 않고 검색 버튼을 누르면
아래처럼 검색 결과가 전혀 나오지 않았다.

 

 

- 고민 : 값이 없을 때는 (" ")가 아니라 null이 들어가도록 -

myBatis 에서는 null 체크를 이미 걸어 두었다.

<if test ="dateEnd != null">

 

근데 로그를 보니, null로 인식하지 않고,
"" 공백으로 인식하여 검색 조건에 그대로 들어갔다.

null 체크에 공백 조건까지 추가하였다.

<if test ="dateEnd != null and dateEnd != '' ">

 

그럼에도 공백일 때는 검색 조건이 활성화되었다.

값이 없을 때는 확실히 null이 들어가도록 해야 했다.
먼저 프론트를 수정해 보았다.
초기 화면인지 검색 결과인지에 따라서 ajax에 넣는 파라미터가 달랐는데,
아무 값도 없을 때는 확실히 null을 넣어서 보내도록 만들어야 했다.

//소스 -  javascript 
if (type == getOrderListType.ready) {
    console.log('ready list')
    data = {
        currentPage: 1
    };
} else if (type == getOrderListType.search) {
    console.log('search list')
    data = {
        searchKeyword: $('input#searchModelName').val(),
        dateStart: $('input#purDateStart').val(),
        dateEnd: $('input#purDateEnd').val(),
        currentPage: currentPage,
    };
}

$.ajax({
    url: 'getOrderList',
    data: data,
    method: 'POST',
    success: function(result) {
        console.log(result);
    }
});

controller로 보내는 파라미터인

data 부분을 보자.

//수정 전
    data = {
        searchKeyword: $('input#searchModelName').val(),
        dateStart: $('input#purDateStart').val(),
        dateEnd: $('input#purDateEnd').val(),
        currentPage: currentPage,
    };

//수정 후
    data = {
        searchKeyword: $('input#searchModelName').val() || null,
        dateStart: $('input#purDateStart').val() || null,
        dateEnd: $('input#purDateEnd').val() || null,
        currentPage: currentPage,
    };

수정한 뒤에도 제대로 작동하지 않았다.
javascript에서는 확실히 null로 보내더라도,
controller에서 받을 때 null 이면 String '' 로 받고 있었다.
해결할 방법을 두 가지를 생각해 봤다.

 

- 해결 방법 1 -

첫 번째 방법은 백엔드에서 강제로 null 을 넣는 것이다.
공백일 경우 controller로 넘어온 뒤에 강제로 null을 넣는다.
javascript에서 공백을 넘기든 null을 넘기든
controller에서는 공백( ' ' ) 으로 받는다.
그러면 받고 나서 공백 값을 다시 null로 넣으면 될 일이다.
controller에서 파라미터를 받는 DTO 클래스의 setter 메소드를 건드렸다.

//java 수정 전
public void setDateEnd(String dateEnd) {
	this.dateEnd = dateEnd;
}

//java 수정 후
public void setDateEnd(String dateEnd) {
    if("".equals(dateEnd)) {
    	dateEnd = null;
	}
    this.dateEnd = dateEnd;
}

여기서 주의할 점이 한 가지 있다.

if( dateEnd.equals("") ) { // do something }

 

공백을 위처럼 체크하는 것보다 아래처럼 체크하는 쪽이 더 안전하다.

if( "".equals(dateEnd) ) { // do something }

 

이유는 null 포인터 에러 때문이다.
앞에 객체 변수가 오는 게 안정감이 들어서 위쪽처럼 하고 싶은 느낌이 든다.
하지만 dateEnd 변수값이  null이라면 null pointer 에러가 발생한다.
반면에 "" 는 값이 확실한 객체이므로 null 에러가 발생할 걱정이 없다.
따라서 공백 체크를 할 때는 순서를 아래처럼 하는 습관을 들이면 좋다.

 

 - 해결 방법 2 -

두 번째 방법은 프론트에서 '%'를 넣는 방법이다.
javascript에서 null을 보내든 '' 공백을 보내든
controller에서는 '' 공백으로 받는다.
반면에 null 또는 공백이 아닌 값을 보내면 
controller에서 공백으로 바꾸어서 받지 않으므로
sql문에는 그대로 들어갈 것이다.

sql 에서 '%' 는 '와일드 카드'라고도 한다.
카드 게임에서 조커가 있는 것처럼,
sql에서 '%' 는 어떤 문자로도 대체될 수 있다.
자세한 사용법은 sql 와일드 카드 등으로 검색해보면 된다.

아래 두 sql 명령문은 같은 결과를 출력한다.

SELECT * FROM tb_purchase p;
SELECT * FROM tb_purchase p WHERE p.loginID like '%';

 

따라서 javascript에서 null체크를 해 보고,
공백이나 null 이라면 '%' 를 넣어서 ajax에 보내면 된다.

 

//javascript 수정 전
$.ajax({
    url: 'getOrderList',
    data: {
        keyword : document.querySelector('input#keyword').value
    },
    method: 'POST',
    success: function(result) {
        console.log(result);
    }
});

//javascript 수정 후
$.ajax({
    url: 'getOrderList',
    data: {
        keyword : document.querySelector('input#keyword').value || '%'
    },
    method: 'POST',
    success: function(result) {
        console.log(result);
    }
});

> 주의할 점 : 최소값 / 최대값
여기서도 주의할 점이 있다.
날짜 자료형일 때는 제대로 작동하지 않을 수 있다.

SELECT * FROM tb_purchase p
SELECT * FROM tb_purchase p WHERE p.purdate > ''
SELECT * FROM tb_purchase p WHERE p.purdate > '%'

위 sql 문은 세 가지 모두 같은 결과를 보여준다.
하지만 아래 두 줄은 서로 다른 결과다.

SELECT * FROM tb_purchase p
SELECT * FROM tb_purchase p WHERE p.purdate < ''

날짜 자료형에서 공백을 최소값으로 두면
모든 날짜를 포함한 결과를 출력하지만,
공백을 최대값으로 두면 아예 어떤 결과도 포함하지 않는다.

 

-220412