Hi ,

I am usin Hibernate version:
3.0 and I am facing a major problem when I am testing my application using say 60 concurrent user.The application is throwing Connection refused Connections could not be acquired from the underlying database Exception.

I have used c3p0 (Ver .9.0) for connection pooling.The underlying database is mysql 5.0 and I have set max_connections at 100 their.

when I am trying to see the process list mysql is showing around 100 threads all of which are in sleeping state .

I have set timeout to be 10 but nothing is happening.

Please go through the Mapping file & suggest me a solution





Mapping documents:

Hibernatecfg.XML
*************************************************************
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
<session-factory>
<property name="myeclipse.connection.profile">mysql</property>
<property name="connection.url">jdbc:mysql://localhost:3306/ehs?zeroDateTimeBehavior=convertToNull</property>
<property name="connection.username">root</property>
<property name="connection.password">mysql4u</property>
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="c3p0.max_size">200</property>
<property name="c3p0.min_size">10</property>
<property name="c3p0.timeout">100</property>
<property name="c3p0.acquire_increment">3</property>
<property name="c3p0.idle_test_period">300</property>
<mapping resource="com/ivistar/ehs/CPE.hbm.xml" />
<mapping resource="com/ehs/backend/oss/hbm/Radacct.hbm.xml" />
<mapping resource="com/ehs/backend/oss/hbm/OssmsAction.hbm.xml"></mapping>
<mapping resource="com/ehs/backend/oss/hbm/OssmsRole.hbm.xml"></mapping>
<mapping resource="com/ehs/backend/oss/hbm/OssrlRoleaction.hbm.xml"></mapping>
<mapping resource="com/ehs/backend/oss/hbm/OssrlMenuaction.hbm.xml" />
<mapping resource="com/ehs/backend/oss/hbm/OssmsAdminUserInfo.hbm.xml" />
<mapping resource="com/ehs/backend/oss/hbm/OssmsAdminuser.hbm.xml" />
<mapping resource="com/ehs/backend/oss/hbm/OssmsPublicuser.hbm.xml" />
<mapping resource="com/ehs/backend/oss/hotspotmanagement/hbm/OssmsHotspot.hbm.xml" />
<mapping resource="com/ehs/backend/oss/hotspotmanagement/hbm/OssmsCountry.hbm.xml" />
<mapping resource="com/ehs/backend/oss/acs/hbm/OssmsTr069.hbm.xml"></mapping>
<mapping resource="com/ehs/backend/oss/acs/hbm/OssmsUploadFirmware.hbm.xml"></mapping>
<mapping resource="com/ehs/backend/oss/hbm/Radcheck.hbm.xml"></mapping>
<mapping resource="com/ehs/backend/oss/hbm/Radreply.hbm.xml"></mapping>
<mapping resource="com/ehs/backend/oss/email/hbm/OssmsEmail.hbm.xml" />
<mapping resource="com/ehs/backend/oss/configuration/hbm/OssmsProperties.hbm.xml"></mapping>
<mapping resource="com/ehs/backend/oss/acs/hbm/OsstrxDowntime.hbm.xml" />
<mapping resource="com/ehs/backend/oss/hbm/OssmsBillPlan.hbm.xml" />
<mapping resource="com/ehs/backend/oss/hbm/Clickandbuy.hbm.xml" />
<mapping resource="com/ehs/backend/oss/hbm/OssmsServiceports.hbm.xml" />
<mapping resource="com/ehs/backend/oss/hbm/Radpostauth.hbm.xml"></mapping>


</session-factory>
</hibernate-configuration>

***********************************************************

The Related HBM Files are

1)OSSMSACTION TABLE


2)OSSMSROLE TABLE

<?xml version="1.0" encoding='UTF-8'?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<!-- DO NOT EDIT: This is a generated file that is synchronized -->
<!-- by MyEclipse Hibernate tool integration. -->
<!-- Created Mon Jan 22 11:42:29 GMT+05:30 2007 -->
<hibernate-mapping package="com.ehs.backend.oss.hbm">

