Examples with AlbumRowMapper com.github.hakko.musiccabinet.dao.jdbc.rowmapper.AlbumRowMapper used on opensource projects

Example 1 with AlbumRowMapper

use of com.github.hakko.musiccabinet.dao.jdbc.rowmapper.AlbumRowMapper in project musiccabinet by hakko.

the class JdbcLibraryBrowserDao method getStarredAlbums.

@Override
public List<Album> getStarredAlbums(String lastFmUsername, int offset, int limit, String query) {
    String userTable = "", userCriteria = "", albumNameCriteria = "";
    List<Object> args = new ArrayList<>();
    if (lastFmUsername != null) {
        userTable = " inner join music.lastfmuser u on sa.lastfmuser_id = u.id";
        userCriteria = " and u.lastfm_user = upper(?)";
        args.add(lastFmUsername);
    }
    if (query != null) {
        albumNameCriteria = " and la.album_name_search like ?";
        args.add(getNameQuery(query));
    }
    args.add(offset);
    args.add(limit);
    String sql = "select ma.artist_id, a.artist_name_capitalization, ma.id, ma.album_name_capitalization, la.year," + " d1.path, f1.filename, d2.path, f2.filename, ai.largeimageurl, tr.track_ids from" + " (select lt.album_id as album_id, array_agg(lt.id order by coalesce(ft.disc_nr, 1)*100 + coalesce(ft.track_nr, 0)) as track_ids, filter.added" + " from library.track lt" + " inner join music.album ma on lt.album_id = ma.id" + " inner join library.filetag ft on ft.file_id = lt.file_id" + " inner join (select sa.album_id, sa.added from library.starredalbum sa " + " inner join library.album la on sa.album_id = la.album_id" + userTable + " where true" + userCriteria + albumNameCriteria + " order by sa.added desc offset ? limit ?) filter on lt.album_id = filter.album_id" + " group by lt.album_id, filter.added) tr" + " inner join library.album la on la.album_id = tr.album_id" + " inner join music.album ma on la.album_id = ma.id" + " inner join music.artist a on ma.artist_id = a.id" + " left outer join library.file f1 on f1.id = la.embeddedcoverartfile_id" + " left outer join library.directory d1 on f1.directory_id = d1.id" + " left outer join library.file f2 on f2.id = la.coverartfile_id" + " left outer join library.directory d2 on f2.directory_id = d2.id" + " left outer join music.albuminfo ai on ai.album_id = la.album_id" + " order by added desc";
    return jdbcTemplate.query(sql, args.toArray(), new AlbumRowMapper());
}

Example 2 with AlbumRowMapper

use of com.github.hakko.musiccabinet.dao.jdbc.rowmapper.AlbumRowMapper in project musiccabinet by hakko.

the class JdbcLibraryBrowserDao method getMostPlayedAlbums.

@Override
public List<Album> getMostPlayedAlbums(String lastFmUsername, int offset, int limit, String query) {
    String userTable = "", userCriteria = "", albumNameCriteria = "";
    List<Object> args = new ArrayList<>();
    if (lastFmUsername != null) {
        userTable = " inner join music.lastfmuser u on pc.lastfmuser_id = u.id";
        userCriteria = " and u.lastfm_user = upper(?)";
        args.add(lastFmUsername);
    }
    if (query != null) {
        albumNameCriteria = " and la.album_name_search like ?";
        args.add(getNameQuery(query));
    }
    args.add(offset);
    args.add(limit);
    String sql = "select ma.artist_id, a.artist_name_capitalization, ma.id, ma.album_name_capitalization, la.year," + " d1.path, f1.filename, d2.path, f2.filename, ai.largeimageurl, tr.track_ids from" + " (select lt.album_id as album_id, array_agg(lt.id order by coalesce(ft.disc_nr, 1)*100 + coalesce(ft.track_nr, 0)) as track_ids, filter.cnt" + " from library.track lt" + " inner join music.album ma on lt.album_id = ma.id" + " inner join library.filetag ft on ft.file_id = lt.file_id" + " inner join (select la.album_id, count(la.album_id) as cnt" + " from library.playcount pc" + " inner join library.album la on pc.album_id = la.album_id" + userTable + " where true" + userCriteria + albumNameCriteria + " group by la.album_id order by cnt desc offset ? limit ?) filter on lt.album_id = filter.album_id" + " group by lt.album_id, filter.cnt) tr" + " inner join library.album la on la.album_id = tr.album_id" + " inner join music.album ma on la.album_id = ma.id" + " inner join music.artist a on ma.artist_id = a.id" + " left outer join library.file f1 on f1.id = la.embeddedcoverartfile_id" + " left outer join library.directory d1 on f1.directory_id = d1.id" + " left outer join library.file f2 on f2.id = la.coverartfile_id" + " left outer join library.directory d2 on f2.directory_id = d2.id" + " left outer join music.albuminfo ai on ai.album_id = la.album_id" + " order by cnt desc";
    return jdbcTemplate.query(sql, args.toArray(), new AlbumRowMapper());
}

Example 3 with AlbumRowMapper

use of com.github.hakko.musiccabinet.dao.jdbc.rowmapper.AlbumRowMapper in project musiccabinet by hakko.

the class JdbcNameSearchDao method getAlbums.

