base.jee.api.sql.GetPendingEmail Maven / Gradle / Ivy

Go to download
/**
 * Creative commons Attribution-NonCommercial license.
 *
 * http://creativecommons.org/licenses/by-nc/2.5/au/deed.en_GB
 *
 * NO WARRANTY IS GIVEN OR IMPLIED, USE AT YOUR OWN RISK.
 */
package base.jee.api.sql;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.sql.DataSource;

import base.Query;
import base.jee.api.model.Email;

/**
 * Return a list of emails ready to be delivered. To prevent the scenario
 * where an email is delivered but not marked as delivered in the database,
 * email that is in the process of being delivered is marked as "in-progress".
 * Email that is delivered but never unmarked as "in-progress" requires human
 * interaction with the system to check what happened.
 */
public class GetPendingEmail extends Query {

	private DataSource ds;
	private int count;

	public GetPendingEmail(DataSource ds, int count) {
		this.ds = ds;
		this.count = count;
	}

	@Override
	public Query newWithParameters(Map parameters) {
		return new GetPendingEmail(
				(DataSource)parameters.get("ds"),
				Integer.parseInt((String)parameters.get("count")));
	}

	public List execute() throws IOException {
		List results = new LinkedList<>();
		Connection c = null;
		PreparedStatement s = null;
		PreparedStatement t = null;
		ResultSet r = null;

		try {
			c = ds.getConnection();
			c.setAutoCommit(false);

			t = c.prepareStatement("update email set retries=retries+1,in_progress=1,attempt_at=? where uuid=?");
			s = c.prepareStatement(
					"select uuid,email,retries from email " +
					"where in_progress=0 and attempt_at<=? " +
					"order by retries, attempt_at " +
					"limit " + count);
			s.setLong(1, new Date().getTime());
			r = s.executeQuery();
			while(r.next()) {
				Email e = new Email(r.getString(2));
				e.setUuid(UUID.fromString(r.getString(1)));
				results.add(e);
				t.setLong(1, (new Date().getTime()) + 60*1000 + r.getLong(3)*5*60*1000);
				t.setString(2, r.getString(1));
				t.execute(); // Set email as in progress, and prepare retry time in case delivery fails
			}
			t.close();
			t = null;
			c.commit();
			c.close();
			c = null;
		} catch(SQLException e) {
			throw new IOException(e);
		} finally {
			if(r != null) { try { r.close(); } catch (SQLException e) {} }
			if(s != null) { try { s.close(); } catch (SQLException e) {} }
			if(t != null) { try { t.close(); } catch (SQLException e) {} }
			if(c != null) {
				try { c.rollback(); } catch (SQLException e) {}
				try { c.close(); } catch (SQLException e) {}
			}
		}
		return results;
	}

	@Override
	public String getJsonParameters() {
		return "{" +
				"\"count\":" + count +
				"}";
	}

}