<class name="OssmsRole" table="ossms_role">
<id name="roleid" column="ROLEID" type="java.lang.Integer">
<generator class="increment"/>
</id>

<property name="rolename" column="ROLENAME" type="java.lang.String" not-null="true" />
<property name="roledesc" column="ROLEDESC" type="java.lang.String" />
<property name="createdby" column="CREATEDBY" type="java.lang.String" not-null="true" />
<property name="createdon" column="CREATEDON" type="java.util.Date" />
<property name="modifiedby" column="MODIFIEDBY" type="java.lang.String" />
<property name="modifiedon" column="MODIFIEDON" type="java.util.Date" />

<set name="ossmsAdminuserSet" inverse="true">
<key column="roleid"/>
<one-to-many class="OssmsAdminuser"/>
</set>
</class>

</hibernate-mapping>


3)OSSMSROLEACTION
<?xml version="1.0" encoding='UTF-8'?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<!-- DO NOT EDIT: This is a generated file that is synchronized -->
<!-- by MyEclipse Hibernate tool integration. -->
<!-- Created Mon Jan 22 16:26:07 GMT+05:30 2007 -->
<hibernate-mapping package="com.ehs.backend.oss.hbm">

<class name="OssrlRoleaction" table="ossrl_roleaction">
<composite-id name="id" class="OssrlRoleactionKey">
<key-many-to-one name="ossmsAction" column="ACTIONID" class="OssmsAction"/>
<key-many-to-one name="ossmsRole" column="ROLEID" class="OssmsRole"/>
</composite-id>

<property name="createdby" column="CREATEDBY" type="java.lang.String" not-null="true" />
<property name="createdon" column="CREATEDON" type="java.util.Date" />
<property name="modifiedby" column="MODIFIEDBY" type="java.lang.String" />
<property name="modifiedon" column="MODIFIEDON" type="java.util.Date" />
</class>

</hibernate-mapping>

4)OSSRLMENUACTION
<?xml version="1.0" encoding='UTF-8'?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<!-- DO NOT EDIT: This is a generated file that is synchronized -->
<!-- by MyEclipse Hibernate tool integration. -->
<!-- Created Mon Jan 22 11:42:30 GMT+05:30 2007 -->
<hibernate-mapping package="com.ehs.backend.oss.hbm">

<class name="OssrlMenuaction" table="ossrl_menuaction">
<id name="menuid" column="MENUID" type="java.lang.Integer">
<generator class="increment"/>
</id>

<property name="menuname" column="MENUNAME" type="java.lang.String" not-null="true" />
<property name="url" column="URL" type="java.lang.String" />
<property name="parent" column="PARENT" type="java.lang.Integer" />

<many-to-one name="ossmsAction" column="ACTIONID" class="OssmsAction" not-null="true" />
</class>

</hibernate-mapping>

5)OSSMSADMINUSERINFO
<?xml version="1.0" encoding='UTF-8'?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<!-- DO NOT EDIT: This is a generated file that is synchronized -->
<!-- by MyEclipse Hibernate tool integration. -->
<!-- Created Fri Feb 09 19:25:45 GMT+05:30 2007 -->
<hibernate-mapping package="com.ehs.backend.oss.hbm">

<class name="OssmsAdminUserInfo" table="ossms_admin_user_info">
<id name="empcode" column="empcode" type="string">
<generator class="assigned"/>
</id>

<property name="firstname" column="firstname" type="string" />
<property name="lastname" column="lastname" type="string" />
<property name="username" column="username" type="string" />
<property name="dept" column="dept" type="string" />
<property name="mobile" column="mobile" type="string" />
<property name="phoneno" column="phoneno" type="string" />
<property name="email" column="email" type="string" />
<property name="location" column="location" type="string" />
<property name="createdby" column="createdby" type="string" />
<property name="createdon" column="createdon" type="timestamp" />
<property name="address" column="address" type="string" />

