Acessando banco de dados com Servlets

Front-end
#1

oi galera…
tô pra ter um infarte…
a vários dias que tento fazer uma consulta a um banco, mando os parâmentros de um JSP para um servlet que, teoricamente, deveria conectar com o banco e fazer a consulta, maaaaaaaaaaaaas isso não ocorre. desconfio seriamente que seja um problema de configuração da aplicação até por que estou migrando essa aplicação de uma máquina para outra. agradeço deste já pela atenção. vejam os códigos e o erro:

envia.jsp

<%@page errorPage="error.jsp" %>

<html>
<body bgcolor="#cccccc"> <!-- #c8d8f8 cor original -->

<form method=POST action="http://localhost:8080/invente/servlet/RootSearchEnredo2" >
<center>

<table cellpadding=4 cellspacing=2 border=0>

<th bgcolor="gray" colspan=4> <!-- #CCCCFF -->
<font color="#ffffff"><h2>ENREDO - Busca</h2></font>
</th>
<tr>
<!--
<td valign=center><b>Title</b></td>
<td><input type="text" name="title" size=20></td>
-->
<td valign=center><b>Instituição</b></td>
<td><select name="institution" size="1" >
<option value="%">any institution        </option>
<option value="CEFET-CE">CEFET-CE</option>
<option value="INT-EVRY">INT-EVRY</option>
<option value="PARIS VI">PARIS VI</option>
<option value="PUC-Rio">PUC-Rio</option>
<option value="UECE">UECE</option>
<option value="UNED-CEDRO">UNED-CEDRO</option>
<option value="UNED-JUAZEIRO">UNED-JUAZEIRO</option>
<option value="UNIV-EVRY">UNIV-EVRY</option>
</select>
</td>


<td valign=center><b>Language</b></td>
<td><select name="language" size="1" >
<option value="%">any language        </option>
<option value="English">English</option>
<option value="French">French</option>
<option value="German">German</option>
<option value="Italian">Italian</option>
<option value="Portuguese">Portuguese</option>
<option value="Spanish">Spanish</option>
</select>
</td>
</tr>
<tr>

<td valign=center><b>Title</b></td>
<td><input type="text" name="title" size=20></td>

<td valign=center><b>Delivery Mode</b></td>
<td><select name="deliverymode" size="1" >
<option value="%">any mode</option>
<option value="Assynchronous">Assynchronous</option>
<option value="Synchronous">Synchronous</option>
</select>
</td>

<!-- <td align=bottom>
<!-- <input type="submit" value="Search">
<input type="image"
src="/searchbutton.gif" value="search" name="search"
width=57 height=21 alt="Search the Site" border="0">
</td> -->
</tr>

<tr>
<td valign=center><b>Author</b></td>
<td><input type="text" name="responsible" size=20></td>

<td valign=center><b>Media Type</b></td>
<td><select name="mediatype" size="1" >
<option value="%">any media        </option>
<option value="text">Text</option>
<option value="sound">Sound</option>
<option value="Voice">Voice</option>
<option value="Image">Image</option>
<option value="Animation">Animation</option>
</select>
</td>
</tr>

<tr>
<td valign=center><b>Keywords</b></td>
<td><input type="text" name="keywords" size=20></td>

<td valign=center><b>Agregation Level</b></td>
<td><select name="aggregation" size="1" >
<option value="%">any level</option>
<option value="Course">Course</option>
<option value="Discipline">Discipline/Bloc</option>
<option value="Unit">Unit</option>
<option value="Lesson">Lesson</option>
<option value="Thema">Thema/Article</option>
<option value="Concept">Definitions/Concept</option>
</select>
</td>
</tr>

<tr>
<td valign="center"><b>Learning Object Type</b></td>
<td><select name="learningobjecttype" size="1" >
<option value="%" selected>any type</option>
<option value="Narrative Text">Narrative Text</option>
<option value="Expositive Presentation">Expositive Presentation</option>
<option value="Simulation">Simulation</option>
<option value="Exercise">Exercise</option>
<option value="Problem">Problem</option>
<option value="Self Assessment">Self Assessment</option>
<option value="Exam">Exam</option>
<option value="Ilustration">Ilustration</option>
<option value="Diagram">Diagram</option>
<option value="Table">Table</option>
<option value="Graph">Graph</option>
<option value="Example">Example</option>
<option value="Demo">Demo</option> </select>
</td>
</tr>

<tr>
<td colspan=4 align="center">
<input type="image" src="/searchbutton.gif" value="search" name="search"
width=57 height=21 alt="Search the Site" border="0">

</tr>
</table>

</center>

<!--
<table>
<tr>
<div align=center>
<td valign=center>All Conected by a logical
<input type="radio" name="connector" value="&&" checked><b>AND</b>
<input type="radio" name="connector" value="||"><b>OR</b>
</div>
</td>
</tr>
</table>
-->