@Override
public NameSearchResult<Album> getAlbums(String userQuery, int offset, int limit) {
    String sql = "select mart.id, mart.artist_name_capitalization," + " malb.id, malb.album_name_capitalization, la.year," + " d1.path, f1.filename, d2.path, f2.filename, null, array[]::int[]" + " from library.album la" + " inner join music.album malb on la.album_id = malb.id" + " inner join music.artist mart on malb.artist_id = mart.id" + " left outer join library.file f1 on f1.id = la.embeddedcoverartfile_id" + " left outer join library.directory d1 on f1.directory_id = d1.id" + " left outer join library.file f2 on f2.id = la.coverartfile_id" + " left outer join library.directory d2 on f2.directory_id = d2.id" + " where la.album_name_search like ?" + " order by malb.album_name" + " offset ? limit ?";
    List<Album> albums = jdbcTemplate.query(sql, new Object[] { getNameQuery(userQuery), offset, limit }, new AlbumRowMapper());
    return new NameSearchResult<>(albums, offset);
}

Example 4 with AlbumRowMapper

use of com.github.hakko.musiccabinet.dao.jdbc.rowmapper.AlbumRowMapper in project musiccabinet by hakko.

the class JdbcLibraryBrowserDao method getRecentlyAddedAlbums.

@Override
public List<Album> getRecentlyAddedAlbums(int offset, int limit, String query) {
    String sql = "select ma.artist_id, a.artist_name_capitalization, ma.id, ma.album_name_capitalization, la.year," + " d1.path, f1.filename, d2.path, f2.filename, ai.largeimageurl, tr.track_ids from" + " (select lt.album_id as album_id, array_agg(lt.id order by coalesce(ft.disc_nr, 1)*100 + coalesce(ft.track_nr, 0)) as track_ids, filter.sort_id" + " from library.track lt" + " inner join music.album ma on lt.album_id = ma.id" + " inner join library.filetag ft on ft.file_id = lt.file_id" + " inner join (select la.album_id, la.id as sort_id " + "  from library.album la " + (query == null ? "" : " where la.album_name_search like ?") + "  order by la.id desc offset ? limit ?) filter on lt.album_id = filter.album_id" + " group by lt.album_id, filter.sort_id) tr" + " inner join library.album la on la.album_id = tr.album_id" + " inner join music.album ma on la.album_id = ma.id" + " inner join music.artist a on ma.artist_id = a.id" + " left outer join library.file f1 on f1.id = la.embeddedcoverartfile_id" + " left outer join library.directory d1 on f1.directory_id = d1.id" + " left outer join library.file f2 on f2.id = la.coverartfile_id" + " left outer join library.directory d2 on f2.directory_id = d2.id" + " left outer join music.albuminfo ai on ai.album_id = la.album_id" + " order by sort_id desc";
    Object[] params = query == null ? new Object[] { offset, limit } : new Object[] { getNameQuery(query), offset, limit };
    return jdbcTemplate.query(sql, params, new AlbumRowMapper());
}

Example 5 with AlbumRowMapper

use of com.github.hakko.musiccabinet.dao.jdbc.rowmapper.AlbumRowMapper in project musiccabinet by hakko.

the class JdbcLibraryBrowserDao method getRecentlyPlayedAlbums.

@Override
public List<Album> getRecentlyPlayedAlbums(String lastFmUsername, int offset, int limit, String query) {
    String userTable = "", userCriteria = "", albumNameCriteria = "";
    List<Object> args = new ArrayList<>();
    if (lastFmUsername != null) {
        userTable = " inner join music.lastfmuser u on pc.lastfmuser_id = u.id";
        userCriteria = " and u.lastfm_user = upper(?)";
        args.add(lastFmUsername);
    }
    if (query != null) {
        albumNameCriteria = " and la.album_name_search like ?";
        args.add(getNameQuery(query));
    }
    args.add(offset);
    args.add(limit);
    String sql = "select ma.artist_id, a.artist_name_capitalization, ma.id, ma.album_name_capitalization, la.year," + " d1.path, f1.filename, d2.path, f2.filename, ai.largeimageurl, tr.track_ids from" + " (select lt.album_id as album_id, array_agg(lt.id order by coalesce(ft.disc_nr, 1)*100 + coalesce(ft.track_nr, 0)) as track_ids, filter.last_invocation_time" + " from library.track lt" + " inner join music.album ma on lt.album_id = ma.id" + " inner join library.filetag ft on ft.file_id = lt.file_id" + " inner join (select la.album_id, max(invocation_time) as last_invocation_time" + " from library.playcount pc" + " inner join library.album la on pc.album_id = la.album_id" + userTable + " where true" + userCriteria + albumNameCriteria + " group by la.album_id order by last_invocation_time desc offset ? limit ?) filter on lt.album_id = filter.album_id" + " group by lt.album_id, filter.last_invocation_time) tr" + " inner join library.album la on la.album_id = tr.album_id" + " inner join music.album ma on la.album_id = ma.id" + " inner join music.artist a on ma.artist_id = a.id" + " left outer join library.file f1 on f1.id = la.embeddedcoverartfile_id" + " left outer join library.directory d1 on f1.directory_id = d1.id" + " left outer join library.file f2 on f2.id = la.coverartfile_id" + " left outer join library.directory d2 on f2.directory_id = d2.id" + " left outer join music.albuminfo ai on ai.album_id = la.album_id" + " order by last_invocation_time desc";
    return jdbcTemplate.query(sql, args.toArray(), new AlbumRowMapper());
}