java.sql.SQLException: Io exception: Connection reset by peer: socket write error

Question

I am using oracle 11g,hibernate 3 and jsf2.I deployed my application on was7.Every thing is going well but when i try to login after 5-6 hours it is gives me error

ERROR org.hibernate.util.JDBCExceptionReporter - Io exception: Connection reset by peer: socket write error
[5/28/13 11:31:25:048 IST] 00000024 SystemErr     R org.hibernate.exception.GenericJDBCException: could not execute query
    at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.loader.Loader.doList(Loader.java:2231)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
    at org.hibernate.loader.Loader.list(Loader.java:2120)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
    at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:361)
    at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1148)
    at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)

I unable to find out how this error to solve.Please help me.Thanks in advance.

Now i have solved this problem by code but i don'nt know is it proper way or not.will you suggest me, i should continue with this solution on not.

public class ApplicationUtilityBean implements Serializable {
private SessionFactory sessionFactory;
private AnnotationConfiguration cfg;
public String filePath;
private String realPath = Config.PATH;

public ApplicationUtilityBean() throws URISyntaxException {
    getConnection();
}

public void getConnection() {
    URL r = this.getClass().getClassLoader().getResource("hibernate.cfg.xml");
            cfg = new AnnotationConfiguration();
    cfg.configure(r);
    String pwd = cfg.getProperty("hibernate.connection.password");
    TripleDESEncryption tripledesenc = null;
    try {
        tripledesenc = new TripleDESEncryption();
    } catch (Exception e) {
        e.printStackTrace();
    }
    cfg.setProperty("hibernate.connection.password",
            tripledesenc.decrypt(pwd));
    sessionFactory = cfg.buildSessionFactory();

    System.out.println("cfg: " + cfg);
    System.out.println("sessionFactory: " + sessionFactory);
}

public Session getSession() {
    System.out.println("Going to get session");
    Session session = null;
    try {
        System.out.println("cfg is: " + cfg);
        System.out.println("sessionFactory: " + sessionFactory);
        session = sessionFactory.openSession();
        if(session != null){
            try {
                Transaction trxn = session.beginTransaction();
                Query queryResult = session.createSQLQuery("select * from dual");
                List<GTS_USER>listgtsuser = queryResult.list();     
                                } catch (Exception e) {                 
                e.printStackTrace();
                System.out.println("Creating new connection............");
                session.close();
                sessionFactory.close();
                getConnection();
                session = sessionFactory.openSession();
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return session;
}

}

and my hibernate config file is

<hibernate-configuration>
<session-factory>

    <property name="hibernate.cache.region.factory_class">net.sf.ehcache.hibernate.EhCacheRegionFactory</property>
    <property name="hibernate.cache.use_query_cache">true</property>
    <property name="hibernate.cache.use_second_level_cache">true</property>

    <property name="hibernate.connection.driver_class">oracle.jdbc.OracleDriver</property>
    <property name="hibernate.connection.url">jdbc:oracle:thin:@xxxxxxxxx:1521:HMS</property>
    <property name="hibernate.connection.username">xxxxx</property>
    <property name="hibernate.connection.password">xxxxxxxxxxx</property>
    <property name="hibernate.connection.pool_size">10</property> 
    <property name="show_sql">true</property>
    <property name="dialect">org.hibernate.dialect.OracleDialect</property>
    <property name="hibernate.hbm2ddl.auto">update</property>

Answer 1

You are probably facing a database connection timeout. On every databases there is a timeout when a connection is open and there is no activity for a certain period. You need a connection pool manager.

If you install c3p0 and configure it correctly it will allow hibernate to keep your connection alive and/or re-open it when you need it.

Here is an example for MySQL and Hibernate indeed but it is the same. You have to include c3p0.jar and also add this to your hibernade configuration file :

<property name="c3p0.min_size">5</property>
<property name="c3p0.max_size">20</property>
<property name="c3p0.timeout">1800</property>
<property name="c3p0.max_statements">50</property>
<property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>

Make sure to configure c3p0.timeout according to your database!

Answer 2

The issue here is that an idle connection will be closed by the database server. The application doesn't come to know about it and tries to use a stale connection (from the pool that was created during initialization). The fix for this is to configure the connection pool to do a test for liveness (usually by firing a simple select query) before using a connection from the pool.

How to do this varies on how you have setup the datasource / connection pooling. If you provide more details on it I can provide more specific instructions.