Dynamic Query 만들 떄 에로사항

JPA를 이용 중 RDS Table에 한 칼럼에 배열형태의 자료형을 넣는 대표적인 꼼수? 를 사용했다, AttributeConverter를 사용해서, 문자열 , 로 구분해서 그냥 집어넣은 것

@Convert
class CopyRightHolderConverter(
) : AttributeConverter<MutableList<CopyRightHolder?>, String> {

    val delimiters = ","

    override fun convertToDatabaseColumn(attribute: MutableList<CopyRightHolder?>): String {
        val joiner = StringJoiner(delimiters)
        for (copyRightHolder in attribute) {
            joiner.add(copyRightHolder?.info)
        }
        return joiner.toString()
    }

    override fun convertToEntityAttribute(dbData: String?): MutableList<CopyRightHolder?> {

        val stringList = dbData?.split(delimiters) ?: return mutableListOf()
        var copyRightHolders: MutableList<CopyRightHolder?> = mutableListOf()
        for (code in stringList) {
            val copyRightHolder = CopyRightHolder.ofCode(code)
            copyRightHolders.add(copyRightHolder)
        }
        return copyRightHolders
    }


}

그런데 동적 쿼리를 만들 때, 에로사항이 이뤄났다. 나는 JPA 쿼리빌더 라이브러리 중, line/kotlin-jdsl 를 사용중인데, 일반적으로 동적쿼리를 이렇게 작성하였다.

inline fun <reified T> SpringDataCriteriaQueryDsl<T>.isSongSearchable(
    searchCondition: SongSearchCondition,
): PredicateSpec {
    val (songName, albumName, audioType, copyRightHolders, isShow) = searchCondition


    return and(
        songName?.let { column(Song::name).like("%$it%") },
        albumName?.let { nestedCol(col(Song::album as KProperty1<Song, Album>), Album::name).like("%$it%") },
        audioType?.let { column(Song::audioType).equal(it) },
//        copyRightHolders?.let {
//            column(Song::copyRightHolders).`in`(it.)
//        },
        isShow?.let {
            if (it) column(Song::deletedAt).isNull()
            else column(Song::deletedAt).isNotNull()
        }
    )
}

문제는 주석처리를 했다시피, criteriaBuilder 기반의 JDSL이 요런 상황을 지원해주지 않는 다는 것이다 ㅠㅠ

그래서 어쩔 수 없이 직접 문자열로 동적 쿼리를 만들어내는 함수를 만들어냈다..

fun generateSongSearchDynamicQuery(searchCondition: SongSearchCondition, rootPath: String): String {

    val (songName, albumName, audioType, copyRightHolders, isShow) = searchCondition
    val andSql = " 1=1"
    var whereSql = ""
    //println("???? $copyRightHolders")
    if (songName != null) {
        whereSql += " and $rootPath.name like '%$songName%' "
    }
    if (albumName != null) {
        whereSql += " and a.name like '%$albumName%' "
    }
    if (audioType != null) {
        whereSql += " and $rootPath.audio_type like '%$audioType%' "
    }
    if (copyRightHolders != null && copyRightHolders.isNotEmpty()) {
        val replace = copyRightHolders.map { it?.info }
            //.joinToString(",")
        for (word in replace) {
            //println(word)
            whereSql += " and $rootPath.copyrightholder like '%$word%' "
        }
    }
    whereSql += makeIsShowSql(isShow, rootPath)

    return andSql + whereSql
}

private fun makeIsShowSql(isShow: Boolean?, rootPath: String): String {

    if (isShow == null) return ""
    if (isShow) {
        return " and $rootPath.deleted_at is not null"
    }
    return " and $rootPath.deleted_at is null"
}

확실히 꼼수를 부리면 댓가가 존재하나보다..

  val sql:String =     """
            select
                *                
            from
                song s
            left outer join album a on a.id = s.album_id
            where
                ${generateSongSearchDynamicQuery(searchCondition, "s")}
            order by s.id desc
        """.trimIndent()

        val result: MutableList<Song?> = em.createNativeQuery(sql, Song::class.java)
            .setFirstResult(pageable.offset.toInt()).setMaxResults(pageable.pageSize).resultList as MutableList<Song?>

        val count = em.createNativeQuery(sql, Song::class.java).resultList.count()