package be.pxl.ticket.dao;

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

import be.pxl.ticket.bean.TicketBean;

public class TicketDAO {
    private String url;
    private String user;
    private String password;

public TicketDAO(String url, String user, String password) {
    this.url = url;
    this.user = user;
    this.password = password;
}

public void setDriver(String driver)
         throws ClassNotFoundException {
      // Laad de driver
       Class.forName(driver);
   }

public TicketBean getTicketById(int id) {
    if (id > 0) {
        try (Connection con = getConnection();
                PreparedStatement stmt = con.prepareStatement(
                        "SELECT id, state, location, description, title, timeCreated, submitBy, assignedTo from tickettable WHERE id=?")) {
            stmt.setInt(1, id);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    TicketBean ticket = new TicketBean();
                    ticket.setId(id);
                    ticket.setState(rs.getString("state"));
                    ticket.setLocation(rs.getString("location"));
                    ticket.setDescription(rs.getString("description"));
                    ticket.setTitle(rs.getString("title"));
                    ticket.setTimeCreated(rs.getDate("timeCreated"));
                    ticket.setSubmitBy(rs.getString("submitBy"));
                    ticket.setAssignedTo(rs.getString("assignedTo"));
                    return ticket;
                } else {
                    return null;// als ticket niet wordt gevonden
                }
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println("Failed to get Ticket");
                return null;
            }
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
            System.out.println("Failed to get Ticket 2");
            return null;
        }
    } else {
        System.out.println("Id must be greater than 0");
        return null;
    }
}

private Connection getConnection() throws SQLException {
       return DriverManager.getConnection(url, user, password);
}

public void setUrl(String url) {
    this.url = url;
}

public void setUser(String user) {
    this.user = user;
}

public void setPassword(String password) {
    this.password = password;
}

package be.pxl.ticket.servlet;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import be.pxl.ticket.bean.TicketBean;
import be.pxl.ticket.dao.TicketDAO;
import be.pxl.ticket.service.ITicketService;
import be.pxl.ticket.service.TicketService;

@WebServlet("/Ticket")
public class TicketServlet extends HttpServlet {
    private ITicketService service;

    public void init() throws ServletException{
        super.init();
        service = new TicketService();
        service.setTicketDAO(new TicketDAO("mysql.jdbc://localhost/ticketdb1","root",""));
    }

    public void doGet(HttpServletRequest req, HttpServletResponse res) {
        int id = Integer.parseInt(req.getParameter("id"));
        TicketBean ticket = service.getTicketById(id);
        System.out.println(id);
        req.setAttribute("tb", ticket);
        try {
            req.getRequestDispatcher("TicketView.jsp").forward(req, res);//!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
        } catch (ServletException e) {
            System.out.println("servlet-exception :0");
            e.printStackTrace();
        } catch (IOException e) {
            System.out.println("something wrong with IO");
            e.printStackTrace();
        }

        System.out.println("triggered doGet");
    }

    public void doPost(HttpServletRequest req, HttpServletResponse res) {
        System.out.println("triggered doPost");
    }

}

package be.pxl.ticket.service;

import java.sql.SQLException;

import be.pxl.ticket.bean.TicketBean;
import be.pxl.ticket.dao.TicketDAO;

public class TicketService implements ITicketService {
    private TicketDAO ticketDAO;

    @Override
    public TicketBean getTicketById(int id){
        // TODO Auto-generated method stub
            return ticketDAO.getTicketById(id);

    }

    public TicketDAO getTicketDAO() {
        return ticketDAO;
    }

    public void setTicketDAO(TicketDAO ticketDAO) {
        this.ticketDAO = ticketDAO;
    }

}