카테고리 보관물: 프로그래밍

MySQL 프로시저 변수값에 따른 where 및 order by 변경

개요

MySQL 프로시저내에서 인수로 전달받은 값에 따라 where 및 order by 부분을 달리하고 싶은 경우가 있습니다. Dynamic SQL로 가능하지만 이 글에서는 case 문을 이용한 방법을 살펴보겠습니다.

where 절

프로시저의 인수로 전달받은 값에 따라 where 절의 조건을 변경하는 방법입니다. 회사ID 가 0이면 모든 회사를 보여주고 회사ID가 0 보다 크면 해당하는 자료만 보여주는 쿼리를 보겠습니다. _company_id 가 인수로 전달받은 변수입니다.

select
    *
from
    company
where
    1 = 1
    and case when _company_id > 0 then company_id = _company_id else 1 = 1 end

7행을 보면 and 뒤쪽에 case 문을 이용해서 조건을 변경한 것을 알 수 있습니다.

order by

프로시저 인수로 특정한 필드의 정렬 조건을 넘겨받는 경우가 있습니다. 토글 형태로 오름차순과 내림차순이 번갈아 가면서 order by 부분에 적용되어야 합니다. 정렬을 적용해야 하는 필드가 적으면 조건으로 나누어 처리할 수 있습니다. 하지만 필드의 개수가 많아지면 조건으로 나누어 작업하는 것이 조합이 많아지게 되어 불가능 합니다. where 절과 마찬가지로 case 문을 이용해서 order by 부분의 필드와 정렬기준을 변경할 수 있습니다.

회사이름과 직원수 필드로 정렬하는 쿼리문을 예로 들어보겠습니다. _company_name_sort_order, _employee_sort_order 가 인수로 전달받은 변수입니다.

select
    *
from
    company
where
    company_id > 0
order by
    case when lower(_company_name_sort_order) = 'a' then company_name end asc
    , case when lower(_company_name_sort_order) = 'd' then company_name end desc
    , case when lower(_employee_sort_order) = 'a' then employee end asc
    , case when lower(_employee_sort_order) = 'd' then employee end desc

8-11행을 보면 case 문으로 전달된 변수의 값으로 정렬기준을 변경한 것을 알 수 있습니다. 쿼리문이 이상하게 보이는 것 같지만 이런 방식으로 프로시저의 크기를 줄일 수 있습니다.

이상으로 MySQL 프로시저 변수값에 따른 where 및 order by 변경 방법에 대해 알아보았습니다.

Android ROOM 사용한 Master/Detail 관계 정의

개요

Android ROOM 은 Android Jetpack 에 포함된 컴포넌트 입니다. SQLite DB에 쉽게 접속하고 이용할 수 있게 해 주는 일종의 ORM(Object-relational mapping) 라이브러리 입니다. 이전에는 SQLite DB에 접속하려면 정형화된 별도의 클래스를 사용했습니다. 조금 지루한 작업이라고 할 수 있었습니다. 이것을 줄여주고 쉽게 SQLite DB 연동작업을 할 수 있도록 도와줍니다.

기초적인 연동방법은 좋은 글이 많으므로 그것을 참고하시면 됩니다. 이 글에서는 부모가 되는 테이블과 자식이 되는 테이블을 어떻게 정의하는지 살펴보도록 하겠습니다. 언어는 코틀린(Kotlin)을 기준으로 합니다.

Master/Detail 관계 정의

Master/Detail 관계는 기본적으로 1:N(일대다) 으로 이해하시면 됩니다. 하나의 부모에 자식이 여럿있는 개념입니다. 1:N(일대다) 관련 자료를 찾아보시면 더 자세한 내용을 보실 수 있습니다.

Android ROOM 에서는 클래스 파일에 테이블을 정의합니다. 코드는 다음과 같습니다.

@Entity(tableName = "test_parent")
class TestParent(@PrimaryKey @ColumnInfo(name = "parent_seq") var parentSeq: Int,
          @ColumnInfo(name = "parent_name") var parentName: String?,
.
.
.
)

@Entity(
    tableName = "test_detail",
    indices = [Index("parent_seq")],
    foreignKeys = [ForeignKey(entity = TestParent::class, parentColumns = ["parent_seq"], childColumns = ["parent_seq"], onDelete = ForeignKey.NO_ACTION)]
)

class TestDetail(@PrimaryKey(autoGenerate = true) @ColumnInfo(name = "test_detail_seq") var testDetailSeq: Int,
                @ColumnInfo(name = "parent_seq") var parentSeq: Int,
.
.
.
)

12행이 핵심입니다. 부모 컬럼과 자식 컬럼이 지정된 것을 볼 수 있습니다. onDelete는 부모 테이블의 자료가 삭제되었을때의 동작입니다. 예제에서는 아무런 동작도 하지 않도록 했습니다. 각자의 상황에 맞는 값을 지정하면 됩니다. 사용할 수 있는 값과 의미는 다음의 주소에서 확인할 수 있습니다.

https://developer.android.com/reference/android/arch/persistence/room/ForeignKey.html#CASCADE

결과만 보면 간단한데 Master/Detail 관계를 정의한 예제를 거의 찾아볼 수 없었습니다. 코틀린(Kotlin)으로 된 예제는 없었습니다. 위 코드를 참조하셔서 필요한 부분에 적용하시면 됩니다.

필요한 필드만 업데이트

android ROOM을 이용해서 테이블을 갱신(update)해야 하는 경우가 있습니다. 기본적인 예제를 적용해보면 모든 필드가 갱신되는 것을 알 수 있습니다. 필요한 필드만 갱신하려면 Query 형태로 정의해야 합니다. Data Object Access(DAO) 클래스에 다음과 같이 정의합니다.


    @Query("update test_parent set parent_name = :parentName, photo = :parentPhoto, memo = :parentMemo where parent_seq = :parentSeq")
    fun updateParent(parentSeq:Int, parentName:String?, parentPhoto:String?, parentMemo:String?)

변수로 값을 전달해서 Query 내에 :변수명 형태로 대입해서 필요한 필드만 갱신 되도록 작업하시면 됩니다.

이상 Android ROOM 라이브러리를 사용한 Master/Detail 관계 정의에 대해서 알아보았습니다.