Changeset 2ce7c1e


Ignore:
Timestamp:
02/05/26 21:26:46 (5 months ago)
Author:
Filip Gavrilovski <filipgavrilovski28@…>
Branches:
main
Children:
b071fb4
Parents:
cb4a1da
Message:

replace string concatenation in jpql queries with text blocks

Location:
finkwave/src/main/java/com/ukim/finki/develop/finkwave/repository
Files:
11 edited

Legend:

Unmodified
Added
Removed

  • finkwave/src/main/java/com/ukim/finki/develop/finkwave/repository/AlbumRepository.java

    rcb4a1da r2ce7c1e  
    1616    List<Album>findAllByIdIn(List<Long>ids);
    1717
    18 
    1918    @Query("""
    20             SELECT NEW com.ukim.finki.develop.finkwave.model.dto.MusicalEntityDto(
     19        SELECT NEW com.ukim.finki.develop.finkwave.model.dto.MusicalEntityDto(
    2120            a.id, me.title, me.genre, 'ALBUM', u.fullName, me.cover, FALSE )
    22             FROM Album a
    23             JOIN a.musicalEntities me
    24             JOIN me.releasedBy rb
    25             JOIN rb.nonAdminUser nau
    26             JOIN nau.user u
    27             WHERE me.title ILIKE %:searchTerm%
    28             """
    29     )
     21        FROM Album a
     22        JOIN a.musicalEntities me
     23        JOIN me.releasedBy rb
     24        JOIN rb.nonAdminUser nau
     25        JOIN nau.user u
     26        WHERE me.title ILIKE '%' || :searchTerm || '%'
     27    """)
    3028    List<MusicalEntityDto> searchAlbums(@Param("currentUserId")Long currentUserId, @Param("searchTerm") String searchTerm);
    3129

  • finkwave/src/main/java/com/ukim/finki/develop/finkwave/repository/ArtistContributionRepository.java

    rcb4a1da r2ce7c1e  
    1414public interface ArtistContributionRepository extends JpaRepository<ArtistContribution, ArtistContributionId> {
    1515
    16     @Query("SELECT NEW com.ukim.finki.develop.finkwave.model.dto.ArtistContributionDto( ac.musicalEntity.id," +
    17             "ac.musicalEntity.title," +
    18             "ac.musicalEntity.genre," +
    19             "ac.role, " +
    20             "CASE WHEN s.id IS NOT NULL THEN 'SONG' " +
    21             "WHEN a.id IS NOT NULL THEN 'ALBUM' " +
    22             "ELSE 'Unknown' END," +
    23             "(CASE WHEN l.id IS NOT NULL THEN true ELSE false END)) " +
    24             "FROM ArtistContribution ac " +
    25             "LEFT JOIN Song s  ON s.musicalEntities.id=ac.musicalEntity.id " +
    26             "LEFT JOIN Album a ON a.musicalEntities.id=ac.musicalEntity.id " +
    27             "LEFT JOIN Like l ON l.musicalEntity.id=ac.musicalEntity.id AND l.listener.id=:currentUserId " +
    28             "WHERE ac.artist.id=:artistId")
     16    @Query("""
     17        SELECT NEW com.ukim.finki.develop.finkwave.model.dto.ArtistContributionDto(
     18            ac.musicalEntity.id, ac.musicalEntity.title, ac.musicalEntity.genre, ac.role,
     19            (CASE WHEN s.id IS NOT NULL THEN 'SONG'
     20                  WHEN a.id IS NOT NULL THEN 'ALBUM'
     21                  ELSE 'Unknown' END),
     22            (CASE WHEN l.id IS NOT NULL THEN true ELSE false END))
     23        FROM ArtistContribution ac
     24        LEFT JOIN Song s  ON s.musicalEntities.id=ac.musicalEntity.id
     25        LEFT JOIN Album a ON a.musicalEntities.id=ac.musicalEntity.id
     26        LEFT JOIN Like l ON l.musicalEntity.id=ac.musicalEntity.id AND l.listener.id=:currentUserId
     27        WHERE ac.artist.id=:artistId
     28        """)
    2929    List<ArtistContributionDto>findContributionsByArtistId(@Param("currentUserId")Long currentUserId,@Param("artistId")Long artistId);
    3030}

  • finkwave/src/main/java/com/ukim/finki/develop/finkwave/repository/ArtistRepository.java

    rcb4a1da r2ce7c1e  
    1313public interface ArtistRepository extends JpaRepository<Artist, Long> {
    1414
    15     @Query("SELECT DISTINCT a FROM Artist a "+
    16         "JOIN FETCH a.nonAdminUser nu "+
    17         "JOIN FETCH nu.user "+
    18         "WHERE a.id=:artistId")
     15    @Query("""
     16        SELECT DISTINCT a FROM Artist a
     17        JOIN FETCH a.nonAdminUser nu
     18        JOIN FETCH nu.user
     19        WHERE a.id=:artistId
     20        """)
    1921    Optional<Artist>findByIdWithUser(@Param("artistId")Long artistId);
    2022
    21     @Query("SELECT a FROM Artist a " +
    22             "JOIN FETCH a.nonAdminUser nau " +
    23             "JOIN FETCH nau.user u " +
    24             "WHERE u.username = :username")
     23    @Query("""
     24            SELECT a FROM Artist a
     25            JOIN FETCH a.nonAdminUser nau
     26            JOIN FETCH nau.user u
     27            WHERE u.username = :username
     28            """)
    2529    Optional<Artist> findByUsername(@Param("username") String username);
    2630

  • finkwave/src/main/java/com/ukim/finki/develop/finkwave/repository/FollowRepository.java

    rcb4a1da r2ce7c1e  
    2020    long countByFollowerId(Long userId);
    2121
    22     @Query("SELECT CASE WHEN COUNT (f)>0 THEN  true ELSE false END " +
    23             "FROM Follow f "+
    24             "WHERE f.followee.id=:selectedUserId AND f.follower.id=:currentUserId")
     22    @Query("""
     23        SELECT CASE WHEN COUNT (f)>0 THEN  true ELSE false END
     24        FROM Follow f
     25        WHERE f.followee.id=:selectedUserId AND f.follower.id=:currentUserId
     26        """)
    2527    boolean isFollowing(@Param("currentUserId") Long currentUSerId,@Param("selectedUserId")Long selectedUserId);
    2628
    … …  
    2830    List<Follow>getFollowsByFollower_Id(Long id);
    2931
    30     @Query("SELECT f FROM Follow f " +
    31             "JOIN FETCH f.follower nu " +
    32             "JOIN FETCH nu.user " +
    33             "WHERE f.followee.id = :id")
     32    @Query("""
     33        SELECT f FROM Follow f
     34        JOIN FETCH f.follower nu
     35        JOIN FETCH nu.user
     36        WHERE f.followee.id = :id
     37        """)
    3438    List<Follow> findFollowersWithProfile(@Param("id") Long id);
    3539
    36     @Query("SELECT f FROM Follow f " +
    37             "JOIN FETCH f.followee nu " +
    38             "JOIN FETCH nu.user " +
    39             "WHERE f.follower.id = :id")
     40    @Query("""
     41        SELECT f FROM Follow f
     42        JOIN FETCH f.followee nu
     43        JOIN FETCH nu.user
     44        WHERE f.follower.id = :id
     45        """)
    4046    List<Follow> findFollowingWithProfile(@Param("id") Long id);
    4147

  • finkwave/src/main/java/com/ukim/finki/develop/finkwave/repository/LikeRepository.java

    rcb4a1da r2ce7c1e  
    11package com.ukim.finki.develop.finkwave.repository;
    22
    3 import com.ukim.finki.develop.finkwave.model.MusicalEntity;
    43import com.ukim.finki.develop.finkwave.model.dto.MusicalEntityDto;
    54import org.springframework.data.jpa.repository.JpaRepository;
    … …  
    1514@Repository
    1615public interface LikeRepository extends JpaRepository<Like, LikeId> {
    17     @Query("SELECT NEW com.ukim.finki.develop.finkwave.model.dto.MusicalEntityDto(" +
    18             "me.id, " +
    19             "me.title, " +
    20             "me.genre, " +
    21             "CASE WHEN s.id IS NOT NULL THEN 'SONG' " +
    22             "     WHEN a.id IS NOT NULL THEN 'ALBUM' " +
    23             "     ELSE 'UNKNOWN' END, " +
    24             "u.fullName, " +
    25             "(CASE WHEN currentUserLike.id IS NOT NULL THEN true ELSE false END)" +
    26             ") " +
    27             "FROM Like l " +
    28             "JOIN l.musicalEntity me " +
    29             "JOIN me.releasedBy nu " +
    30             "JOIN nu.nonAdminUser.user u " +
    31             "LEFT JOIN Song s ON s.musicalEntities.id = me.id " +
    32             "LEFT JOIN Album a ON a.musicalEntities.id = me.id " +
    33             "LEFT JOIN Like currentUserLike ON currentUserLike.musicalEntity.id = me.id " +
    34             "AND currentUserLike.listener.id = :currentUserId " +
    35             "WHERE l.listener.id = :listenerId")
     16    @Query("""
     17        SELECT NEW com.ukim.finki.develop.finkwave.model.dto.MusicalEntityDto(
     18            me.id,
     19            me.title,
     20            me.genre,
     21            (CASE WHEN s.id IS NOT NULL THEN 'SONG'
     22                 WHEN a.id IS NOT NULL THEN 'ALBUM'
     23                 ELSE 'UNKNOWN' END),
     24            u.fullName,
     25            (CASE WHEN currentUserLike.id IS NOT NULL THEN true ELSE false END))
     26        FROM Like l
     27        JOIN l.musicalEntity me
     28        JOIN me.releasedBy nu
     29        JOIN nu.nonAdminUser.user u
     30        LEFT JOIN Song s ON s.musicalEntities.id = me.id
     31        LEFT JOIN Album a ON a.musicalEntities.id = me.id
     32        LEFT JOIN Like currentUserLike ON currentUserLike.musicalEntity.id = me.id
     33        AND currentUserLike.listener.id = :currentUserId
     34        WHERE l.listener.id = :listenerId
     35        """)
    3636    List<MusicalEntityDto> findLikedEntitiesWithTypeByListenerId(@Param("currentUserId")Long currentUserId,@Param("listenerId") Long listenerId);
    3737
    3838
    39     @Query("SELECT CASE WHEN COUNT (l)>0 THEN true ELSE false END " +
    40             "FROM MusicalEntity  me " +
    41             "JOIN Like  l on l.musicalEntity.id=me.id " +
    42             "WHERE l.listener.id=:userId"
    43 
    44     )
     39    @Query("""
     40        SELECT CASE WHEN COUNT (l)>0 THEN true ELSE false END
     41        FROM MusicalEntity me
     42        JOIN Like  l on l.musicalEntity.id=me.id
     43        WHERE l.listener.id=:userId
     44        """)
    4545    boolean isLikedByUser(@Param("userId") Long userId);
    4646}

  • finkwave/src/main/java/com/ukim/finki/develop/finkwave/repository/ListenerRepository.java

    rcb4a1da r2ce7c1e  
    1212@Repository
    1313public interface ListenerRepository extends JpaRepository<Listener, Long> {
    14     @Query("SELECT DISTINCT l FROM Listener l " +
    15            "JOIN FETCH l.nonAdminUser nu " +
    16            "JOIN FETCH nu.user " +
    17            "WHERE l.id = :listenerId")
     14    @Query("""
     15        SELECT DISTINCT l FROM Listener l
     16       JOIN FETCH l.nonAdminUser nu
     17       JOIN FETCH nu.user
     18       WHERE l.id = :listenerId
     19       """)
    1820    Optional<Listener> findByIdWithUser(@Param("listenerId") Long listenerId);
    1921
    20     @Query("SELECT l FROM Listener l " +
    21             "JOIN FETCH l.nonAdminUser nu " +
    22             "JOIN FETCH nu.user u " +
    23             "WHERE u.username=:username")
    24 
     22    @Query("""
     23        SELECT l FROM Listener l
     24        JOIN FETCH l.nonAdminUser nu
     25        JOIN FETCH nu.user u
     26        WHERE u.username=:username
     27        """)
    2528    Optional<Listener>findByUsername(@Param("username")String username);
    2629}

  • finkwave/src/main/java/com/ukim/finki/develop/finkwave/repository/NonAdminUserRepository.java

    rcb4a1da r2ce7c1e  
    1414public interface NonAdminUserRepository extends JpaRepository<NonAdminUser, Long> {
    1515
    16     @Query("SELECT n FROM NonAdminUser n JOIN FETCH n.user")
     16    @Query("""
     17        SELECT n FROM NonAdminUser n JOIN FETCH n.user
     18    """)
    1719    List<NonAdminUser> findAllWithUser();
    1820
    19     @Query("SELECT u FROM NonAdminUser u " +
    20             "WHERE u.user.fullName ILIKE CONCAT('%', :name, '%' ) "+
    21             "OR  u.user.username ILIKE CONCAT('%', :name, '%' ) ")
     21    @Query("""
     22        SELECT u FROM NonAdminUser u
     23        WHERE u.user.fullName ILIKE '%' || :name || '%'
     24        OR  u.user.username ILIKE '%' || :name || '%'
     25    """)
    2226    List<NonAdminUser> searchByName(String name);
    2327
    24     @Query("SELECT nau FROM NonAdminUser nau " +
    25             "JOIN FETCH nau.user u " +
    26             "WHERE u.username = :username")
     28    @Query("""
     29        SELECT nau FROM NonAdminUser nau
     30        JOIN FETCH nau.user u
     31        WHERE u.username = :username
     32        """)
    2733    Optional<NonAdminUser> findByUsername(@Param("username") String username);
    2834

  • finkwave/src/main/java/com/ukim/finki/develop/finkwave/repository/PlaylistRepository.java

    rcb4a1da r2ce7c1e  
    1414
    1515
    16     @Query("SELECT DISTINCT p FROM Playlist p " +
    17             "JOIN FETCH p.createdBy l " +
    18             "JOIN FETCH l.nonAdminUser nu " +
    19             "JOIN FETCH nu.user u " +
    20             "WHERE l.id = :creatorId")
     16    @Query("""
     17        SELECT DISTINCT p FROM Playlist p
     18        JOIN FETCH p.createdBy l
     19        JOIN FETCH l.nonAdminUser nu
     20        JOIN FETCH nu.user u
     21        WHERE l.id = :creatorId
     22        """)
    2123    List<Playlist> findByCreatorId(@Param("creatorId") Long creatorId);
    2224

  • finkwave/src/main/java/com/ukim/finki/develop/finkwave/repository/SavedPlaylistRepository.java

    rcb4a1da r2ce7c1e  
    1717    List<SavedPlaylist>findAllByListener_Id(Long currentUserId);
    1818
    19     @Query("SELECT p FROM SavedPlaylist sp " +
    20             "JOIN sp.playlist p " +
    21             "JOIN FETCH p.createdBy l " +
    22             "JOIN FETCH l.nonAdminUser nau " +
    23             "JOIN FETCH nau.user u " +
    24             "WHERE sp.id.listenerId = :listenerId")
     19    @Query("""
     20        SELECT p FROM SavedPlaylist sp
     21        JOIN sp.playlist p
     22        JOIN FETCH p.createdBy l
     23        JOIN FETCH l.nonAdminUser nau
     24        JOIN FETCH nau.user u
     25        WHERE sp.id.listenerId = :listenerId
     26        """)
    2527    List<Playlist> findSavedPlaylistsMetadata(@Param("listenerId") Long listenerId);
    2628}

  • finkwave/src/main/java/com/ukim/finki/develop/finkwave/repository/SongRepository.java

    rcb4a1da r2ce7c1e  
    1616
    1717    @Query("""
    18             SELECT NEW com.ukim.finki.develop.finkwave.model.dto.MusicalEntityDto(
    19                 s.id, me.title, me.genre, 'SONG', u.fullName, me.cover,
    20                 (CASE WHEN :currentUserId IS NOT NULL AND l.id IS NOT NULL THEN true ELSE false END)
    21             )
    22             FROM Song s
    23             JOIN s.musicalEntities me
    24             JOIN me.releasedBy a
    25             JOIN a.nonAdminUser nau
    26             JOIN nau.user u
    27             LEFT JOIN Like l ON l.musicalEntity.id = s.id AND l.listener.id = :currentUserId
    28             WHERE s.album.id = :albumId
    29             """
    30     )
     18        SELECT NEW com.ukim.finki.develop.finkwave.model.dto.MusicalEntityDto(
     19            s.id, me.title, me.genre, 'SONG', u.fullName, me.cover,
     20            (CASE WHEN :currentUserId IS NOT NULL AND l.id IS NOT NULL THEN true ELSE false END)
     21        )
     22        FROM Song s
     23        JOIN s.musicalEntities me
     24        JOIN me.releasedBy a
     25        JOIN a.nonAdminUser nau
     26        JOIN nau.user u
     27        LEFT JOIN Like l ON l.musicalEntity.id = s.id AND l.listener.id = :currentUserId
     28        WHERE s.album.id = :albumId
     29    """)
    3130    List<MusicalEntityDto>findSongsByAlbum(@Param("albumId") Long albumId, @Param("currentUserId")Long currentUserId);
    3231
    3332
    3433    @Query("""
    35             SELECT NEW com.ukim.finki.develop.finkwave.model.dto.MusicalEntityDto(
     34        SELECT NEW com.ukim.finki.develop.finkwave.model.dto.MusicalEntityDto(
    3635            s.id, me.title, me.genre, 'SONG', u.fullName, me.cover,
    3736            (CASE WHEN :currentUserId IS NOT NULL AND l.id IS NOT NULL THEN true ELSE false END))
    38             FROM Song s
    39             JOIN s.musicalEntities me
    40             JOIN me.releasedBy a
    41             JOIN a.nonAdminUser nau
    42             JOIN nau.user u
    43             JOIN PlaylistSong ps ON ps.song.id=s.id
    44             LEFT JOIN Like l ON l.musicalEntity.id=s.id AND l.listener.id=:currentUserId
    45             WHERE ps.playlist.id=:playlistId
     37        FROM Song s
     38        JOIN s.musicalEntities me
     39        JOIN me.releasedBy a
     40        JOIN a.nonAdminUser nau
     41        JOIN nau.user u
     42        JOIN PlaylistSong ps ON ps.song.id=s.id
     43        LEFT JOIN Like l ON l.musicalEntity.id=s.id AND l.listener.id=:currentUserId
     44        WHERE ps.playlist.id=:playlistId
    4645    """)
    4746    List<MusicalEntityDto>findSongsByPlaylistId(@Param("playlistId")Long playlistId, @Param("currentUserId")Long currentUserId);
    4847
    4948    @Query("""
    50             SELECT NEW com.ukim.finki.develop.finkwave.model.dto.MusicalEntityDto(
    51             s.id, me.title, me.genre, 'SONG', u.fullName, me.cover, FALSE)
    52             FROM Song s
    53             JOIN s.musicalEntities me
    54             JOIN me.releasedBy a
    55             JOIN a.nonAdminUser nau
    56             JOIN nau.user u
    57             JOIN Listen l on l.song.id = s.id
    58             GROUP BY s.id, me.title, me.genre, u.fullName, me.cover
    59             ORDER BY count(*) desc
    60     """
    61     )
     49        SELECT NEW com.ukim.finki.develop.finkwave.model.dto.MusicalEntityDto(
     50        s.id, me.title, me.genre, 'SONG', u.fullName, me.cover, FALSE)
     51        FROM Song s
     52        JOIN s.musicalEntities me
     53        JOIN me.releasedBy a
     54        JOIN a.nonAdminUser nau
     55        JOIN nau.user u
     56        JOIN Listen l on l.song.id = s.id
     57        GROUP BY s.id, me.title, me.genre, u.fullName, me.cover
     58        ORDER BY count(*) desc
     59    """)
    6260    // todo: handle likes, momentalno site se false za da raboti joinot
    6361    // todo: add paging
    … …  
    6765    // todo: fix is liked by user, currently returns hard coded false
    6866    @Query("""
    69             SELECT NEW com.ukim.finki.develop.finkwave.model.dto.MusicalEntityDto(
     67        SELECT NEW com.ukim.finki.develop.finkwave.model.dto.MusicalEntityDto(
    7068            s.id, me.title, me.genre, 'SONG', u.fullName, me.cover, FALSE )
    71             FROM Song s
    72             JOIN s.musicalEntities me
    73             JOIN me.releasedBy a
    74             JOIN a.nonAdminUser nau
    75             JOIN nau.user u
    76             WHERE me.title ILIKE %:searchTerm%
    77             """
    78     )
     69        FROM Song s
     70        JOIN s.musicalEntities me
     71        JOIN me.releasedBy a
     72        JOIN a.nonAdminUser nau
     73        JOIN nau.user u
     74        WHERE me.title ILIKE %:searchTerm%
     75    """)
    7976    List<MusicalEntityDto> searchSongs(@Param("currentUserId")Long currentUserId, @Param("searchTerm") String searchTerm);
    8077

  • finkwave/src/main/java/com/ukim/finki/develop/finkwave/repository/UserRepository.java

    rcb4a1da r2ce7c1e  
    1616    @Query("""
    1717        SELECT u from User u
    18         WHERE (u.fullName ilike %:searchTerm% or u.username ilike %:searchTerm%)
    19             and u.listener = true and u.artist = false 
     18        WHERE (u.fullName ILIKE '%' || :searchTerm || '%' or u.username ILIKE '%' || :searchTerm || '%')
     19            and u.listener = true and u.artist = false
    2020        """)
    2121    List<User> searchListeners(String searchTerm);
    … …  
    2323    @Query("""
    2424        SELECT u from User u
    25         WHERE (u.fullName ilike %:searchTerm% or u.username ilike %:searchTerm%)
     25        WHERE (u.fullName ILIKE '%' || :searchTerm || '%' or u.username ILIKE '%' || :searchTerm || '%')
    2626            and u.artist = true
    2727        """)
Note: See TracChangeset for help on using the changeset viewer.