Olá pessoal, primeiramente quero parabenizar essa comunidade JAVA pela qualidade e a disposição dos membros para a propagação do conhecimento. Mas depois de consultar varios posts resolvi uns probleminhas mas continuei com outros. Postarei aqui e vou agradecer ao amigo(a) que puder me ajudar. Eu tenho uma aplicação web (JSP) onde tenho diversos cadastros. Eu consigo realizar todos os metodos basicos de um CRUD em cada um só que num determinado cadastro eu tenho o PACIENTE e dentro desse form eu tenho uma comboBox que teria que me listar os Convenios que eu tenho cadastrado no meu BD. Pois bem, eu consegui listar apenas um objeto de Convenio mas tenho N cadastrados. Para facilidar estou enviando minhas classes para maior compreensao. Obrigado ao amigos(as).
cadastroPaciente.jsp
"…
PARTICULAR
CONVENIADO
<div align="center">
<select name="convenio" size="1" >
<%
ConvenioDAO convDAO = new ConvenioDAO();
List<Convenio> listaConvenio = convDAO.listarConvenios();
for(Convenio conv:listaConvenio ){
%>
<option value="<%=conv.getId() %>"><%=conv.getRazao_social() %> </option>
</select></div>
<%
}
%>
</td>
<td> </td>
<td width="34%"><div align="center">
<input name="num_conv" id="num_conv" type="text" align="absmiddle" size="40" maxlength="15" />
</div></td>
</tr>
</table>......"
PacienteDAO
public class PacienteDAO extends ConexaoDAO {
public void cadastrar(Paciente p) throws Exception {
OpenDatabase();
String SQL = "INSERT INTO pacientes (nome,dt_nasc,dt_cadastro,sexo,naturalidade," +
" rg,cpf,estado_civil,tel_res,tel_com,tel_cel,email,logradouro," +
" num_res,compl_num,bairro,cep,cidade,estado,tp_atendimento,convenio,num_conv,obs)" +
" VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?,?);";
pstmt = con.prepareStatement(SQL);
pstmt.setString(1,p.getNome());
pstmt.setString(2,p.getDt_cadastro());
pstmt.setString(3,p.getDt_nasc());
pstmt.setString(4,p.getSexo());
pstmt.setString(5,p.getNaturalidade());
pstmt.setString(6,p.getRg());
pstmt.setString(7,p.getCpf());
pstmt.setString(8,p.getEstado_civil());
pstmt.setString(9,p.getTel_res());
pstmt.setString(10,p.getTel_com());
pstmt.setString(11,p.getTel_cel());
pstmt.setString(12,p.getEmail());
pstmt.setString(13,p.getLogradouro());
pstmt.setString(14,p.getNum_res());
pstmt.setString(15,p.getCompl_num());
pstmt.setString(16,p.getBairro());
pstmt.setString(17,p.getCep());
pstmt.setString(18,p.getCidade());
pstmt.setString(19,p.getEstado());
pstmt.setString(20,p.getTp_atendimento());
pstmt.setInt(21,p.getConvenio().getId());
pstmt.setString(22,p.getNum_conv());
pstmt.setString(23,p.getObs());
pstmt.execute();
CloseDatabase();
}
public void deletarPorRg(String rg)throws Exception{
try{
OpenDatabase();
String sqlDeletar=("DELETE FROM pacientes WHERE rg =?");
pstmt = con.prepareStatement(sqlDeletar);
pstmt.setString(1, rg);
pstmt.execute();
CloseDatabase();
}catch(Exception e){
e.printStackTrace();
}
}
public void atualizar(Paciente p)throws SQLException {
try{
OpenDatabase();
Statement stmt;
String sql;
sql = "update pacientes set nome='"+p.getNome() +"',dt_nasc='"+p.getDt_nasc()+"',dt_cadastro='"+p.getDt_cadastro()+"',"
+ "sexo='"+p.getSexo()+"',naturalidade='"+p.getNaturalidade()+"',rg='"+p.getRg()+"',"
+ "cpf='"+p.getCpf()+"',estado_civil='"+p.getEstado_civil()+"',tel_res='"+p.getTel_res()+"',tel_com='"+p.getTel_com()+"',"
+ "tel_cel='"+p.getTel_cel()+"',email='"+p.getEmail() +"',cep='"+p.getCep()+"', logradouro='"+p.getLogradouro()+"',num_res='"+p.getNum_res()+"',"
+ "compl_num='"+p.getCompl_num()+"',bairro='"+p.getBairro()+"',cidade='"+p.getCidade()+"',estado='"+p.getEstado()+"',"
+"tp_atendimento='"+p.getTp_atendimento()+"',convenio='"+p.getConvenio().getId()+"',num_conv='"+p.getNum_conv()+"',obs='"+p.getObs()+"'"
+" where rg='"+p.getRg()+"'";
stmt = con.createStatement();
int i = stmt.executeUpdate(sql);
CloseDatabase();
}catch (Exception e){
e.printStackTrace();
}
}
public Paciente buscaPorRg(String rg)throws Exception{
Paciente p = null;
try{
OpenDatabase();
String SQL = "SELECT * FROM pacientes WHERE rg=?" ;
pstmt = con.prepareStatement(SQL);
pstmt.setString(1, rg);
rs = pstmt.executeQuery();
if(rs.next()){
p = new Paciente();
p.setNome(rs.getString("nome"));
p.setDt_nasc(rs.getString("dt_nasc"));
p.setDt_cadastro(rs.getString("dt_cadastro"));
p.setSexo(rs.getString("sexo"));
p.setNaturalidade(rs.getString("naturalidade"));
p.setRg(rs.getString("rg"));
p.setCpf(rs.getString("cpf"));
p.setEstado_civil(rs.getString("estado_civil"));
p.setTel_res(rs.getString("tel_res"));
p.setTel_com(rs.getString("tel_com"));
p.setTel_cel(rs.getString("tel_cel"));
p.setEmail(rs.getString("email"));
p.setLogradouro(rs.getString("logradouro"));
p.setNum_res(rs.getString("num_res"));
p.setCompl_num(rs.getString("compl_num"));
p.setBairro(rs.getString("bairro"));
p.setCep(rs.getString("cep"));
p.setCidade(rs.getString("cidade"));
p.setEstado(rs.getString("estado"));
p.setTp_atendimento(rs.getString("tp_atendimento"));
p.setNum_conv(rs.getString("num_conv"));
p.setObs(rs.getString("obs"));
Convenio c = new Convenio();
c.setId(rs.getInt("convenio"));
rs.close();
CloseDatabase();
}
}catch(Exception e){
e.printStackTrace();
}
return p;
}
public List<Paciente> listarPacientes(){
List<Paciente> lista = new ArrayList<Paciente>();
try{
OpenDatabase();
String sqlLista = ("SELECT * FROM pacientes ORDER BY nome");
pstmt = con.prepareStatement(sqlLista);
rs = pstmt.executeQuery();
while(rs.next()){
Paciente p = new Paciente();
p.setDt_cadastro(rs.getString("dt_cadastro"));
p.setNome(rs.getString("nome"));
p.setRg(rs.getString("rg"));
p.setTel_res(rs.getString("tel_res"));
p.setTp_atendimento(rs.getString("tp_atendimento"));
lista.add(p);
}
rs.close();
CloseDatabase();
}
catch (Exception ex){
ex.printStackTrace();
}
{
}
return lista;
}
}
PacienteServlet
public class PacienteServlet extends HttpServlet {
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException{
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
try {
String action=request.getParameter("action");
if(action.equals("cadastrar")){
Paciente p = new Paciente();
p.setNome(request.getParameter("nome"));
p.setDt_cadastro(request.getParameter("dt_cadastro"));
p.setDt_nasc(request.getParameter("dt_nasc"));
p.setSexo(request.getParameter("sexo"));
p.setNaturalidade(request.getParameter("naturalidade"));
p.setRg(request.getParameter("rg"));
p.setCpf(request.getParameter("cpf"));
p.setEstado_civil(request.getParameter("estado_civil"));
p.setTel_res(request.getParameter("tel_res"));
p.setTel_com(request.getParameter("tel_com"));
p.setTel_cel(request.getParameter("tel_cel"));
p.setEmail(request.getParameter("email"));
p.setCep(request.getParameter("cep"));
p.setLogradouro(request.getParameter("logradouro"));
p.setNum_res(request.getParameter("num_res"));
p.setCompl_num(request.getParameter("compl_num"));
p.setBairro(request.getParameter("bairro"));
p.setCidade(request.getParameter("cidade"));
p.setEstado(request.getParameter("estado"));
p.setTp_atendimento(request.getParameter("tp_atendimento"));
p.setNum_conv(request.getParameter("num_conv"));
p.setObs(request.getParameter("obs"));
Convenio conv = new Convenio();
conv.setId(Integer.parseInt(request.getParameter("convenio")));
PacienteDAO pacDAO = new PacienteDAO();
try{
pacDAO.cadastrar(p);
response.sendRedirect("cadastroSucesso.jsp");
}
catch(Exception e){
out.print("Erro: "+ e.getMessage());
}
}
if(action.equals("deletar")){
String rg = (request.getParameter("rg"));
PacienteDAO pDAO = new PacienteDAO();
try {
pDAO.deletarPorRg(rg);
} catch (Exception ex) {
ex.printStackTrace();
}
List<Paciente> lista = pDAO.listarPacientes();
getServletContext().setAttribute("listaTodos", lista);
RequestDispatcher dispatcher = getServletContext().getRequestDispatcher("/listagemPaciente.jsp");
dispatcher.forward(request,response);
}
if(action.equals("listar")){
PacienteDAO pacDAO = new PacienteDAO();
List<Paciente> lista= pacDAO.listarPacientes();
getServletContext().setAttribute("listaTodos",lista);
RequestDispatcher rd = getServletContext().getRequestDispatcher("/listagemPaciente.jsp") ;
rd.forward(request, response);
}
if (action.equals("alterar")){
String rg = request.getParameter("rg");
try{
PacienteDAO pacDAO = new PacienteDAO();
Paciente p = pacDAO.buscaPorRg(rg);
getServletContext().setAttribute("paciente", p);
RequestDispatcher dispatcher = getServletContext().getRequestDispatcher("/atualizarPaciente.jsp");
dispatcher.forward(request,response);
}catch(Exception e){
e.printStackTrace();
}
}
if(action.equals("salvar")){
try{
Paciente p = new Paciente();
p.setNome(request.getParameter("nome"));
p.setDt_nasc(request.getParameter("dt_nasc"));
p.setDt_cadastro(request.getParameter("dt_cadastro"));
p.setSexo(request.getParameter("sexo"));
p.setNaturalidade(request.getParameter("naturalidade"));
p.setRg(request.getParameter("rg"));
p.setCpf(request.getParameter("cpf"));
p.setEstado_civil(request.getParameter("estado_civil"));
p.setTel_res(request.getParameter("tel_res"));
p.setTel_com(request.getParameter("tel_com"));
p.setTel_cel(request.getParameter("tel_cel"));
p.setEmail(request.getParameter("email"));
p.setCep(request.getParameter("cep"));
p.setLogradouro(request.getParameter("logradouro"));
p.setNum_res(request.getParameter("num_res"));
p.setCompl_num(request.getParameter("compl_num"));
p.setBairro(request.getParameter("bairro"));
p.setCidade(request.getParameter("cidade"));
p.setEstado(request.getParameter("estado"));
p.setTp_atendimento(request.getParameter("tp_atendimento"));
p.setNum_conv(request.getParameter("num_conv"));
p.setObs(request.getParameter("obs"));
Convenio conv = new Convenio();
conv.setId(Integer.parseInt(request.getParameter("convenio")));
PacienteDAO pDAO = new PacienteDAO();
try{
pDAO.atualizar(p);
}
catch(Exception e){
out.print("Erro: "+ e.getMessage());
}
// depois lista aqui
List<Paciente> lista = pDAO.listarPacientes();
getServletContext().setAttribute("listaTodos", lista);
RequestDispatcher dispatcher = getServletContext().getRequestDispatcher("/listagemPaciente.jsp");
dispatcher.forward(request,response);
}catch(Exception e){
e.printStackTrace();
}
}
}
finally {
out.close();
}
}
// <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
/**
* Handles the HTTP <code>GET</code> method.
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}
/**
* Handles the HTTP <code>POST</code> method.
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}
/**
* Returns a short description of the servlet.
* @return a String containing servlet description
*/
@Override
public String getServletInfo() {
return "Short description";
}// </editor-fold>
}
ConvenioDAO
public class ConvenioDAO extends ConexaoDAO {
public void cadastrar(Convenio conv) throws Exception {
try{
OpenDatabase();
String SQL = "INSERT INTO convenios (rz_social,cnpj,insc_estadual,logradouro,"
+"numero,cep,bairro,cidade,estado,tel_com,fax,email,vigencia,dt_cadastro,"
+"website) VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
pstmt = con.prepareStatement(SQL);
pstmt.setString(1,conv.getRazao_social());
pstmt.setString(2,conv.getCnpj());
pstmt.setString(3,conv.getInsc_estadual());
pstmt.setString(4,conv.getLogradouro());
pstmt.setString(5,conv.getNumero());
pstmt.setString(6,conv.getCep());
pstmt.setString(7,conv.getBairro());
pstmt.setString(8,conv.getCidade());
pstmt.setString(9,conv.getEstado());
pstmt.setString(10,conv.getTel_com());
pstmt.setString(11,conv.getFax());
pstmt.setString(12,conv.getEmail());
pstmt.setString(13,conv.getVigencia());
pstmt.setString(14,conv.getDt_cadastro());
pstmt.setString(15,conv.getWebsite());
pstmt.execute();
CloseDatabase();
}catch(Exception e){
e.printStackTrace();
}
}
public void deletarPorCnpj(String cnpj)throws Exception{
try{
OpenDatabase();
String sqlDeletar=("DELETE FROM convenios WHERE cnpj =?");
pstmt = con.prepareStatement(sqlDeletar);
pstmt.setString(1, cnpj);
pstmt.execute();
CloseDatabase();
}catch(Exception e){
e.printStackTrace();
}
}
public void atualizar(Convenio conv)throws SQLException {
try{
OpenDatabase();
Statement stmt;
String sql;
sql = "UPDATE convenios SET rz_social='"+conv.getRazao_social() +"',cnpj='"+conv.getCnpj()+"',insc_estadual='"+conv.getInsc_estadual()+"',"
+"logradouro='"+conv.getLogradouro()+"',numero='"+conv.getNumero()+"',cep='"+conv.getCep()+"',bairro='"+conv.getBairro()+"',"
+"cidade='"+conv.getCidade()+"',estado='"+conv.getEstado()+"',tel_com='"+conv.getTel_com()+"',fax='"+conv.getFax()+"',"
+"email='"+conv.getEmail() +"',vigencia='"+conv.getVigencia()+"',dt_cadastro='"+conv.getDt_cadastro()+"',website='"+conv.getWebsite()+"'"
+" WHERE id_conv='"+conv.getId()+"'";
stmt = con.createStatement();
int i = stmt.executeUpdate(sql);
CloseDatabase();
}catch (Exception e){
e.printStackTrace();
}
}
public Convenio buscaPorId(Integer id)throws Exception{
Convenio conv = null;
try{
OpenDatabase();
String SQL = "SELECT * FROM convenios WHERE id_conv=?" ;
pstmt = con.prepareStatement(SQL);
pstmt.setInt(1, id);
rs = pstmt.executeQuery();
if(rs.next()){
conv = new Convenio();
conv.setId(rs.getInt("id_conv"));
conv.setRazao_social(rs.getString("rz_social"));
conv.setCnpj(rs.getString("cnpj"));
conv.setInsc_estadual(rs.getString("insc_estadual"));
conv.setLogradouro(rs.getString("logradouro"));
conv.setNumero(rs.getString("numero"));
conv.setCep(rs.getString("cep"));
conv.setBairro(rs.getString("bairro"));
conv.setCidade(rs.getString("cidade"));
conv.setEstado(rs.getString("estado"));
conv.setTel_com(rs.getString("tel_com"));
conv.setFax(rs.getString("fax"));
conv.setEmail(rs.getString("email"));
conv.setVigencia(rs.getString("vigencia"));
conv.setDt_cadastro(rs.getString("dt_cadastro"));
conv.setWebsite(rs.getString("website"));
rs.close();
CloseDatabase();
}
}catch(Exception e){
e.printStackTrace();
}
return conv;
}
public List<Convenio> listarConvenios(){
List<Convenio> lista = new ArrayList<Convenio>();
try{
OpenDatabase();
String sqlLista = ("SELECT * FROM convenios ORDER BY rz_social");
pstmt = con.prepareStatement(sqlLista);
rs = pstmt.executeQuery();
while(rs.next()){
Convenio conv = new Convenio();
conv.setId(rs.getInt("id_conv"));
conv.setDt_cadastro(rs.getString("dt_cadastro"));
conv.setRazao_social(rs.getString("rz_social"));
conv.setCnpj(rs.getString("cnpj"));
conv.setTel_com(rs.getString("tel_com"));
conv.setFax(rs.getString("fax"));
conv.setVigencia(rs.getString("vigencia"));
lista.add(conv);
}
rs.close();
CloseDatabase();
}
catch (Exception ex){
ex.printStackTrace();
}
{
}
return lista;
}
}
ConvenioServlet
public class ConvenioServlet extends HttpServlet {
/**
* Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
try {
String action=request.getParameter("action");
if(action.equals("cadastrar")){
Convenio conv = new Convenio();
conv.setRazao_social(request.getParameter("rz_social"));
conv.setCnpj(request.getParameter("cnpj"));
conv.setInsc_estadual(request.getParameter("insc_estadual"));
conv.setLogradouro(request.getParameter("logradouro"));
conv.setNumero(request.getParameter("numero"));
conv.setCep(request.getParameter("cep"));
conv.setBairro(request.getParameter("bairro"));
conv.setCidade(request.getParameter("cidade"));
conv.setEstado(request.getParameter("estado"));
conv.setTel_com(request.getParameter("tel_com"));
conv.setFax(request.getParameter("fax"));
conv.setEmail(request.getParameter("email"));
conv.setVigencia(request.getParameter("vigencia"));
conv.setDt_cadastro(request.getParameter("dt_cadastro"));
conv.setWebsite(request.getParameter("website"));
ConvenioDAO convDAO = new ConvenioDAO();
try{
convDAO.cadastrar(conv);
response.sendRedirect("cadastroSucesso.jsp");
}
catch(Exception e){
out.print("Erro: "+ e.getMessage());
}
}
if(action.equals("deletar")){
String cnpj = (request.getParameter("cnpj"));
ConvenioDAO convDAO = new ConvenioDAO();
try {
convDAO.deletarPorCnpj(cnpj);
} catch (Exception ex) {
ex.printStackTrace();
}
List<Convenio> lista = convDAO.listarConvenios();
getServletContext().setAttribute("listaTodos", lista);
RequestDispatcher dispatcher = getServletContext().getRequestDispatcher("/listagemConvenio.jsp");
dispatcher.forward(request,response);
}
if(action.equals("listar")){
ConvenioDAO convDAO = new ConvenioDAO();
List<Convenio> lista= convDAO.listarConvenios();
getServletContext().setAttribute("listaTodos",lista);
RequestDispatcher rd = getServletContext().getRequestDispatcher("/listagemConvenio.jsp") ;
rd.forward(request, response);
}
if (action.equals("alterar")){
Integer id =Integer.parseInt(request.getParameter("id_conv"));
try{
ConvenioDAO convDAO = new ConvenioDAO();
Convenio conv = convDAO.buscaPorId(id);
getServletContext().setAttribute("convenio", conv);
RequestDispatcher dispatcher = getServletContext().getRequestDispatcher("/atualizarConvenio.jsp");
dispatcher.forward(request,response);
}catch(Exception e){
e.printStackTrace();
}
}
if(action.equals("salvar")){
try{
Convenio conv = new Convenio();
conv.setRazao_social(request.getParameter("rz_social"));
conv.setCnpj(request.getParameter("cnpj"));
conv.setInsc_estadual(request.getParameter("insc_estadual"));
conv.setLogradouro(request.getParameter("logradouro"));
conv.setNumero(request.getParameter("numero"));
conv.setCep(request.getParameter("cep"));
conv.setBairro(request.getParameter("bairro"));
conv.setCidade(request.getParameter("cidade"));
conv.setEstado(request.getParameter("estado"));
conv.setTel_com(request.getParameter("tel_com"));
conv.setFax(request.getParameter("fax"));
conv.setEmail(request.getParameter("email"));
conv.setVigencia(request.getParameter("vigencia"));
conv.setDt_cadastro(request.getParameter("dt_cadastro"));
conv.setWebsite(request.getParameter("website"));
ConvenioDAO convDAO = new ConvenioDAO();
try{
convDAO.atualizar(conv);
}
catch(Exception e){
out.print("Erro: "+ e.getMessage());
}
// depois lista aqui
List<Convenio> lista = convDAO.listarConvenios();
getServletContext().setAttribute("listaTodos", lista);
RequestDispatcher dispatcher = getServletContext().getRequestDispatcher("/listagemConvenio.jsp");
dispatcher.forward(request,response);
}catch(Exception e){
e.printStackTrace();
}
}
} finally {
out.close();
}
}
// <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
/**
* Handles the HTTP <code>GET</code> method.
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}
/**
* Handles the HTTP <code>POST</code> method.
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}
/**
* Returns a short description of the servlet.
* @return a String containing servlet description
*/
@Override
public String getServletInfo() {
return "Short description";
}// </editor-fold>
}