Angående JDBC og connection pools
jeg har fået til opgave at rette nogle servlets som åbenbart ikke opfører sig som de bør. Der opstår exceptions forskellige steder men alle har de denne overskrift.<BEA-000627> <Reached maximum capacity of pool "ConnectionPool", making "0" new resource instances instead of "1".>
<BEA-101020> <[ServletContext(id=32869231,name=*******,context-path=)] Servlet failed with Exception
Dette får mig så til at tro at database forbindelserne ikke bliver lukket korrekt. Men det ser de nu ellers ud til at bliver i kildekoden. Så er der nogen der kan fortælle hvorfor at der løbes tør for forbindelser til DB?
public class setBestilling extends HttpServlet
{
public void init(ServletConfig config) throws ServletException
{
super.init(config);
}
public void service(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
Connection connection = null;
Statement statement = null;
Context ctx = null;
ResultSet res = null;
String sql="";
String next = "/onlinebestilling.jsp";
boolean zero = true;
request.setCharacterEncoding("ISO-8859-1");
request.setAttribute("sendt","false");
request.setAttribute("zero","false");
try
{
ctx = new InitialContext();
Object o = ctx.lookup("defaultPool");
DataSource ds = (DataSource)PortableRemoteObject.narrow(o, DataSource.class);
connection = ds.getConnection();
statement = connection.createStatement();
}
catch (SQLException e){System.out.println(e);}
catch(NamingException ne){System.out.println(ne);}
HttpSession session = request.getSession();
if(session.getAttribute("user") == null) next = "/index.jsp";
else
{
String bestilling = "*************"
bestilling+="</table></body></html>";
String modtager = "<html>"+
"<head>"+
"<meta http-equiv='Content-Language' content='dk'>"+
"<meta http-equiv='Content-Type' content='text/html; charset=ISO-8859-1'>"+
"</head>"+
"<body>"+
"<br> Bestilling afgivet af : <br>"+
"<br> "+request.getParameter("navn")+
"<br> "+request.getParameter("adresse")+
"<br> "+request.getParameter("postnr")+
"<br> "+request.getParameter("by")+"<br><br>";
if(!zero) request.setAttribute("sendt","true");
else request.setAttribute("zero","true");
try
{
sql = " SELECT FORNAVN,EFTERNAVN,ADRESSE,POSTNR,BYNAVN"+
" FROM BRUGERE"+
" WHERE BRUGERNAVN LIKE '"+session.getAttribute("user")+"'";
res = statement.executeQuery(sql); res.next();
request.setAttribute("fornavn",res.getString("fornavn"));
request.setAttribute("efternavn",res.getString("efternavn"));
request.setAttribute("adresse",res.getString("adresse"));
request.setAttribute("postnr",res.getString("postnr"));
request.setAttribute("bynavn",res.getString("bynavn"));
}
catch(SQLException se)
{
System.out.println(se);
next = "/error.jsp";
}
finally
{
try
{
statement.close();
connection.close();
ctx.close();
}
catch (SQLException e){System.out.println(e);}
catch(NamingException ne){System.out.println(ne);}
}
}
RequestDispatcher rd;
rd = getServletContext().getRequestDispatcher(next);
rd.forward(request,response);
}
public void sendMail (String to, String from, String subject, String content)
{
try
{
Authenticator authenticator = null;
Properties properties = new Properties ();
properties.put ("mail.smtp.host","localhost");
javax.mail.internet.MimeMessage msg = new javax.mail.internet.MimeMessage (
javax.mail.Session.getInstance ( properties, authenticator) );
msg.setRecipients (Message.RecipientType.TO,to);
msg.setFrom (new InternetAddress(from));
msg.setSubject(subject,"ISO-8859-1");
msg.setHeader("Content-Language","dk");
msg.setContent(content,"text/html; charset=ISO-8859-1");
Transport.send ( msg );
}
catch (Exception ex){System.out.println(ex);}
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
doPost(request,response);
}
public void destroy(){}
}
////////////////////////
public class RetrieveOrders extends HttpServlet
{
Connection connection = null;
Statement statement = null;
Context ctx = null;
public RetrieveOrders()
{
}
public void init(ServletConfig config)
throws ServletException
{
super.init(config);
System.out.println(" Servlet SecurityCheckAdmin : Initialized");
}
protected void service(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
request.setCharacterEncoding("UTF-8");
//request.setCharacterEncoding("ISO-8859-1");
HttpSession session = request.getSession(true);
if(request.getParameter("delete").equals("yes")){ //get new orders
File file = new File("orders.csv");
file.delete();
RequestDispatcher rd = getServletContext().getRequestDispatcher("/admin.jsp");
rd.forward(request, response);
}
if(request.getParameter("next").equals("new")){
Connection conn = getConnection();
try{
Statement statement = conn.createStatement();
ResultSet res = statement.executeQuery("select Count(new) as count from visitkort where new = '1'");//
res.next();
if(res.getInt(1) == 0){
RequestDispatcher rd = getServletContext().getRequestDispatcher("/noOrders.jsp");
rd.forward(request, response);
}
}catch(SQLException e){
e.printStackTrace();
System.out.println(e.toString());
}
File file = retrieveCSVOrders(conn, "SELECT * FROM visitkort WHERE new LIKE '1'");
updateOrders(conn,"UPDATE visitkort SET new = '0' WHERE new = '1'");
}
if(request.getParameter("next").equals("all")) { // get all orders
Connection conn = getConnection();
File file = retrieveCSVOrders(conn, "SELECT * FROM visitkort");
updateOrders(conn,"UPDATE visitkort SET new = '0' WHERE new = '1'");
}
RequestDispatcher rd = getServletContext().getRequestDispatcher("/orders.jsp");
rd.forward(request, response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
doPost(request, response);
}
public void destroy()
{
try
{
statement.close();
connection.close();
ctx.close();
System.out.println(" Servlet afmeld : Destroyed");
}
catch (SQLException e){System.out.println("Error destroying servlet : "+e);}
catch(NamingException ne){System.out.println("Error destroying servlet : "+ne);}
}
private Connection getConnection(){
try
{
ctx = new InitialContext();
Object o = ctx.lookup("defaultPool");
DataSource ds = (DataSource)PortableRemoteObject.narrow(o, DataSource.class);
connection = ds.getConnection();
}
catch (SQLException e){System.out.println(e);}
catch(NamingException ne){System.out.println(ne);}
catch(Exception ee){ee.printStackTrace();}
return connection;
}
protected File retrieveCSVOrders(Connection conn, String SQL)
{
String orders ="Navn;Titel;Arbejdsplads;Adresse;Postnummer;By;Telefon;Email;Tid1;Tid2;Tid3;Bestillingsdato"+"\n";
String tid1;
String tid2;
String tid3;
File file = null;
try{
file = new File("orders.csv");
FileWriter outFile = new FileWriter(file);
BufferedWriter out = new BufferedWriter(outFile);
StringBuffer buffer = new StringBuffer();
// lav CSV liner her
Statement statement = conn.createStatement();
ResultSet res = statement.executeQuery(SQL);//
if(res==null){
}
// sorts the resultset and places the various recepients into their respective groups and assigns the to an arraylist
while(res.next()){
if (res.getString(9) == null){
tid1 = "";
} else {
tid1=res.getString(9);
}
if (res.getString(10) == null){
tid2 = "";
} else {
tid2=res.getString(10);
}
if (res.getString(11) == null){
tid3 = "";
} else {
tid3=res.getString(11);
}
orders += ""+res.getString(2)+";\"rådgiver\";\""+res.getString(3)+"\";\""+res.getString(4)+"\";\""+res.getInt(5)+"\";\""+res.getString(6)+"\";\""+res.getString(7)+"\";\""+res.getString(8)+"\";\""+tid1+"\";\""+tid2+"\";\""+tid3+"\";\""+res.getString(12)+"\""+"\n";
}
buffer.append(orders);
buffer.append(System.getProperty("line.separator"));
out.write(buffer.toString());
buffer = null;
out.flush();
out.close();
outFile.close();
}catch(SQLException e){
e.printStackTrace();
System.out.println(e.toString());
}catch(IOException ee){
System.out.println(ee.toString());
ee.printStackTrace();
}
return file;
}
private void updateOrders(Connection conn, String SQL){
try{
Statement statement = conn.createStatement();
statement.executeUpdate(SQL);
conn.close();
}catch(SQLException e){
e.printStackTrace();
System.out.println(e.toString());
}
}
}
