diff --git a/src/main/kotlin/controller/api/AudioApiController.kt b/src/main/kotlin/controller/api/AudioApiController.kt index 1918015..fe26b9f 100644 --- a/src/main/kotlin/controller/api/AudioApiController.kt +++ b/src/main/kotlin/controller/api/AudioApiController.kt @@ -32,7 +32,11 @@ ): ResponseEntity = ResponseEntity.ok( audioService.getByKeywords( - audioName, artistName, albumName, tagName, PAGE_LIMIT + audioName?.split("\\s".toRegex())?.toList(), + artistName?.split("\\s".toRegex())?.toList(), + albumName?.split("\\s".toRegex())?.toList(), + tagName?.split("\\s".toRegex())?.toList(), + PAGE_LIMIT ) ) diff --git a/src/main/kotlin/dao/AlbumDao.kt b/src/main/kotlin/dao/AlbumDao.kt index 532d0c3..c8fcdcc 100644 --- a/src/main/kotlin/dao/AlbumDao.kt +++ b/src/main/kotlin/dao/AlbumDao.kt @@ -8,7 +8,10 @@ import net.piedpiper.bremer.entity.AudioEntity import net.piedpiper.bremer.entity.AudioPlayHistoryEntity import net.piedpiper.bremer.utils.DaoUtils +import net.piedpiper.bremer.utils.SelectSQLBuilder +import net.piedpiper.bremer.utils.WHERE_LIKES import org.apache.ibatis.annotations.* +import org.apache.ibatis.jdbc.SelectBuilder import org.springframework.stereotype.Repository @Repository("bremer.dao.AlbumDao") @@ -35,9 +38,9 @@ ): List @ResultMap("net.piedpiper.bremer.AlbumEntity") - @Select("SELECT * FROM album WHERE name LIKE CONCAT('%', #{nameLike}, '%') LIMIT #{limit}") - fun findAllByNameLikeLimit( - @Param("nameLike") nameLike: String, + @SelectProvider(type = Sql::class, method = "findAllByNamesLikeLimit") + fun findAllByNamesLikeLimit( + @Param("namesLike") namesLike: List, @Param("limit") limit: Int ): List @@ -49,6 +52,15 @@ @JvmStatic fun updateOne(@Param("entity") entity: AlbumEntity): String = DaoUtils.updateOne(entity) + + @JvmStatic + fun findAllByNamesLikeLimit( + @Param("namesLike") namesLike: List, + ): String = SelectSQLBuilder(AlbumEntity::class) + .toSql() + .WHERE_LIKES("name", "namesLike", namesLike.size) + .LIMIT("#{limit}") + .toString() } } } \ No newline at end of file diff --git a/src/main/kotlin/dao/ArtistDao.kt b/src/main/kotlin/dao/ArtistDao.kt index e3ec6fb..197b8bf 100644 --- a/src/main/kotlin/dao/ArtistDao.kt +++ b/src/main/kotlin/dao/ArtistDao.kt @@ -4,8 +4,11 @@ package net.piedpiper.bremer.dao +import net.piedpiper.bremer.entity.AlbumEntity import net.piedpiper.bremer.entity.ArtistEntity import net.piedpiper.bremer.utils.DaoUtils +import net.piedpiper.bremer.utils.SelectSQLBuilder +import net.piedpiper.bremer.utils.WHERE_LIKES import org.apache.ibatis.annotations.* import org.springframework.stereotype.Repository @@ -14,9 +17,9 @@ interface ArtistDao { @ResultMap("net.piedpiper.bremer.ArtistEntity") - @Select("SELECT * FROM artist WHERE name LIKE CONCAT('%', #{nameLike}, '%') LIMIT #{limit}") - fun findAllByNameLikeLimit( - @Param("nameLike") nameLike: String, + @SelectProvider(type = Sql::class, method = "findAllByNamesLikeLimit") + fun findAllByNamesLikeLimit( + @Param("namesLike") namesLike: List, @Param("limit") limit: Int ): List @@ -32,6 +35,15 @@ @JvmStatic fun updateOne(@Param("entity") entity: ArtistEntity): String = DaoUtils.updateOne(entity) + + @JvmStatic + fun findAllByNamesLikeLimit( + @Param("namesLike") namesLike: List, + ): String = SelectSQLBuilder(ArtistEntity::class) + .toSql() + .WHERE_LIKES("name", "namesLike", namesLike.size) + .LIMIT("#{limit}") + .toString() } } } \ No newline at end of file diff --git a/src/main/kotlin/dao/AudioDao.kt b/src/main/kotlin/dao/AudioDao.kt index 6fe3f82..1214fd4 100644 --- a/src/main/kotlin/dao/AudioDao.kt +++ b/src/main/kotlin/dao/AudioDao.kt @@ -8,6 +8,7 @@ import net.piedpiper.bremer.utils.DaoUtils import net.piedpiper.bremer.utils.SelectSQLBuilder import net.piedpiper.bremer.utils.WHERE_IN +import net.piedpiper.bremer.utils.WHERE_LIKES import org.apache.ibatis.annotations.* import org.springframework.stereotype.Repository @@ -44,9 +45,9 @@ fun findAllBySlugIn(@Param("slugs") ids: List): List @ResultMap("net.piedpiper.bremer.AudioEntity") - @SelectProvider(type = Sql::class, method = "findAllByNameLikeLimit") - fun findAllByNameLikeLimit( - @Param("nameLike") nameLike: String, + @SelectProvider(type = Sql::class, method = "findAllByNameLikesLimit") + fun findAllByNameLikesLimit( + @Param("nameLikes") nameLikes: List, @Param("limit") limit: Int ): List @@ -58,7 +59,11 @@ @JvmStatic fun findOneById(): String = SelectSQLBuilder(AudioEntity::class) .leftOuterJoin(AlbumEntity::class, "audio.album_id = album.id") - .leftOuterJoin(ArtistEntity::class, "album.artist_id = artist.id", AlbumEntity::class) + .leftOuterJoin( + ArtistEntity::class, + "album.artist_id = artist.id", + AlbumEntity::class + ) .leftOuterJoin(AudioNameEntity::class, "audio.id = audio_name.audio_id") .toSql() .WHERE("audio.id = #{id}") @@ -67,7 +72,11 @@ @JvmStatic fun findOneBySlug(): String = SelectSQLBuilder(AudioEntity::class) .leftOuterJoin(AlbumEntity::class, "audio.album_id = album.id") - .leftOuterJoin(ArtistEntity::class, "album.artist_id = artist.id", AlbumEntity::class) + .leftOuterJoin( + ArtistEntity::class, + "album.artist_id = artist.id", + AlbumEntity::class + ) .leftOuterJoin(AudioNameEntity::class, "audio.id = audio_name.audio_id") .toSql() .WHERE("audio.slug = #{slug}") @@ -77,7 +86,11 @@ fun findAllByAlbumIdIn(@Param("albumIds") albumIds: List): String = SelectSQLBuilder(AudioEntity::class) .leftOuterJoin(AlbumEntity::class, "audio.album_id = album.id") - .leftOuterJoin(ArtistEntity::class, "album.artist_id = artist.id", AlbumEntity::class) + .leftOuterJoin( + ArtistEntity::class, + "album.artist_id = artist.id", + AlbumEntity::class + ) .leftOuterJoin(AudioNameEntity::class, "audio.id = audio_name.audio_id") .toSql() .WHERE_IN("audio.album_id", "albumIds", albumIds.size) @@ -88,7 +101,11 @@ fun findAllByIdIn(@Param("ids") ids: List): String = SelectSQLBuilder(AudioEntity::class) .leftOuterJoin(AlbumEntity::class, "audio.album_id = album.id") - .leftOuterJoin(ArtistEntity::class, "album.artist_id = artist.id", AlbumEntity::class) + .leftOuterJoin( + ArtistEntity::class, + "album.artist_id = artist.id", + AlbumEntity::class + ) .leftOuterJoin(AudioNameEntity::class, "audio.id = audio_name.audio_id") .toSql() .WHERE_IN("audio.id", "ids", ids.size) @@ -99,7 +116,11 @@ fun findAllBySlugIn(@Param("slugs") slugs: List): String = SelectSQLBuilder(AudioEntity::class) .leftOuterJoin(AlbumEntity::class, "audio.album_id = album.id") - .leftOuterJoin(ArtistEntity::class, "album.artist_id = artist.id", AlbumEntity::class) + .leftOuterJoin( + ArtistEntity::class, + "album.artist_id = artist.id", + AlbumEntity::class + ) .leftOuterJoin(AudioNameEntity::class, "audio.id = audio_name.audio_id") .toSql() .WHERE_IN("audio.slug", "slugs", slugs.size) @@ -110,7 +131,11 @@ fun findAllByTagIdIn(@Param("tagIds") tagIds: List): String = SelectSQLBuilder(AudioEntity::class) .leftOuterJoin(AlbumEntity::class, "audio.album_id = album.id") - .leftOuterJoin(ArtistEntity::class, "album.artist_id = artist.id", AlbumEntity::class) + .leftOuterJoin( + ArtistEntity::class, + "album.artist_id = artist.id", + AlbumEntity::class + ) .leftOuterJoin(AudioNameEntity::class, "audio.id = audio_name.audio_id") .leftOuterJoin(AudioTagEntity::class, "audio.id = audio_tag.audio_id") .toSql() @@ -119,19 +144,22 @@ .toString() @JvmStatic - fun findAllByNameLikeLimit( - @Param("nameLike") nameLike: String, + fun findAllByNameLikesLimit( + @Param("nameLikes") nameLikes: List, @Param("limit") limit: Long ): String = SelectSQLBuilder(AudioEntity::class) .leftOuterJoin(AlbumEntity::class, "audio.album_id = album.id") - .leftOuterJoin(ArtistEntity::class, "album.artist_id = artist.id", AlbumEntity::class) + .leftOuterJoin( + ArtistEntity::class, + "album.artist_id = artist.id", + AlbumEntity::class + ) .leftOuterJoin(AudioNameEntity::class, "audio.id = audio_name.audio_id") .toSql() - .WHERE( - "audio.name LIKE CONCAT('%', #{nameLike}, '%') OR " - + "audio_name.name LIKE CONCAT('%', #{nameLike}, '%')" - ) + .WHERE_LIKES("audio.name", "nameLikes", nameLikes.size) + .OR() + .WHERE_LIKES("audio_name.name", "nameLikes", nameLikes.size) .ORDER_BY("audio.album_id, audio.sequence") .LIMIT("#{limit}") .toString() diff --git a/src/main/kotlin/dao/TagDao.kt b/src/main/kotlin/dao/TagDao.kt index 9d314b8..3ae9f9f 100644 --- a/src/main/kotlin/dao/TagDao.kt +++ b/src/main/kotlin/dao/TagDao.kt @@ -4,8 +4,11 @@ package net.piedpiper.bremer.dao +import net.piedpiper.bremer.entity.ArtistEntity import net.piedpiper.bremer.entity.TagEntity import net.piedpiper.bremer.utils.DaoUtils +import net.piedpiper.bremer.utils.SelectSQLBuilder +import net.piedpiper.bremer.utils.WHERE_LIKES import org.apache.ibatis.annotations.* import org.springframework.stereotype.Repository @@ -26,9 +29,9 @@ fun findAll(): List @ResultMap("net.piedpiper.bremer.TagEntity") - @Select("SELECT * FROM tag WHERE name LIKE CONCAT('%', #{nameLike}, '%') LIMIT #{limit}") - fun findAllByNameLikeLimit( - @Param("nameLike") nameLike: String, + @SelectProvider(type = Sql::class, method = "findAllByNamesLikeLimit") + fun findAllByNamesLikeLimit( + @Param("namesLike") namesLike: List, @Param("limit") limit: Int ): List @@ -52,6 +55,15 @@ @JvmStatic fun updateOne(@Param("entity") entity: TagEntity): String = DaoUtils.updateOne(entity) + + @JvmStatic + fun findAllByNamesLikeLimit( + @Param("namesLike") namesLike: List, + ): String = SelectSQLBuilder(TagEntity::class) + .toSql() + .WHERE_LIKES("name", "namesLike", namesLike.size) + .LIMIT("#{limit}") + .toString() } } } \ No newline at end of file diff --git a/src/main/kotlin/service/AudioService.kt b/src/main/kotlin/service/AudioService.kt index db4b4ae..0fcbdd7 100644 --- a/src/main/kotlin/service/AudioService.kt +++ b/src/main/kotlin/service/AudioService.kt @@ -45,24 +45,24 @@ /** キーワード検索 */ @Transactional fun getByKeywords( - audioNameLike: String?, - artistNameLike: String?, - albumNameLike: String?, - tagNameLike: String?, + audioNamesLike: List?, + artistNamesLike: List?, + albumNamesLike: List?, + tagNamesLike: List?, pageSize: Int ): AudioListResponse { val set = mutableSetOf() - if (audioNameLike?.isNotEmpty() == true) { - set.addAll(audioDao.findAllByNameLikeLimit(audioNameLike, pageSize)) + if (audioNamesLike?.isNotEmpty() == true) { + set.addAll(audioDao.findAllByNameLikesLimit(audioNamesLike, pageSize)) } - if (artistNameLike?.isNotEmpty() == true && set.size <= pageSize) { - set.addAll(findAllByArtistNameLikeLimit(artistNameLike, pageSize)) + if (artistNamesLike?.isNotEmpty() == true && set.size <= pageSize) { + set.addAll(findAllByArtistNamesLikesLimit(artistNamesLike, pageSize)) } - if (albumNameLike?.isNotEmpty() == true && set.size <= pageSize) { - set.addAll(findAllByAlbumNameLikeLimit(albumNameLike, pageSize)) + if (albumNamesLike?.isNotEmpty() == true && set.size <= pageSize) { + set.addAll(findAllByAlbumNamesLikeLimit(albumNamesLike, pageSize)) } - if (tagNameLike?.isNotEmpty() == true && set.size <= pageSize) { - set.addAll(findAllByTagNameLikeLimit(tagNameLike, pageSize)) + if (tagNamesLike?.isNotEmpty() == true && set.size <= pageSize) { + set.addAll(findAllByTagNamesLikeLimit(tagNamesLike, pageSize)) } return AudioListResponse( set.sortedWith(compareBy({ it.albumId }, { it.sequence })) @@ -118,11 +118,11 @@ } } - private fun findAllByArtistNameLikeLimit( - artistNameLike: String, + private fun findAllByArtistNamesLikesLimit( + artistNameLikes: List, limit: Int ): List { - val artists = artistDao.findAllByNameLikeLimit(artistNameLike, limit) + val artists = artistDao.findAllByNamesLikeLimit(artistNameLikes, limit) if (artists.isEmpty()) { return emptyList() } @@ -133,14 +133,14 @@ return audioDao.findAllByAlbumIdIn(albums.map { it.id }.distinct()) } - private fun findAllByAlbumNameLikeLimit(albumNameLike: String, limit: Int): List { - val albums = albumDao.findAllByNameLikeLimit(albumNameLike, limit) + private fun findAllByAlbumNamesLikeLimit(albumNamesLike: List, limit: Int): List { + val albums = albumDao.findAllByNamesLikeLimit(albumNamesLike, limit) return if (albums.isNotEmpty()) audioDao .findAllByAlbumIdIn(albums.map { it.id }) else emptyList() } - private fun findAllByTagNameLikeLimit(tagNameLike: String, limit: Int): List { - val tags = tagDao.findAllByNameLikeLimit(tagNameLike, limit) + private fun findAllByTagNamesLikeLimit(tagNamesLike: List, limit: Int): List { + val tags = tagDao.findAllByNamesLikeLimit(tagNamesLike, limit) return if (tags.isNotEmpty()) audioDao.findAllByTagIdIn(tags.map { it.id }) else emptyList() } } \ No newline at end of file diff --git a/src/main/kotlin/utils/SqlUtils.kt b/src/main/kotlin/utils/SqlUtils.kt index 49745e7..37d2af5 100644 --- a/src/main/kotlin/utils/SqlUtils.kt +++ b/src/main/kotlin/utils/SqlUtils.kt @@ -144,4 +144,20 @@ WHERE("${targetColumn} IN ${it}") } return this +} + +fun SQL.WHERE_LIKES( + targetColumn: String, + parameterName: String, + num: Int, +): SQL { + if (num == 0) { + throw IllegalArgumentException() + } + generateSequence(0) { v -> v + 1 }.take(num).map { + "${targetColumn} LIKE CONCAT('%', #{${parameterName}[${it}]}, '%')" + }.joinToString(" AND ").let { + WHERE("${it}") + } + return this } \ No newline at end of file