Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL Exception on refeshing the servlet.

 
Kavita Ghia
Ranch Hand
Posts: 91
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello friends,
I have a servlet written which is making connection to my database(Ms Access)if it helps and then performing search query.
The connection is done in the init() method of the servlet and the query is executed in the doGet() method.If the search query contains the data then it displays the result else the message record not found.Now my problem is this-
the whole procedure is happening only once when my servlet is called for the first time.i.e. when my init method is invoked.Subsequently whenever I try to refresh the servlet for other search result I get an SQLException stating the "General Error".Basically it is not creating or executing the following:
Statement st=con.createStatement();.
Can anyone explain me the reason for such a weird behavior.
Thanking in advance,
Regards,
Kavita.
 
Kavita Ghia
Ranch Hand
Posts: 91
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
More information-I am closing the connection,statement and result set.My program is below:
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;
public class SearchServlet extends HttpServlet
{
//shared by all the requests
Statement st;
Connection dbConn;
String word;

public void init(ServletConfig config) throws
ServletException{
super.init(config);
try
{
//load jdbc-odbc bridge
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//establish database connection to netsparsh
dbConn=
DriverManager.getConnection"jdbc dbc:netsparsh");
System.out.println("connection established");
}
//thrown by Class.forName
catch(ClassNotFoundException e)
{
System.out.println("JDBC-ODBC bridge not found");
return;
}
catch(SQLException e)
{
System.out.println("SQL Exception thrown!");
}
}
public void doPost(HttpServletRequest req,HttpServletResponse res) throws
ServletException,IOException
{
try
{
System.out.println("in doPost method");
res.setContentType("text/html");

//get handle to output stream
PrintWriter out=res.getWriter();

out.println("<HTML>");
out.println("<BODY>");
word=req.getParameter("address");

//create statement
st=dbConn.createStatement();
//query database for result set
String query="select membership_no,name,url from member where lcase(name)='"+(word)+"' ";
ResultSet rs=st.executeQuery(query);

if(rs.next()==true) {
String temp="http://";
String temp1=rs.getString("url");
res.sendRedirect(temp+temp1);
}
else
out.println("NO URL Found");
out.println("</BODY>");
out.println("</HTML>");
rs.close();
st.close();
out.close();
dbConn.close();

}
catch(SQLException e)
{
e.printStackTrace();
}
}
public String getServletInfo()
{
return "SearchServlet........and enjoy!";
}
}
 
pk mast
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
put the connection part in the dopost method and it will work.well logically it doesnt make any sense but thats the way it goes.all the best kavita.
 
Peter den Haan
author
Ranch Hand
Posts: 3252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Kavita Ghia:
Hello friends,
Now my problem is this-
the whole procedure is happening only once when my servlet is called for the first time.i.e. when my init method is invoked.Subsequently whenever I try to refresh the servlet for other search result I get an SQLException stating the "General Error". [...] Can anyone explain me the reason for such a weird behavior.

Not weird, but perfectly normal...
You're opening your database just once, in the servlet intialisation code. Yet you are closing both your statement and your database connection in the request handler. After the first request, you will have closed your connection no more database interaction will be possible until you reconnect.
What you probably intend to do is not close your statement and connection in doPost(), but in destroy(). That way they stay open until the container cleans up your servlet.
But in applications that reach any kind of volume, you don't want to statically allocate a database connection for each servlet. Nor do you want to open and close one for each request. Instead, use a database connection pool. One easy way of achieving this is to move your database access layer to an EJB container.
Regards
- Peter
 
Peter den Haan
author
Ranch Hand
Posts: 3252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Peter den Haan:
What you probably intend to do is not close your statement and connection in doPost(), but in destroy(). That way they stay open until the container cleans up your servlet.

I should add that, if you do this, your servlet needs to implement the SingleThreadModel interface as (afaik) connection objects are not threadsafe.
- Peter
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic