Here I have the problem in connection issues.
I have a web service deployed in Tomcat 7. In the web service, I am using Filter and in the init(), method I am calling connection.
public static Connection connection = null;
@Override
public void init(FilterConfig config) throws ServletException {
connection = ConnectionFactoryJndi.getConnection();
}
Now I am using the same connection object throughout the application. Thus in this way I am creating only one connection and executing sql query. It is running fine. But when I am accessing the service url; it is giving error "unable to connect; no operation after connection closed" I am also set maxWait="28800000" that is 8 hours; maximum duration. My question is how to keep it open throughout. or when I accessed the url it should not give the same error and able to connect automatically. I am using MySql.
Here is the server.xml conf -
<Resource
name="jdbc/xxxxx"
auth="Container"
type="javax.sql.DataSource"
maxActive="200"
maxIdle="30"
maxWait="28800000"
username="xxx"
password="xxxxx"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost/xxxxx" />
In the class ConnectionFactoryJndi class -
public Connection createConnection() {
Connection connection = null;
try {
try {
envContext = new InitialContext();
} catch (NamingException e) {
}
Context initContext = null;
try {
initContext = (Context) envContext.lookup("java:/comp/env");
} catch (NamingException e) {
}
DataSource datasource = null;
try {
datasource = (DataSource) initContext.lookup("jdbc/xxxxxx");
} catch (NamingException e) {
System.out.println("Name not found");
}
connection = datasource.getConnection();
} catch (SQLException e) {
System.out.println("ERROR: Unable to Connect to Database.");
}
return connection;
}
Please suggest best practices.
Thanks.
Server Log -
Dec 05, 2013 10:09:44 AM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet [ServletAdaptor] in context with path [/IntellixWebApi] threw exception
java.lang.NullPointerException
at com.astroved.intellix.jdbc.dao.ClientDAO.getClientDetails(ClientDAO.java:44)
at com.astroved.intellix.security.ValidateClient.isValidClient(ValidateClient.java:14)
at com.astroved.intellix.security.SecurityFilter.doFilter(SecurityFilter.java:110)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1023)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:744)
However, It is coming due to connection failures; then not able to execute query.
ClientDao class -
ResultSet rs = null;
try {
try {
statement = connection.createStatement();
} catch (SQLException e) {
log.error("Error in Client DAO class (unable to connect connection) "
+ e.getMessage());
// e.printStackTrace();
}
try {
rs = statement.executeQuery(query);
} catch (SQLException e) {
log.error("Error in Client DAO class (unable to Execute Query ) "
+ e.getMessage());
// e.printStackTrace();
}
try {
while (rs.next()) {
client = new ClientTO();
client.setClientId(rs.getInt("clientid"));
client.setClientUrl(rs.getString("clienturl"));
client.setClientSecurityKey(rs
.getString("clientserviceapiKey"));
clientList.add(client);
}
} catch (SQLException e) {
log.error("Error in Client DAO class ( Error in iteration ) "
+ e.getMessage());
// e.printStackTrace();
}
} finally {
DbUtil.close(rs);
DbUtil.close(statement);
//DbUtil.close(connection);
}
private Connection connection = SecurityFilter.connection;
Server.xml -
<Resource
name="jdbc/xxxxx"
auth="Container"
type="javax.sql.DataSource"
maxActive="200"
maxIdle="30"
maxWait="28800000"
username="xxxxxx"
password="xxxxxx"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost/xxxxxx" />
</GlobalNamingResources>
Client DAO class-
ResultSet rs = null;
try {
try {
connection = ConnectionFactoryJndi.getConnection();
statement = connection.createStatement();
rs = statement.executeQuery(query);
} catch (SQLException e) {
log.error("Error in Client DnaReportDao class (unable to Execute Query ) "+ e.getMessage());
}
try {
if(rs != null) {
while (rs.next()) {
codeValue = new DnaReportTO();
codeValue.setNature(rs.getString("Nature"));
codeValue.setInterestedSubject(rs.getString("InterestdSubject"));
codeValue.setTipToParent(rs.getString("TipsToParent"));
}
}
} catch (SQLException e) {
log.error("Error in DnaReportDao class ( Error in iteration ) "
+ e.getMessage());
}
} finally {
DbUtil.close(rs);
DbUtil.close(statement);
}
Currenr Server Log
Dec 05, 2013 12:22:22 PM com.sun.jersey.spi.container.ContainerResponse logException
SEVERE: Mapped exception to response: 500 (Internal Server Error)
javax.ws.rs.WebApplicationException: com.sun.jersey.api.MessageException: A message body writer for Java class com.astroved.intellix.resource.DnaReportResource, and Java type class com.astroved.intellix.resource.DnaReportResource, and MIME media type application/xml was not found
at com.sun.jersey.spi.container.ContainerResponse.write(ContainerResponse.java:285)
at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1448)
at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1360)
at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1350)
at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:416)
at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:538)
at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:716)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at com.astroved.intellix.security.SecurityFilter.doFilter(SecurityFilter.java:112)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1023)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:744)
Caused by: com.sun.jersey.api.MessageException: A message body writer for Java class com.astroved.intellix.resource.DnaReportResource, and Java type class com.astroved.intellix.resource.DnaReportResource, and MIME media type application/xml was not found
... 27 more
ERROR: Unable to Connect to Database.
ERROR: Unable to Connect to Database.
ERROR: Unable to Connect to Database.
ERROR: Unable to Connect to Database.
ERROR: Unable to Connect to Database.
ERROR: Unable to Connect to Database.
ERROR: Unable to Connect to Database.
ERROR: Unable to Connect to Database.
ERROR: Unable to Connect to Database.
ERROR: Unable to Connect to Database.
ERROR: Unable to Connect to Database.
ERROR: Unable to Connect to Database.
from the error that you have given here it seems that the connection is bring closed by the application. the error no operation after connection closed could really mean that something in your application is closing it. Without the logs it would be difficult to diagnose whats causing the problem.
The correct way to use a connection pool is to borrow a connection every time you need it. Never store a reference to the connection in you own class as a singleton or a static field. This is because the pool may recycle the connection after sometime and you will end up with a valid connection object which is shutdown. This may be the reason of your problems too. The right way to do it is as follows:
ResultSet rs = null;
try {
try {
connection = ConnectionFactoryJndi.getConnection(); //always do this everytime you need a conenction.
statement = connection.createStatement();
} catch (SQLException e) {
log.error("Error in Client DAO class (unable to connect connection) "
+ e.getMessage());
// e.printStackTrace();
}
try {
if(statement != null){
rs = statement.executeQuery(query);
}else{
//log error and return or throw exception
}
} catch (SQLException e) {
log.error("Error in Client DAO class (unable to Execute Query ) "
+ e.getMessage());
// e.printStackTrace();
}
try {
if(rs != null){
while (rs.next()) {
client = new ClientTO();
client.setClientId(rs.getInt("clientid"));
client.setClientUrl(rs.getString("clienturl"));
client.setClientSecurityKey(rs
.getString("clientserviceapiKey"));
clientList.add(client);
}
}else{
//log error and return or throw exception
}
} catch (SQLException e) {
log.error("Error in Client DAO class ( Error in iteration ) "
+ e.getMessage());
// e.printStackTrace();
}
} finally {
//close rs
//close statement
//never close a conenction
}