RssDAO.java

/*
*
* Copyright 2013 Entando S.r.l. (http://www.entando.com) All rights reserved.
*
* This file is part of Entando software. 
* Entando is a free software; 
* You can redistribute it and/or modify it
* under the terms of the GNU General Public License (GPL) as published by the Free Software Foundation; version 2.
* 
* See the file License for the specific language governing permissions   
* and limitations under the License
* 
* 
* 
* Copyright 2013 Entando S.r.l. (http://www.entando.com) All rights reserved.
*
*/
package com.agiletec.plugins.jprss.aps.system.services.rss;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import com.agiletec.aps.system.common.AbstractDAO;

/**
 * Data Access Object for Channel Object. 
 */
public class RssDAO extends AbstractDAO implements IRssDAO {
	
	@Override
	public void addChannel(Channel channel) {
		Connection conn = null;
		PreparedStatement stat = null;
		try {
			conn = this.getConnection();
			conn.setAutoCommit(false);
			stat = conn.prepareStatement(ADD_CHANNEL);
			stat.setInt(1, channel.getId());
			stat.setString(2, channel.getTitle());
			stat.setString(3, channel.getDescription());
			stat.setString(4, new Boolean(channel.isActive()).toString());
			stat.setString(5, channel.getContentType());
			stat.setString(6, channel.getCategory());
			if (null != channel.getFilters() && channel.getFilters().length() > 0) {
				stat.setString(7, channel.getFilters());
			} else {
				stat.setNull(7, Types.VARCHAR);
			}
			stat.setString(8, channel.getFeedType());
			if (channel.getMaxContentsSize() > 0) {
				stat.setInt(9, channel.getMaxContentsSize());
			} else {
				stat.setNull(9, Types.INTEGER);
			}
			stat.executeUpdate();
			conn.commit();
		} catch (Throwable t) {
			processDaoException(t, "Error adding a channel", "addChannel");
		} finally {
			closeDaoResources(null, stat, conn);
		}
	}
	
	@Override
	public void updateChannel(Channel channel) {
		Connection conn = null;
		PreparedStatement stat = null;
		try {
			conn = this.getConnection();
			conn.setAutoCommit(false);
			stat = conn.prepareStatement(UPDATE_CHANNEL);
			//id, title, description, active, contentType, fileters, feedtype
			stat.setString(1, channel.getTitle());
			stat.setString(2, channel.getDescription());
			stat.setString(3, new Boolean(channel.isActive()).toString());
			stat.setString(4, channel.getContentType());
			if (null != channel.getCategory() && channel.getCategory().length() > 0 ) {
				stat.setString(5, channel.getCategory());
			} else {
				stat.setNull(5, Types.VARCHAR);
			}
			stat.setString(6, channel.getFilters());
			stat.setString(7, channel.getFeedType());
			if (channel.getMaxContentsSize() > 0) {
				stat.setInt(8, channel.getMaxContentsSize());
			} else {
				stat.setNull(8, Types.INTEGER);
			}
			stat.setInt(9, channel.getId());
			stat.executeUpdate();
			conn.commit();
		} catch (Throwable t) {
			processDaoException(t, "Error updating a channel", "updateChannel");
		} finally {
			closeDaoResources(null, stat, conn);
		}
	}
	
	@Override
	public void deleteChannel(int id) {
		Connection conn = null;
		PreparedStatement stat = null;
		try {
			conn = this.getConnection();
			conn.setAutoCommit(false);
			stat = conn.prepareStatement(DELETE_CHANNEL);
			stat.setInt(1, id);
			stat.executeUpdate();
			conn.commit();
		} catch (Throwable t) {
			processDaoException(t, "Error deleting a channel", "deleteChannel");
		} finally {
			closeDaoResources(null, stat, conn);
		}
	}
	
	@Override
	public List<Channel> getChannels(int status) throws Throwable {
		List<Channel> channels = new ArrayList<Channel>();
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet res = null;
		try {
			conn = this.getConnection();
			if (status == Channel.STATUS_ALL) {
				stat = conn.prepareStatement(LOAD_CHANNELS);
			} else {
				stat = conn.prepareStatement(LOAD_CHANNELS_BY_STATUS);
				stat.setString(1, new Boolean(status == Channel.STATUS_ACTIVE).toString());
			}
			res = stat.executeQuery();
			Channel channel;
			while (res.next()) {
				channel = this.getChannelFromResultSet(res);
				channels.add(channel);
			}
		} catch (Throwable t) {
			processDaoException(t, "Error loading the channels list with status", "getChannels");
		} finally {
			closeDaoResources(res, stat, conn);
		}
		return channels;
	}
	
	@Override
	public Channel getChannel(int id) {
		Channel channel = null;
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet res = null;
		try {
			conn = this.getConnection();
			stat = conn.prepareStatement(LOAD_CHANNEL_BY_ID);
			stat.setInt(1, id);
			res = stat.executeQuery();
			while (res.next()) {
				channel = this.getChannelFromResultSet(res);
			}
		} catch (Throwable t) {
			processDaoException(t, "Error loading a channel", "getChannel");
		} finally {
			closeDaoResources(res, stat, conn);
		}
		return channel;
	}
	
	private Channel getChannelFromResultSet(ResultSet res) throws Throwable {
		Channel channel = new Channel();
		try {
			channel.setActive(res.getString("active").equalsIgnoreCase("true"));
			channel.setContentType(res.getString("contenttype"));
			channel.setCategory(res.getString("category"));
			channel.setDescription(res.getString("description"));
			channel.setFeedType(res.getString("feedtype"));
			channel.setFilters(res.getString("filters"));
			channel.setId(res.getInt("id"));
			channel.setTitle(res.getString("title"));
			
			int maxContentSize = res.getInt("maxcontentsize");
			if (maxContentSize > 0) {
				channel.setMaxContentsSize(maxContentSize);
			}
		} catch (Throwable t) {
			throw new Throwable("Error creating a channel from resultset", t);
		}
		return channel;
	}

	private static final String ADD_CHANNEL = 
		"INSERT INTO jprss_channel (id, title, description, active, contentType, category, filters, feedtype, maxcontentsize) " +
		"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
	
	private static final String DELETE_CHANNEL = 
		"DELETE FROM jprss_channel WHERE id = ?";
	
	private static final String UPDATE_CHANNEL = 
		"UPDATE jprss_channel SET title =?, description=?, active=?, contentType=?, category=?, filters=?, feedtype=?, maxcontentsize=? WHERE id=?";
	
	private static final String LOAD_CHANNELS_BASE_BLOCK = 
		"SELECT id, title, description, active, contentType, category, filters, feedtype, maxcontentsize from jprss_channel ";
	
	private static final String LOAD_CHANNELS_ORDER_BLOCK = "ORDER BY description ";
	
	private static final String LOAD_CHANNELS = 
		LOAD_CHANNELS_BASE_BLOCK + LOAD_CHANNELS_ORDER_BLOCK;
	
	private static final String LOAD_CHANNELS_BY_STATUS = 
		LOAD_CHANNELS_BASE_BLOCK + " where active = ? " + LOAD_CHANNELS_ORDER_BLOCK;
	
	private static final String LOAD_CHANNEL_BY_ID = 
		LOAD_CHANNELS_BASE_BLOCK + " where id = ? " + LOAD_CHANNELS_ORDER_BLOCK;
	
}