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