DAO

package model;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.sql.*;


public class FilmDAO {

	Film oneFilm = null;
	Connection conn = null;
    Statement stmt = null;
	String user = "myerss";
    String password = "glisDest6";
    // Note none default port used, 6306 not 3306
    String url = "jdbc:mysql://mudfoot.doc.stu.mmu.ac.uk:6306/"+user;

	public FilmDAO() {}


	private void openConnection(){
		// loading jdbc driver for mysql
		try{
		    Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch(Exception e) { System.out.println(e); }

		// connecting to database
		try{
			// connection string for demos database, username demos, password demos
 			conn = DriverManager.getConnection(url, user, password);
		    stmt = conn.createStatement();
		} catch(SQLException se) { System.out.println(se); }
    }
	private void closeConnection(){
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private Film getNextFilm(ResultSet rs){
    	Film thisFilm=null;
		try {
			thisFilm = new Film(
					rs.getInt("id"),
					rs.getString("title"),
					rs.getInt("year"),
					rs.getString("director"),
					rs.getString("stars"),
					rs.getString("review"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return thisFilm;
	}



   public ArrayList<Film> getAllFilms(){

		ArrayList<Film> allFilms = new ArrayList<Film>();
		openConnection();

	    // Create select statement and execute it
		try{
		    String selectSQL = "select * from films limit 20";
		    ResultSet rs1 = stmt.executeQuery(selectSQL);
	    // Retrieve the results
		    while(rs1.next()){
		    	oneFilm = getNextFilm(rs1);
		    	allFilms.add(oneFilm);
		   }

		    stmt.close();
		    closeConnection();
		} catch(SQLException se) { System.out.println(se); }

	   return allFilms;
   }

   public Film getFilmByID(int id){

		openConnection();
		oneFilm=null;
	    // Create select statement and execute it
		try{
		    String selectSQL = "select * from films where id="+id;
		    ResultSet rs1 = stmt.executeQuery(selectSQL);
	    // Retrieve the results
		    while(rs1.next()){
		    	oneFilm = getNextFilm(rs1);
		    }

		    stmt.close();
		    closeConnection();
		} catch(SQLException se) { System.out.println(se); }

	   return oneFilm;
   }

   public boolean deleteFilmByID(int id){

		openConnection();
		oneFilm=null;
	    // Create select statement and execute it
		try{
		    String selectSQL = "delete from films where id="+id;
		    ResultSet rs1 = stmt.executeQuery(selectSQL);
	    // Confirms Delete
		    while(rs1.next()){
		    	oneFilm = getNextFilm(rs1);
		    }

		    stmt.close();
		    closeConnection();
		} catch(SQLException se) { System.out.println(se); }

	   return false;
   }

   /*
public boolean insertFilm(Film film) {
	openConnection();
	oneFilm=null;

    try {
    	 String selectSQL = "INSERT INTO film VALUES (id, title, year, director, stars, review)";

    	 //Fix this

        ps.setString(1, user.getName());
        ps.setString(2, user.getPass());
        ps.setInt(3, user.getAge());
        int i = ps.executeUpdate();
      if(i == 1) {
        return true;
        }

    } catch (SQLException ex) {
        ex.printStackTrace();
    }
    return false;
}  */




}