<many-to-one name="ossmsAdminuser" column="userid" class="OssmsAdminuser" not-null="true" />
</class>

</hibernate-mapping>

6)OSSMSADMINUSER

<?xml version="1.0" encoding='UTF-8'?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<!-- DO NOT EDIT: This is a generated file that is synchronized -->
<!-- by MyEclipse Hibernate tool integration. -->
<!-- Created Fri Feb 09 19:25:46 GMT+05:30 2007 -->
<hibernate-mapping package="com.ehs.backend.oss.hbm">

<class name="OssmsAdminuser" table="ossms_adminuser">
<id name="userid" column="userid" type="string">
<generator class="assigned"/>
</id>

<property name="username" column="username" type="string" />
<property name="password" column="password" type="string" />
<property name="active" column="active" type="integer" />

<many-to-one name="ossmsRole" column="roleid" class="OssmsRole" />

<set name="ossmsAdminUserInfoSet" inverse="true">
<key column="userid"/>
<one-to-many class="OssmsAdminUserInfo"/>
</set>
</class>

</hibernate-mapping>

My AUthentication uses the following code :
Code between sessionFactory.openSession() and session.close():

public UserDTO authenticate(String userName, String password)
throws EHSException {
String methodName="authenticate()";
UserDTO userRecord = new UserDTO();
Session session = null;
try {
String dbPassword="";
String dbUsername="";
password=PasswordService.getInstance().encrypt(password);
session = (Session) HibernateSessionFactory.currentSession();

Query query = session.
createQuery("select username ,password from OssmsAdminuser where username= :username and password= :password and active=1");
query.setParameter("username", userName);
query.setParameter("password", password);
Iterator it=query.iterate();
while(it.hasNext()){
Object[] dbResult=(Object[] )it.next();
dbUsername=String.valueOf(dbResult[0].toString());
dbPassword=String.valueOf(dbResult[1].toString());
}
int len = (query.list()).size();
if (len != 0&&(dbPassword.equals(password)&&dbUsername.equals(userName))) {
UserDTO userPersonalinfo = getUserPersonalRecord(userName);
UserDTO tempUser = fetchUserRecord(userName);
OssmsAdminuser user=new OssmsAdminuser();
user.setActive(tempUser.getactive());
userPersonalinfo.setRole(tempUser.getRole());
user.setUserid(tempUser.getUserId());
user.setUsername(tempUser.getUserName() );
userRecord = populateDTO(userPersonalinfo, user);
}
else
userRecord=null;

} catch (HibernateException e) {

AppLogger
.logErrorMessage(UserManagementDAO.ClassName+methodName+IConstants.HException
+ e.getMessage());

throw new EHSException(ErrorMessage.AUTHENTICATE_FAILED);
} catch (Exception e) {

AppLogger
.logErrorMessage(UserManagementDAO.ClassName+methodName+IConstants.GException
+ e.getMessage());

throw new EHSException(ErrorMessage.AUTHENTICATE_FAILED);
} finally {
AppLogger
.logDebugMessage(UserManagementDAO.ClassName+methodName+" method Ends");
session.close();
}

return userRecord;
}


