We're using hibernate 3 and c3p0-0.9.1. We've an Hibernate criteria based query to check the keyed in user name and password from database tables as well as other queries. During execution of first query i.e the login page, we're facing the connecion reset issue with SQLState as null. If provide the details again in the page, everything is working on fine for some time. If we try after some time, again the same problem for the first time and when re-enter the user name and password, it is working fine for sometime. Please find the below error messages for reference.

00:12:42 WARN [org.hibernate.util.JDBCExceptionReporter:77 logExceptions] - SQL Error: 17002, SQLState: null 00:12:42 ERROR [org.hibernate.util.JDBCExceptionReporter:78 logExceptions] - Io exception: Connection reset

Here is the sample code...

public UserSample fetchUserDetails(final String loginId) throws DataAccessException {    
       return (UserSample) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session)
                    throws HibernateException, SQLException {

                Criteria criteria = getSession().createCriteria(UserSample.class)
                    .createAlias("rolesample", "rolesample")
                    .createAlias("accountsample", "accountsample");
                try {
                    if(loginId!= null){
                        criteria.add(Restrictions.eq("loginName",loginId));
                    }

            criteria.setProjection(Projections.projectionList()
                .add(Projections.property("loginName").as("loginName2"))
                .add(Projections.property("password").as("password"))
                .add(Projections.property("name").as("name"))
                .add(Projections.property("roleId").as("roleId"))               );

                criteria.addOrder(Order.desc("loginName"));
                criteria.setFetchSize(1);
                criteria.setMaxResults(1);
                criteria.setResultTransformer(Transformers.aliasToBean(UserSample.class));

                ArrayList list = (ArrayList)criteria.list();
                if(list != null && list.size() > 0) {
                    return  (User)criteria.list().get(0);
                }else{
                    return new User();
                }
            }
                catch (Exception e) {
                    LogUtil.info(e.getMessage());
                    return null;
                }
            }
        }); 
   }

The connection pooling in applicationContext.xml is as below:

        

        
            
                org.hibernate.dialect.OracleDialect
                true 1, false 0, yes 'Y', no 'N'
                50
                false                
                org.hibernate.connection.C3P0ConnectionProvider                

                
                3
                50
                1800
                100
                600
                1
                SELECT 1 FROM DUAL
                        
        

When I executed the generated query in oracle client such as SQL deverloper, able to get the result. But, the when run through the application, facing this issue randomly. Can you please assist? Thanks.

Best Regards, Lucky