• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

MySQLNonTransientConnectionException

 
Amruta Jegarkal
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Connection is an instance variable in my class.Im initialising it in methods where im using it and closing it in a finally block of those methods.But i'm getting this below exception,though im initialising it in methods locally and closing it and declaration i ve done to class level.Reason for this?

Exception Description:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed
 
Rob Spoor
Sheriff
Pie
Posts: 20611
63
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're still doing something after closing the connection. Can you show us the code that includes the closing?
 
Amruta Jegarkal
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is my code....Here ConnectionUtil is a Singleton class.

 
Rob Spoor
Sheriff
Pie
Posts: 20611
63
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't see any database code after the closing, so I can't really explain this error message.

However, you create a new connection (and close it again) for every method call. While that can be inefficient if you call these methods often you should at least consider making the Connection and Statement local variables instead of instance fields, as they are only used inside the methods and each method has the pattern of "open - use - close".

Some other improvements:
1) Close the ResultSet after line 132. If you don't get an exception before that point you only close the second ResultSet.
2) Use PreparedStatement instead of Statement. Your code is vulnerable for SQL injection.
3) Use database pooling. That way your code looks as if it uses a new connection for each method call but in reality it can share existing connections.
 
Amruta Jegarkal
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you
 
Amruta Jegarkal
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have come to know where is the problem but how to resolve i dnt know.........
Connection is an instance variable in my ConnectionUtil class and ConnectionUtil is SingleTon class and im initialising connection in constructor of ConnectionUtil....
In DAO layer once when i close connection in finally block of any method then it is closed permanently....As constructor of ConnectionUtil is executed only once..


So how to resolve this.
 
Amruta Jegarkal
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
My Util class..

package com.simpragma.assignment.multithreading.messagehandler.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.log4j.Logger;

import com.simpragma.assignment.multithreading.messagehandler.constants.MessageHandlerConstants;
import com.simpragma.assignment.multithreading.messagehandler.customexception.MessageHandlerException;

/**
* Name ConnectionUtil.java
*
* @author Amruta <br/>
* <b>Purpose </b> This class is designed based on Single ton design
* pattern.
*
******************************************************************************
* Audit Trails.</br> <br/>
* who when version comment</br>
* Amruta 20th December 2012 1.0 Base version created
******************************************************************************
*/

public class ConnectionUtil {
// A static logger variable so that it references the Logger instance named
// "EventManagerService.class"
private static Logger logger = Logger.getLogger(ConnectionUtil.class);

//Instance variables
static private ConnectionUtil connectionUtil=null;
private static Connection connection = null;
private static Properties properties=null;

//getter method for connection
public static Connection getConnection() {

return connection;
}


private ConnectionUtil() throws MessageHandlerException {
logger.debug("Entering constructor");
properties = new Properties();
properties.setProperty("driverClass", "com.mysql.jdbc.Driver");
properties.setProperty("uri", "jdbc:mysql://localhost:3306/SIMPRAGMA");
properties.setProperty("username", "root");
properties.setProperty("password", "123");

try {
properties.store(new FileOutputStream("/home/harshal/Amruta/" +
"Simpragma/Multithreading/MessageHandler/src/configfiles" +
"/config.properties"),null);
properties.load(new FileInputStream("/home/harshal/Amruta/Simpragma/"
+ "Multithreading/MessageHandler/src/configfiles/" +
"config.properties"));
// Loading driver class
Class.forName(properties.getProperty("driverClass"));
// Establishing connection

connection = DriverManager.getConnection(
properties.getProperty("uri"),
properties.getProperty("username"),
properties.getProperty("password"));

} catch (FileNotFoundException e) {
logger.error("Error Description",e);
MessageHandlerException exception = new MessageHandlerException();
exception.setErrorKey(MessageHandlerConstants.FILE_NOT_FOUND_KEY);
throw exception;
} catch (IOException e) {
logger.error("Error Description",e);
MessageHandlerException exception = new MessageHandlerException();
exception.setErrorKey(MessageHandlerConstants.IO_FAILURE_KEY);
throw exception;
} catch (ClassNotFoundException e) {
logger.error("Error Description",e);
MessageHandlerException exception = new MessageHandlerException();
exception.setErrorKey(MessageHandlerConstants.CLASS_NOT_FOUND_KEY);
throw exception;
} catch (SQLException e) {
logger.error("Error Description",e);
MessageHandlerException exception = new MessageHandlerException();
exception.setErrorKey(MessageHandlerConstants.SQL_ERROR_KEY);
throw exception;
}
logger.debug("Exiting constructor");
}

/**
* Instantiates ConnectionUtil class
*
* @return reference of ConnectionUtil class
* @throws MessageHandlerException
*/

public static ConnectionUtil getConnectionUtil() throws MessageHandlerException
{
logger.debug("Entering getConnectionUtil()");
if(connectionUtil==null)
{
connectionUtil=new ConnectionUtil();
}
logger.debug("Exiting getConnectionUtil()");
return connectionUtil;
}



/**
* Used to close object level resources.
*//*
@Override
protected void finalize() throws Throwable {
if(connection!=null)
{
connection.close();
}
}*/
}
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic