/*
 * Created on 2006-01-12
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package mrPack.web;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
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.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

/**
 * @author 112
 * 
 * TODO To change the template for this generated type comment go to Window -
 * Preferences - Java - Code Style - Code Templates
 */

public class Main extends HttpServlet {
    private ArrayList miasta = new ArrayList();

    private ArrayList rodzaje = new ArrayList();

    private ArrayList panstwa = new ArrayList();

    DataSource dataSource = null;

    public void init() throws ServletException {
        try {
            InitialContext ctx = new InitialContext();
            Context envContext = (Context) ctx.lookup("java:comp/env");
            dataSource = (DataSource) envContext.lookup("jdbc/myDerby");
        } catch (Exception exc) {
            throw new ServletException("Nie ustanowiono połaczenia z bazą", exc);
        }
    }

    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws IOException, ServletException {

        Connection con = null;

        String query = "select DISTINCT biuro.panstwo.namePanstwo, biuro.miasto.nameMiasto,"
                    + " biuro.rodzaj.nameRodzaj from biuro.miasto, biuro.panstwo, "
                    + "biuro.rodzaj, biuro.oferta where "
                    + "biuro.oferta.miasto = biuro.miasto.idMiasto and biuro.oferta.panstwo ="
                    + " biuro.panstwo.idPanstwo and biuro.oferta.rodzaj = biuro.rodzaj.idRodzaj";

        Statement st = null;
        ResultSet rs = null;

        try {
            con = dataSource.getConnection();
            st = con.createStatement();
            rs = st.executeQuery(query);

            while (rs.next()) {
                miasta.add(rs.getString("nameMiasto"));
                panstwa.add(rs.getString("namePanstwo"));
                rodzaje.add(rs.getString("nameRodzaj"));
            }

            request.setAttribute("miasta", miasta);
            request.setAttribute("panstwa", panstwa);
            request.setAttribute("rodzaje", rodzaje);

            rs.close();
            st.close();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        RequestDispatcher view = request.getRequestDispatcher("Welcome.jsp");

        view.forward(request, response);
        miasta.clear();
        panstwa.clear();
        rodzaje.clear();

    }

    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        doGet(request, response);
    }

}