CourseQueries

package planningtool;


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

	public class courseQueries {
	/*
	 *
	 *
	 * This Class manages database operations associated to the Course Class
	 * Don't worry yet about the try/catch/finally blocks in some methods. This will be explained later in the course
	 */
		// DB connection details
		private static final String URL = "jdbc:mysql://eu-cdbr-azure-west-b.cloudapp.net:3306/inkinen_jesse";
		private static final String USERNAME = "b9b0dcc9056907";
		private static final String PASSWORD = "0d8483fc";

		private Connection connection = null;
		private PreparedStatement selectAllCourses = null;
		private PreparedStatement insertCourse = null;

		public courseQueries()
		{
			try
			{
				connection = DriverManager.getConnection(URL, USERNAME, PASSWORD); // Starts a connection to the database
				selectAllCourses = connection.prepareStatement("SELECT * FROM courses"); // Prepare the select query that gets all Courses from the database

				// Here you will need to prepare the insert query
				insertCourse = connection.prepareStatement("INSERT INTO courses VALUES (?,?,?,?)");
			}
			catch (SQLException sqlException)
			{
				sqlException.printStackTrace();
				System.exit(1);
			}
		}

		/*
		 * This method will execute the select query that gets all Courses from the database.
		 * It returns an ArrayList containing Course objects initialized with Course data from each row in the Courses table (database)
		 */
		public ArrayList<Course> getAllCourses()
		{
			ArrayList<Course> results = null;
			ResultSet resultSet = null;

			try
			{
				resultSet = selectAllCourses.executeQuery(); // Here is where we actually execute the select query. resultSet contains the rows returned by the query
				results = new ArrayList<Course>();

				while(resultSet.next()) // for each row returned by the select query...
				{
					// Initialize a new Course object with the row's data. Add the Course object to the results ArrayList
					results.add(new Course(
						resultSet.getString("plateNr"), // get the value associated to the platNr column
						resultSet.getString("colour"), // get the value associated to the colour column
						resultSet.getString("model"), // get the value associated to the model column
						resultSet.getInt("year"))); // get the value associated to the year column
				}
			} // end try
			catch (SQLException sqlException)
			{
				sqlException.printStackTrace();
			}
			finally
			{
				try
				{
					resultSet.close();
				}
				catch (SQLException sqlException)
				{
					sqlException.printStackTrace();
				}
			} // end finally

			return results;
		} // end method getAllCourses

		/*
		 * Method that inserts a new Course in the database
		 */
		public void addCourse(String plateNr, String colour, String model, int modelYear)
		{
			try
			{
				// Setting the values for the question marks '?' in the prepared statement
				insertCourse.setString(1, plateNr);
				insertCourse.setString(2, colour);
				insertCourse.setString(3, model);
				insertCourse.setInt(4, modelYear);

				// result will contain the amount of updated rows. It should be 1. To simplify the code let's not verify this
				int result = insertCourse.executeUpdate();
			}
			catch (SQLException sqlException)
			{
				sqlException.printStackTrace();
			}
		}
	}