aspose file tools*
The moose likes JDBC and the fly likes MySQLNonTransientConnectionException Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "MySQLNonTransientConnectionException" Watch "MySQLNonTransientConnectionException" New topic
Author

MySQLNonTransientConnectionException

Amruta Jegarkal
Greenhorn

Joined: Nov 29, 2012
Posts: 29
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

Joined: Oct 27, 2005
Posts: 19760
    
  20

You're still doing something after closing the connection. Can you show us the code that includes the closing?


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Amruta Jegarkal
Greenhorn

Joined: Nov 29, 2012
Posts: 29
This is my code....Here ConnectionUtil is a Singleton class.

Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19760
    
  20

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

Joined: Nov 29, 2012
Posts: 29
Thank you
Amruta Jegarkal
Greenhorn

Joined: Nov 29, 2012
Posts: 29
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

Joined: Nov 29, 2012
Posts: 29
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();
}
}*/
}
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: MySQLNonTransientConnectionException