com.webstersmalley.tv.db.JdbcTvDao Maven / Gradle / Ivy

Go to download
/*
 * Copyright 2013 Webster Smalley
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package com.webstersmalley.tv.db;

import com.webstersmalley.tv.domain.Channel;
import com.webstersmalley.tv.domain.Program;
import com.webstersmalley.tv.domain.Recording;
import com.webstersmalley.tv.domain.SearchQuery;
import org.joda.time.DateTime;
import org.joda.time.Weeks;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service("tvDao")
public class JdbcTvDao implements TvDao {
    private static final DateTime currentDate = new DateTime();
    private Logger logger = LoggerFactory.getLogger(getClass());
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    private JdbcTemplate jdbcTemplate;
    private Map channelMap = new HashMap();

    @Resource(name = "listerService")
    private static final class CustomBeanPropertySqlParameterSource extends BeanPropertySqlParameterSource {
        CustomBeanPropertySqlParameterSource(Object o) {
            super(o);
        }

        @Override
        public Object getValue(String paramName) {
            Object result = super.getValue(paramName);
            if (result != null && result instanceof DateTime) {
                return ((DateTime) result).toDate();
            } else {
                return result;
            }

        }
    }

    private static final class ChannelRowMapper implements RowMapper {
        public Channel mapRow(ResultSet rs, int rowNum) throws SQLException {
            Channel channel = new Channel();
            channel.setId(rs.getInt("id"));
            channel.setName(rs.getString("name"));
            return channel;
        }
    }

    private static final class ProgramRowMapper implements RowMapper {

        private TvDao tvDao;

        public ProgramRowMapper(TvDao tvDao) {
            this.tvDao = tvDao;
        }

        public Program mapRow(ResultSet rs, int rowNum) throws SQLException {
            Program program = new Program();
            program.setId(rs.getInt("id"));
            program.setTitle(rs.getString("title"));
            program.setSubtitle(rs.getString("subtitle"));
            program.setStartTime(new DateTime(rs.getTimestamp("starttime")));
            program.setDuration(rs.getInt("duration"));
            program.setDescription(rs.getString("description"));
            program.setChannel(tvDao.getChannelById(rs.getInt("channelid")));
            program.setEpisodeNumber(rs.getString("episodeNumber"));
            program.setSeasonNumber(rs.getString("seasonNumber"));
            program.setRepeat(rs.getBoolean("repeat"));
            program.setNewSeries(rs.getBoolean("newSeries"));
            program.setHasNameMatchedRecordings(rs.getBoolean("recordingsTitleMatch"));
            program.setHasNameAndChannelMatchedRecordings(rs.getBoolean("recordingsTitleAndChannelMatch"));
            program.setHasExactMatchRecordings(rs.getBoolean("recordingsTimeMatch"));
            program.setMovie(rs.getBoolean("movie"));
            program.setYear(rs.getString("year"));
            program.setImdbId(rs.getString("imdbId"));
            program.setImdbRating(rs.getBigDecimal("imdbRating"));
            program.setImdbSource(rs.getString("imdbSource"));
            program.setWeekNumber(Weeks.weeksBetween(currentDate, program.getStartTime()).getWeeks());
            program.calculateTags();
            return program;
        }
    }

    @Resource(name = "dataSource")
    public void setDataSource(DataSource dataSource) {
        this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Override
    public List getChannels() {
        String sql = "select * from channels";
        RowMapper mapper = new ChannelRowMapper();
        return jdbcTemplate.query(sql, mapper);
    }

    @Override
    public void addChannel(Channel channel) {
        try {
            getChannel(channel.getName());
        } catch (EmptyResultDataAccessException e) {
            String sql = "insert into channels (id, name) values (:id, :name)";
            SqlParameterSource namedParameters = new CustomBeanPropertySqlParameterSource(channel);
            namedParameterJdbcTemplate.update(sql, namedParameters);
        }
    }

    @Override
    public Channel getChannel(String channelName) {
        String sql = "select * from channels where name = ?";
        RowMapper mapper = new ChannelRowMapper();
        return jdbcTemplate.queryForObject(sql, mapper, channelName);
    }

    @Override
    public Channel getChannelById(int id) {
        if (channelMap.containsKey(id)) {
            return channelMap.get(id);
        } else {
            String sql = "select * from channels where id = ?";
            RowMapper mapper = new ChannelRowMapper();
            Channel channel = jdbcTemplate.queryForObject(sql, mapper, id);
            channelMap.put(id, channel);
            return channel;
        }
    }

    @Override
    public List getProgramsByTitleAndChannel(String programTitle, String channel) {
        String sql = "select * from vprograms where title = ? and channelid in (select id from channels where name = ?)";
        RowMapper mapper = new ProgramRowMapper(this);
        return jdbcTemplate.query(sql, mapper, programTitle, channel);
    }

    @Override
    public List getProgramsByChannel(Channel channel) {
        String sql = "select * from vprograms where channelid in (select id from channels where id = ?)";
        RowMapper mapper = new ProgramRowMapper(this);
        return jdbcTemplate.query(sql, mapper, channel.getId());
    }

    @Override
    public void addProgram(Program program) {
        String sql = "insert into programs (title, searchTitle, subtitle, startTime, duration, channelid, description, episodeNumber, seasonNumber, repeat, newSeries, movie, year, imdbId, imdbRating, imdbSource) values (:title, :searchTitle, :subtitle, :startTime, :duration, :channel.id, :description, :episodeNumber, :seasonNumber, :repeat, :newSeries, :movie, :year, :imdbId, :imdbRating, :imdbSource)";
        SqlParameterSource namedParameters = new CustomBeanPropertySqlParameterSource(program);
        namedParameterJdbcTemplate.update(sql, namedParameters);
    }

    private void updateKeywords(Program program) {
        Program programWithId;
        if (program.getId() == -1) {
            programWithId = getProgramByDetails(program);
        } else {
            programWithId = program;
        }
        removeKeywords(programWithId);
        addAllKeywords(programWithId);
    }

    private void removeKeywords(Program program) {
        String sql = "delete from keywords where programid = ?";
        jdbcTemplate.update(sql, program.getId());
    }

    private void addAllKeywords(Program program) {
        String sql = "insert into keywords (programid, keyword) values (?, ?)";
        for (String keyword : program.getKeywords()) {
            jdbcTemplate.update(sql, program.getId(), keyword);
        }
    }

    @Override
    public List getProgramsByTitle(String programTitle) {
        String sql = "select * from vprograms where title = ?";
        RowMapper mapper = new ProgramRowMapper(this);
        return jdbcTemplate.query(sql, mapper, programTitle);
    }

    public Program getProgramByDetails(Program program) {
        String sql = "select * from vprograms where title = :title and startTime = :startTime and channelId = :channel.id";
        SqlParameterSource namedParameters = new CustomBeanPropertySqlParameterSource(program);
        try {
            return namedParameterJdbcTemplate.queryForObject(sql, namedParameters, new ProgramRowMapper(this));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public void clearAllPrograms(Channel channel) {
        jdbcTemplate.update("delete from programs where channelId = ?", channel.getId());

    }

    @Override
    public void addRecording(Recording recording) {
        if (recording.getChannelId() == -1) {
            try {
                recording.setChannelId(getChannel(recording.getChannelName()).getId());
            } catch (EmptyResultDataAccessException e) {
                logger.debug("Couldn't find channel: {}", recording.getChannelName());
            }
        }
        String sql = "insert into recordings (title, channelId, episodeNumber, seasonNumber, startTime, endTime, recStatus) values (:title, :channelId, :episodeNumber, :seasonNumber, :startTime, :endTime, :recStatus)";
        SqlParameterSource namedParameters = new CustomBeanPropertySqlParameterSource(recording);
        namedParameterJdbcTemplate.update(sql, namedParameters);
    }

    private static final class RecordingRowMapper implements RowMapper {

        @Override
        public Recording mapRow(ResultSet rs, int rowNum) throws SQLException {
            Recording recording = new Recording();
            recording.setTitle(rs.getString("title"));
            recording.setChannelId(rs.getInt("channelId"));
            recording.setEpisodeNumber(rs.getString("episodeNumber"));
            recording.setSeasonNumber(rs.getString("seasonNumber"));
            recording.setStartTime(new DateTime(rs.getTimestamp("startTime")));
            recording.setEndTime(new DateTime(rs.getTimestamp("endTime")));
            recording.setRecStatus(rs.getString("recStatus"));
            return recording;
        }
    }

    @Override
    public List getAllRecordings() {
        return jdbcTemplate.query("select * from vrecordings", new RecordingRowMapper());
    }

    @Override
    public List> getAllRecordingsAsMap() {
        return jdbcTemplate.queryForList("select * from vrecordings");
    }

    long queryTime = 0;

    @Override
    public long getQueryTime() {
        return queryTime;
    }

    @Override
    public List getMovies(BigDecimal minimumRating) {
        String sql = "select * from vprograms where imdbRating >= ?";
        return jdbcTemplate.query(sql, new ProgramRowMapper(this), minimumRating);
    }

    @Override
    public List getProgramSummaries() {
        String sql = "select * from vprogramsummaries";
        return jdbcTemplate.query(sql, new ProgramRowMapper(this));
    }

    @Override
    public void deleteAllRecordings() {
        jdbcTemplate.update("delete from recordings");
    }

    @Override
    public List getProgramsBySearchQuery(SearchQuery searchQuery) {
        RowMapper mapper = new ProgramRowMapper(this);

        String sql = null;

        MapSqlParameterSource params = new MapSqlParameterSource();
        if (searchQuery.getKeywords() != null && searchQuery.getKeywords().size() > 0) {
            params.addValue("keywords", searchQuery.getKeywords());
            sql = "select * from vprograms, keywords where programs.id = keywords.programid and keyword in (:keywords)";
        } else {
            sql = "select * from vprograms where 1=1";
        }
        if (searchQuery.getSeasonNumber() != null) {
            params.addValue("seasonNumber", searchQuery.getSeasonNumber());
            sql += " and seasonNumber >= :seasonNumber";
        }
        if (searchQuery.getEpisodeNumber() != null) {
            params.addValue("episodeNumber", searchQuery.getEpisodeNumber());
            sql += " and episodeNumber >= :episodeNumber";
        }
        if (searchQuery.getTitleSearch() != null) {
            String titleSearch = searchQuery.getTitleSearch().toUpperCase();
            if (titleSearch.length() > 2 && titleSearch.startsWith("\"") && titleSearch.endsWith("\"")) {
                titleSearch = titleSearch.substring(1, titleSearch.length() - 1);
                params.addValue("titleSearch", titleSearch);
                sql += " and searchTitle = :titleSearch";
            } else {
                params.addValue("titleSearch", "%" + titleSearch + "%");
                sql += " and searchTitle like :titleSearch";
            }
        }
        sql += " and movie = :isMovie";
        params.addValue("isMovie", searchQuery.isMovie());
        long start = System.currentTimeMillis();
        List returnVal = namedParameterJdbcTemplate.query(sql, params, mapper);
        queryTime += System.currentTimeMillis() - start;
        return returnVal;
    }


}