public UserDTO getUserPersonalRecord(String username) throws EHSException {
String methodName="getUserPersonalRecord(String username)";
OssmsAdminUserInfo user= new OssmsAdminUserInfo();
OssmsAdminuser activeStatus=new OssmsAdminuser();
UserDTO dto=new UserDTO();
Session session = null;
try {
session = (Session) HibernateSessionFactory.currentSession();
Transaction tx = session.beginTransaction();
Query query = session
.createQuery("from OssmsAdminUserInfo where username='"+username+"'");
Query query1 = session
.createQuery("from OssmsAdminuser where username='"+username+"'");
// List l = query.list();
//List l2= query1.list();

Iterator userInfo=query.iterate();
Iterator isActive=query1.iterate();

//userDTO = (OssmsAdminUserInfo) l.get(0);
if(userInfo.hasNext()){
user = (OssmsAdminUserInfo)userInfo.next();
dto.setAddress(user.getAddress());
dto.setFirstName(user.getFirstname());
dto.setLastName(user.getLastname());
dto.setEmpCode(user.getEmpcode());
dto.setDept(user.getDept());
dto.setEmail(user.getEmail());
dto.setMobileNo(user.getMobile());
dto.setPhoneNo(user.getPhoneno());
dto.setLocation(user.getLocation());
dto.setCrtdBy(user.getCreatedby());
}
if(isActive.hasNext()){
activeStatus=(OssmsAdminuser)isActive.next();
dto.setActive(activeStatus.getActive());
dto.setRole(activeStatus.getOssmsRole().getRoleid());

}

tx.commit();

} catch (HibernateException e) {
e.printStackTrace();
AppLogger
.logErrorMessage(UserManagementDAO.ClassName+methodName+IConstants.HException
+ e.getMessage());

throw new EHSException(ErrorMessage.FETCH_USER_FAILED);
} catch (ClassCastException e) {
e.printStackTrace();
AppLogger
.logErrorMessage(UserManagementDAO.ClassName+methodName+IConstants.GException
+ e.getMessage());

throw new EHSException(ErrorMessage.FETCH_USER_FAILED);
} finally {
AppLogger
.logDebugMessage(UserManagementDAO.ClassName+methodName+" method Ends");
session.close();
}
return dto;

}

private UserDTO fetchUserRecord(String str) throws EHSException {
String methodName="fetchUserRecord(String str)";
OssmsAdminuser user = new OssmsAdminuser();
UserDTO userDto=new UserDTO();
Session session = null;
try {

session = (Session) HibernateSessionFactory.currentSession();
Transaction tx = session.beginTransaction();
Query query = session
.createQuery("from OssmsAdminuser where username=:username");
query.setParameter("username", str);
Iterator userData = query.iterate();

while (userData.hasNext()) {
user = (OssmsAdminuser) userData.next();
}

tx.commit();
userDto.setUserId(user.getUserid());
userDto.setUserName(user.getUsername());
userDto.setRole(user.getOssmsRole().getRoleid());
userDto.setActive(user.getActive());
userDto.setPassword(user.getPassword());
return userDto;
} catch (HibernateException e) {
e.printStackTrace();
AppLogger
.logErrorMessage(UserManagementDAO.ClassName+methodName+IConstants.HException
+ e.getMessage());

throw new EHSException(ErrorMessage.GET_USER_FAILED);
} catch (Exception e) {
e.printStackTrace();
AppLogger
.logErrorMessage(UserManagementDAO.ClassName+methodName+IConstants.GException
+ e.getMessage());

throw new EHSException(ErrorMessage.GET_USER_FAILED);
} finally {
AppLogger
.logDebugMessage(UserManagementDAO.ClassName+methodName+" method Ends");
session.close();
}

}

private UserDTO populateDTO(UserDTO userPersonalinfo, OssmsAdminuser user) {
UserDTO userRecord = new UserDTO();
userRecord.setAddress(userPersonalinfo.getAddress());
userRecord.setDept(userPersonalinfo.getDept());
userRecord.setEmail(userPersonalinfo.getEmail());
userRecord.setEmpCode(userPersonalinfo.getEmpCode().toString());
userRecord.setFirstName(userPersonalinfo.getFirstName());
userRecord.setLastName(userPersonalinfo.getLastName());
userRecord.setLocation(userPersonalinfo.getLocation());
userRecord.setMobileNo(userPersonalinfo.getMobileNo());
userRecord.setPhoneNo(userPersonalinfo.getPhoneNo());
userRecord.setRole(userPersonalinfo.getRole());
userRecord.setUserId(user.getUserid());
userRecord.setUserName(user.getUsername());
return userRecord;
}




[