Executing native SQL under SSH causes the session to be disabled

Because hibernate's gethibernatetemplate () can only support hql queries, other methods are required when querying through Native SQL, therefore, when native SQL is executed, spring cannot manage sessions in some cases. Therefore, the connection pool overflows if the session is disabled!

Project uses the proxool database connection pool

1. Execute the callback function using the execute method of gethibernatetemplate () to execute native SQL

CodeExample:

List resultlist = (listdomainthis.gethibernatetemplate(cmd.exe cute (
New hibernatecallback (){
Public object doinhibernate (session) throws hibernateexception, sqlexception {
Query queryobject = session. createsqlquery (querysqlstring );

If (Params! = NULL ){
For (INT I = 0; I <Params. length; I ++ ){
Queryobject. setparameter (I, Params [I]);
}
}
Return queryobject. List ();
}
});

Conclusion:Gethibernatetemplate () is managed through spring, soGethibernatetemplate ()After the operation is completed, spring Automatically releases the connection resource.

 

2. Use the createsqlquery method returned by getsession () to perform native SQL queries.

Sample Code:List resultlist =Getsession (). createsqlquery (SQL );

 

Conclusion: Spring generally does not perform activity management on the objects obtained by getsession (), which is equivalent to SQL operations performed directly on the underlying connection, but does not require manual commit, but the connection object will not be released, and the execution of the Loop will consume all connections in the connection pool instantly.

Solution: If you need to use the second method but do not want to manually release the connection, you can add transaction management to the DaO caller service of the current method, data is automatically submitted and the connection object is released after the method ends !!!