Need suggestions on: Database connection performance confirmation.

[Kanuri Phanibabu]

Hi Friends,

Please send me your openion regarding this issue.

Actually i am using a DAOImpl class where i will get the data base connection. The code snippet for one method is like this...
And i am repeating the same procedure in each method like opening the connection and closing the connection in each method.

My problem is:

Is it right to open and closing connection in each method of DAO implementaton class?
and how can i alter the code if there exist any performance issue?

This application is a web based appliation using struts.

In the Action class i am calling each required implementation method.

public EmployeeDAOImpl(DataSource dataSource) {
this.dataSource = dataSource;
}

/**
Checks if an employee is Admin for the specified module in RMS_MODULE_ADMINS table.
**/

public boolean isAdmin(String employeeId, String moduleId) {

logger
.debug("Entered - isAdmin() of EmployeeDAOIMPL with arguments EmployeeId:"
+ employeeId + "and ModuleId:" + moduleId + ".");

Connection conn_Admin = null;
PreparedStatement pst_Admin = null;
ResultSet rs_Admin = null;

boolean isAdmin = false;

try {
conn_Admin = dataSource.getConnection();
pst_Admin = conn_Admin
.prepareStatement("SELECT ROLE_ID FROM RMS_MODULE_ADMINS "
+ "WHERE EMP_ID = ? AND MODULE_ID = ?");
pst_Admin.setString(1, employeeId.toLowerCase());
pst_Admin.setString(2, moduleId);
rs_Admin = pst_Admin.executeQuery();

if (rs_Admin.next()) {

isAdmin = true;

}
} catch (Exception e) {
logger.error("Error in isAdmin() of EmployeeDAOIMPL:"
+ e.getMessage());
} finally {
Util.closeConnection(rs_Admin, pst_Admin, conn_Admin);
logger.debug("Exiting - isAdmin() of EmployeeDAOIMPL");
}
return isAdmin;
}

[Herman Schelti]

hi Phanibabu,

About opening and closing connections: it is right to close a connection as soon as you don't need it anymore.

If you have a transaction that has 2 or more insert-methods, you will not want to open and close a new connection with every method.
You can use a framework like Spring to open the connection for you, pass the connection to the insert-methods that need and then close it.

About performance: if necessary you can ask your datasource for more connections(by configuration), so that you don't need to change any Java-code.

I think your code is OK, it would be a little faster if you would put an if(...) around the debug-logmessages.

if (logger.isDebugEnabled()){
logger.debug("Entered - isAdmin()...")
}

Herman

[Kanuri Phanibabu]

Thankyou Herman Scheltinga, for your reply and suggesions.
If you have any tips that increase the performance of the application / know any url's please suggest me.

[Herman Schelti]

hi,

here are some of my tips (all SQL/database related)


-if something is not likely to change (somebody will probably have admin rights for a long time), you can cache the result (eg in session-scope of your webapplication), so you don't have to execute the same sql with the same result everytime.

-if you think your SQL looks complicated, or if you are doing complicated stuff in your Java-code with the results: talk to somebody who knows SQL more than you do.

-ask your Database administrator if the database is tuned right for the sql you want to execute. Tuning can make a BIG difference.

-monitor your application, so that you know how long sql-statements take.
In a framework like Spring it's easy to write a class that does this.
(Aspect Oriented Programminng)

-don't overdo this performance tuning, especially if you don't have and don't expect any problems.

Herman