</form>
</body>
</html>

RootSearchEnredo2.java

import java.io.*;
import java.util.*;

import java.sql.*; // JDBC
// import java.security.cert.*;

import javax.servlet.*; // JSDK
import javax.servlet.http.*;
import br.cefetce.lar.invente.EnredoBean;

public class RootSearchEnredo2 extends HttpServlet
{
Statement stmt; // objeto para execucao do comando SQL
PreparedStatement prepStmt;
ResultSet rs; // objeto para obtencao do resultado do comando SQL
ResultSet rsId;
Connection con;
// FormResultBean beanArray;
Vector vetor = new Vector();
static final String consultaBasica = "SELECT id, generaltitle, generalaggregationlevel, generalinstitution, generalresponsible, " +
"lifecyclecontributedate, technicalmediatype, educationallearningobjecttype from lominvente WHERE generallanguage LIKE ? "
+ "&& generalaggregationlevel LIKE ? && generalinstitution LIKE ? "
+ "&& technicalmediatype LIKE ? && technicaldeliverymode LIKE ? "
+ "&& educationallearningobjecttype LIKE ?";

String consulta;

public void init(ServletConfig config) throws ServletException
{
super.init(config);

try
{
try
{
Class.forName("org.gjt.mm.mysql.Driver").newInstance();
}
catch (Exception exception)
{

}
con = DriverManager.getConnection( "jdbc:mysql://localhost:3306/invente","root","rafael57");
//stmt = con.createStatement();
}
catch (SQLException exception)
{
System.out.println ("ERRO2 Servlet/JDBC> SQL Exception " + exception.getMessage());
}
} //init ===========================================================

public void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException
{

log(req.getParameter("showId"));
}
public void doPost(HttpServletRequest req, HttpServletResponse res)throws ServletException, IOException
{
if (!(req.getParameter("showId")!=null) )
{
vetor.clear();

pesquisar(treat(req), req);


req.setAttribute("vetor",vetor);

RequestDispatcher rd;
rd = getServletContext().getRequestDispatcher("/jsp/enredo/ResultsSearch2.jsp");

rd.forward(req,res);
log("dispachpu");
}
else
{
req.setAttribute("bean",getById(req.getParameter("showId")));
RequestDispatcher rd;
rd = getServletContext().getRequestDispatcher("/jsp/enredo/ResultDetail.jsp");
rd.forward(req,res);
}

}//fim do doPost

private EnredoBean getById(String id)
{
String consulta = "SELECT * FROM lominvente WHERE id=" + id;
try
{
stmt = con.createStatement();
rsId = stmt.executeQuery(consulta);
rsId.next();
}
catch(SQLException e)
{
log("excecao no getbyid: " + e.getMessage());
}
return (makeBeanById(rsId));
}


private FormResultBean makeBean(ResultSet rs)
{
FormResultBean bean = new FormResultBean();

try
{
bean.setId(rs.getInt("id"));
bean.setTitle(rs.getString("generaltitle"));
bean.setAuthor(rs.getString("generalresponsible"));
bean.setInstitution(rs.getString("generalinstitution"));
bean.setProvisionDate(rs.getDate("lifecyclecontributedate"));
bean.setAggregationLevel(rs.getString("generalaggregationlevel"));
bean.setMediaType(rs.getString("technicalmediatype"));
bean.setLearningObjectType(rs.getString("educationallearningobjecttype"));
}
catch(SQLException ex)
{
log("no makebean:" + ex.getMessage());
}
return bean;
}

private EnredoBean makeBeanById(ResultSet rsId)
{
EnredoBean bean = new EnredoBean();

try
{
bean.setgeneraltitle(rsId.getString("generaltitle"));
bean.setgenerallanguage(rsId.getString("generallanguage"));
bean.setgeneraldescription(rsId.getString("generaldescription"));
bean.setgeneralkeywords(rsId.getString("generalkeywords"));
bean.setgeneralaggregationlevel(rsId.getString("generalaggregationlevel"));
bean.setgeneralinstitution(rsId.getString("generalinstitution"));
bean.setgeneralresponsible(rsId.getString("generalresponsible"));
bean.setgenerallocation(rsId.getString("generallocation"));
bean.setlifecyclestatus(rsId.getString("lifecyclestatus"));
bean.setlifecycleversion(rsId.getString("lifecycleversion"));
bean.setlifecyclecontributedate(rsId.getDate("lifecyclecontributedate").toString());
bean.setmetametadatacontributeentity(rsId.getString("metametadatacontributeentity"));
bean.setmetametadatacontributerole(rsId.getString("metametadatacontributerole"));
bean.setmetametadatametadatascheme(rsId.getString("metametadatametadatascheme"));
bean.setmetametadatalanguage(rsId.getString("metametadatalanguage"));
bean.setmetametadataauthoningtool(rsId.getString("metametadataauthoningtool"));
bean.setmetametadatacontributeemail(rsId.getString("metametadatacontributeemail"));
bean.settechnicaldeliverymode(rsId.getString("technicaldeliverymode"));
bean.settechnicalmediatype(rsId.getString("technicalmediatype"));
bean.settechnicalformat(rsId.getString("technicalformat"));
bean.settechnicalsize(rsId.getString("technicalsize"));
bean.settechnicalrequirementsoperatingsystem(rsId.getString("technicalrequirementsoperatingsystem"));
bean.settechnicalrequirementsbrowser(rsId.getString("technicalrequirementsbrowser"));
bean.settechnicalrequirementsapplication(rsId.getString("technicalrequirementsapplication"));
bean.settechnicalrequirementsnetwork(rsId.getString("technicalrequirementsnetwork"));
bean.settechnicalrequirementslink(rsId.getString("technicalrequirementslink"));
bean.settechnicalrequirementsduration(rsId.getString("technicalrequirementsduration"));
bean.seteducationallearningobjecttype(rsId.getString("educationallearningobjecttype"));
bean.seteducationaltypicallearningtime(rsId.getString("educationaltypicallearningtime"));
bean.setrelationkind(rsId.getString("relationkind"));
bean.setrelationobjectreference(rsId.getString("relationobjectreference"));
bean.setrelationobjectname(rsId.getString("relationobjectname"));
bean.setrelationdescription(rsId.getString("relationdescription"));
bean.setrightscopyright(rsId.getString("rightscopyright"));
bean.setrightscomments(rsId.getString("rightscomments"));
}
catch(SQLException ex)
{
log("no makebean:" + ex.getMessage());
}
return bean;
}
private ArrayList treat(HttpServletRequest req)
{
ArrayList elementos = new ArrayList();
String connector = req.getParameter("connector");
consulta = consultaBasica;
elementos.add(manipulateTitle(req));
elementos.add(manipulateKeyWords(req));
// elementos.add(manipulateEducationalObjective(req));
elementos.add(manipulateAuthor(req));
log("kjfkjjfhvj");
log(consulta);
return (elementos);
}

private String[] manipulateTitle(HttpServletRequest req)
{
StringBuffer queryString = new StringBuffer(req.getParameter("title"));
String consultaBasicaAdd = "&& generaltitle LIKE ? ";
String strRetorno[] = null;

if (!(req.getParameter("title").equals("")))
{

if (((queryString.charAt(0)=='\"') && (queryString.charAt(queryString.length()-1)=='\"'))
|| ((queryString.charAt(0)=='\'') && (queryString.charAt(queryString.length()-1)=='\'')))
{
strRetorno = treatPhrase(queryString, consultaBasicaAdd);
} //fim do if ((queryString.charAt(0)=='\"') && (queryString.charAt(queryString.length()-1))=='\"')
else
{
strRetorno = treatWords(queryString, consultaBasicaAdd);

} //fim do else if ((queryString.charAt(0)=='\"') && (queryString.charAt(queryString.length()-1))=='\"')

return (strRetorno);
}
else
{
consulta += consultaBasicaAdd;
return (new String[] {"%"});
}
}//fim do manipulateTitle()

private String[] manipulateKeyWords(HttpServletRequest req)
{
String consultaBasicaAdd = "&& generalkeywords LIKE ? ";

if (!(req.getParameter("keywords").equals("")))
{
StringBuffer queryString = new StringBuffer(req.getParameter("keywords"));
String strRetorno[] = null;

strRetorno = treatWords(queryString, consultaBasicaAdd);

return (strRetorno);
}
else
{
consulta += consultaBasicaAdd;
return (new String[] {"%"});
}
}//fim do manipulateKeyWords()

private String[] manipulateAuthor(HttpServletRequest req)
{
String consultaBasicaAdd = "&& generalresponsible LIKE ? ";

if (!(req.getParameter("responsible").equals("")))
{
StringBuffer queryString = new StringBuffer(req.getParameter("responsible"));
String strRetorno[] = null;

strRetorno = treatWords(queryString, consultaBasicaAdd);

return (strRetorno);
}
else
{
consulta += consultaBasicaAdd;
return (new String[] {"%"});
}
}//fim do manipulateKeyWords()

/*
private String[] manipulateEducationalObjective(HttpServletRequest req)
{
StringBuffer queryString = new StringBuffer(req.getParameter("edobjective"));
String consultaBasicaAdd = "&& educationaldescription LIKE ? ";
String strRetorno[] = null;

if (!(req.getParameter("edobjective").equals("")))
{
if (((queryString.charAt(0)=='\"') && (queryString.charAt(queryString.length()-1)=='\"'))
|| ((queryString.charAt(0)=='\'') && (queryString.charAt(queryString.length()-1)=='\'')))
{
strRetorno = treatPhrase(queryString, consultaBasicaAdd);
} //fim do if ((queryString.charAt(0)=='\"') && (queryString.charAt(queryString.length()-1))=='\"')
else
{
strRetorno = treatWords(queryString, consultaBasicaAdd);

} //fim do else if ((queryString.charAt(0)=='\"') && (queryString.charAt(queryString.length()-1))=='\"')

return (strRetorno);
}
else
{
consulta += consultaBasicaAdd;
return (new String[] {"%"});
}
}//fim do manipulateEducationalObjective()
*/

private void pesquisar(ArrayList elem,HttpServletRequest req)
{
int param = 1;
try
{
consulta += ";";
prepStmt = con.prepareStatement(consulta);
prepStmt.setString(param++,req.getParameter("language"));
prepStmt.setString(param++,req.getParameter("aggregation"));
prepStmt.setString(param++,req.getParameter("institution"));
prepStmt.setString(param++,req.getParameter("mediatype"));
prepStmt.setString(param++,req.getParameter("deliverymode"));
prepStmt.setString(param++,req.getParameter("learningobjecttype"));
for(int i=0;i<elem.size();i++)
{
String temp[] = (String []) elem.get(i);

for(int k=0;k<temp.length;k++)
{

prepStmt.setString(param++,temp[k]);
log(temp[k]);
}
}
rs = prepStmt.executeQuery();

for(int i=0;rs.next();i++)
{
vetor.addElement(makeBean(rs));
}
}//fim do try
catch(SQLException e)
{
System.err.println(e.getMessage());
}//fim do catch
finally
{
try
{
prepStmt.clearParameters();
}
catch (SQLException e)
{
e.printStackTrace();
}
try
{
if ( rs != null )
{
rs.close();
}
}
catch (SQLException e)
{
e.printStackTrace();
}
}//fim do finally
}

private String[] treatPhrase(StringBuffer queryString, String consultaBasicaAdd)
{
queryString.setCharAt(0,'%');
queryString.setCharAt(queryString.length()-1,'%');
consulta += consultaBasicaAdd;
return (new String[] {queryString.toString()});


}//fim do método String[] treatPhrase(StringBuffer queryString, String consultaBasicaAdd)

private String[] treatWords(StringBuffer queryString, String consultaBasicaAdd)
{
String separador = "";
StringTokenizer queryStringTokenizer = new StringTokenizer("");
boolean umaPalavra = false;

if (queryString.toString().indexOf(' ')>=0)
{
separador = " ";
queryStringTokenizer = new StringTokenizer(queryString.toString(),separador);
}
else
if (queryString.toString().indexOf(',')>=0)
{
separador = ",";
queryStringTokenizer = new StringTokenizer(queryString.toString(),separador);
}
else
umaPalavra = true;

//numero de palavras digitadas pelo usuario
int numPalavras = 0;
if (umaPalavra)
numPalavras = 1;
else
numPalavras = queryStringTokenizer.countTokens();

String palavras[] = new String[numPalavras];

for(int i=0;i<numPalavras;i++)
{
if (umaPalavra)
{
queryString = queryString.insert(0,'%');
queryString = queryString.append('%');
palavras[i] = queryString.toString();
consulta += consultaBasicaAdd;
break;
}

StringBuffer item = new StringBuffer(queryStringTokenizer.nextToken());
item = item.insert(0,'%');
item = item.append('%');

palavras[i] = item.toString();
log(palavras[i]);
consulta += consultaBasicaAdd;
log(consulta);
}//fim do for(int i=0;i<numPalavras;i++)
return palavras;
}

}

erro.tomcat

HTTP Status 500 -

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

java.lang.NullPointerException
RootSearchEnredo2.pesquisar(RootSearchEnredo2.java:323)
RootSearchEnredo2.doPost(RootSearchEnredo2.java:63)
javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.catalina.servlets.InvokerServlet.serveRequest(InvokerServlet.java:419)
org.apache.catalina.servlets.InvokerServlet.doPost(InvokerServlet.java:169)
javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

note The full stack trace of the root cause is available in the Apache Tomcat/5.5.17 logs.
Apache Tomcat/5.5.17

[size=“11”][color=“red”]* Editado: Lembre-se de utilizar BBCode em seus códigos - Reifel[/color][/size] :joia:

#2

eh… vc quer contratar um debugger? resume isso aí na dúvida principal e no erro…

#3

Eita… dá uma resumida no teu problema, não consegui entender muito bem.

#4