Untitled

/////////////// pocetak.jsp///////////////

<%--
    Document   : pocetak
    Created on : Apr 21, 2016, 12:03:09 PM
    Author     : Admin
--%>

<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>

        <form method="POST" action="ControllerSloj">
            <input type="number" name="idZupanije"/>
            <input type="submit" value="Dohvati" />
        </form>
        <br>


        <table border="1" width="50%">
                <tr>
                    <th>Broj županije</th>
                    <th>Županija</th>
                    <th>Broj mjesta u županiji</th>
                </tr>
                <tr>
            <c:forEach var="rezultat" items="${rezultat}">
                    <td><c:out value="${rezultat}"/></td>
            </c:forEach>
                </tr>
        </table>
  <br />
    </body>
</html>

/////////////// controllerSloj servlet ///////////////


    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        String idZupanijeS = null;

        idZupanijeS = request.getParameter("idZupanije");

        SpajanjeNaBazu spoj = new SpajanjeNaBazu();

        if(spoj.checkID(idZupanijeS)) {
            List<String> rezultati = spoj.GetData(idZupanijeS);

            request.setAttribute("rezultat", rezultati);
        }
        else {
            request.setAttribute("rezultat", "Unesite broj između 1 i 21!");
        }

        RequestDispatcher view = request.getRequestDispatcher("pocetak.jsp");
        view.forward(request, response);
    }

/////////////// spajanjeNaBazu ///////////////

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package model;

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 java.util.List;

/**
 *
 * @author Admin
 */
public class SpajanjeNaBazu {

    String url = "jdbc:mysql://localhost/wauj5";

    public List<String> GetData(String idZupanije) {
        List<String> rezultati = new ArrayList<>();

        try {
            Class.forName("org.mariadb.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new IllegalStateException("Cannot find the driver in the classpath!", e);
        }

        try(Connection connection = DriverManager.getConnection(url, "root", "")) {
            Statement stmt = connection.createStatement();
            String query = "select zupanije.idzupanije, nazivzupanije, count(mjesta.idzupanije) as broj_mjesta from zupanije left join mjesta on (mjesta.idzupanije = zupanije.idzupanije) where zupanije.idzupanije = " + Integer.valueOf(idZupanije);
            ResultSet rs = stmt.executeQuery(query);

            while(rs.next()) {
                rezultati.add(Integer.toString(rs.getInt("idzupanije")));
                rezultati.add(rs.getString("nazivzupanije"));
                rezultati.add(Integer.toString(rs.getInt("broj_mjesta")));
            }

            rs.close();
            stmt.close();
            connection.close();
        } catch (SQLException e) {
            throw new IllegalStateException("Neuspjesno spajanje.", e);
        }

        return rezultati;
    }

    public boolean checkID(String idZupanijeS) {

        Integer idZupanijeI;

        try {
            idZupanijeI = Integer.valueOf(idZupanijeS);
        } catch (Exception e) {
            return false;
        }

        return !(idZupanijeI > 21 || idZupanijeI < 1);
    }

}