Table does not display data

Asked

Viewed 35 times

-1

The table doesn’t show the data, but I’ve been going over it for four hours and I don’t think anything’s wrong

package dao;

import Connection.Conexao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import model.Paises;

/**
 *
 * @author pedro
 */
public class AmericaSulDao {
    
    private Connection connection;

    public AmericaSulDao() {
        connection = Conexao.getConnection();
    }
    
        public void addPaises(Paises americaSul) {
        Connection con = Conexao.getConnection();
        
        PreparedStatement stmt = null;

        try {
            stmt = con.prepareStatement("INSERT INTO paises (nome_pais, continente_id_continente, id_paises)VALUES(?,?,?)");
            stmt.setString(1, americaSul.getNomePais());
            stmt.executeUpdate();

        } catch (SQLException ex) {
            System.out.println(ex);
        }
        }
        
        public void deleteAmericaSul(int IdAmericaSul) {
        String            sqlString = "update continente set nome_pais=?, continente_id_continente=?, id_paises=? where cd_paises=?";
        PreparedStatement stm       = null;
        
        try {
            stm = connection.prepareStatement("delete from  where id_paises=?");
            // Parameters start with 1
            stm.setInt(1, IdAmericaSul);  
            stm.executeUpdate();
            stm.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
        
        public void updateAmericaSul(Paises americaSul) {
        
        PreparedStatement stm       = null;
        
        try {
            stm = connection.prepareStatement("update continente set nome_pais=?, continente_id_continente=?, id_paises=? where cd_paises=?");
            
            stm.setString(1, americaSul.getNomePais());
            stm.setInt(2, americaSul.getContinente_id_continente());
            stm.setInt(3, americaSul.getIdPaises());  
            stm.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
        
    }
    
        public List<Paises> getAllAmericaSul() {
        List<Paises> listaAmericaSul = new ArrayList<Paises>();
        try {
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("select nome_pais from paises, continente where nome_continente = America do Sul");
            while (rs.next()) {
                Paises americaSul = new Paises();  
                americaSul.setNomePais(rs.getString("nome_pais"));
                americaSul.setIdContinente(rs.getInt("continente_id_continente"));
                americaSul.setIdPaises(rs.getInt("id_paises"));
                listaAmericaSul.add(americaSul);
                
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return listaAmericaSul;
    }

    public Paises getAmericaSulByID(int idAmericaSul) {
        Paises americaSul = new Paises();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement("select nome_pais from paises, continente where nome_continente = America do Sul and id_paises=?");
            preparedStatement.setInt(1, idAmericaSul);
            ResultSet rs = preparedStatement.executeQuery();
            if (rs.next()) {
                americaSul.setIdContinente(rs.getInt("continente_id_continente"));
                americaSul.setIdPaises(rs.getInt("id_paises"));
                americaSul.setNomePais(rs.getString("nm_paises"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return americaSul;
    }
    
}

DAO ARCHIVE

package controller;

import dao.AmericaSulDao;

import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.RequestDispatcher;
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 model.Continentes;
import model.Paises;

/**
 *
 * @author pedro
 */
@WebServlet(name = "AmericaSul", urlPatterns = {"/AmericaSulController"})
public class AmericaSulController extends HttpServlet {
    private static final long serialVersionUID = 1L;
    private static String INSERT_OR_EDIT = "/telaAmericaSul.jsp";
    private static String LIST_CONT = "/telaAmericaSul.jsp";
    private AmericaSulDao dao;

    public AmericaSulController() {
        super();
        dao = new AmericaSulDao();
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String forward="";
        String action = request.getParameter("action");

        if (action.equalsIgnoreCase("delete")){
            int IdAmericaSul = Integer.parseInt(request.getParameter("IdAmericaSul"));
            dao.deleteAmericaSul(IdAmericaSul);
            forward = LIST_CONT;
            request.setAttribute("americaSul", dao.getAllAmericaSul()); 
        } 
        
        else if (action.equalsIgnoreCase("edit")){
            forward = INSERT_OR_EDIT;
            int IdAmericaSul = Integer.parseInt(request.getParameter("IdAmericaSul"));
            Paises americaSul = dao.getAmericaSulByID(IdAmericaSul);
            request.setAttribute("americaSul", americaSul);     
        } 
        
        else if (action.equalsIgnoreCase("listaAmericaSul")){
            forward = LIST_CONT;
            request.setAttribute("americaSul", dao.getAllAmericaSul());
        }
        
        else {
            forward = INSERT_OR_EDIT;
        }

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

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        Paises americaSul = new Paises();
        americaSul.setIdContinente(Integer.parseInt(request.getParameter("id_paises")));
        americaSul.setNomePais(request.getParameter("nome_pais"));
        americaSul.setIdContinente(Integer.parseInt(request.getParameter("continente_id_continente")));

        String IdAmericaSul = request.getParameter("IdAmericaSul");
        if(IdAmericaSul == null || IdAmericaSul.isEmpty())
        {
            dao.addPaises(americaSul);
        }
        else
        {
            americaSul.setIdPaises(Integer.parseInt(IdAmericaSul));
            dao.updateAmericaSul(americaSul);
        }
        RequestDispatcher view = request.getRequestDispatcher(LIST_CONT);
        request.setAttribute("IdAmericaSul", dao.getAllAmericaSul());
        view.forward(request, response);
    }
}
java jsp

1 answer

0


The bugs are in the codes you’re doing nome_continente = America do Sul.

public List<Paises> getAllAmericaSul() {
        List<Paises> listaAmericaSul = new ArrayList<Paises>();
        try {
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("select nome_pais from paises, continente where nome_continente = America do Sul");
            while (rs.next()) {
                Paises americaSul = new Paises();  
                americaSul.setNomePais(rs.getString("nome_pais"));
                americaSul.setIdContinente(rs.getInt("continente_id_continente"));
                americaSul.setIdPaises(rs.getInt("id_paises"));
                listaAmericaSul.add(americaSul);
                
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return listaAmericaSul;
    }

Note this line:

ResultSet rs = stmt.executeQuery("select nome_pais from paises, continente where nome_continente = America do Sul");

Correct for:

public List<Paises> getAllAmericaSul() {
        List<Paises> listaAmericaSul = new ArrayList<Paises>();
        String clausulaProcura = "select nome_pais from paises, continente where nome_continente = ?";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(clausulaProcura);
            preparedStatement.setString(1, "America do Sul");
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                Paises americaSul = new Paises();  
                americaSul.setNomePais(rs.getString("nome_pais"));
                americaSul.setIdContinente(rs.getInt("continente_id_continente"));
                americaSul.setIdPaises(rs.getInt("id_paises"));
                listaAmericaSul.add(americaSul);
                
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return listaAmericaSul;
}

Correct your select making the table junctions appropriately according to their table structure using left join or inner join. I suggest that before you put the query in the code, test before in an appropriate query tool SQL.

Also correct the method getAmericaSulByID(int idAmericaSul), as it has similar error.

Watch the line down and make the necessary correction:

PreparedStatement preparedStatement = connection.prepareStatement("select nome_pais from paises, continente where nome_continente = America do Sul and id_paises=?");

  • Even if I do the Inner Join and fix the code the error persists, there is something else that might be causing problem?

  • In the method getAllAmericaSul() is generating an error? Or at the end of the procedure is the variable listaAmericaSul is empty? No select is select nome_pais from paises, continente where nome_continente = ?, so you’re just selecting the field nome_pais and is trying to access other fields such as continente_id_continente and id_paises. If you want to access, you need to include such fields in your select.

  • Followed my suggestion: "I suggest that before putting the query in the code, test before in a proper SQL query tool.". There are tools like Dbeaver that you can connect to various types of database.

  • Thanks for the help, I managed to tidy up, was passing a wrong parameter at the time of receiving the data.

  • Great @Pedroviniciussantosrodrigue, good that I helped. Please check How and why to accept an answer .

Browser other questions tagged java jsp

You are not signed in. Login or sign up in order to post.