TaskService.java

package tasks;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import javax.ws.rs.Consumes;
import javax.ws.rs.DELETE;
import javax.ws.rs.GET;
import javax.ws.rs.POST;
import javax.ws.rs.Path;
import javax.ws.rs.PathParam;
import javax.ws.rs.Produces;
import javax.ws.rs.core.MediaType;
import javax.ws.rs.core.Response;

@Path("")
public class TaskService {

	// testing the service without any database interaction
	@GET
	@Path("/hello")
	@Produces(MediaType.TEXT_PLAIN)
	public String sayHello() {
		return "Hello Webservice!";
	}

	// get task collection
	@GET
	@Path("/tasks")
	@Produces(MediaType.APPLICATION_JSON)
	public Response getTasks() {
		System.out.println("Fetching tasks from DB.");
		ArrayList<Task> tasks = new ArrayList<Task>();
		Connection con;
		try {
			Context initContext = new InitialContext();
			Context envContext = (Context) initContext.lookup("java:comp/env");
			DataSource ds = (DataSource) envContext.lookup("jdbc/todolist");
			con = ds.getConnection();
			Statement statement = con.createStatement();
			String query = "SELECT task.id id, task.description description, task.urgency urgency, task.user_id user_id, user.firstname user_name FROM task INNER JOIN user on task.user_id = user.id";
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String description = resultSet.getString("description");
				int urgency = resultSet.getInt("urgency");
				int user_id = resultSet.getInt("user_id");
				String user_name = resultSet.getString("user_name");
				Task t = new Task(id, description, urgency, user_id, user_name);
				tasks.add(t);
			}
			con.close();
		} catch (SQLException | NamingException e) {
			return Response.status(404).entity("There was a database error while fetching tasks.").build();
		}

		return Response.status(200).entity(tasks).build();
	}

	// create a new task
	@POST
	@Path("/tasks")
	@Consumes({ MediaType.APPLICATION_JSON })
	@Produces(MediaType.TEXT_PLAIN)
	public Response newTask(Task newTask) {
		System.out.println("Adding new task. ");
		Connection con;
		try {
			Context initContext = new InitialContext();
			Context envContext = (Context) initContext.lookup("java:comp/env");
			DataSource ds = (DataSource) envContext.lookup("jdbc/todolist");
			con = ds.getConnection();
			String query = "INSERT INTO task (description, urgency, user_id) VALUES (?, ?, ?)";
			PreparedStatement statement = con.prepareStatement(query);
			statement.setString(1, newTask.getDescription());
			statement.setInt(2, newTask.getUrgency());
			statement.setInt(3, newTask.getUser_id());
			statement.executeUpdate();
			con.close();
		} catch (NamingException | SQLException e) {
			return Response.status(500).entity("Could not add new task.").build();
		}
		return Response.status(201).entity("Added task: " + newTask.getDescription()).build();
	}

	// delete a task
	@DELETE
	@Path("/tasks/{id}")
	@Produces(MediaType.TEXT_PLAIN)
	public Response deleteTask(@PathParam("id") int id) {
		System.out.println("Deleting task with id " + id);
		Connection con;
		try {
			Context initContext = new InitialContext();
			Context envContext = (Context) initContext.lookup("java:comp/env");
			DataSource ds = (DataSource) envContext.lookup("jdbc/todolist");
			con = ds.getConnection();
			String query = "DELETE FROM task WHERE id = ?";
			PreparedStatement statement = con.prepareStatement(query);
			statement.setInt(1, id);
			statement.executeUpdate();
			con.close();
		} catch (SQLException | NamingException e) {
			return Response.status(404).entity("There was a database error while fetching tasks.").build();
		}
		return Response.status(200).entity("ok").build();
	}
}