Music Store with postgres

I need you to start thinking how to design your projects. So I'm going to walk you through a process of how we will be building our applications. 
What do we already know (by the end of this lecture)?

• How to build REST web services (you must do that)
• How to deploy our application to public cloud
• How to create a database and connect to it (Mongo or Postgres)
• How to work with files

What we may possibly still need to learn?

• How to build Front-End (a web application that will consume our APIs)
• How to authenticate and work with other 3rd part APIs

Our music store will have following functionality:

1. Browse all songs
2. Add a song
3. Update a song
4. Delete a song
5. Search songs

Start by building the shells for your services. For instance, "browse all songs" will be GET, will produce JSON, will implement pagination - allow returning only the subset of data, etc

package services;

import java.util.ArrayList;
import java.util.HashMap;

import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.QueryParam;
import javax.ws.rs.core.MediaType;
import javax.ws.rs.core.Response;

import com.fasterxml.jackson.databind.ObjectMapper;

import model.Song;
import command.ListSongsCommand;
import util.Constants;

@Path("song")
public class Services {
	ObjectMapper mapper = new ObjectMapper();

	// Browse all songs
	@GET
	@Produces({ MediaType.APPLICATION_JSON })
	public Response browseSongs(@QueryParam("offset") int offset, @QueryParam("count") int count ) {
		ListSongsCommand command = new ListSongsCommand();
		ArrayList list = command.execute();
		HashMap hm = new HashMap();
		hm.put(Constants.Pagination.DATA, list);
		hm.put(Constants.Pagination.OFFSET, offset);
		hm.put(Constants.Pagination.COUNT, count);
		String songString = null;
		try {
			songString = mapper.writeValueAsString(hm);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return Response.status(200).entity(songString).build();
	}
	// Add a song
	// Update a song
	// Delete a song
	// Search songs
}

You may need shells for your data models and some commands:

package command;

import java.util.ArrayList;

import model.Song;

public class ListSongsCommand {

	public ArrayList execute() {
		// TODO Auto-generated method stub
		return null;
	}

}




package model;

public class Song {

}

I also use a Constants class for any string constants that I may need to use in the project, in this case, it is pagination-related constants

package util;

public class Constants {
	public static class Pagination {
		public static String DATA = "data";
		public static String OFFSET = "offset";
		public static String COUNT = "count";
	}
}

Create your Data Store and Data Model:

CREATE TABLE SONGS(id   serial PRIMARY KEY,title text NOT NULL, artist text NOT NULL);
INSERT INTO SONGS(title, artist) VALUES('Oops I did it again', 'Britney Spears');
INSERT INTO SONGS(title, artist) VALUES('You and I', 'Scorpions');

package model;

public class Song {
	String title;
	String artist;
	int id;
}

Now you can implement the list songs command

package command;

import java.net.URISyntaxException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import model.Song;
import connectionprovider.ConnectionProvider;

public class ListSongsCommand {

	public ArrayList execute() {
		ArrayList ret = new ArrayList();
		try {
			Connection connection = ConnectionProvider.getConnection();
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM Songs");
			while (rs.next()) {
				Song s = new Song();
				s.setArtist(rs.getString("artist"));
				s.setTitle(rs.getString("title"));
				s.setId(rs.getInt("id"));
				ret.add(s);
			}
		} catch (URISyntaxException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ret;
	}

}

Your service is now available at http://localhost:8080/unh/rest/song