connection pooling: where have I gone wrong?
Hi all, I really can't see where I've gone wrong setting up connection pooling. Can anyone show me what I need to do to fix this?
I am getting a NullPointerException at this line:
statement = connection.createStatement();
in web.xml:
<listener>
<listener-class>DBCPoolingListener</listener-class>
</listener>
<!-- This component has a dependency on an external resource-->
<resource-ref>
<description>DB Connection Pooling</description>
<res-ref-name>jdbc/TestDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
<servlet>
<servlet-name>Login</servlet-name>
<servlet-class>Login</servlet-class>
</servlet>
in server.xml:
<Context path="/testconn" docBase="testconn" debug="5"
reloadable="true" crossContext="true">
<Resource name="jdbc/TestDB" auth="Container"
type="javax.sql.DataSource" removeAbandoned="true"
removeAbandonedTimeout="30" maxActive="100"
maxIdle="30" maxWait="10000" username="username"
password="password"
driverClassName="oracle.jdbc.driver.OracleDriver"
url="WorkingDbUrl"/>
</Context>
<!-- Configuring the request and response endpoints -->
<Connector port="80" maxHttpHeaderSize="8192"maxProcessors="150"
maxThreads="150" minSpareThreads="25" maxSpareThreads="75"
enableLookups="false" redirectPort="8443" acceptCount="150"
connectionTimeout="20000" disableUploadTimeout="true" />
code that uses the db connection:
import java.sql.*;
import java.util.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
publicclass Loginextends HttpServlet{
protectedvoid doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException{
String sql="SELECT * FROM users";
Connection connection = DBCPoolingListener.getConnection();
Statement statement =null;
try{
statement = connection.createStatement();
// more code
code that creates the connection:
import javax.servlet.*;
import javax.servlet.http.*;
import javax.sql.DataSource;
import java.sql.*;
import javax.naming.*;
publicclass DBCPoolingListenerimplements ServletContextListener{
InitialContext context =null;
Context envContext =null;
DataSource ds =null;
static Connection connection;
publicvoid contextInitialized(ServletContextEvent sce){
try{
context =new InitialContext();
envContext = (Context) context.lookup("java:comp/env");
ds = (DataSource) envContext.lookup("jdbc/TestDB");
connection = ds.getConnection();
}catch(NamingException ne){
// log the naming exception
}catch(SQLException sqle){
// handle the SLQ exception (log)
}
}
publicstatic Connection getConnection(){
return connection;
}
publicvoid contextDestroyed(ServletContextEvent sce){
}
}
