package kz.sysdesign.app.DAO;
import java.util.List;
import javax.persistence.*;
import kz.sysdesign.app.Entities.Download;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import kz.sysdesign.app.Entities.Album;
import kz.sysdesign.app.Entities.Artist;
import kz.sysdesign.app.Entities.Track;
/**
* Implementation for Track DAO.
* Methods support database interaction for track data.
*
* @author Kaspars Zarinovs <k.zarinovs@ncl.ac.uk>
*
*/
@Repository
public class TrackDAOImpl implements TrackDAO {
@PersistenceContext(unitName="JpaPersistenceUnit")
private EntityManager em;
/**
* @see kz.sysdesign.app.DAO.TrackDAO#addTrack(kz.sysdesign.app.Entities.Track)
*/
@Transactional
public boolean addTrack(Track track)
{
if(track == null)
return false;
em.persist(track);
return true;
}
/**
* @see kz.sysdesign.app.DAO.TrackDAO#getTracksByPattern(java.lang.String)
*/
public List<Object[]> getTracksByPattern(String pattern)
{
if(pattern == null)
return null;
String nativeQuery = "SELECT " +
"ar.id as artist_id, ar.name as artist_name, " +
"t.id as track_id, t.name as track_name, t.year as track_year, t.md5checksum as track_checksum, " +
"al.id as album_id, al.name as album_name, al.year as album_year " +
"FROM " + Track.class.getSimpleName() + " t " +
"INNER JOIN " + Artist.class.getSimpleName() + " ar ON ar.id = t.artist_id " +
"LEFT OUTER JOIN track_album x ON x.track_id = t.id " +
"LEFT OUTER JOIN " + Album.class.getSimpleName() + " al ON al.id = x.albums_id " +
"WHERE LOWER(t.name) LIKE LOWER(:pattern) " +
"OR LOWER(ar.name) LIKE LOWER(:pattern) " +
"OR LOWER(al.name) LIKE LOWER(:pattern)";
Query query = em.createNativeQuery(nativeQuery).setParameter("pattern", "%" + pattern + "%");
// If no results are found, empty list is returned.
List<Object[]> resultList = (List<Object[]>) query.getResultList();
return resultList;
}
/**
* @see kz.sysdesign.app.DAO.TrackDAO#getAllTracks()
*/
public List<Object[]> getAllTracks()
{
String nativeQuery = "SELECT " +
"ar.id as artist_id, ar.name as artist_name, " +
"t.id as track_id, t.name as track_name, t.year as track_year, t.md5checksum as track_checksum, " +
"al.id as album_id, al.name as album_name, al.year as album_year " +
"FROM " + Track.class.getSimpleName() + " t " +
"INNER JOIN " + Artist.class.getSimpleName() + " ar ON ar.id = t.artist_id " +
"LEFT OUTER JOIN track_album x ON x.track_id = t.id " +
"LEFT OUTER JOIN " + Album.class.getSimpleName() + " al ON al.id = x.albums_id";
Query query = em.createNativeQuery(nativeQuery);
// If no results are found, empty list is returned.
List<Object[]> resultList = (List<Object[]>) query.getResultList();
return resultList;
}
/**
* @see kz.sysdesign.app.DAO.TrackDAO#getTopTenTracks()
*/
public List<Object[]> getTopTenTracks()
{
String nativeQuery = "SELECT t.id, COUNT(t.id) as appearances, a.name as artist_name, t.name as track_name, t.year " +
"FROM " + Download.class.getSimpleName() + " d " +
"INNER JOIN " + Track.class.getSimpleName() + " t ON t.id = d.track_id " +
"INNER JOIN " + Artist.class.getSimpleName() + " a ON a.id = t.artist_id " +
"WHERE date_trunc('week', d.timestamp) = date_trunc('week', CURRENT_TIMESTAMP) " +
"GROUP BY t.id, t.name, t.year, a.name " +
"ORDER BY appearances DESC " +
"LIMIT 10";
Query query = em.createNativeQuery(nativeQuery);
// If no results are found, empty list is returned.
List<Object[]> resultList = (List<Object[]>) query.getResultList();
return resultList;
}
/**
* @see kz.sysdesign.app.DAO.TrackDAO#getTrackByChecksum(java.lang.String)
*/
public Track getTrackByChecksum(String checksum)
{
String jpqlQuery = "FROM " + Track.class.getSimpleName() + " t WHERE t.md5checksum = :checksum";
Query query = em.createQuery(jpqlQuery, Track.class).setParameter("checksum", checksum);
Track link = null;
try
{
link = (Track) query.getSingleResult();
}
catch(NonUniqueResultException ue) { }
catch(NoResultException e) { }
return link;
}
/**
* @see kz.sysdesign.app.DAO.TrackDAO#addDownload(kz.sysdesign.app.Entities.Download)
*/
@Transactional
public boolean addDownload(Download download)
{
if(download == null)
return false;
em.persist(download);
return true;
}
/**
* @see kz.sysdesign.app.DAO.TrackDAO#getAllTracksList()
*/
public List<Track> getAllTracksList()
{
String jpqlQuery = "FROM " + Track.class.getSimpleName();
Query query = em.createQuery(jpqlQuery);
// If no results are found, empty list is returned.
List<Track> resultList = (List<Track>) query.getResultList();
return resultList;
}
}